Skip to content

Instantly share code, notes, and snippets.

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 and drop the -a parameter of pg_dump.

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

See also

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