pgBackRest is an open source backup tool for PostgreSQL which offers easy configuration and reliable backups. So if you want to protect your database and create backups easily, pgBackRest is a good solution to make that happen. In this blog, we are going to go through the basic steps of using pgBackRest for full and differential backup of PostgreSQL.
Some of the key features of pgBackRest are:
- Parallel backup & restore
- Local and remote operations
- Full, incremental & differential backups
- Backup rotation & archive expiration
- Backup integrity
- Page checksums
- Resume backups
- Streaming compression and checksums
To read about them more in detail, please visit pgbackrest.org.
Here, we are going to build pgBackRest from the source and install it on the host where a test DB cluster is running.

Installation:
Installing from Debian / Ubuntu packages:
sudo apt-get install pgbackrest
For manual installation, download the source on a build host. Please avoid building the source on a production server, as the tools required should not be installed on a production machine:
Download pgBackRest Version 2.14:
sudo wget -q -O - \ https://github.com/pgbackrest/pgbackrest/archive/release/2.14.tar.gz | \ sudo tar zx -C /root
Install the dependencies and check for 64-bit integers:
sudo apt-get install build-essential libssl-dev \ libxml2-dev libperl-dev zlib1g-dev perl -V | grep USE_64_BIT_INT
Build pgbackrest package:
(cd /root/pgbackrest-release-2.14/src && ./configure) make -s -C /root/pgbackrest-release-2.14/src
Copy from build host to DB host:
sudo scp BUILD_HOST:/root/pgbackrest-release-2.14/src/pgbackrest /usr/bin/ sudo chmod 755 /usr/bin/pgbackrest
Install Perl packages:
sudo apt-get install libdbd-pg-perl
pgBackRest config files & directories on DB test host:
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
Create repository for pgBackRest
sudo mkdir -p /var/lib/pgbackrest sudo chmod 750 /var/lib/pgbackrest sudo chown postgres:postgres /var/lib/pgbackrest
Set the Repo path and PostgreSQL cluster directory in the pgbackrest file:
cat /etc/pgbackrest/pgbackrest.conf [demo] pg1-path=/data/postgres/pgdata/data1 [global] repo1-path=/var/lib/pgbackrest
Configure archiving on the PostgreSQL cluster:
Change the following parameters in postgresql.conf
:
archive_command = 'pgbackrest --stanza=demo archive-push %p' archive_mode = on listen_addresses = '*' log_line_prefix = '' max_wal_senders = 3 wal_level = replica
Create stanza as postgres
user:
The stanza-create
command must be run on the host where the repository is located to initialize the stanza. It is recommended that the check
command be run after stanza-create
to ensure archiving and backups are properly configured.
$ pgbackrest --stanza=demo --log-level-console=info stanza-create 2019-07-03 12:26:40.060 P00 INFO: stanza-create command begin 2.14: --log-level-console=info --pg1-path=/data/postgres/pgdata/data1 --repo1-path=/var/lib/pgbackrest --stanza=demo 2019-07-03 12:26:40.494 P00 INFO: stanza-create command end: completed successfully (435ms) $ pgbackrest --stanza=demo --log-level-console=info check 2019-07-03 12:27:11.996 P00 INFO: check command begin 2.14: --log-level-console=info --pg1-path=/data/postgres/pgdata/data1 --repo1-path=/var/lib/pgbackrest --stanza=demo 2019-07-03 12:27:13.386 P00 INFO: WAL segment 000000010000000000000003 successfully stored in the archive at '/var/lib/pgbackrest/archive/demo/10-1/0000000100000000/000000010000000000000003-b346d07d4b31e54e31d9204204816cde3cfcca3a.gz' 2019-07-03 12:27:13.387 P00 INFO: check command end: completed successfully (1392ms)
Use the info
command to get information about the backup
Since we haven’t made any backups yet, we will get the following result:
$ pgbackrest info stanza: demo status: error (no valid backups) cipher: none db (current) wal archive min/max (10-1): 000000010000000000000001/000000010000000000000003
Backup:
Let’s make the first backup. By default, it will be full even if we specify type
as differential:
$ pgbackrest --stanza=demo --log-level-console=info backup 2019-07-03 12:37:38.366 P00 INFO: backup command begin 2.14: --log-level-console=info --pg1-path=/data/postgres/pgdata/data1 --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=demo WARN: no prior backup exists, incr backup has been changed to full 2019-07-03 12:37:39.200 P00 INFO: execute non-exclusive pg_start_backup() with label "pgBackRest backup started at 2019-07-03 12:37:38": backup begins after the next regular checkpoint completes 2019-07-03 12:37:39.500 P00 INFO: backup start archive = 000000010000000000000005, lsn = 0/5000028 . . . 2019-07-03 12:37:45.212 P00 INFO: full backup size = 22.5MB 2019-07-03 12:37:45.212 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive 2019-07-03 12:37:45.313 P00 INFO: backup stop archive = 000000010000000000000005, lsn = 0/5000130 2019-07-03 12:37:45.558 P00 INFO: new backup label = 20190703-123738F 2019-07-03 12:37:45.586 P00 INFO: backup command end: completed successfully (7221ms) 2019-07-03 12:37:45.586 P00 INFO: expire command begin 2019-07-03 12:37:45.594 P00 INFO: full backup total < 2 - using oldest full backup for 10-1 archive retention 2019-07-03 12:37:45.596 P00 INFO: expire command end: completed successfully (10ms)
Now when we run the info
command again:
$ pgbackrest info stanza: demo status: ok cipher: none db (current) wal archive min/max (10-1): 000000010000000000000005/000000010000000000000005 full backup: 20190703-123738F timestamp start/stop: 2019-07-03 12:37:38 / 2019-07-03 12:37:45 wal start/stop: 000000010000000000000005 / 000000010000000000000005 database size: 22.6MB, backup size: 22.6MB repository size: 2.7MB, repository backup size: 2.7MB
Take a differential backup of the cluster
$ pgbackrest --stanza=demo --log-level-console=info --type=diff backup 2019-07-03 12:40:05.749 P00 INFO: backup command begin 2.14: --log-level-console=info --pg1-path=/data/postgres/pgdata/data1 --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=demo --type=diff 2019-07-03 12:40:05.951 P00 INFO: last backup label = 20190703-123738F, version = 2.14 2019-07-03 12:40:06.657 P00 INFO: execute non-exclusive pg_start_backup() with label "pgBackRest backup started at 2019-07-03 12:40:05": backup begins after the next regular checkpoint completes 2019-07-03 12:40:06.958 P00 INFO: backup start archive = 000000010000000000000007, lsn = 0/7000028 2019-07-03 12:40:08.414 P01 INFO: backup file /data/postgres/pgdata/data1/global/pg_control (8KB, 99%) checksum c8b3635ef4701b19bff56fcd5ca33d41eaf3ce5b 2019-07-03 12:40:08.421 P01 INFO: backup file /data/postgres/pgdata/data1/pg_logical/replorigin_checkpoint (8B, 100%) checksum 347fc8f2df71bd4436e38bd1516ccd7ea0d46532 2019-07-03 12:40:08.439 P00 INFO: diff backup size = 8KB 2019-07-03 12:40:08.439 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive 2019-07-03 12:40:08.540 P00 INFO: backup stop archive = 000000010000000000000007, lsn = 0/70000F8 2019-07-03 12:40:08.843 P00 INFO: new backup label = 20190703-123738F_20190703-124005D 2019-07-03 12:40:08.938 P00 INFO: backup command end: completed successfully (3189ms) 2019-07-03 12:40:08.938 P00 INFO: expire command begin 2019-07-03 12:40:08.949 P00 INFO: full backup total < 2 - using oldest full backup for 10-1 archive retention 2019-07-03 12:40:08.951 P00 INFO: expire command end: completed successfully (13ms)
To see the changes, check info
:
$ pgbackrest info stanza: demo status: ok cipher: none db (current) wal archive min/max (10-1): 000000010000000000000005/000000010000000000000007 full backup: 20190703-123738F timestamp start/stop: 2019-07-03 12:37:38 / 2019-07-03 12:37:45 wal start/stop: 000000010000000000000005 / 000000010000000000000005 database size: 22.6MB, backup size: 22.6MB repository size: 2.7MB, repository backup size: 2.7MB diff backup: 20190703-123738F_20190703-124005D timestamp start/stop: 2019-07-03 12:40:05 / 2019-07-03 12:40:08 wal start/stop: 000000010000000000000007 / 000000010000000000000007 database size: 22.6MB, backup size: 8.2KB repository size: 2.7MB, repository backup size: 468B backup reference list: 20190703-123738F
If you want to learn more about how to protect data, check out my blog on PostgreSQL TDE (“Transparent Data Encryption”) .
Also if you want to make sure that your database performs well, check out our blog posts on PostgreSQL performance.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.