Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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

share|improve this question
add comment (requires an account with 50 reputation)

closed as off topic by Dave Jarvis, Jan Hančič, Don Roby, Veger, Mr. Alien Dec 24 '12 at 11:15

Questions on Stack Overflow are expected to relate to programming within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

4 Answers

up vote 82 down vote accepted

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.

share|improve this answer
10  
There's no need for intermediate files - you may use compressed SSH tunnel or simply pipe: pg_dump | bzip2 | ssh "bunzip2 | pg_restore" – porneL Aug 6 '09 at 12:46
If you use bzip2, turn off ssh compression to speed up the transfer! – lzap Jun 19 '12 at 9:34
The command ssh already has the compression disabled by default. In the example when using ssh compression, I am not using bzip2 and when using bzip2 I am not using ssh compression. – Ferran Jun 19 '12 at 16:07
1  
How can I work compressed if I'm pulling data from production down into development? I have set up an SSH connection from development into production. So would it be ssh remoteuser@remotehost "pg_dump -C dbname | bzip2" | bunzip2 | psql dbname? – Jeromy French Feb 26 at 2:51
add comment (requires an account with 50 reputation)
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
share|improve this answer
Some one told me this can be problematic - permissions problems causing either the dump or restore to die when it hits a trigger? – Robin Barnes Aug 6 '09 at 9:26
3  
@rmbarnes: If there are problems - they have to be fixed. Without detailed knowledge what this "Some one" did - nobody can confirm nor dismiss this claim. – user80168 Aug 6 '09 at 10:06
Use the --no-owner flag with pg_dump. This skips the problem and the first edit of this post used it -- but then I thought you might need more precise fidelity to the original database. – unmounted Aug 6 '09 at 17:05
add comment (requires an account with 50 reputation)

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
share|improve this answer
add comment (requires an account with 50 reputation)

For PostgreSQL you can use either of these operations

  1. replicate the database using Slony.
  2. use dump and restore.
share|improve this answer
add comment (requires an account with 50 reputation)

Not the answer you're looking for? Browse other questions tagged or ask your own question.