主页 / dba / 问题 / 154061
Accepted
uberrebu
Asked: 2016-11-03 08:06:01 +0800 CST

删除 Postgres 数据库中的所有数据

  • 772

--data-only我已经使用and标志从生产服务器创建了一个新的数据库转储--column-inserts，因此在登台服务器上执行还原时，我只有一堆插入语句来插入数据。

pg_dump -h localhost -U adminuser --data-only --column-inserts maindb > maindb.sql

在从生产转储中恢复数据之前，如何先删除登台服务器数据库中的所有数据？

我只想删除所有数据，这样我就不必删除和创建数据库以及所有这些东西。我只想删除数据并插入新数据。

由于多种原因，我无法选择删除和​​创建数据库。我将不得不删除所有数据并仅插入，因此无论如何找到如何做到这一点，我都愿意这样做，但显然需要帮助。

我还需要自动化这个过程。将自动“从生产数据库转储数据”，然后“删除暂存数据库上的数据”，然后“将数据恢复到暂存数据库”。我只需要“删除暂存数据库上的数据”部分的帮助。

我在 PostgreSQL 9.5.2 上运行

postgresql postgresql-9.5

6 个回答

  1. Best Answer

    您不必删除数据库，删除数据库中的所有对象就足够了。这可以使用

    drop owned by adminuser

    如果您随后创建包含create table语句的 SQL 转储（因此没有选项--data-only），一切都应该没问题。

    您也可以删除--column-insertsthen，这将使导入更快。

    但是，如果您确实想删除所有内容，可以使用一点动态 SQL 来完成：

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

    这将使用单个语句截断模式中的所有表，public即使有许多连接所有表的外键约束也将起作用。如果您的表分布在多个模式中，则需要在where条件中添加它们。

    • 34

  2. pg_restore 有一个 --clean 标志（或者可能是 --create），它将在运行操作之前自动删除数据。

    优秀的文档应该对您有很大帮助...

    只是为了澄清，以防它令人困惑：

    在重新创建它们之前清理（删除）数据库对象。（除非使用 --if-exists，否则如果目标数据库中不存在任何对象，这可能会生成一些无害的错误消息。）

    这不会删除实际的数据库..只有表/视图/等。

    如果由于某种原因，删除和重新创建表是不可接受的，那么您将不得不投入更多的工作来手动创建一个脚本，该脚本data only从源数据库、问题TRUNCATE或DELETE目标数据库中创建转储，然后加载数据转储。据我所知，没有快速/巧妙的方法可以做到这一点。

    • 7 
  3. 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;

    上述查询将为数据库中的所有表生成截断查询。

    • 4

  4. 很简单。

    1. 删除数据库的级联公共架构和
    2. 在您的数据库中创建一个新的公共架构。

    您可以使用 pgAdmin 来执行此操作。

    • 2

  5. 注意：我的回答是关于真正删除表和其他数据库对象；对于删除表中的所有数据，即截断所有表，Endre Both 在一个月后提供了一个同样执行良好（直接执行）的语句。

    对于你不能的情况DROP SCHEMA public CASCADE;，DROP OWNED BY current_user;或者别的什么，这是我写的一个独立的 SQL 脚本，它是事务安全的（即你可以把它放在两者之间BEGIN;，要么ROLLBACK;只是测试它，要么COMMIT;实际做事）和清理“所有”数据库对象......好吧，我们的应用程序使用的数据库中使用的所有对象，或者我可以明智地添加，即：

    • 表上的触发器
    • 表上的约束（FK、PK CHECK、、、UNIQUE）
    • 指数
    • VIEWs（正常或物化）
    • 表
    • 序列
    • 例程（聚合函数、函数、过程）
    • “我们”拥有的所有非默认（即非public或 DB 内部）模式：该脚本在以“非数据库超级用户”身份运行时很有用；超级用户可以删除所有模式（但真正重要的仍然被明确排除）
    • 扩展（用户贡献，但我通常故意将它们留在里面）

    没有删除的是（有些是故意的；有些只是因为我在我们的数据库中没有示例）：

    • 架构（例如，public用于扩展提供的东西）
    • 排序规则和其他语言环境的东西
    • 事件触发器
    • 文本搜索的东西，......（请参阅此处了解我可能错过的其他内容）
    • 角色或其他安全设置
    • 复合类型
    • 烤面包桌
    • FDW 和外部表

    这对于您要恢复的转储与您要恢复到的数据库不同的数据库模式版本（例如，使用 Debian 、Flyway 或 Liquibase/DB-Manul）的情况非常有用。dbconfig-common

    我还有一个版本，它删除“除了两个表和属于它们的所有内容”（一个序列，手动测试，对不起，我知道，无聊）以防有人感兴趣；差异很小。如果有兴趣，请联系我或查看此 repo 。

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

    在 PostgreSQL 9.6 ( ) 上经过测试，除了后来的添加（extensions由Clément Prévostjessie-backports提供）。在 9.6 和 12.2 上测试了骨料去除，也在 12.2 上测试了过程去除。欢迎进行错误修复和进一步改进！

    • 0

  6. 另一种方法是使用 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"

    schema_migrations如果有一个表，它不会截断表。它希望您的表名不包含双引号。第一个命令生成如下脚本：

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

    第二个执行它。

    • 0

相关问题