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:

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:

CODE

yum install -y pgbackrest

By default, pgBackRest tool configuration file(pgbackrest.conf) location is /etc and add below entry into /etc/pgbackrest.conf file:

CODE

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:

CODE

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

Add the following lines to the 'postgresql.conf' file:

CODE

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)

CODE

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:

CODE

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:

CODE

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:

CODE

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:

CODE

pgbackrest --stanza data info

Backup directory location

CODE

-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:

CODE

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

CODE

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