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:

http://www.pgbackrest.org

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
CODE


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
CODE

Configure PostgreSQL:

This is automated by ansible.

Edit the 'postgresql.conf' file:

vi /var/lib/pgsql/9.6/data/postgresql.conf
CODE

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
CODE

Restart the database (root user)

systemctl restart postgresql-9.6 systemctl status postgresql-9.6
CODE

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'
CODE

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'
CODE

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'
CODE

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
CODE


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
CODE

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
CODE

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
CODE 


# 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
CODE