将postgres数据库从一台服务器复制到另一台服务器

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但是恢复违反了一堆外来约束,因此无法正常恢复。我已经读过你可以进行数据恢复,它将消除所有数据,在数据加载时禁用约束,然后在加载数据时重新启用约束,但这假定源和目标数据库具有相同的表结构,但它们没有。如果有一种方法只转储模式和数据,我会想象这会起作用,但我还没有办法做到这一点。

2 个解决方案

#1

If you want to take a database mydb on server and copy it to mydb on server2, completely replacing all contents of mydb on server2, dump with something like:

如果你想在服务器上获取数据库mydb并将其复制到server2上的mydb,完全替换server2上mydb的所有内容,转储如下:

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:

DROP database mydb on server2, completely and permanently destroying all data in mydb on server2

服务器2上的DROP数据库mydb,完全永久地销毁server2上mydb中的所有数据

Re-CREATE database mydb on server2 from a totally empty template

从完全空的模板重新创建server2上的数据库mydb

Restore the copy of mydb on server into server2

将服务器上的mydb副本还原到server2

Another option is to use pg_restore --clean without 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而不使用drop并创建。这将删除所有表格,然后重新创建它们。我宁愿放弃整个数据库,而是选择一个干净的数据库。

#2

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.

您可以执行“自定义”格式备份,然后在新位置的空白数据库上执行还原。这在过去对我有用。但是如果你做一个纯文本备份,你就会得到你所得到的错误。

#1