I am searching copying a production postgres database to some development server. What is the fastest, simplest approach to take about carrying this out?

You don't have to create an itermediate file. That you can do

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


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

Using psql or pg_dump for connecting to some remote host.

Having a large database or perhaps a slow connection, dumping personal files and transfering the file compressed might be faster.

Edit As porneL stated there's you don't need to dump to some intermediate file, if you wish to work compressed that you can do having 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 obtain a session both in finishes.

pg_dump the_db_name > the_backup.sql

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

psql the_new_dev_db < the_backup.sql

Use pg_dump, and then psql or pg_restore - depending whether you select -Fp or -Fc choices to pg_dump.

Illustration 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

For PostgreSQL you should use either of those procedures

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