Configure backup for PostgreSQL
Learn how to take powerful backups of the PostgreSQL database server with the pgBackRest backup tool.
About pgBackRest
pgBackRest is a powerful backup and restore tool for PostgreSQL. It is effective for all database sizes and performs backups internally, reducing limitations associated with external tools such as tar and rsync for backup. This process also strengthens security.
Instead of relying on traditional backup tools such as tar and rsync, pgBackRest implements all backup features internally and uses a custom protocol for communicating with remote systems.
Check for the latest version of pgBackRest tool and in-depth knowledge on the official site of pgBackRest:
Essential pgBackRest features
pgBackRest is a simple backup and restore system that can seamlessly scale up to the largest databases and workloads. Primary pgBackRest features:
Local or remote backup
Multi-threaded backup/restore for performance
Checksums
Full, differential, and incremental backups
Backup rotation and minimum retention rules with optionally separate retention for archiving
In-stream compression/decompression
Archiving and retrieval of logs for replicas/restores
Asynchronous archiving for very busy systems (including space limits)
Tablespace support
Restore using timestamp/size or checksum
Restore remapping base/tablespaces
Install pgBackRest
Install pgBackRest tool through yum as below:
yum install -y pgbackrest
By default, pgBackRest tool configuration file(pgbackrest.conf) location is /etc and add below entry into /etc/pgbackrest.conf file:
vi /etc/pgbackrest.conf
[global] repo-path=/var/lib/pgbackrest retention-full=2 [data] db-path=/var/lib/pgsql/9.6/data
Configure PostgreSQL:
This is automated by ansible.
Edit the 'postgresql.conf' file:
vi /var/lib/pgsql/9.6/data/postgresql.conf
Add the following lines to the 'postgresql.conf' file:
archive_command = 'pgbackrest --stanza=data archive-push %p'
archive_mode = on
listen_addresses = '*'
log_line_prefix = ''
max_wal_senders = 3
wal_level = hot_standby
Restart the database (root user)
systemctl restart postgresql-9.6 systemctl status postgresql-9.6
Configure pgbackrest config
First, define the stanza on the backup server. data is the DB cluster name here:
su - postgres -c 'pgbackrest --stanza=data --log-level-console=info stanza-create'
A stanza defines the backup configuration for a specific PostgreSQL database cluster. The stanza section must define the database cluster path and host/user if the database cluster is remote. Also, any global configuration sections can be overridden to define stanza-specific settings).
Now, check the stanza configuration by executing the below command. data is the DB cluster name here:
su - postgres -c 'pgbackrest --stanza=data --log-level-console=info check'
Create database backup
The first time, there is a full backup. After that, incremental backups are taken. data is the DB cluster name here:
su - postgres -c 'pgbackrest --stanza=data --log-level-console=info backup'
If you run the above command again, the tool automatically figures out which backup is required, either incremental or full backup.
List out backups
Use the below command to list out all the available backups. data is the DB cluster name here:
pgbackrest --stanza data info
Backup directory location
-bash-4.2$ pwd
/var/lib/pgbackrest/backup/data
-bash-4.2$ ls -ltrh
total 3.9G
-rw-r--r-- 1 root root 3.9G Dec 1 09:16 f
drwxr-x--- 3 postgres postgres 4.0K Jan 7 04:52 20180107-043001F
drwxr-x--- 4 postgres postgres 4.0K Jan 7 04:52 backup.history
drwxr-x--- 3 postgres postgres 4.0K Jan 8 04:50 20180107-043001F_20180108-043001D
drwxr-x--- 3 postgres postgres 4.0K Jan 9 04:50 20180107-043001F_20180109-043001D
drwxr-x--- 3 postgres postgres 4.0K Jan 10 04:49 20180107-043001F_20180110-043001D
drwxr-x--- 3 postgres postgres 4.0K Jan 11 04:51 20180107-043001F_20180111-043001D
drwxr-x--- 3 postgres postgres 4.0K Jan 12 04:51 20180107-043001F_20180112-043002D
drwxr-x--- 3 postgres postgres 4.0K Jan 13 04:51 20180107-043001F_20180113-043002D
drwxr-x--- 3 postgres postgres 4.0K Jan 14 04:52 20180114-043001F
drwxr-x--- 3 postgres postgres 4.0K Jan 15 04:48 20180114-043001F_20180115-043001D
drwxr-x--- 3 postgres postgres 4.0K Jan 16 04:51 20180114-043001F_20180116-043001D
drwxr-x--- 3 postgres postgres 4.0K Jan 17 04:50 20180114-043001F_20180117-043001D
drwxr-x--- 3 postgres postgres 4.0K Jan 18 04:50 20180114-043001F_20180118-043001D
lrwxrwxrwx 1 postgres postgres 33 Jan 18 04:50 latest -> 20180114-043001F_20180118-043001D
-rw-r----- 1 postgres postgres 8.3K Jan 18 04:50 backup.info
-rw-r----- 1 postgres postgres 8.3K Jan 18 04:50 backup.info.copy
Sample crontab from docs
To schedule cron job from Postgres user:
#m h dom mon dow command
30 06 * * 0 pgbackrest --type=full --stanza=data backup
30 06 * * 1-6 pgbackrest --type=diff --stanza=data backup
Restore database backup
Use the below command to restore the full database cluster, i.e. 'data'.
First, delete all the contents of the 'data' (cluster) directory. data is the DB cluster directory here:
sudo -u postgres find /var/lib/pgsql/9.6/data -mindepth 1 -delete
# Full restore
sudo -u postgres pgbackrest --stanza=data restore
...
# ... or recovery to specific time
sudo -u postgres pgbackrest --stanza=data --delta --recovery-option='recovery_target_time=2018-01-15 13:00' restore