current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.6 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 278 down vote favorite
166

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

share|improve this question
up vote 382 down vote accepted

You don't need to create an intermediate 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.

As Kornel said there is no need to dump to a intermediate file, if you want to work compressed you can use 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
18  
There's no need for intermediate files - you may use compressed SSH tunnel or simply pipe: pg_dump | bzip2 | ssh "bunzip2 | pg_restore" – Kornel Aug 6 '09 at 12:46
1  
If you use bzip2, turn off ssh compression to speed up the transfer! – lzap Jun 19 '12 at 9:34
5  
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 '13 at 2:51
1  
This is what I did: (1) pg_dump -C -h remotehost -U remoteuser x | psql -h localhost -U localuser (2) dropdb y (3) psql -U postgres -c 'ALTER DATABASE "x" RENAME TO "y"' – Darin Peterson Mar 4 '14 at 17:22
1  
I used the command in the answer to copy a database from local host to remotehost, but it always tries to create a database with the name of database on localhost side, no matter what name I give in the second part of command. I had to rename it before copying it to the remote host. Any idea how to do it in the same command? – Dark Light Jul 8 '14 at 18:35
up vote 71 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
1  
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
9  
@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
1  
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
1  
For me, above approach worked in following way: pg_dump -C -h host -U username db_name > /any_directory/dump_schema_and_data_file .And for restoring from file: psql -h host -U username db_name < dump_schema_and_data_file – Ali Raza Bhayani Mar 26 '14 at 9:38
up vote 29 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 9 down vote

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

share|improve this answer
5  
Could you provide more details in your answer, such as an example? – Magnilex Feb 4 '15 at 21:46
2  
This only works when both machines have the same PG versions, though. – s.m. Mar 3 '16 at 14:00
up vote 6 down vote

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.

share|improve this answer
up vote 3 down vote

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
share|improve this answer
up vote 3 down vote

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 new_database_name < 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.

share|improve this answer

protected by durron597 Sep 18 '15 at 13:54

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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