postgresql 将 postgres 数据库从一台服务器复制到另一台服务器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30043392/
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
Copying a postgres database from one server to another
提问by zelinka
I have a postgres database with a number of schemas and a number of tables within each of the schemas, and the tables have a bunch of foreign key relationships with one another. All I want to do is copy everything from one server to another server, essentially creating a copy of the first server. At this point, I don't care if I have to knock either or both servers completely out of commission while I do this.
我有一个 postgres 数据库，其中包含多个模式和每个模式中的多个表，并且这些表彼此之间有一堆外键关系。我想要做的就是将所有内容从一台服务器复制到另一台服务器，实际上是创建第一台服务器的副本。在这一点上，我不在乎我是否必须在执行此操作时将其中一个或两个服务器完全停用。
I can't figure out a way to just copy everything in the first database directly into the second database. I tried pg_dump and pg_restore but the restore violated a bunch of the foreign constraints, and therefore didn't restore properly. I have read that you can do a data only restore that will eliminate all of the data, disable constraints while the data is loading, and then re-enable the constraints when the data has been loaded, but this assumes the source and the target database have the same table structure, which they do not. If there was a way to dump just the schema and just the data, I would imagine that this would work, but I have not found a way to do that either.
我想不出一种方法将第一个数据库中的所有内容直接复制到第二个数据库中。我尝试了 pg_dump 和 pg_restore 但恢复违反了一堆外部约束，因此没有正确恢复。我已经读到您可以执行仅数据恢复，这将消除所有数据，在加载数据时禁用约束，然后在加载数据后重新启用约束，但这假设源数据库和目标数据库具有相同的表结构，而它们没有。如果有一种方法可以只转储模式和数据，我会想象这会起作用，但我也没有找到一种方法来做到这一点。
回答by Craig Ringer
If you want to take a database mydbon serverand copy it to mydbon server2, completely replacing all contents of mydbon server2, dump with something like:
如果你想利用一个数据库mydb上server，并将其复制到mydb的server2，完全替代的所有内容mydb对server2喜欢的东西，转储：
pg_dump -Fc -f mydb.dump -h server1 mydb
then restore with:
然后恢复：
dropdb -h server2 mydb
createdb -h server2 -T template0 mydb
pg_restore -d mydb -h server2 mydb.dump
This will:
这会：
DROPdatabasemydbonserver2, completely and permanently destroying all data inmydbonserver2- Re-
CREATEdatabasemydbonserver2from a totally empty template - Restore the copy of
mydbonserverintoserver2
DROP数据库mydb上server2，完全和永久性销毁所有数据mydb上server2- 重新
CREATE数据库mydb上server2从一个完全空白模板 - 将
mydbon的副本恢复server到server2
Another option is to use pg_restore --cleanwithout the drop and create. That'll drop all tables then re-create them. I prefer to drop the whole DB and get a clean one instead.
另一种选择是在pg_restore --clean没有删除和创建的情况下使用。这将删除所有表，然后重新创建它们。我更喜欢放弃整个数据库并获得一个干净的数据库。
回答by Jyoti Yadav
Scenario: To cop Db1 from PC1 to PC2
场景：将 Db1 从 PC1 复制到 PC2
In PC1:
在 PC1 中：
Run PgAdmin
运行 PgAdmin
Right CLick on the Db1 >Backup.
右键单击 Db1 > 备份。
Give a Filename and Save.
给一个文件名并保存。
Copy the saved file from the PC to another PC.
将保存的文件从 PC 复制到另一台 PC。
In PC2:
在 PC2 中：
Right Click on Database> Create> Assign Filename "Db1" (same name as in PC1)
右键单击“数据库”>“创建”>“分配文件名”“Db1”（与 PC1 中的名称相同）
Right Click Db1> Restore>select the copied file (if file is not getting displayed, click show all files)
右键单击 Db1> 还原 > 选择复制的文件（如果文件未显示，请单击显示所有文件）
Done!!
完毕！！
回答by Peter pete
You can do a "custom" format backup, and then on a blank database at the new place, do a restore. That has worked for me in the past. But if you do a plain text backup, you're going to get the errors you've been getting.
您可以进行“自定义”格式备份，然后在新位置的空白数据库上进行还原。这在过去对我有用。但是如果你做一个纯文本备份，你会得到你一直得到的错误。
