Point in time recovery: PITR using pg_basebackup with PostgreSQL 9.2


This post explain how to do point in time recovery: PITR using pg_basebackup with PostgreSQL 9.2. This is a bit easier than using PITR with low level API backups as explained in this post. This is very nice powerful utility provided along with PostgreSQL installation for taking low-level backups.

1. Backup Process

In order to make this basebackup tool work, we need to first set some configuration variables in postgresql.conf file which is usually located under data directory [Default of Redhat based systems is: /var/lib/pgsql/9.2/data].

Edit configuration file:

vi /var/lib/pgsql/9.2/data/postgresql.conf

And update the following variables:

wal_level = hot_standby
archive_mode = on
archive_command = 'test ! -f /var/lib/pgsql/pg_log_archive/%f && cp %p /var/lib/pgsql/pg_log_archive/%f'
max_wal_senders = 2

Image 001

Now create archive directory to ship log segment files to a directory other than pg_xlog directory which is located under postgres data directory, as this pg_xlog directory will not be backed up when you use pg_basebackup utility.

mkdir /var/lib/pgsql/pg_log_archive/
chown postgres.postgres /var/lib/pgsql/pg_log_archive/
chmod 700 /var/lib/pgsql/pg_log_archive/

Create a user with replication privilege as pg_basebackup needs replication privilege for creating a base backup [There are couple of ways to create a user, I am following one]:

su - postgres
psql -c "CREATE ROLE base_backup_user REPLICATION LOGIN PASSWORD 'backupuser';"

Image 002

Exit out of postgresql user. Update pg_hba.conf file located under postgres data directory [/var/lib/pgsql/9.2/data] and add rule for enabling this newly created user under replication connections section of this file, which is all the way to the end of file :

vi /var/lib/pgsql/9.2/data/pg_hba.conf
host     replication     base_backup_user     trust

Image 003

Note: This hba rule is just example. Please change these according to your needs/environment.

Now you are ready to restart database engine for making all these changes to be in effect.

service postgresql-9.2 restart

Image 004

At this point you are ready to take base backup using pg_basebackup. To backup your database simply run the following under your home directory:

/usr/pgsql-9.2/bin/pg_basebackup -h127.0.0.1 -U base_backup_user -D backup -Ft -z -P

Image 005

You have successfully backed up your data directories. There should be a directory named "backup" created from where you have issued this command. If you open this directory you will see the tar file of the data directory. If you have multiple tables spaces then you will see multiple files here one for each table space.

Image 006

2. Restore Process

Lets say the day have arrived and bad things happened. Now you have your archived logs and base backups to restore data from. Stop PostgreSQL server if running and move contents existing data directory to different location for restoring backups.

