Viswamitra's blog

PGbackrest is one of the simple and reliable backup tools. Below are the some of the interesting features of it.

1. Supports parallel backup and restore
   • This will reduce the time duration to take the backup and do the restoration
   • This can leverage the multi core CPU available in the database server.
2. Local or Remote operation
   • We can backup, restore and archive locally or remotely using SSH.
3. Full, Incremental & Differential Backups
   • This tool supports the incremental and differential backups which not available by default in PostgreSQL.
4. Backup Rotation & Archive Expiration
   • This automatically maintain the retention period for the backups and required archive logs.
5. Backup Integrity
   • Check-sums are calculated for every file in the backup and rechecked during a restore. After a backup finishes copying files, it waits until every WAL segment required to make the backup consistent reaches the repository.
6. Backup Resume
   • An aborted backup can be resumed from the point where it was stopped. Files that were already copied are compared with the check-sums in the manifest to ensure integrity. Since this operation can take place entirely on the backup server, it reduces load on the database server and saves time since checksum calculation is faster than compressing and re-transmitting data.
7. Streaming Compression and Check-sums
   • Compression and checksum calculations are performed in stream while files are being copied to the repository, whether the repository is located locally or remotely.
8. S3-Compatible object store support
   • pgBackRest repositories can be located in S3-compatible object stores to allow for virtually unlimited capacity and retention.
9. Encryption
   • PGBackRest can encrypt the repository to secure backups wherever they are stored.

Installation using source code

Install prerequisites: 
sudo yum install make gcc openssl-devel libxml2-devel lz4-devel
Install pgbackrest using source code:
wget https://github.com/pgbackrest/pgbackrest/archive/release/2.25.tar.gz

tar xvf 2.25.tar.gz
cd pgbackrest-release-2.25/src/
export PATH=/usr/pgsql-9.3/bin:$PATH
./configure
make
make install
chmod 755 /usr/bin/pgbackrest

PGBackRest requires log and configuration directories and a configuration file. Create the folder structure

sudo mkdir -p -m 770 /var/log/pgbackrest
sudo chown postgres:postgres /var/log/pgbackrest
sudo mkdir -p /etc/pgbackrest
sudo mkdir -p /etc/pgbackrest/conf.d
sudo touch /etc/pgbackrest/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf

Configure Cluster Stanza

Stanza is the configuration of the PostgreSQL database cluster whose backups need to be taken, where to store the backup, archive logs and other information. Each database cluster will have a stanza.

Name the stanza in way that represents the purpose of the database cluster like the application name or client name etc. In future we will use this name to take backups and perform restoration of the cluster.

Open the configuration file “/etc/pgbackrest/pgbackrest.conf” and add stanza details.

[epos]
# Database server details
pg1-host= 192.168.159.129
pg1-port=5493
# Data directory location whose backup we need to take
pg1-path=/var/lib/pgsql/9.3/data
# IF your socket is different than /var/lib/postgresql
pg1-socket-path=/tmp
[global]
# Set log level
log-level-file=detail
# Repository location where the backups and archive logs will be stored
repo1-path=/var/lib/pgbackrest
# Encrypt the backups(optional)
repo1-cipher-pass=zWaf6XtpjIVZC5444yXB+cgFDFl7MxGlgkZSaoPvTGirhPygu4jOKOXf9LO4vjfO
repo1-cipher-type=aes-256-cbc
# Backup retention (2 full backups)
repo1-retention-full=2
# Start backup by doing manual checkpoint without waiting for scheduled checkpoint
start-fast=y
# Execute pg_stop_backup command after a failure restart of backup
stop-auto=y
# Ser parallel processes to take the backup
process-max=3
# Per process memory for performing the compression, copy and uncompress
buffer-size=256K
# Compress the backup files
compress=y
# Set the compression level
compress-level=9

Configure PostgreSQL Cluster

wal_level=hot_standby
archive_mode=on
archive_command='pgbackrest --stanza=epos archive-push %p'
max_wal_senders=10

Create Stanza

The stanza create command must be run on the host where the repository is located to initialize the stanza. It is recommended to run check command to verify the configuration.

-bash-4.2$ pgbackrest --stanza=epos --log-level-console=info stanza-create
2020-04-10 07:26:30.597 P00   INFO: stanza-create command begin 2.25: --buffer-size=262144 --log-level-console=info --log-level-file=detail --pg1-path=/var/lib/pgsql/9.3/data --pg1-port=5493 --pg1-socket-path=/tmp --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --stanza=epos
2020-04-10 07:26:53.424 P00   INFO: stanza-create command end: completed successfully (22830ms)

Check the configuration

The check command validates that pgBackRest and the archive_command setting are configured correctly for archiving and backups. It detects misconfigurations, particularly in archiving, that result in incomplete backups because required WAL segments did not reach the archive. 

-bash-4.2$ pgbackrest --stanza=epos --log-level-console=info check
2020-04-10 07:30:20.305 P00   INFO: check command begin 2.25: --buffer-size=262144 --log-level-console=info --log-level-file=detail --pg1-path=/var/lib/pgsql/9.3/data --pg1-port=5493 --pg1-socket-path=/tmp --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --stanza=epos
LOG:  restore point "pgBackRest Archive Check" created at 0/229D2B78
STATEMENT:  select pg_catalog.pg_create_restore_point('pgBackRest Archive Check')::text
2020-04-10 07:30:35.414 P00   INFO: WAL segment 000000010000000000000022 successfully archived to '/var/lib/pgbackrest/archive/epos/9.3-1/0000000100000000/000000010000000000000022-23eacd87c2f3cab204b492ad6367065bc37f426b.gz'
2020-04-10 07:30:35.414 P00   INFO: check command end: completed successfully (15112ms)

Perform the cluster backup

Configuration of pgbackrest is completed, now we can start taking backups.

pgbackrest --stanza=epos --log-level-console=info –type=full backup

If you want to take differential or incremental backups use the “type=diff/incr”

Check the backups

To check the backups

pgbackrest --stanza=epos info

Restore backup

To restore the backup

pgbackrest --stanza=epos restore

This will restore the location you gave in the pgbackrest configuration. If you want to restore the backup to some other location

pgbackrest --stanza=epos --pg1-path=/tmp/epos restore

To perform the point in time recovery use below command

pgbackrest --stanza=epos --delta --type=time "--target=2020-04-10 07:49:23.713846-07" --target-action=promote --pg1-path=/tmp/epos restore

Stop and Start Stanza

Sometimes we need to stop pgbackrest from running like during time of switchover to standby.

# This command will stop all the backups of clusters configured
pgbackrest stop
# To stop backups of a single stanza
pgbackrest stop --stanza epos
# To start pgbackrest
pgbackrest start

Delete Stanza

To delete the Stanza configuration

# Stop the PostgreSQL cluster
pg_ctl -D /tmp/epos stop
# Stop the stanza
pgbackrest --stanza=epos --log-level-console=info stop
# Delete the stanza
pgbackrest --stanza=epos --log-level-console=info stanza-delete