Incremental backups for PostgreSQL

PostgreSQL doesn't support incremental backups out of the box. You have to use a tool called pg_rman.

You need to install pg_rman (if you are using RedHat familly OS, you can download rpm page this page), otherwise you have to compile it, before you start you will need a few additional packages: libedit-dev, libpam-dev, postgresql-server-dev-9.2.

pg_rman installation process:

sudo su
cd ~
wget http://pg-rman.googlecode.com/files/pg_rman-1.2.4.tar.gz
tar xvfz pg_rman-1.2.4.tar.gz
cd pg_rman-1.2.4
make USE_PGXS=1
make USE_PGXS=1 install
ln -s /usr/lib/postgresql/9.2/bin/pg_rman /bin/pg_rman

You have to configure PostgreSQL like below (file: /etc/postgresql/9.2/main/postgresql.conf) :

wal_level = archive
archive_mode = on
archive_command = 'cp "%p" "/mnt/pg_arclog/%f"'

pg_rman setup:

sudo su
mkdir /mnt/backup_pg_rman
mkdir /mnt/pg_arclog
pg_rman init -B /mnt/backup_pg_rman/ -D /var/lib/postgresql/9.2/main/

pg_rman configuration (file: /mnt/backuppgrman/pg_rman.ini):

SRVLOG_PATH='/var/log/postgresql'
ARCLOG_PATH='/mnt/pg_arclog'

BACKUP_MODE = INCREMENTAL
COMPRESS_DATA = YES

Set correct rights for pg_rman directories:

sudo su
chown -R postgres:postgres /mnt/backup_pg_rman
chown -R postgres:postgres /mnt/pg_arclog

Triggering backups:

• full

  pgrman backup -B /mnt/backuppg_rman/ --pgdata=/var/lib/postgresql/9.2/main/ -b full

• incremental

  pgrman backup -B /mnt/backuppg_rman/ --pgdata=/var/lib/postgresql/9.2/main/

Send backup to S3. Just create a file ~/send_backup_to_s3 (as user postgres and add execute rightschmod +x ~/send_backup_to_s3) and paste following lines:

#!/bin/bash

DAY=`ls -lt /mnt/backup_pg_rman/ | grep $USER | grep -vE "tar|gz" | head -n 1 | awk {'print $9'}`
BACKUP=`ls -lt /mnt/backup_pg_rman/$DAY | grep $USER | grep -vE "tar|gz" | head -n 1 | awk {'print $9'}`

LOCAL_STORE='/mnt/backup_pg_rman/'$DAY
BACKUP_NAME=$LOCAL_STORE\/$DAY\_$BACKUP\.tar\.gz
DIRECTORY_TO_BACKUP=/mnt/backup_pg_rman/$DAY/$BACKUP

if [ ! -f $BACKUP_NAME ]
then
        tar cfzP $BACKUP_NAME $DIRECTORY_TO_BACKUP
        s3cmd put $BACKUP_NAME s3://backup/pg_rman/
fi

Autobackup by cron, example for one full backup per day, and one incremental backup per hour.

10   7 * * 1-5 /bin/bash -l -c 'source /etc/environment && pg_rman backup -B /mnt/backup_pg_rman/ --pgdata=/var/lib/postgresql/9.2/main/ -b full && pg_rman validate -B /mnt/backup_pg_rman ~/send_pg_rman_backup_to_s3'
20 */1 * * 1-5 /bin/bash -l -c 'source /etc/environment && pg_rman backup -B /mnt/backup_pg_rman/ --pgdata=/var/lib/postgresql/9.2/main/ && pg_rman validate -B /mnt/backup_pg_rman && ~/send_pg_rman_backup_to_s3'

Enjoy.