service postgresql-9.2 stop
mkdir /tmp/data.old
mv /var/lib/pgsql/9.2/data/* /tmp/data.old/

Now copy tar file into postgresql data directory and extract files :

cp backup/base.tar.gz /var/lib/pgsql/9.2/data/
cd /var/lib/pgsql/9.2/data/
tar -xvf base.tar.gz
rm base.tar.gz

At this point you have your files extracted properly into your directory. Go ahead and remove base.tar.gz file from this data directory to keep it clean.

Image 007

Now create a recovery.conf file. A sample recovery file is provided with installation of the database server which should be located in share directory under the installation directory of postgres [ex: /usr/pgsql-9.2/share] you can copy sample file to /var/lib/pgsql/9.2/data directory and rename it as recovery.conf file. Alternately create a file with the contents mentioned below:

I added the following line into this recovery.conf file :

restore_command = 'cp /var/lib/pgsql/pg_log_archive/%f %p'

Your final recovery.conf file should look as below:

# -------------------------------
# PostgreSQL recovery config file
# -------------------------------
# Edit this file to provide the parameters that PostgreSQL needs to
# perform an archive recovery of a database, or to act as a replication
# standby.
# If "recovery.conf" is present in the PostgreSQL data directory, it is
# read on postmaster startup.  After successful recovery, it is renamed
# to "recovery.done" to ensure that we do not accidentally re-enter
# archive recovery or standby mode.
# This file consists of lines of the form:
#   name = value
# Comments are introduced with '#'.
# The complete list of option names and allowed values can be found
# in the PostgreSQL documentation.
# restore_command
# specifies the shell command that is executed to copy log files
# back from archival storage.  The command string may contain %f,
# which is replaced by the name of the desired log file, and %p,
# which is replaced by the absolute path to copy the log file to.
# This parameter is *required* for an archive recovery, but optional
# for streaming replication.
# It is important that the command return nonzero exit status on failure.
# The command *will* be asked for log files that are not present in the
# archive; it must return nonzero when so asked.
# NOTE that the basename of %p will be different from %f; do not
# expect them to be interchangeable.
#restore_command = ''           # e.g. 'cp /mnt/server/archivedir/%f %p'
restore_command = 'cp /var/lib/pgsql/pg_log_archive/%f %p'
# archive_cleanup_command
# specifies an optional shell command to execute at every restartpoint.
# This can be useful for cleaning up the archive of a standby server.
#archive_cleanup_command = ''
# recovery_end_command
# specifies an optional shell command to execute at completion of recovery.
# This can be useful for cleaning up after the restore_command.
#recovery_end_command = ''
# By default, recovery will rollforward to the end of the WAL log.
# If you want to stop rollforward at a specific point, you
# must set a recovery target.
# You may set a recovery target either by transactionId, by name,
# or by timestamp. Recovery may either include or exclude the
# transaction(s) with the recovery target value (ie, stop either
# just after or just before the given target, respectively).
#recovery_target_name = ''      # e.g. 'daily backup 2011-01-26'
#recovery_target_time = ''      # e.g. '2004-07-14 22:39:00 EST'
#recovery_target_xid = ''
#recovery_target_inclusive = true
# If you want to recover into a timeline other than the "main line" shown in
# pg_control, specify the timeline number here, or write 'latest' to get
# the latest branch for which there's a history file.
#recovery_target_timeline = 'latest'
# If pause_at_recovery_target is enabled, recovery will pause when
# the recovery target is reached. The pause state will continue until
# pg_xlog_replay_resume() is called. This setting has no effect if
# hot standby is not enabled, or if no recovery target is set.
#pause_at_recovery_target = true
# standby_mode
# When standby_mode is enabled, the PostgreSQL server will work as a
# standby. It will continuously wait for the additional XLOG records, using
# restore_command and/or primary_conninfo.
#standby_mode = off
# primary_conninfo
# If set, the PostgreSQL server will try to connect to the primary using this
# connection string and receive XLOG records continuously.
#primary_conninfo = ''          # e.g. 'host=localhost port=5432'
# By default, a standby server keeps restoring XLOG records from the
# primary indefinitely. If you want to stop the standby mode, finish recovery
# and open the system in read/write mode, specify path to a trigger file.
# The server will poll the trigger file path periodically and start as a
# primary server when it's found.
#trigger_file = ''
# Hot Standby related parameters are listed in postgresql.conf

Make sure that this file is owned by postgres user and permissions are set right:

chown postgres.postgres /var/lib/pgsql/9.2/data/recovery.conf
chmod 600 /var/lib/pgsql/9.2/data/recovery.conf

Image 008

Now you are ready to restart your postgres engine. Database server will automatically go into recovery mode for restoring all the data properly. Its highly recommended to update your hba.conf file to not allow any other users until this recovery is properly complete. PostgreSQL engine with rename the file recovery.conf to recovery.done under your data directory [/var/lib/pgsql/9.2/data] after it finishes restoring, in addition to this it will also rename backup_label to backup_label.old.

Start the database engine:

service postgresql-9.2 start

And after couple of minutes you should have successfully restored your database to last check point that your archive logs recorded.

Image 009

Make sure that you cleanup your wal archive logs located under /var/lib/pgsql/pg_log_archive/ from time to time, if not you will soon run out of disk space.

3. Cleanup archive logs

PostgreSQL provides us with a archive log cleanup utility. Use this to cleanup logs from time to time. You can always simply delete logs before last backup point. Backup point files are denoted with .backup extension under your archive directory.

Sample cleanup command to clean up all logs before previously taken base backup using pg_archivecleanup:

/usr/pgsql-9.2/bin/pg_archivecleanup -d /var/lib/pgsql/pg_log_archive/ `cd /var/lib/pgsql/pg_log_archive/ ; ls -rt *.backup | tail -1`

Image 010

Thats all folks, try it on non-prod servers couple of times before you implement on prod environments. Also tweak the commands to your needs.


About Prashanth Goriparthi

Data Janitor


  1. Pingback: How to do point in time recovery with PostgreSQL 9.2 : PITR in CentOS 6/Redhat EL6

  2. Pingback: Setup replication with Postgres 9.2 on CentOS 6/Redhat EL6/Fedora

  3. Thanks for the detailed post.

    I’m a PostgreSQL user, not an administrator.
    So your post helps me a lot to sort out my problem, that is funally handled.

  4. This post has been an invaluable resource for setting up and testing continuous backups. Thanks for that.

    With the help of section 23.3.4 in the documentation (http://www.postgresql.org/docs/9.2/static/continuous-archiving.html) as well as another post (http://www.xzilla.net/blog/2009/Feb/Controlled-failover-for-PITR-Warm-Standby.html), I would add a couple steps to the recovery. This applies ONLY if your old instance is still there:

    1. With the old server running, log in as a superuser and run "checkpoint;" and "select pg_switch_xlog();". This forces logs to flushed. If the old server is dead, skip this step.

    2. You want to use the most recent pg_xlog data, if available. If you do not have the /tmp/data.old directory, skip this step.

    2a. Delete the contents of /var/lib/pgsql/9.2/data/pg_xlog as restored from the base backup.

    2b. COPY (don’t move) the contents of /tmp/data.old/pg_xlog (the more recent data) to /var/lib/pgsql/9.2/data/pg_xlog.

  5. Thanks for the detailed post. I have a question, is it mandatory to set the wal_level to ‘hotstandby’? I am running a standalone server and I am using pg_basebackup only to backup the data which can be used by the same server when it comes up after crash. Does wal_level=’archive’ suits better in my case?

  6. Quick question – All my wal archive files are gzip’ed during the archive process, so how would i modify my restore command to first gunzip the files before restoring?


  7. Pingback: Streaming replication - small hints - PostgreSQL Candies

Leave a Reply

* Captcha [Please fill/solve below] : * Time limit is exhausted. Please reload the CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.