pg_dump和pg_restore（DROP IF EXISTS）

Question

我希望有一种完全基于命令行的解决方案来测试我的数据库，通过运行终端命令将其还原到一致的状态。

我是这样转储我的数据库的：

pg_dump -F c -b -f -i -h domain.edu -p 5432 -n myschema -U me mydatabase -W -f mydump.sql

然后我想这样恢复它：

pg_restore -h domain.edu -p 5432 -U me -d mydatabase -W mydump.sql

但是它无法正常工作，因为我会收到大量类似这样的错误：

pg_restore: [archiver (db)] could not execute query: ERROR:  constraint "settings_person_id_fkey" for relation "settings" already exists
    Command was: ALTER TABLE ONLY settings
    ADD CONSTRAINT settings_learner_id_fkey FOREIGN KEY (person_id) REFERENCES pe...

基本上，只需先删除一堆东西然后再重新添加它们（DROP TABLE <whatever> IF EXISTS，类型也是如此等等）。

我如何在Postgres中实现这个过程？我不想使用 psql 控制台，只想用Linux终端。

- lollercoaster

---

“控制台”和“Linux终端”有什么区别？您需要使用pg_restore的--clean选项。 - user330315

@a_horse_with_no_name，我认为他们是指psql控制台。您在--clean选项上是正确的。 - crazymykl

@a_horse_with_no_name @crazymykl，那样做还不够。我加了--clean标志，但是当我在转储后插入行，然后使用转储来恢复数据库时，这些行仍然存在。这不是我想要的效果。 - lollercoaster

你的脚本肯定有问题。 --clean 参数在导入数据之前会 删除 所有内容。那些行不能存在。 - user330315

--clean - 这个选项仅对纯文本格式有意义。对于存档格式，您可以在调用pg_restore时指定该选项。来自文档。 - winsent

Answer 1

您可以在 pg_dump 中使用 --clean 先删除对象：

pg_dump --clean

备份

pg_dump --clean -U user database > database.sql

恢复

psql -U user -d database -f database.sql

- Cassio Seffrin

---

谢谢，其实诀窍就是使用--clean来进行导出，这样它就会像mysql导出一样表现 :) - MacK

Answer 2

如果您想将数据库恢复到一致状态，我建议在恢复转储之前删除并重新创建它：

dropdb -h domain.edu -U me mydatabase
createdb -h domain.edu -U me -T template0 mydatabase # adjust encoding/locale if necessary
pg_restore -h domain.edu -p 5432 -U me -d mydatabase -W mydump.sql

- MatheusOl

---

请注意，当数据库存在开放连接时，drop 操作会失败。虽然 Postgres 13 引入了一个强制 drop 的选项，但这在之前的版本中是不可用的。有关详细信息和解决方法，请参见 Force drop db while others may be connected。 - not2savvy

Answer 3

在恢复数据时，您可以使用--clean --if-exists选项。

pg_restore -h domain.edu -p 5432 -U me --clean --if-exists -d mydatabase -W mydump.sql

--clean使pg_restore首先删除所有对象，--if-exists防止不存在的对象导致失败。

请注意，--if-exists在pg_restore的Postgres文档中没有列为单独的选项，但在--clean选项的描述中提到了它。

-c
--clean

Clean (drop) database objects before recreating them. (Unless --if-exists is used, this might generate some harmless error messages, if any objects were not present in the destination database.)

- not2savvy

---

Tom Lane的文档提案，旨在使pg_restore的--clean和--if-exists更易理解。 - undefined