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

我正在编写一个 shell 脚本(将成为一个骗子) ，它将:

1: 转储我的生产数据库

2: 将转储导入到我的开发数据库中

在步骤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
319425 次浏览
小开

虽然下面这行代码来自一个 Windows 批处理脚本，但是这个命令应该非常类似:

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

此命令用于通过实际删除整个数据库来清除数据库。命令中的 $DATABASE(在 Windows 中应该是 %DATABASE%)是一个计算结果为数据库名称的 Windows 样式环境变量。你需要用你的 development_db_name代替它。

小开
最佳答案

我只需要删除数据库，然后重新创建它:

$ dropdb development_db_name
$ createdb development_db_name

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

小开

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

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

上面两个应该是这样的:

pg_dump -U username --clean | pg_restore -U username
小开

我用过:

pg_restore -c -d database_name filename.dump
小开

倾倒:

pg_dump -Fc mydb > db.dump

恢复:

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

注意: 我的答案是关于真正删除表和其他数据库对象; 对于 删除 中的所有数据 < em > ，即截断所有表，Endre Two 在一个月后提供了一个类似的执行良好(直接执行)的语句。

对于那些你不能仅仅使用 DROP SCHEMA public CASCADE;、 DROP OWNED BY current_user;或者其他什么的情况，这里有一个我写的独立的 SQL 脚本，它是事务安全的(也就是说，你可以把它放在 BEGIN;和 ROLLBACK;之间来测试它，或者 COMMIT;来实际执行它) ，并且清除“所有”数据库对象... 好吧，所有那些在我们的应用程序使用的数据库中使用的，或者我可以明智地添加的，那就是:

  • 桌子上的触发器
  • 对表的约束(FK，PK，CHECK，UNIQUE)
  • 印度语
  • VIEW(正常或成形)
  • 桌子
  • 序列
  • 例程(聚合函数、函数、过程)
  • 所有 n-default (即不是 public或 DB-Internal)模式“ we”拥有: 当作为“非数据库超级用户”运行时，该脚本非常有用; 超级用户可以删除 所有模式(但仍然显式排除了真正重要的模式)
  • 扩展(用户提供的，但我通常故意留下他们)

未删除的是(有些是故意的; 有些只是因为我在 DB 中没有示例) :

  • public模式(例如，它们中的扩展提供的内容)
  • 校对和其他地区的东西
  • 事件触发器
  • 文本搜索的东西，... (见 给你的其他东西，我可能会错过)
  • 角色或其他安全设置
  • 复合类型
  • 烤面包桌
  • FDW 和外表

对于要还原的转储与要还原到的数据库模式版本不同的情况(例如，使用 Debian dbconfig-common、 Flyway 或 Liquibase/DB-Manul) ，这是 真的。

我还有一个版本，如果有人感兴趣，它会删除“除了两个表以外的所有内容和属于它们的内容”(一个序列，手动测试，抱歉，我知道，很无聊) ; 区别很小。如果感兴趣，请联系我或 看看这个回购。

SQL

-- Copyright © 2019, 2020
--      mirabilos <t.glaser@tarent.de>
--
-- 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(jessie-backports)上进行了测试，后面的添加(由 克莱蒙 · 普雷沃斯特提供的 extensions)除外。在9.6和12.2进行了集料清除测试，在12.2进行了程序清除测试。欢迎修正错误和进一步的改进！

小开

如果希望清理名为“ example _ db”的数据库:

1)登录到另一个 db (例如“ postgres”) :

psql postgres

2)删除你的资料库:

DROP DATABASE example_db;

3)重建你的数据库:

CREATE DATABASE example_db;
小开

完全清除数据库

注意: pg _ dump & pg _ return 不能完全清除整个数据库。

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

如果使用 PostgreSQL 9.3或更高版本，可能还需要恢复默认授权。

GRANT ALL ON SCHEMA public TO <your_db_username>;
GRANT ALL ON SCHEMA public TO public;

提交答案