up vote 46 down vote favorite
24

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

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 or software development within the scope defined in the FAQ. 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 closed questions here.

4 Answers

up vote 60 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
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
up vote 10 down vote 
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. – depesz 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. – bvmou Aug 6 '09 at 17:05
up vote 7 down vote

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
up vote 0 down vote

For PostgreSQL you can use either of these operations

  1. replicate the database using Slony.
  2. use dump and restore.
share|improve this answer

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