PostgreSQL 9.4 – Database dumps and Restores

Create a Compressed dump file of a Remote Database and Restore it

  • Create a highly compressed data dump
pg_dump -h 10.0.0.19 -U seeker -C -c -v -F c -d servers -f data_dump.dump
  • Restore the data dump you created
pg_restore -C -v -U seeker -d servers -h 10.0.0.32  data_dump.dump

Create SQL data dumps and Restore them

  • Create a SQL Schema only dump
pg_dump -U  seeker  -d servers   -h 10.0.0.32 --schema-only -f schema_only.sql
  • Create a SQL Data only dump
pg_dump -U  seeker  -d servers   -h 10.0.0.32 --data-only -f data_only.sql
  • Restore SQL data dump using psql
\i "file_name".sql
     Important Notes about Backing up and Restoring

The database, schema and the role must be first created.

Back up and Restore specific tables

  • Backup a specific table
 pg_dump -Fc -v -U seeker -d servers -t a_table > /tmp/a_table.pgdump
  • View the tables of a data dump
pg_restore -l  data_dump_file.dump
  • Extract a table from a dump file
pg_restore -U seeker --data-only --table=a_table database.pgdump > a_table.pg
  • Upload the data of this table to a table in the database
 psql -U user_name -d database_name < name_of_your_dump.pg
Share Button

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload the CAPTCHA.