How to copy postgres database to another server

Question

I'm looking to copy a production postgres database to a development server. What's the quickest, easiest way to go about doing this?

Answer 1

For PostgreSQL you can use either of these operations

1. replicate the database using Slony.
2. use dump and restore.

Answer 2

pg_dump the_db_name > the_backup.sql

Then copy the backup to your development server, restore with:

psql the_new_dev_db < the_backup.sql

Answer 3

Use pg_dump, and later psql or pg_restore - depending whether you choose -Fp or -Fc options to pg_dump.

Example of usage:

ssh production
pg_dump -C -Fp -f dump.sql -U postgres some_database_name
scp dump.sql development:
rm dump.sql
ssh development
psql -U postgres -f dump.sql

Answer 4

You don't need to create an itermediate file. You can do

pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname

or

pg_dump -C -h remotehost -U remoteuser dbname | psql -h localhost -U localuser dbname

Using psql or pg_dump to connect to a remote host.

With a big database or a slow connection, dumping a file and transfering the file compressed may be faster.

Edit As porneL said there is no need to dump to a intermediate file, if you want to work compressed you can do with a compressed tunnel .

pg_dump -C dbname | bzip2 | ssh  remoteuser@remotehost "bunzip2 | psql dbname"

or pg_dump -C dbname | ssh -C remoteuser@remotehost "psql dbname"

But this solution also requires to get a session in both ends.