Posted on by Granthana Biswas

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:

  1. Parallel backup & restore
  2. Local and remote operations
  3. Full, incremental & differential backups
  4. Backup rotation & archive expiration
  5. Backup integrity
  6. Page checksums
  7. Resume backups
  8. 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.

Granthana Biswas

My passion for databases had me working as an Oracle and MySQL DBA after graduating with a Bachelor's degree in Computer Science from the University of Pune - until I was introduced to PostgreSQL in 2010. Ever since then, I have worked extensively on PostgreSQL databases. I like the simplicity behind PostgreSQL administration and the various open source tools that add useful features to PostgreSQL.