pgbackrest is open source solution for PostgreSQL backup. It has capability to perform full database backup, incremental backup, differential backup. It is very robust tool to perform full cluster recovery, particular database restore/recovery or point in time recovery. With pgbackrest, you can specify backup retention and purging policy. If your cluster size is huge and you need to restore a particular database, pgbackrest can achieve the objective easily.

In this blog, we will go through pgbackrest installation for Fujitsu Enterprise Postgres version 9.5 on RHEL 7 for pgbackrest 2.02. I believe same steps can be applied to other version of PostgreSQL OSS and Linux without any significant change.

 

Install pre-requisite O.S packages –

 

pgbackrest Installation require PostgreSQL perl Interface. You can use yum repository to download and install it.

yum install perl-DBD-Pg

yum install openssl-devel

 

Download software –

Create directory to download software

# mkdir –p /software/ pgbackrest2.02 ## to download software in this dir

Download pgbackrest software package.

# wget -q -O – https://github.com/pgbackrest/pgbackrest/archive/release/2.02.tar.gz |  tar zx -C /software/pgbackrest2.02

 

 

pgbackrest installation –

 

cp -r /software/pgbackrest2.02/pgbackrest-release-2.02/lib/pgBackRest        /usr/share/perl5

 

find /usr/share/perl5/pgBackRest -type f -exec chmod 644 {} +

find /usr/share/perl5/pgBackRest -type d -exec chmod 755 {} +

mkdir -m 770 /var/log/pgbackrest

chown postgres:postgres /var/log/pgbackrest

mkdir /etc/pgbackrest

mkdir /etc/pgbackrest/conf.d

touch /etc/pgbackrest/pgbackrest.conf

chmod 640 /etc/pgbackrest/pgbackrest.conf

chown postgres:postgres /etc/pgbackrest/pgbackrest.conf

 

 

sh -c ‘cd /software/pgbackrest2.02/pgbackrest-release-2.02/libc && perl Makefile.PL INSTALLMAN1DIR=none INSTALLMAN3DIR=none’

make -C /software/pgbackrest2.02/pgbackrest-release-2.02/libc test

make -C /software/pgbackrest2.02/pgbackrest-release-2.02/libc install

 

 

Configure pgbackrest

 

Configure /etc/pgbackrest/pgbackrest.conf

 

You will require to provide location of Fujitsu Enterprise Postgres cluster to create corresponding stanza. Under global stanza, you will need to provide directory location for backup repository (repo-path).

Create /etc/pgbackrest/pgbackrest.conf file and add below mentioned lines in it.

[PD2]

db-path=/database/inst1

 

[global]

repo-path=/var/lib/pgbackrest

 

Note:    PD2 is stanza name

/database/inst1 is Fujitsu Enterprise Postgres cluster directory.

 

Configure postgresql.conf with below mentioned parameters.

 

archive_command = ‘pgbackrest –stanza=PD2 archive-push %p’
archive_mode = on
listen_addresses = ‘*’
log_line_prefix = ”
max_wal_senders = 3
wal_level = hot_standby

 

 

 

Some other useful parameters which you can add in /etc/pgbackrest/pgbackrest.conf

 [global]

repo-path=/database/inst1_pgbackrest_repo

repo1-retention-full=1

repo1-retention-diff=1

start-fast=y

stop-auto=y

 

[global:archive-push]

compress-level=3

 

repo-path : defines the directory path to store postgresql cluster backup.

repo1-retention-full : defines the retention policy for full backup

repo1-retention-diff : defines the retention policy for diff backup

start-fast : Postgresql will not wait for auto checkpoint to complete but will execute checkpoint before starting postgresql cluster backup.

stop-auto : Stop postgresql backup in case of any failure.

 

 

Create Stanza

 

Once you have added stanza entry in /etc/pgbackrest/pgbackrest.conf, you are ready to create stanza by executing below mentioned command.

 

pgbackrest –stanza=PD2 –log-level-console=detail –db-port=5102  stanza-create

 

Note: –db-port is postgresql cluster port.

–stanza is stanza name as defined in /etc/pgbackrest/pgbackrest.conf

–log-level-console: it may be detail or info depending upon level of output needed.

Perform Database Cluster backup:

 

With pgbackrest, you can perform full cluster hot backup, cluster differential backup or cluster incremental backup. You can define backup retention policy in /etc/pgbackrest/pgbackrest.conf.

 

Cluster Fullbackup

 

[testuser@myserver database]$  pgbackrest –stanza=PD2 –log-level-console=detail –db-port=5102 –type=full backup

2018-06-12 12:13:15.435 P00   INFO: backup command begin 2.02: –log-level-console=detail –pg1-path=/database/inst1 –pg1-port=5102 –repo1-path=/database/inst1_pgbackrest_repo –repo1-retention-diff=1 –repo1-retention-full=1 –stanza=PD2 –start-fast –stop-auto –type=full

2018-06-12 12:13:16.377 P00   INFO: execute non-exclusive pg_start_backup() with label “pgBackRest backup started at 2018-06-12 12:13:15”: backup begins after the requested immediate checkpoint completes

2018-06-12 12:13:16.680 P00   INFO: backup start archive = 000000040000000000000057, lsn = 0/57000028

.

.

 

 

 

Cluster Differential backup:

Backup of all changes since last full backup. If you have huge database with very high volume of transactions per day then combination of full backup, differential backup and incremental backup may constitute the best backup policy.

 

[testuser@myserver database]$  pgbackrest –stanza=PD2 –log-level-console=detail –db-port=5102 –type=diff backup

2018-06-12 12:17:03.369 P00   INFO: backup command begin 2.02: –log-level-console=detail –pg1-path=/database/inst1 –pg1-port=5102 –repo1-path=/database/inst1_pgbackrest_repo –repo1-retention-diff=1 –repo1-retention-full=1 –stanza=PD2 –start-fast –stop-auto –type=diff

2018-06-12 12:17:04.520 P00   INFO: last backup label = 20180612-121315F, version = 2.02

2018-06-12 12:17:05.241 P00   INFO: execute non-exclusive pg_start_backup() with label “pgBackRest backup started at 2018-06-12 12:17:03”: backup begins after the requested immediate checkpoint completes

2018-06-12 12:17:05.545 P00   INFO: backup start archive = 000000040000000000000059, lsn = 0/59000028

2018-06-12 12:17:13.162 P01   INFO: backup file /database/inst1/pg_log/postgresql-2018-06-12_101201.log (41KB, 83%) checksum d853d8f16d72263e4df4f3010d537b580cc73a4e

2018-06-12 12:17:13.172 P01   INFO: backup file /database/inst1/global/pg_control (8KB, 100%) checksum 793560b1a8b4ea1be1d23d5bfe4dfd56820d79fd

2018-06-12 12:17:13.384 P00   INFO: diff backup size = 49KB

2018-06-12 12:17:13.384 P00   INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive

2018-06-12 12:17:14.495 P00   INFO: backup stop archive = 000000040000000000000059, lsn = 0/59000130

2018-06-12 12:17:14.506 P00 DETAIL: wrote ‘pg_data/backup_label’ file returned from pg_stop_backup()

2018-06-12 12:17:14.514 P00 DETAIL: wrote ‘pg_data/tablespace_map’ file returned from pg_stop_backup()

2018-06-12 12:17:15.739 P00   INFO: new backup label = 20180612-121315F_20180612-121703D

2018-06-12 12:17:15.998 P00   INFO: backup command end: completed successfully

2018-06-12 12:17:15.998 P00   INFO: expire command begin

2018-06-12 12:17:16.014 P00 DETAIL: archive retention on backup 20180612-121315F, archiveId = 9.6-1, start = 000000040000000000000057

2018-06-12 12:17:16.014 P00 DETAIL: no archive to remove, archiveId = 9.6-1

2018-06-12 12:17:16.015 P00   INFO: expire command end: completed successfully

 

 

Cluster Incremental backup:

Backup of all changes since last incremental or differential or full backup.

 

If you have huge database with normal transaction rate, then having weekly full backup and daily incremental backup may the best as it will decrease the backup window time during business days.

 

[testuser@myserver database]$  pgbackrest –stanza=PD2–log-level-console=detail –db-port=5102 –type=incr backup

2018-06-12 12:18:09.419 P00   INFO: backup command begin 2.02: –log-level-console=detail –pg1-path=/database/inst1 –pg1-port=5102 –repo1-path=/database/inst1_pgbackrest_repo –repo1-retention-diff=1 –repo1-retention-full=1 –stanza=PD2 –start-fast –stop-auto –type=incr

2018-06-12 12:18:10.804 P00   INFO: last backup label = 20180612-121315F_20180612-121703D, version = 2.02

2018-06-12 12:18:11.517 P00   INFO: execute non-exclusive pg_start_backup() with label “pgBackRest backup started at 2018-06-12 12:18:09”: backup begins after the requested immediate checkpoint completes

2018-06-12 12:18:11.718 P00   INFO: backup start archive = 00000004000000000000005B, lsn = 0/5B000028

2018-06-12 12:18:18.644 P01   INFO: backup file /database/inst1/pg_log/postgresql-2018-06-12_101201.log (42.7KB, 84%) checksum 5677cb75002427afd2ea1aea5ce9f4eb1c9e2056

2018-06-12 12:18:18.651 P01   INFO: backup file /database/inst1/global/pg_control (8KB, 100%) checksum 589429cb25571823ca2d8b7f215f5fdf3d0e3612

2018-06-12 12:18:18.845 P00   INFO: incr backup size = 50.7KB

2018-06-12 12:18:18.846 P00   INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive

2018-06-12 12:18:19.956 P00   INFO: backup stop archive = 00000004000000000000005B, lsn = 0/5B000130

2018-06-12 12:18:19.965 P00 DETAIL: wrote ‘pg_data/backup_label’ file returned from pg_stop_backup()

2018-06-12 12:18:19.972 P00 DETAIL: wrote ‘pg_data/tablespace_map’ file returned from pg_stop_backup()

2018-06-12 12:18:21.131 P00   INFO: new backup label = 20180612-121315F_20180612-121809I

2018-06-12 12:18:21.371 P00   INFO: backup command end: completed successfully

2018-06-12 12:18:21.371 P00   INFO: expire command begin

2018-06-12 12:18:21.385 P00 DETAIL: archive retention on backup 20180612-121315F, archiveId = 9.6-1, start = 000000040000000000000057

2018-06-12 12:18:21.386 P00 DETAIL: no archive to remove, archiveId = 9.6-1

2018-06-12 12:18:21.387 P00   INFO: expire command end: completed successfully

 

 

Review Backup information

 

Pgbackrest provide very simple interface to view backup information.

 

[fsepuser@c7m1 inst3]$ pgbackrest info

stanza: PD2

status: ok

 

db (current)

wal archive min/max (9.6-1): 00000004000000000000000A / 000000060000000000000018

 

full backup: 20180618-150724F

timestamp start/stop: 2018-06-18 15:07:24 / 2018-06-18 15:07:35

wal start/stop: 00000004000000000000000A / 00000004000000000000000A

database size: 36.3MB, backup size: 36.3MB

repository size: 4.2MB, repository backup size: 4.2MB

 

diff backup: 20180618-150724F_20180618-161158D

timestamp start/stop: 2018-06-18 16:11:58 / 2018-06-18 16:12:02

wal start/stop: 000000060000000000000018 / 000000060000000000000018

database size: 36.3MB, backup size: 1MB

repository size: 4.2MB, repository backup size: 95.9KB

backup reference list: 20180618-150724F

 

stanza: PD3

status: error (no valid backups)

 

db (current)

wal archive min/max (9.6-1): none present

 

stanza: demo

status: ok

 

db (current)

wal archive min/max (9.6-1): 0000000C0000000000000064 / 0000000C0000000000000066

 

full backup: 20180612-141234F

timestamp start/stop: 2018-06-12 14:12:34 / 2018-06-12 14:13:19

wal start/stop: 0000000C0000000000000064 / 0000000C0000000000000064

database size: 149.5MB, backup size: 149.5MB

repository size: 8.9MB, repository backup size: 8.9MB

 

You can also filter the output for particular cluster by using –stanza option.

[fsepuser@c7m1 inst3]$ pgbackrest info –stanza=PD2

stanza: PD2

status: ok

 

db (current)

wal archive min/max (9.6-1): 00000004000000000000000A / 000000060000000000000018

 

full backup: 20180618-150724F

timestamp start/stop: 2018-06-18 15:07:24 / 2018-06-18 15:07:35

wal start/stop: 00000004000000000000000A / 00000004000000000000000A

database size: 36.3MB, backup size: 36.3MB

repository size: 4.2MB, repository backup size: 4.2MB

 

diff backup: 20180618-150724F_20180618-161158D

timestamp start/stop: 2018-06-18 16:11:58 / 2018-06-18 16:12:02

wal start/stop: 000000060000000000000018 / 000000060000000000000018

database size: 36.3MB, backup size: 1MB

repository size: 4.2MB, repository backup size: 95.9KB

backup reference list: 20180618-150724F

 

Pgbackrest 2.02 Installation & backup PostgreSQL Cluster