Saturday, 16 November 2013

Backup my PostgreSQL Database

There are three fundamentally different approaches to backing up PostgreSQL databases:

1. SQL dump
2. File system level backup
3. Continuous Archiving

1. SQL dump:
•  Generate a text file with SQL commands
•  PostgreSQL provides the utility program pg_dump for this purpose.

For more details please go through the below link:-

Let us see some examples here:

 --> Take the dump of whole cluster(use pg_dumpall for that).
     ---> Go to your binaries location and execute below command:

     -bash-4.1$ ./pg_dumpall -p 5433 > /tmp/dumpall.sql

      Where -p is port of cluster.

--> Take the dump of a database(use pg_dump for that)
   plain format:
  -bash-4.1$ ./pg_dump -p 5433 -U postgres postgres >> /tmp/dump_postgres_db.sql
   Compressed Format:
   -bash-4.1$ ./pg_dump -p 5433 -U postgres postgres -Fc -f /tmp/dump.dmp

   Where -p - port
         -U - user
         -Fc - Format Compressed(you can use tar by using -Ft)
         -f - dumpfile.

--> Take the dump of a table/sequence

     -bash-4.1$ ./pg_dump -p 5433 -U postgres -t test postgres >> /tmp/dump_test_table.sql

     Where -t - tablename/sequencename

2. File system level backup:
•  An alternative backup strategy is to directly copy the files that PostgreSQL uses to store the data in the database.
•  You can use whatever method you prefer for doing usual file system backups, for example:
   tar -cf backup.tar <location of data directory>
•  The database server must be shut down in order to get a usable backup.
•  File system backups only work for complete backup and restoration of an entire database cluster.
•  File system snapshots work for live servers.

If you take File system level backup, your database has no need to run on archivelog mode but nees to shutdown the server while taking the backup. It is a consistence backup. But you have chances to loose the data.

If you have taken the backup at 8p.m and your database is crashed due to some reason at 9p.m. Then you will loose the data of 8p.m to 9p.m as we have backup at 8p.m only. The 1 hour data will not be recovarable.

3. Continuous Archiving:
•  PostgreSQL maintains WAL files for all transactions in pg_xlog directory
•  PostgreSQL automatically maintains the WAL logs which are full and switched
•  Continuous archiving can be setup to keep a copy of switched WAL Logs which can be later used for recovery.
•  It also enables online file system backup of a database cluster.
•  Requirements:
   wal_level must be set to archive
   archive_mode must be set to on
   archive_command must be set in postgresql.conf

For more details on how to enable archiving, you can follow the below link:-

Benefits of running database in Continuous Archiving:
a. No need to shutdown the server(Means no downtime required).
b. You will not loose the data.
c. Running in archivelog mode does copy your all WAL files to the location mentioned in configuration file.
d. You can have multiple locations(duplexing of archives) for your archivelog files.(recommended to have these locations on different mount points because if one mount point got corrupted then you can get them from other one).

If you have taken the backup at 8p.m and your database is crashed due to some reason at 9p.m, then you can recover the data till 9PM as we will have copied archive files from 8p.m to 9p.m in archive log location mentioned in the configuration file. If you recover backup of 8p.m , it will automatically apply these archivelogs while database recovery, no manual operation is required.


  1. This is very helpful for new beginners, Thanks for posting such a nice blog.

  2. Replies
    1. excellent blog............very helpful for begineer................thanks a lot.