Skip to content

Instantly share code, notes, and snippets.

@MickaelBergem

MickaelBergem/gist:d710c5b3cd917035d183

Created December 4, 2014 14:50
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save MickaelBergem/d710c5b3cd917035d183 to your computer and use it in GitHub Desktop.
Save MickaelBergem/d710c5b3cd917035d183 to your computer and use it in GitHub Desktop.
Download ZIP
Moving PostgreSQL tables from a database to another
Raw
gistfile1.md

Copies only the data, assuming the tables are empty (TRUNCATE).

Source database :

pg_dump -a -U bnpipflux bnpipfluxdb | gzip > dump-prod-DD-MM-YYYY.sql

Destination database :

scp -i .ssh/key source_host:dump-prod-DD-MM-YYYY.sql .
psql -U username tablename # Truncate the tables
gunzip -c dump-prod-DD-MM-YYYY.sql | psql --set ON_ERROR_STOP=on -U bnpipflux bnpipfluxdb

You can also drop the tables with http://stackoverflow.com/questions/3327312/drop-all-tables-in-postgresql and drop the -a parameter of pg_dump.

Use pg_dump -t table to dump only one or more tables.

See also http://www.postgresql.org/docs/9.3/static/backup-dump.html.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment