Different methods

All the backup methods on this page are zero-copy. That is, the data will be directly transferred to the backup server without creating a local copy first.

Backup using SQL dump

Advantages:

Disadvantages:

Conclusion: Use this backup method if you have a small database (e.g. 1GB) on Linux.

How to setup:

On Linux with the binary client: Change 0 to 1 in /usr/local/etc/urbackup/postgresqldump.conf.

How to restore:

Start with an empty database, download the SQL dump from the server and then apply the SQL dump with:

psql -f postgresqldump.sql postgres

Backup using snapshot

Advantages:

Disadvantages:

Conclusion: Easiest method on Windows, good method on Linux if a file system snapshotting method is available. Best method if change block tracking is available.

How to setup:

Configure the client to backup the PostgreSQL data directory. E.g. /var/lib/postgresql/9.5/main on Linux and C:\Program Files\PostgreSQL\9.5\data on Windows. Make sure you have configured a snapshotting method on Linux.

How to restore:

Stop PostgreSQL and then restore /var/lib/postgresql/9.5/main on Linux and C:\Program Files\PostgreSQL\9.5\data on Windows from one of the file backups.

Binary backup without snapshot and with log shipping

Advantages:

Disadvantages:

Conclusion: Best method for high traffic and large PostgreSQL instances.

How to setup (Linux):

Change 0 to 1 in /usr/local/etc/urbackup/postgresbase.conf.

Add a virtual client with:

urbackupclientctl set-settings -v virtual_clients -k wal

Allow the postgres user to establish a replication connection in your pg_hba.conf (Uncomment the local replication postgres peer line).

Set max_wal_senders in postgresql.conf to something higher than zero and set wal_level to archive.

Restart PostgreSQL and test if the full backup works by running

/usr/local/share/urbackup/scripts/postgresbase > /dev/null

If you do not want to have a continuous WAL backup you can stop at this point. To frequently backup WAL data to your backup server, PostgreSQL has to be configured such that the WAL data is copied somewhere and then deleted after a WAL backup completes.

Configure PostgreSQL to archive WAL files to a directory setting archive_mode to on and archive_command to

cp %p /var/lib/walarchive/incoming/%f; mv /var/lib/walarchive/incoming/%f /var/lib/walarchive/staging/%f

Create the archive directories:

mkdir -p /var/lib/walarchive/{incoming,staging,backup}
chown postgres:postgres /var/lib/walarchive/{incoming,staging,backup}

Configure the WAL archives to be backed up:

urbackupclientctl add-backupdir --keep -d /var/lib/walarchive/backup -v wal

Add a pre file backup script (/usr/local/etc/urbackup/prefilebackup):

#!/bin/bash
set -e
exists() { [[ -e $1 ]]; }
# Argument three not null means virtual client
if [ $3 != 0 ] && exists /var/lib/walarchive/staging/*
then
    mv /var/lib/walarchive/staging/* /var/lib/walarchive/backup/
fi

Add a post file backup script (/usr/local/etc/urbackup/postfilebackup):

#!/bin/bash
set -e
exists() { [[ -e $1 ]]; }
# Argument one null means main client
if [ $1 = 0 ]
then
    urbackupclientctl reset-keep -v wal
elif exists /var/lib/walarchive/backup/*
then
    rm /var/lib/walarchive/backup/*
fi

Do not forget to make the pre/postbackup scripts executable with chmod +x /usr/local/etc/urbackup/*.

Then configure the backup interval of [wal] to be relatively small and the backup interval of the main client with the base backup script relatively big and schedule it via backup window at a time where it does not interfere with database usage.

How to restore:

First restore the base backup via:

urbackupclientctl restore-start -b last -d urbackup_backup_scripts/postgresbase

Then copy the most recent set of WAL files to the database server and follow the recovery instructions in https://www.postgresql.org/docs/current/static/continuous-archiving.html (section 24.3.4).