database Postgres：在从 bash 脚本重新创建/重新填充之前清除整个数据库

Postgres: clear entire database before re-creating / re-populating from bash script

提问by Hoff

I'm writing a shell script (will become a cronjob) that will:

我正在编写一个 shell 脚本（将成为一个 cronjob），它将：

1: dump my production database

1：转储我的生产数据库

2: import the dump into my development database

2：将转储导入我的开发数据库

Between step 1 and 2, I need to clear the development database (drop all tables?). How is this best accomplished from a shell script? So far, it looks like this:

在第 1 步和第 2 步之间，我需要清除开发数据库（删除所有表？）。如何从 shell 脚本中最好地实现这一点？到目前为止，它看起来像这样：

#!/bin/bash
time=`date '+%Y'-'%m'-'%d'`
# 1. export(dump) the current production database
pg_dump -U production_db_name > /backup/dir/backup-${time}.sql

# missing step: drop all tables from development database so it can be re-populated

# 2. load the backup into the development database
psql -U development_db_name < backup/dir/backup-${time}.sql

回答by Haes

I'd just drop the database and then re-create it. On a UNIX or Linux system, that should do it:

我只是删除数据库，然后重新创建它。在 UNIX 或 Linux 系统上，应该这样做：

$ dropdb development_db_name
$ createdb developmnent_db_name

That's how I do it, actually.

事实上，我就是这样做的。

回答by Bandi-T

If you don't actually need a backup of the database dumped onto disk in a plain-text .sql script file format, you could connect pg_dumpand pg_restoredirectly together over a pipe.

如果您实际上不需要以纯文本 .sql 脚本文件格式转储到磁盘上的数据库备份，您可以通过管道连接pg_dump并pg_restore直接连接在一起。

To drop and recreate tables, you could use the --cleancommand-line option for pg_dumpto emit SQL commands to clean (drop) database objects prior to (the commands for) creating them. (This will not drop the whole database, just each table/sequence/index/etc. before recreating them.)

要删除和重新创建表，您可以使用--clean命令行选项pg_dump来发出 SQL 命令以在（用于）创建数据库对象之前清除（删除）数据库对象。（在重新创建它们之前，这不会删除整个数据库，只会删除每个表/序列/索引/等。）

The above two would look something like this:

上面两个看起来像这样：

pg_dump -U username --clean | pg_restore -U username

回答by Frank Bollack

Although the following line is taken from a windows batch script, the command should be quite similar:

尽管以下行来自 Windows 批处理脚本，但命令应该非常相似：

psql -U username -h localhost -d postgres -c "DROP DATABASE \"$DATABASE\";"

This command is used to clear the whole database, by actually dropping it. The $DATABASE(in Windows should be %DATABASE%) in the command is a windows style environment variable that evaluates to the database name. You will need to substitute that by your development_db_name.

此命令用于通过实际删除整个数据库来清除它。命令中的$DATABASE（在 Windows 中应该是%DATABASE%）是一个 Windows 风格的环境变量，其计算结果为数据库名称。你需要用你的development_db_name.

回答by Carlos Júlio

To dump:

转储：

pg_dump -Fc mydb > db.dump

To restore:

恢复：

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d my_db db/latest.dump

回答by Troy

I've used:

我用过：

pg_restore -c -d database_name filename.dump

回答by Kamil Siwek

If you want to clean your database named "example_db":

如果要清理名为“example_db”的数据库：

1) Login to another db(for example 'postgres'):

1）登录到另一个数据库（例如'postgres'）：

psql postgres

2) Remove your database:

2）删除您的数据库：

DROP DATABASE example_db;

3) Recreate your database:

3）重新创建您的数据库：

CREATE DATABASE example_db;

回答by mirabilos

Note: my answer is about really deleting the tables and other database objects; for deleting all data inthe tables, i.e. truncating all tables, Endre Both has provided a similarily well-executed (direct execution) statement a month later.

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

For the cases where you can't just DROP SCHEMA public CASCADE;, DROP OWNED BY current_user;or something, here's a stand-alone SQL script I wrote, which is transaction-safe (i.e. you can put it between BEGIN;and either ROLLBACK;to just test it out or COMMIT;to actually do the deed) and cleans up “all” database objects… well, all those used in the database our application uses or I could sensibly add, which is:

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

• triggers on tables
• constraints on tables (FK, PK, CHECK, UNIQUE)
• indicēs
• VIEWs (normal or materialised)
• tables
• sequences
• routines (aggregate functions, functions, procedures)
• all nōn-default (i.e. not publicor DB-internal) schemata “we” own: the script is useful when run as “not a database superuser”; a superuser can drop allschemata (the really important ones are still explicitly excluded, though)
• extensions (user-contributed but I normally deliberately leave them in)

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

Not dropped are (some deliberate; some only because I had no example in our DB):

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

• the publicschema (e.g. for extension-provided stuff in them)
• collations and other locale stuff
• event triggers
• text search stuff, … (see herefor other stuff I might have missed)
• roles or other security settings
• composite types
• toast tables
• FDW and foreign tables

• 该public模式（例如用于扩展他们提供的东西）
• 排序规则和其他语言环境的东西
• 事件触发器
• 文本搜索的东西，...（见这里我可能错过的其他东西）
• 角色或其他安全设置
• 复合类型
• 吐司桌
• FDW 和外部表

This is reallyuseful for the cases when the dump you want to restore is of a different database schema version (e.g. with Debian dbconfig-common, Flyway or Liquibase/DB-Manul) than the database you want to restore it into.

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

I've also got a version which deletes “everything except two tables and what belongs to them” (a sequence, tested manually, sorry, I know, boring) in case someone is interested; the diff is small. Contact me or check this repoif interested.

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

SQL

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

Tested, except later additions (extensionscontributed by Clément Prévost), on PostgreSQL 9.6 (jessie-backports). Aggregate removal tested on 9.6 and 12.2, procedure removal tested on 12.2 as well. Bugfixes and further improvements welcome!

在 PostgreSQL 9.6 ( )上测试，除了后来的添加（extensions由Clément Prévost贡献jessie-backports）。在 9.6 和 12.2 上测试了骨料去除，也在 12.2 上测试了程序去除。欢迎修正错误和进一步改进！