Excluir todos os dados no banco de dados Postgres

Você não precisa descartar o banco de dados, deve ser suficiente descartar todos os objetos no banco de dados. Isso pode ser feito usando

drop owned by adminuser

Se você criar o dump SQL incluindo as create tableinstruções (portanto, sem a --data-onlyopção), tudo ficará bem.

Você também pode remover o --column-insertsthen, o que tornará a importação muito mais rápida.

No entanto, se você deseja excluir tudo, pode fazer isso com um pouco de SQL dinâmico:

do
$$
declare
  l_stmt text;
begin
  select 'truncate ' || string_agg(format('%I.%I', schemaname, tablename), ',')
    into l_stmt
  from pg_tables
  where schemaname in ('public');

  execute l_stmt;
end;
$$

Isso truncará todas as tabelas no esquema publiccom uma única instrução que também funcionará mesmo se houver muitas restrições de chave estrangeira conectando todas as tabelas. Se suas tabelas estiverem espalhadas por vários esquemas, você precisará adicioná-las na wherecondição.

pg_restore tem um sinalizador --clean (ou possivelmente --create) que excluirá automaticamente os dados antes de executar as operações.

A Excelente Documentação deve te ajudar muito...

Só para esclarecer, caso esteja confuso:

Limpe (descarte) objetos de banco de dados antes de recriá-los. (A menos que --if-exists seja usado, isso pode gerar algumas mensagens de erro inofensivas, se algum objeto não estiver presente no banco de dados de destino.)

Isso não irá descartar o banco de dados real .. apenas as tabelas/visualizações/etc.

Se, por algum motivo, descartar e recriar as tabelas não for aceitável, você terá que trabalhar mais para criar manualmente um script que crie um data onlydump do banco de dados de origem, problemas TRUNCATEou DELETEno banco de dados de destino e, em seguida, carrega o dump de dados. Não há uma maneira rápida / esperta de fazer isso, até onde eu saiba.

