Truncar todas as tabelas em um database Postgres

Eu regularmente preciso excluir todos os dados do meu database PostgreSQL antes de uma reconstrução. Como eu faria isso diretamente no SQL?

No momento, consegui criar uma instrução SQL que retorna todos os comandos que preciso executar:

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

Mas não consigo ver uma maneira de executá-las programaticamente depois de tê-las.

FrustratedWithFormsDesigner está correto, o PL / pgSQL pode fazer isso. Aqui está o script:

 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; 

Isso cria uma function armazenada (você precisa fazer isso apenas uma vez), que você pode usar depois disso:

 SELECT truncate_tables('MYUSER'); 

Cursores explícitos raramente são necessários no plpgsql. Basta usar o cursor implícito mais simples e mais rápido de um loop FOR :

Nota: Como os nomes das tabelas não são exclusivos por database, você deve qualificar os nomes das tabelas para ter certeza do esquema. Além disso, limito a function ao esquema padrão ‘public’. Adapte-se às suas necessidades, mas certifique-se de excluir os esquemas de sistema pg_* e information_schema .

Tenha muito cuidado com essas funções. Eles nuke seu database. Eu adicionei um dispositivo de segurança para crianças. Comente a linha de RAISE NOTICE e descomente EXECUTE para preparar a 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() requer o Postgres 9.1 ou posterior. Em versões mais antigas, concatene a string de consulta assim:

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

Comando único, sem loop

Como podemos TRUNCATE várias tabelas ao mesmo tempo, não precisamos de nenhum cursor ou loop:

  • Passando nomes de tabelas em uma matriz

Agregue todos os nomes de tabelas e execute uma única instrução. Mais simples, mais 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; 

Ligar:

 SELECT truncate_tables('postgres'); 

Consulta refinada

Você nem precisa de uma function. No Postgres 9.0+, você pode executar comandos dynamics em uma instrução DO . E no Postgres 9.5+ a syntax pode ser ainda mais simples:

 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$; 

Sobre a diferença entre pg_class , pg_tables e information_schema.tables :

  • Como verificar se existe uma tabela em um determinado esquema

Sobre os nomes das tabelas regclass e quoted:

  • Nome da tabela como um parâmetro de function do PostgreSQL

Para uso repetido

Pode ser mais simples e (muito) mais rápido criar um database “template” (vamos chamar de my_template ) com sua estrutura baunilha e todas as tabelas vazias. Em seguida, passe por um ciclo DROP / CREATE DATABASE :

 DROP DATABASE mydb; CREATE DATABASE mydb TEMPLATE my_template ; 

Isso é extremamente rápido , porque o Postgres copia toda a estrutura no nível do arquivo. Nenhum problema de simultaneidade ou outra sobrecarga o atrapalha.

Se eu tiver que fazer isso, vou simplesmente criar um esquema sql do database atual, em seguida, soltar e criar o database, em seguida, carregar o database com o esquema sql.

Abaixo estão as etapas envolvidas:

1) Criar um dump de esquema do database ( --schema-only )

pg_dump mydb -s > schema.sql

2) Solte o database

drop database mydb;

3) Criar database

create database mydb;

4) Esquema de Importação

psql mydb < schema.sql

Nesse caso, provavelmente seria melhor ter apenas um database vazio que você usa como modelo e, quando precisar atualizá-lo, descarte o database existente e crie um novo a partir do modelo.

Você poderia usar SQL dynamic para executar cada instrução por sua vez? Você provavelmente teria que escrever um script PL / pgSQL para fazer isso.

http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html (seção 38.5.4. Executando comandos dynamics)

Você pode fazer isso com o bash também:

 #!/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 "{}" 

Você precisará ajustar nomes de esquemas, senhas e nomes de usuários para corresponder aos seus esquemas.

Para remover os dados e preservar as estruturas de tabelas no pgAdmin, você pode fazer:

  • Clique com o botão direito do mouse em -> backup, selecione “Somente esquema”
  • Solte o database
  • Criar um novo database e nomeá-lo como o antigo
  • Clique com o botão direito do mouse no novo database -> restaurar -> selecione o backup, selecione “Apenas esquema”

Versão AUTO_INCREMENT limpeza:

 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; 

O melhor e mais limpo é:

1) Criar Esquema dump do database (–schema-only) pg_dump mydb -s> schema.sql

2) Largue o database drop database mydb;

3) Criar database criar database mydb;

4) Importar esquema psql mydb

É trabalho para mim!

Tenha um bom dia. Hiram Walker