Truncar todas las tablas en una base de datos de Postgres

Necesito eliminar regularmente todos los datos de mi base de datos PostgreSQL antes de una reconstrucción. ¿Cómo haría esto directamente en SQL?

Por el momento, he logrado encontrar una statement SQL que devuelve todos los comandos que necesito para ejecutar:

SELECT 'TRUNCATE TABLE ' || tablename || ';' FROM pg_tables WHERE tableowner='MYUSER'; 

Pero no puedo ver una forma de ejecutarlos programáticamente una vez que los tengo.

    FrustratedWithFormsDesigner es correcto, PL / pgSQL puede hacer esto. Aquí está el guión:

     CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$ DECLARE statements CURSOR FOR SELECT tablename FROM pg_tables WHERE tableowner = username AND schemaname = 'public'; BEGIN FOR stmt IN statements LOOP EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;'; END LOOP; END; $$ LANGUAGE plpgsql; 

    Esto crea una función almacenada (debe hacer esto solo una vez) que luego puede usar así:

     SELECT truncate_tables('MYUSER'); 

    Los cursores explícitos rara vez se necesitan en plpgsql. Simplemente use el cursor implícito más simple y más rápido de un bucle FOR :

    Nota: Dado que los nombres de tabla no son únicos por base de datos, tiene que esquematizar los nombres de las tablas para asegurarse. Además, limito la función al esquema predeterminado ‘público’. Adáptese a sus necesidades, pero asegúrese de excluir los esquemas del sistema pg_* y information_schema .

    Tenga mucho cuidado con estas funciones. Destruyen tu base de datos. Agregué un dispositivo de seguridad para niños. Comenta la línea de RAISE NOTICE RAIZ y descomenta el EXECUTE para preparar la bomba …

     CREATE OR REPLACE FUNCTION f_truncate_tables(_username text) RETURNS void AS $func$ DECLARE _tbl text; _sch text; BEGIN FOR _sch, _tbl IN SELECT schemaname, tablename FROM pg_tables WHERE tableowner = _username AND schemaname = 'public' LOOP RAISE NOTICE '%', -- EXECUTE -- dangerous, test before you execute! format('TRUNCATE TABLE %I.%I CASCADE', _sch, _tbl); END LOOP; END $func$ LANGUAGE plpgsql; 

    format() requiere Postgres 9.1 o posterior. En versiones anteriores, concatena la cadena de consulta de la siguiente manera:

     'TRUNCATE TABLE ' || quote_ident(_sch) || '.' || quote_ident(_tbl) || ' CASCADE'; 

    Un solo comando, sin bucle

    Como podemos TRUNCATE varias tablas a la vez, no necesitamos ningún cursor o bucle en absoluto:

    • Pasar nombres de tablas en una matriz

    Agregue todos los nombres de tablas y ejecute una sola statement. Más simple, más rápido:

     CREATE OR REPLACE FUNCTION f_truncate_tables(_username text) RETURNS void AS $func$ BEGIN RAISE NOTICE '%', -- EXECUTE -- dangerous, test before you execute! (SELECT 'TRUNCATE TABLE ' || string_agg(format('%I.%I', schemaname, tablename), ', ') || ' CASCADE' FROM pg_tables WHERE tableowner = _username AND schemaname = 'public' ); END $func$ LANGUAGE plpgsql; 

    Llamada:

     SELECT truncate_tables('postgres'); 

    Consulta refinada

    Ni siquiera necesitas una función. En Postgres 9.0+ puede ejecutar comandos dynamics en una statement DO . Y en Postgres 9.5+ la syntax puede ser incluso más simple:

     DO $func$ BEGIN RAISE NOTICE '%', -- EXECUTE (SELECT 'TRUNCATE TABLE ' || string_agg(oid::regclass::text, ', ') || ' CASCADE' FROM pg_class WHERE relkind = 'r' -- only tables AND relnamespace = 'public'::regnamespace ); END $func$; 

    Acerca de la diferencia entre pg_class , pg_tables y information_schema.tables :

    • Cómo verificar si una tabla existe en un esquema dado

    Acerca de los nombres de las tablas citadas y de los regclass :

    • Nombre de tabla como parámetro de función PostgreSQL

    Para uso repetido

    Podría ser más simple y (mucho) más rápido crear una base de datos “plantilla” (llamémosla my_template ) con su estructura my_template y todas las tablas vacías. Luego pase por un ciclo DROP / CREATE DATABASE :

     DROP DATABASE mydb; CREATE DATABASE mydb TEMPLATE my_template ; 

    Esto es extremadamente rápido , porque Postgres copia toda la estructura en el nivel de archivo. No hay problemas de concurrencia u otros gastos generales que lo ralenticen.

    Si tengo que hacer esto, simplemente crearé un esquema sql de db actual, luego soltaré y crearé db, luego cargaré db con schema sql.

    Debajo están los pasos involucrados:

    1) Crear el volcado de esquema de la base de datos ( --schema-only )

    pg_dump mydb -s > schema.sql

    2) Bajar la base de datos

    drop database mydb;

    3) Crear una base de datos

    create database mydb;

    4) Esquema de importación

    psql mydb < schema.sql

    En este caso, probablemente sea mejor tener una base de datos vacía que usar como plantilla y cuando necesite actualizar, elimine la base de datos existente y cree una nueva de la plantilla.

    ¿Podría usar SQL dynamic para ejecutar cada instrucción por turno? Probablemente tendrías que escribir un script PL / pgSQL para hacer esto.

    http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html (sección 38.5.4. Ejecución de comandos dynamics)

    Puedes hacer esto con bash también:

     #!/bin/bash PGPASSWORD='' psql -h 127.0.0.1 -Upostgres sng --tuples-only --command "SELECT 'TRUNCATE TABLE ' || schemaname || '.' || tablename || ';' FROM pg_tables WHERE schemaname in ('cms_test', 'ids_test', 'logs_test', 'sps_test');" | tr "\\n" " " | xargs -I{} psql -h 127.0.0.1 -Upostgres sng --command "{}" 

    Tendrá que ajustar los nombres de esquema, las contraseñas y los nombres de usuario para que coincidan con sus esquemas.

    Para eliminar los datos y preservar las estructuras de tabla en pgAdmin , puede hacer:

    • Haga clic con el botón derecho en la base de datos -> copia de seguridad, seleccione “Sólo esquema”
    • Suelta la base de datos
    • Crea una nueva base de datos y nómbrala como la anterior
    • Haga clic con el botón derecho en la nueva base de datos -> restaurar -> seleccione la copia de seguridad, seleccione “Solo esquema”

    Limpieza de la versión AUTO_INCREMENT :

     CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$ DECLARE statements CURSOR FOR SELECT tablename FROM pg_tables WHERE tableowner = username AND schemaname = 'public'; BEGIN FOR stmt IN statements LOOP EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;'; IF EXISTS ( SELECT column_name FROM information_schema.columns WHERE table_name=quote_ident(stmt.tablename) and column_name='id' ) THEN EXECUTE 'ALTER SEQUENCE ' || quote_ident(stmt.tablename) || '_id_seq RESTART WITH 1'; END IF; END LOOP; END; $$ LANGUAGE plpgsql; 

    Chicos, la mejor y limpia manera es:

    1) Crear el volcado de esquema de la base de datos (–schema-only) pg_dump mydb -s> schema.sql

    2) Soltar la base de datos de bases de datos mydb;

    3) Crear base de datos crear base de datos mydb;

    4) Importar esquema psql mydb

    ¡Es trabajo para mí!

    Que tengas un buen día. Hiram Walker