Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Moving PostgreSQL tables from a database to another

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
You can’t perform that action at this time.