Tech Tips: How do I Backup and Restore the Postgres Database?

Karan Singh
Jun. 19, 2013 by Karan Singh

First, a disclaimer. This post provides a general guide on how to backup a Postgres Database Table. The examples provided do not refer to any specific table or database. If you are trying to backup a specific table, you should also backup tables which refer to the original tables. As Postgres is a relational database, there will always be references between a number of tables.

This is a ‘how to’ doc which only gives example commands, how it is done actually is solely dependent on the person doing it and specific use cases. Always maintain the integrity of the data you are trying to backup. Backing up the entire database is always a safe option, but when backing up specific tables one needs to be careful.

SQL-dump/pg_dump:

The idea behind the SQL-dump method is to generate a text file with SQL commands that, when fed back to the server, will recreate the database in the same state as it was in at the time of the dump. PostgreSQL provides the utility program pg_dump for this purpose.

pg_dump is an effective and comprehensive tool to take Postgres database backups and use the backups to restore the postgres database. It is however not restricted to Database only. We can use pg_dump to backup tables and then use that to restore individual tables as well. Using pg_dump you can backup the local database and restore it on a remote database.

How to Backup Postgres Database :

  1. Backup a Postgres Table:

    $ /opt/msys/3rdParty/bin/pg_dump

    –table maincontrol.orgs -U ecuser pe -f ms_table.sql

    The above command is an example of how to backup a specific table from a Postgres database. Using the above command we are backing up table ‘orgs’ in schema ‘maincontrol’  from database ‘pe’ to ms_table.sql file. To backup a specific table, use the –table TABLENAME option in the pg_dump command. If there are same table names in different schema then use the –schema SCHEMANAME option.

  2. Backup a Specific Postgres database :

    $ /opt/msys/3rdParty/bin/pg_dump -U ecuser pe -f pe_dump.sql

    This is an example of backing up a specific Postgres database. Using the above command we are backing up the database for Message Central ‘pe’ to file pe_dump.sql. The backup file has create table, alter table and copy commands for all the tables in the ‘pe’ database.

  3. Backup all Postgres Databases :

    $ /opt/msys/3rdParty/bin/pg_dumpall -U ecuser> all_dump.sql

    You can backup all the databases using pg_dumpall command. The above command will create a dump of all the databases that reside on the Postgres instance running on a particular server. To list all the database that have been backed up use the command grep “^[\]connect” all.sql.

How to Restore Postgres Database

  1. Restore a Postgres table :

    $ /opt/msys/3rdParty/bin/psql -U ecuser -f ms_table.sql pe

    The above command will install the table that was backed up in ms_table.sql file to the ‘pe’ database. Make sure that this table does not already exist, or you will see a bunch of ‘already exists’ errors. This command creates the table and exports all the data to the newly created table.

  2. Restore a postgres Database :

    $ /opt/msys/3rdParty/bin/psql -U ecuser -d pe -f pe_dump.sql

    Similar to restoring the table, we can use the above command to restore the complete database. Here we are restoring the ‘pe’ database using the file pe_dump.sql which we had created while backing up the database in the Backup section above.

  3. Restore all Databases :

    $ /opt/msys/3rdParty/bin/psql -u ecuser -f all_dump.sql

    Restore all the databases using the above command. ‘all_dump.sql’ was the file that was created using pg_dumpall. The above command will give us all the Postgres databases in the exact state that they were in when a dump was taken from the original database server.

Message Systems software is used to send 20% of the world’s legitimate email. Find out why more brands use our Momentum software.

Blog_Ads_TEI-Momentum_100413

1 Comment

Related Content

Building an Email Archiving System: The Challenges and of Course the Solution - Part 1 of 4

Our Senior Messaging Engineer describes the process he went through in order to store the email body onto S3 and all relevant log data in MySQL.

read more

S/MIME Part 4: Collecting Recipient Public Keys the Easy Way - with SparkPost Inbound Relay Webhooks

In part 4 of his series, Senior Messaging Engineer, Steve Tuck explains how to collect recipient public keys with SparkPost Inbound Relay webhooks.

read more

Knowing Your Social Media Privacy Settings

Have you reviewed the privacy settings on any of your social media accounts recently? Our guide will help you pick the privacy settings that best suit you.

read more

Get started and start sending

Try SparkPost and see how easy it is to deliver your app’s email on time and to the inbox.

Try Now

Send this to a friend