Copying PostgreSQL database to another server

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

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

pg_basebackup seems to be the better way of doing this now, especially for large databases.

For PostgreSQL you can use either of these operations

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

I struggled quite a lot and eventually the method that allowed me to make it work with Rails 4 was:

on your old server

sudo su - postgres
pg_dump -c --inserts old_db_name > dump.sql

I had to use the postgres linux user to create the dump. also i had to use -c to force the creation of the database on the new server. --inserts tells it to use the INSERT() syntax which otherwise would not work for me :(

then, on the new server, simpy:

sudo su - postgres
psql washr < dump.sql

to transfer the dump.sql file between server I simply used the "cat" to print the content and than "nano" to recreate it copypasting the content.

Also, the ROLE i was using on the two database was different so i had to find-replace all the owner name in the dump.

Run this command with database name you want to backup to take dump of DB.

 pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}

 eg. pg_dump -U postgres mydbname -f mydbnamedump.sql

Now scp this dump file to remote machine where you want to copy DB.

eg. scp mydbnamedump.sql user01@remotemachineip:~/some/folder/

On remote machine run following command in ~/some/folder to restore the DB.

 psql -U {user-name} -d {desintation_db}-f {dumpfilename.sql}

 eg. psql -U postgres -d mynewdb -f mydbnamedump.sql

If you are looking to migrate between versions (eg you updated postgres and have 9.1 running on localhost:5432 and 9.3 running on localhost:5434) you can run:

pg_dumpall -p 5432 -U myuser91 | psql -U myuser94 -d postgres -p 5434

Check out the migration docs.

Another possibility would be to copy the entire data directory, but since it's not fail proof, I'd go with the safer pg_dump method.