postgresql pg_dump 和 pg_restore（如果存在则删除）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22287914/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
pg_dump and pg_restore (DROP IF EXISTS)
提问by lollercoaster
I want a completely shell-based solution for testing my database by allowing my to restore it to a consistent state by running a terminal command.
我想要一个完全基于 shell 的解决方案来测试我的数据库，允许我通过运行终端命令将其恢复到一致状态。
I dump my database like so:
我像这样转储我的数据库：
pg_dump -F c -b -f -i -h domain.edu -p 5432 -n myschema -U me mydatabase -W -f mydump.sql
Then I want to restore it like so:
然后我想像这样恢复它：
pg_restore -h domain.edu -p 5432 -U me -d mydatabase -W mydump.sql
but it doesn't work because I get tons of errors like these:
但它不起作用，因为我收到了大量这样的错误：
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...
basically there are just a bunch of things that need to be dropped first before being re-added (DROP TABLE <whatever> IF EXISTS, same with types, etc).
基本上只有一堆东西需要在重新添加之前先删除（DROP TABLE <whatever> IF EXISTS与类型相同，等等）。
How can I do this with Postgres? I don't want to use the psqlconsole, only the linux terminal.
我怎样才能用 Postgres 做到这一点？我不想使用psql控制台，只想使用 linux 终端。
回答by MatheusOl
If you want to restore the database to a consistent state, I recommend you to drop and recreate it before restoring the dump:
如果您想将数据库恢复到一致状态，我建议您在恢复转储之前删除并重新创建它：
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
回答by Cassio Seffrin
You could use --clean in pg_dump to first drop objects.
您可以在 pg_dump 中使用 --clean 来首先删除对象。
pg_dump --clean
pg_dump --clean
backup
备份
pg_dump --clean -U user database > database.sql
pg_dump --clean -U 用户数据库> database.sql
restore
恢复
psql -U user -d database -f database.sql
psql -U 用户 -d 数据库 -f 数据库.sql
