Copying a postgres database from one server to another

Question

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.

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.

Answer 1

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

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
• Re-CREATE database mydb on server2 from a totally empty template
• Restore the copy of mydb on server1 into 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.

Answer 2

Scenario: To cop Db1 from PC1 to PC2

In PC1:

Run PgAdmin

Right CLick on the Db1 >Backup.

Give a Filename and Save.

Copy the saved file from the PC to another PC.

In PC2:

Right Click on Database> Create> Assign Filename "Db1" (same name as in PC1)

Right Click Db1> Restore>select the copied file (if file is not getting displayed, click show all files)

Done!!