Restablecer las secuencias ID en la base de datos postgresql

Normalmente después de restablecer la base de datos las secuencias de id se pierden provocando errores al momento de escribir en la base de datos. Para corregir todos estos errores cree el siguiente script el cual se ejecuta en la base de datos con esquema publico y agrega tablas a excluir. Ademas dejo dos scripts simples con los cuales comprobar si se corrige el problema.

--Corregir secuencias de id luego de restaurar
DO
$$
DECLARE
v_table VARCHAR;
v_table_cp VARCHAR;
v_table_block VARCHAR;
v_sql_string VARCHAR;
v_exclude_table VARCHAR[] := ARRAY['spatial_ref_sys', 'geometry_columns'];
v_include_table VARCHAR[];
v_i INTEGER;
v_num INTEGER;
v_max INTEGER;
v_next INTEGER;

BEGIN
SELECT INTO v_num count(relname)
FROM pg_class c
JOIN pg_namespace nsp ON c.relnamespace = nsp.oid
WHERE c.relname <> ALL(v_exclude_table) AND
nsp.nspname = 'public'
AND relhasindex = 't';

v_include_table := ARRAY(SELECT c.relname
FROM pg_class c
JOIN pg_namespace nsp ON c.relnamespace = nsp.oid
WHERE c.relname <> ALL(v_exclude_table) AND
nsp.nspname = 'public'
AND relhasindex = 't');

FOR v_i IN 1..v_num
LOOP

BEGIN
v_table := 'SELECT MAX(id) FROM ' || v_include_table[v_i];
EXECUTE v_table INTO v_max;

-- Then run...
-- This should be higher than the last result.
v_table_cp := 'SELECT nextval(''' || v_include_table[v_i] ||'_id_seq'')'; -- || ') INTO v_next';
EXECUTE v_table_cp INTO v_next;

-- If it's not higher... run this set the sequence last to your highest id.
-- (wise to run a quick pg_dump first...)
IF (v_next < v_max) THEN

-- protect against concurrent inserts while you update the counter
v_table_block := 'LOCK TABLE '|| v_include_table[v_i] ||' IN EXCLUSIVE MODE';
EXECUTE v_table_block;
-- Update the sequence
v_sql_string := 'SELECT setval('''|| v_include_table[v_i] || '_id_seq'', COALESCE((SELECT MAX(id)+1 FROM ' || v_include_table[v_i] || '), 1), false)';

EXECUTE v_sql_string;
RAISE NOTICE 'Table %s correct sequence',v_include_table[v_i];
END IF;
EXCEPTION WHEN others THEN
RAISE NOTICE 'Table %s ignore',v_include_table[v_i];
END;

END LOOP;
END
$$
--Para validar cambie tabla por el nombre de la tabla en ambos scripts sql
ultimo id de base de datos para la tabla
--SELECT MAX(id) FROM tabla
id que se genera al insertar un nuevo registro
--SELECT nextval('tabla_id_seq')

Truntate cascade, limpiar todas las tablas de un esquema

Este es muy parecido a la entrada anterior, solo que en vez de eliminar las tablas las deja limpias.
esto lo hago para luego cargar en la base de datos un respaldo sin que alguna tabla interrumpa la carga por tener datos previos.
Para ello realizo el respado con la siguiente instrucción:
<
sudo -u postgres pg_dump -a -Fp --exclude-table=field1 --exclude-table=field2 -v basedata_name > ~/Documentos/Respaldo_DB/dbbackup.sql
>

~/ me lleva directo al /home/usuario
<
DO
$$
DECLARE
v_table VARCHAR;
v_sql_string VARCHAR;
v_exclude_table VARCHAR[] := ARRAY['spatial_ref_sys', 'geometry_columns'];
v_include_table VARCHAR[];
v_i INTEGER;
v_num INTEGER;

BEGIN
SELECT INTO v_num count(relname)
FROM pg_class c
JOIN pg_namespace nsp ON c.relnamespace = nsp.oid
WHERE c.relname <> ALL(v_exclude_table) AND 
nsp.nspname = 'public'
AND relhasindex = 't';

v_include_table := ARRAY(SELECT c.relname
FROM (
SELECT pg_constraint.confrelid, COUNT(confrelid) as total FROM pg_constraint, pg_class WHERE
pg_class.oid = pg_constraint.conindid
GROUP BY pg_constraint.confrelid
) AS rest
RIGHT JOIN pg_class c ON rest.confrelid = c.oid
JOIN pg_namespace nsp ON c.relnamespace = nsp.oid
WHERE c.relname <> ALL(v_exclude_table) AND 
nsp.nspname = 'public'
AND relhasindex = 't'
ORDER BY 
CASE WHEN rest.total IS NULL THEN 0 END, rest.total);

