Dump and Restore PostgreSQL Tables

When doing local web development, working with real data makes it easier to reproduce and debug problems. In particular, if complicated configurations exist only in production or staging, it can be especially handy to populate the data exactly.

To solve this problem, one solution is to load the remote database content in the local table.

pg_dump can be used to dump the remote table to a file as follows. Replace db_connection_string with the real connection string, or perhaps it can be read from an environment variable.

pg_dump --verbose --compress=6 -Fc --no-acl --no-owner -h localhost 
`db_connection_string` -t table_name > tmp/table_dump_file_name.sql

Note also that table_name and table_dump_file_name are placeholders, replace those with the table you wish to work with. Once you get the file created with content:

pg_restore can be used to load the content into the table as follows.

pg_restore -d database_name tmp/table_dump_file_name.sql

Boom. That's it!