Postgres pg_dump straight into another database for backup or development

I frequently use a dump of the live database data to test new migrations, new features.

I used to pg_dump on the remote live server to a file, then scp it across to my local server, psql it back to life in a new database.

I have wondered if it was possible to do it all in one step.  Well it is!

createdb -e pricespy_05_12 -E UTF8
pg_dump pricespy -U wwadmin -h wetware.co.nz | psql pricespy_05_12

generic version:
pg_dump <remote_or_live_db_name> -U <db_username> -h <host_url_or_ip> | psql <local_db_name>

And bing, you will see the commands float onto your terminal screen.  Hopefully in a lovely green text on black background, as I have it.

Posted on May 12, 2009 at 12:28 pm by Jordan Carter · Permalink
In: Server Config · Tagged with: , ,

Leave a Reply