In PostgreSQL, Backup & Recovery are very user friendly comparing with
other database. Many of them won’t agree to this, ok lets not get into
debate. Coming to Backups, PostgreSQL does’nt support INCREMENTAL
BACKUP, however there are very consistant backup tools and OS level
work-around to achieve this goal.
My pictorial presentation on PostgreSQL Backup and Recovery gives a
complete conceptial idea. Looking into Diagram you can make out which
backups can be used to restore or recover.
pg_dump,pg_restore and pg_dumpall utilities used for logical backups.
pg_dump and pg_restore will help in taking backups for Database level,
Schema level and Table Level. Pg_dumpall used for cluster level dump.
Three formats are supported with pg_dump, Plain SQL format, Custom
Format and Tar Format. Custom and Tar Format dumps are compatible with
pg_restore utility whereas Plain SQL format dumps are compatible with
psql utility for restoration.
Below are the examples for each Backup levels and with related restore
Note: Set the defaults for PGDATABASE, PGUSER, PGPASSWORD and PGPORT in
.bash_profile(Environment Variables in Windows)
Plain SQL Format Dump and Restore
$ pg_dump -U username -Fp dbname > filename
$ pg_dump -U username dbname -f filename
$ pg_dump -Fp -U username dbname -f filename
For restoring use psql command
$ psql -U username -f filename dbname
postgres=# \i SQL-file-name //in psql terminal with \i option
$ pg_dump -Fc dbname -f filename
$ pg_restore -Fc -U username -d dbname filename.dmp
$ pg_dump -Ft dbname -f filename
$ pg_restore -U username -d dbname filename
$ cat tar-file.tar | psql -U username dbname
Note: Schema Level and Tables Level dumps can be performed in the same
way by adding related options.
Cluster Level Dump:
$pg_dumpall -p portnumber > filename
For restoring use psql command
$ psql -f filename
There are very best way of taking dumps and restoring methodolgies. In
particular, Simon Riggs and Hannu Krosing - “PostgreSQL 9
Administration Cookbook - 2010” book
is good way to start with PostgreSQL Backup and Recovery published by
Physical Backup (File system Backup)
In cold backup, its a simple file system backup of /data directory when
Postgres Instance is down, mean, to achieve a self-consistent data
directory backup, the database server should be shut down before
copying. PostgreSQL gives flexibility to keep pg_xlog and pg_tblspce
in different mount points via softlink. While copying the /data
directory including the soft link’s data, use the below command.
tar czf backup.tar.gz $PGDATA
cp -r $PGDATA /backup/
rsync -a $PGDATA /wherever/data
Hot Backup (Online Backup):
In Hot Backup, cluster will be up and running and the Database should be
in Archive Log Mode. Two system functions will notify the instance about
starting and stopping the Hot Backup
process(pg_start_backup(),pg_stop_backup()). Before going forward
with Online Backup, let’s discuss on the Database Archive Log mode which
is mandatory for Online Backups.
Enabling WAL Archiving:
Coming posts of mine will brief about PITR / Tunning WAL etc., presently
we look into WAL Archiving. In PostgreSQL database system, the actual
database ‘writes’ to an addition file called write-ahead log (WAL) to
disk. It contains a record of writes that made in the database system.
In the case of Crash, database can be repaired/recovered from these
Normally, the write-ahead log logs at regular intervals (called
Checkpoints) matched against the database and then deleted because it no
longer is required. You can also use the WAL as a backup because,there
is a record of all writes made to the database.
Concept of WAL Archiving:
The write-ahead log is composed of each 16 MB large, which are called
segments. The WALs reside under pg_xlog directory and it is the
subdirectory of ‘data directory’. The filenames will have numerically
named in ascending order by PostgreSQL Instance. To perform a backup on
the basis of WAL, one needs a basic backup that is, a complete backup of
the data directory, and the WAL Segments between the base backup and the
Configuring the archiving of WAL segments can be chosen by setting the
two configuration parameter’s archive_command and archive_mode in the
postgresql.conf. Making the cluster into Archive-log mode requires
archive_mode= on/off (boolean parameter)
archive_command = 'cp –i %p / Archive/Location/ f% '
Note: % p for the file to copy with path used as a file name and
% f without a directory entry for the destination file.
For further information about the Archiver Process, refer to the post
PostgreSQL 9.0 Memory & Processess.
Online Backup :
To take online backup:
Step 1 : Issue pg_start_backup('lable') in the psql terminal
postgres=# select pg_start_backup('fb');
Step 2 : OS level copy the $PGDATA directory to any Backup Location
$ cp -r $PGDATA /anylocation
Step 3 : Issue pg_stop_backup() in psql terminal.
postgres=# select pg_stop_backup();
Note: It is not necessary that these two functions should run in the
same database connection. The backup mode is global and persistent.
In PostgreSQL, there is no catalog to store the Start and Stop time of
the Online backup. However, when online backup is in process, couple of
the files created and deleted.
pg_start_backup(‘label’) and pg_stop_backup are the two system
functions to perform the Online Backup. With pg_start_backup(‘label’)
a file backup_label is created under $PGDATA directory and with
pg_stop_backup() a file ‘wal-segement-number.backup’ file created
under $PGDATA/pg_xlog. Backup_label will give the start time and
Checkpoint location of WAL Segment, it will also notify the PostgreSQL
instance that Cluster is in BACKUP-MODE. ‘wal-segment-number.backup’
file under $PGDATA/pg_xlog directory describes the start and stop time,
Checkpoint location with WAL segement number.
Note: After pg_stop_backup(), backup_label file is deleted by the
Do post your comments, suggestions.