FOR v_i IN 1..v_num
LOOP

v_sql_string := 'TRUNCATE TABLE ' || v_include_table[v_i] || ' CASCADE';
EXECUTE v_sql_string;
RAISE NOTICE 'Table %s Truncate',v_include_table[v_i];

END LOOP;
END
$$
>

restauro la base de datos de la siguiente forma:
<
sudo -u postgres psql basedata_name < ~/dbbackup.sql > dbackup.log
>

dbackup.log contiene un log de la ejecución del comando.

Borrar grupo de tablas PostgreSQL sin que interfieran las restricciones

Estuve algunos días dando vuelta para poder eliminar un grupo de tablas exceptuando solo un grupo en especifico, un grupo muy pequeño de hecho. Conseguí mucha información pero nada lo suficientemente acertado ni practico para aplicarlo así que con esas gotas que fui consiguiendo construí esto

<

DO

$$

DECLARE

v_table VARCHAR;

v_sql_string VARCHAR;

v_exclude_table VARCHAR[] := ARRAY['field1', 'field2', 'field3'];

v_i INTEGER;

v_num INTEGER;

BEGIN

SELECT INTO v_num count(relname)

FROM pg_class c

JOIN pg_namespace nsp ON c.relnamespace = nsp.oid

WHERE c.relname <> ALL(v_exclude_table)

AND nsp.nspname = 'public'

AND relhasindex = 't';

FOR v_i IN 1..v_num

LOOP

SELECT c.relname

INTO v_table

FROM (

SELECT pg_constraint.confrelid, COUNT(confrelid) as total FROM pg_constraint, pg_class WHERE

pg_class.oid = pg_constraint.conindid

GROUP BY pg_constraint.confrelid

) AS rest

RIGHT JOIN pg_class c ON rest.confrelid = c.oid

JOIN pg_namespace nsp ON c.relnamespace = nsp.oid

WHERE c.relname <> ALL(v_exclude_table)

AND nsp.nspname = 'public'

AND relhasindex = 't'

ORDER BY 

CASE WHEN rest.total IS NULL THEN 0 END, rest.total LIMIT 1;

IF EXISTS(SELECT 1 FROM pg_class c

JOIN pg_namespace nsp ON c.relnamespace = nsp.oid

WHERE c.relname <> ALL(v_exclude_table)

AND nsp.nspname = 'public'

AND relhasindex = 't' ORDER BY c.oid desc LIMIT 1) THEN

v_sql_string := 'DROP TABLE ' || v_table;

EXECUTE v_sql_string;

RAISE NOTICE 'Table %s Deleted',v_table;

ELSE

RAISE NOTICE 'Table %s Not Deleted',v_table;

END IF;

END LOOP;

END

$$

>

Estos script SQL me permitieron buscar las tablas del esquema que me interesaba y organizarla de tal forma que las restricciones no interrumpieran su eliminación.

/*

SELECT relname

FROM pg_class c

JOIN pg_namespace nsp ON c.relnamespace = nsp.oid

WHERE c.relname <> ALL(ARRAY['field1', 'field2', 'field3'])

AND nsp.nspname = 'public'

AND relhasindex = 't';

SELECT c.relname, rest.total, c.oid

FROM (

SELECT pg_constraint.confrelid, COUNT(confrelid) as total FROM pg_constraint, pg_class WHERE

pg_class.oid = pg_constraint.conindid

GROUP BY pg_constraint.confrelid

) AS rest

RIGHT JOIN pg_class c ON rest.confrelid = c.oid

JOIN pg_namespace nsp ON c.relnamespace = nsp.oid

WHERE c.relname <> ALL(ARRAY['field1', 'field2', 'field3'])

AND nsp.nspname = 'public'

AND relhasindex = 't'

ORDER BY 

CASE WHEN rest.total IS NULL THEN 0 END, 

    rest.total;

*/

Entre tanto buscar perdí muchas de las referencias...

https://stackoverflow.com/questions/29913856/how-to-drop-a-table-based-on-if-condition-in-postgres
https://stackoverflow.com/questions/4326343/mysql-left-join-order-by-null-values-to-the-end