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:
- Easy to set-up
- Enables migration to a different PostgreSQL version on restore
- Non-binary. For example you could manually delete some tables or modify data before restoring
- Can be smaller because it does not include indexes
Disadvantages:
- Restore time can be longer because PostgreSQL has to rebuild indexes
- Incremental backups can cause large transfers with UrBackup
- The complete database (excl. indexes) has to be read during incremental backups
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:
- Easy to setup on Windows
- UrBackup only transfers changed data
- Proper progress bar
- Fast restores if the network is fast, because indexes are included in the backup
Disadvantages:
- You might not be able to setup a snapshotting method on Linux
- Because indexes are backed up, the backups might be bigger than the SQL dump method
- Restore to a previous PostgreSQL version is not possible
- The complete database has to be read during incremental backups if no change block tracking is available
- Snapshots may slow down your database depending on where the snapshot stores the copy-on-write data (shadow storage on Windows)
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:
- UrBackup only transfers changed data
- The complete database is not read during the more frequent backups
- Fast restores if the network is fast, because indexes are included in the backup
- The database is not slowed down. The backup is properly throttled such that it does not interfere over-much with database usage
Disadvantages:
- Hard to setup
- Might be slightly slower than the backup using snapshots
- Harder to restore
- Currently only implemented and documented for Linux
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).
Like
Follow