SELECT 'TRUNCATE ' || input_table_name || ' CASCADE;' AS truncate_query FROM(SELECT table_schema || '.' || table_name AS input_table_name FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema') AND table_schema NOT LIKE 'pg_toast%') AS information;  

A consulta acima gerará consultas truncadas para todas as tabelas do banco de dados.

Muito simples.

1. Elimine o esquema público Cascade do seu banco de dados e
2. Crie um novo esquema público em seu banco de dados.

Você pode usar o pgAdmin para fazer isso.

Nota: minha resposta é sobre realmente deletar as tabelas e outros objetos de banco de dados; para excluir todos os dados nas tabelas, ou seja, truncando todas as tabelas , Endre Both forneceu uma instrução similarmente bem executada (execução direta) um mês depois.

Para os casos em que você não pode apenas DROP SCHEMA public CASCADE;, DROP OWNED BY current_user;ou algo assim, aqui está um script SQL autônomo que escrevi, que é seguro para transações (ou seja, você pode colocá-lo entre BEGIN;e ROLLBACK;apenas testá-lo ou COMMIT;realmente fazer a ação) e limpa “todos” os objetos de banco de dados… bem, todos aqueles usados ​​no banco de dados que nosso aplicativo usa ou eu poderia adicionar sensatamente, que é:

• gatilhos em tabelas
• restrições em tabelas (FK, PK, CHECK, UNIQUE)
• índices
• VIEWs (normal ou materializado)
• mesas
• sequências
• rotinas (funções agregadas, funções, procedimentos)
• todos os esquemas não padrão (ou seja, não publicou internos ao banco de dados) que “nós” possuímos: o script é útil quando executado como “não um superusuário de banco de dados”; um superusuário pode descartar todos os esquemas (os realmente importantes ainda são explicitamente excluídos)
• extensões (contribuídas pelo usuário, mas normalmente eu as deixo deliberadamente)

Não foram descartados (alguns deliberados; alguns apenas porque não tinha exemplo em nosso banco de dados):

• o publicesquema (por exemplo, para coisas fornecidas por extensão neles)
• agrupamentos e outras coisas de localidade
• acionadores de eventos
• coisas de pesquisa de texto, … (veja aqui outras coisas que eu posso ter perdido)
• funções ou outras configurações de segurança
• tipos compostos
• mesas de torradas
• FDW e tabelas estrangeiras

Isso é realmente útil para os casos em que o dump que você deseja restaurar é de uma versão de esquema de banco de dados diferente (por exemplo, com Debian dbconfig-common, Flyway ou Liquibase/DB-Manul) do banco de dados em que você deseja restaurá-lo.

Também tenho uma versão que apaga “tudo menos duas tabelas e o que pertence a elas” (uma sequência, testada manualmente, desculpe, eu sei, chata) caso alguém se interesse; a diferença é pequena. Entre em contato comigo ou verifique este repositório se estiver interessado.

SQL

-- Copyright © 2019, 2020
--      mirabilos <[email protected]>
--
-- Provided that these terms and disclaimer and all copyright notices
-- are retained or reproduced in an accompanying document, permission
-- is granted to deal in this work without restriction, including un‐
-- limited rights to use, publicly perform, distribute, sell, modify,
-- merge, give away, or sublicence.
--
-- This work is provided “AS IS” and WITHOUT WARRANTY of any kind, to
-- the utmost extent permitted by applicable law, neither express nor
-- implied; without malicious intent or gross negligence. In no event
-- may a licensor, author or contributor be held liable for indirect,
-- direct, other damage, loss, or other issues arising in any way out
-- of dealing in the work, even if advised of the possibility of such
-- damage or existence of a defect, except proven that it results out
-- of said person’s immediate fault when using the work as intended.
-- -
-- Drop everything from the PostgreSQL database.

DO $$
DECLARE
        q TEXT;
        r RECORD;
BEGIN
        -- triggers
        FOR r IN (SELECT pns.nspname, pc.relname, pt.tgname
                FROM pg_catalog.pg_trigger pt, pg_catalog.pg_class pc, pg_catalog.pg_namespace pns
                WHERE pns.oid=pc.relnamespace AND pc.oid=pt.tgrelid
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                    AND pt.tgisinternal=false
            ) LOOP
                EXECUTE format('DROP TRIGGER %I ON %I.%I;',
                    r.tgname, r.nspname, r.relname);
        END LOOP;
        -- constraints #1: foreign key
        FOR r IN (SELECT pns.nspname, pc.relname, pcon.conname
                FROM pg_catalog.pg_constraint pcon, pg_catalog.pg_class pc, pg_catalog.pg_namespace pns
                WHERE pns.oid=pc.relnamespace AND pc.oid=pcon.conrelid
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                    AND pcon.contype='f'
            ) LOOP
                EXECUTE format('ALTER TABLE ONLY %I.%I DROP CONSTRAINT %I;',
                    r.nspname, r.relname, r.conname);
        END LOOP;
        -- constraints #2: the rest
        FOR r IN (SELECT pns.nspname, pc.relname, pcon.conname
                FROM pg_catalog.pg_constraint pcon, pg_catalog.pg_class pc, pg_catalog.pg_namespace pns
                WHERE pns.oid=pc.relnamespace AND pc.oid=pcon.conrelid
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                    AND pcon.contype<>'f'
            ) LOOP
                EXECUTE format('ALTER TABLE ONLY %I.%I DROP CONSTRAINT %I;',
                    r.nspname, r.relname, r.conname);
        END LOOP;
        -- indicēs
        FOR r IN (SELECT pns.nspname, pc.relname
                FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pns
                WHERE pns.oid=pc.relnamespace
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                    AND pc.relkind='i'
            ) LOOP
                EXECUTE format('DROP INDEX %I.%I;',
                    r.nspname, r.relname);
        END LOOP;
        -- normal and materialised views
        FOR r IN (SELECT pns.nspname, pc.relname
                FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pns
                WHERE pns.oid=pc.relnamespace
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                    AND pc.relkind IN ('v', 'm')
            ) LOOP
                EXECUTE format('DROP VIEW %I.%I;',
                    r.nspname, r.relname);
        END LOOP;
        -- tables
        FOR r IN (SELECT pns.nspname, pc.relname
                FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pns
                WHERE pns.oid=pc.relnamespace
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                    AND pc.relkind='r'
            ) LOOP
                EXECUTE format('DROP TABLE %I.%I;',
                    r.nspname, r.relname);
        END LOOP;
        -- sequences
        FOR r IN (SELECT pns.nspname, pc.relname
                FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pns
                WHERE pns.oid=pc.relnamespace
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                    AND pc.relkind='S'
            ) LOOP
                EXECUTE format('DROP SEQUENCE %I.%I;',
                    r.nspname, r.relname);
        END LOOP;
        -- extensions (only if necessary; keep them normally)
        FOR r IN (SELECT pns.nspname, pe.extname
                FROM pg_catalog.pg_extension pe, pg_catalog.pg_namespace pns
                WHERE pns.oid=pe.extnamespace
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
            ) LOOP
                EXECUTE format('DROP EXTENSION %I;', r.extname);
        END LOOP;
        -- aggregate functions first (because they depend on other functions)
        FOR r IN (SELECT pns.nspname, pp.proname, pp.oid
                FROM pg_catalog.pg_proc pp, pg_catalog.pg_namespace pns, pg_catalog.pg_aggregate pagg
                WHERE pns.oid=pp.pronamespace
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
                    AND pagg.aggfnoid=pp.oid
            ) LOOP
                EXECUTE format('DROP AGGREGATE %I.%I(%s);',
                    r.nspname, r.proname,
                    pg_get_function_identity_arguments(r.oid));
        END LOOP;
        -- routines (functions, aggregate functions, procedures, window functions)
        IF EXISTS (SELECT * FROM pg_catalog.pg_attribute
                WHERE attrelid='pg_catalog.pg_proc'::regclass
                    AND attname='prokind' -- PostgreSQL 11+
            ) THEN
                q := 'CASE pp.prokind
                        WHEN ''p'' THEN ''PROCEDURE''
                        WHEN ''a'' THEN ''AGGREGATE''
                        ELSE ''FUNCTION''
                    END';
        ELSIF EXISTS (SELECT * FROM pg_catalog.pg_attribute
                WHERE attrelid='pg_catalog.pg_proc'::regclass
                    AND attname='proisagg' -- PostgreSQL ≤10
            ) THEN
                q := 'CASE pp.proisagg
                        WHEN true THEN ''AGGREGATE''
                        ELSE ''FUNCTION''
                    END';
        ELSE
                q := '''FUNCTION''';
        END IF;
        FOR r IN EXECUTE 'SELECT pns.nspname, pp.proname, pp.oid, ' || q || ' AS pt
                FROM pg_catalog.pg_proc pp, pg_catalog.pg_namespace pns
                WHERE pns.oid=pp.pronamespace
                    AND pns.nspname NOT IN (''information_schema'', ''pg_catalog'', ''pg_toast'')
            ' LOOP
                EXECUTE format('DROP %s %I.%I(%s);', r.pt,
                    r.nspname, r.proname,
                    pg_get_function_identity_arguments(r.oid));
        END LOOP;
        -- nōn-default schemata we own; assume to be run by a not-superuser
        FOR r IN (SELECT pns.nspname
                FROM pg_catalog.pg_namespace pns, pg_catalog.pg_roles pr
                WHERE pr.oid=pns.nspowner
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast', 'public')
                    AND pr.rolname=current_user
            ) LOOP
                EXECUTE format('DROP SCHEMA %I;', r.nspname);
        END LOOP;
        -- voilà
        RAISE NOTICE 'Database cleared!';
END; $$;

Testado, exceto adições posteriores ( extensionscontribuição de Clément Prévost ), no PostgreSQL 9.6 ( jessie-backports). Remoção de agregado testada em 9.6 e 12.2, remoção de procedimento testada em 12.2 também. Correções de bugs e melhorias são bem-vindas!

Outra maneira é usar um script de shell:

script=$(
    psql -tAc "
            SELECT tablename
            FROM pg_tables
            WHERE schemaname = 'public'
                AND tablename != 'schema_migrations'" \
        | sed -E -- 's/^/TRUNCATE TABLE "/; s/$/";/'
)
psql -c "$script" -- "$db"

Ele não trunca schema_migrationsa tabela caso haja uma. Ele espera que seus nomes de tabela não incluam aspas duplas. O primeiro comando gera um script como:

TRUNCATE TABLE "posts";
TRUNCATE TABLE "users";
...

O segundo executa.