7

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.

Improve this question
6
  • You probably want to use a binary dump. If that is still not working for you (it should), you can copy over the data files using "hot standby" mode. See sections 24.2 and 25 of the PostgreSQL Administrator's Guide. Commented May 5, 2015 at 2:48
  • did you try setting constraints to deferrable mode to avoid the foreign constraints violation? Commented May 5, 2015 at 2:58
  • @BadZen That's not a "binary dump", btw, that's just a copy of the whole PostgreSQL install. (I'd expect a "binary dump" to be something still done with pg_dump, writing binary instead of text-file data for COPY. It took me a while to figure out what you meant). You also don't use hot standby, that's quite separate. You want pg_basebackup -X stream, or if you're getting fancy, WAL archiving and pg_start_backup(), rsync, pg_stop_backup(). Commented May 5, 2015 at 4:51
  • 1
    @zelinka Notably missing from this question are the error messages, the exact commands you ran, the PostgreSQL version(s) in use, etc. Commented May 5, 2015 at 4:53
  • @CraigRinger - Sorry, I should have been clearer - there are two things I'm suggesting. One is the binary pg_dump with "custom" format (-c switch), the other is the file based copy. In the latter case, by the way, you don't copy the entire installation, just a subset of the data files. The citation I gave has details. Commented May 5, 2015 at 5:08

4 Answers 4

Reset to default
16

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I'm assuming server1 in the first line of code should read server
3

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!!

Improve this answer

Comments

1

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.

Improve this answer

2 Comments

I use pgAdmin UI to do these tasks.
pgAdmin has a horrible interface to backup/restore (or at least did as of last year at some point) - consider doing them by hand, perhaps.
1

This too will work

pg_dump -Fd -f dump -h serverip -U smartcare

pg_restore -d smartcare -h localhost -U postgres dump

dump and restore db

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.