Fun with pgbackrest

Fun with pgbackrest

pgbackrest is an amazing tool that saves entire PostgreSQL clusters with ease.

In this post we’ll go trough the configuration of the backup using a remote backup server.

For the extensive documentation and detailed instructions please refer to the user guide.

operating system configuration

For our example we’ll use two virtual machines on virtualbox with devuan 1.0 Jessie installed.

Each machine is configured in the same way.

  • 1 cpu
  • 1 GB ram
  • Hard disk of 12 GB with single partition ext4
  • sshd up and running

The machines have one bridged network adapter eth0. The network configuration is using a static ip address.

#db01
allow-hotplug eth0
iface eth0 inet static
    address 192.168.1.21
    netmask 255.255.255.0
    gateway 192.168.1.1

#backupsrv
allow-hotplug eth0
iface eth0 inet static
    address 192.168.1.22
    netmask 255.255.255.0
    gateway 192.168.1.1

pgbackrest and PostgreSQL installation

Devuan Jessie is compatible with the pgdg repositories which configuration is very well explained on https://wiki.postgresql.org/wiki/Apt. The only difference is that we need to use the root user without sudo because Devuan doesn’t ship sudo by default.

Alternatively you may install sudo before proceeding.

apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
apt-get update
apt-get install pgbackrest postgresql-10 postgresql-client-10 postgresql-contrib-10

ssh configuration

Because we don’t have a for name resolution it’s a good idea to configure the hosts file on both machine in order to use names instead of ip addresses.

#db01
127.0.0.1       localhost
127.0.1.1       db01
192.168.1.22    backupsrv


#backupsrv
127.0.0.1       localhost
127.0.1.1       backupsrv
192.168.1.21    db01

On both hosts db01 and backupsrv we need to setup the cross ssh login without password for the os user postgres.

pgbackrest configuration

Now we can configure pgbackrest on both database and backup server.

The default configuration file is /etc/pgbackrest.conf

configuration for the database host db01

[db01]
db-path=/var/lib/postgresql/10/main
db-port=5432


[global]
backup-host=backupsrv
backup-user=postgres
backup-cmd=/usr/bin/pgbackrest

Quoting the pgbackrest user manual

A stanza is the configuration for a PostgreSQL database cluster that defines where it is located, how it will be backed up, archiving options, etc. Most db servers will only have one Postgres database cluster and therefore one stanza, whereas backup servers will have a stanza for every database cluster that needs to be backed up.

In the database host’s configuration file we define the stanza db01 which lists only the database path and port. The global section sets the location of the backup-host along with the username which is used for accessing the backup-host via ssh. In global we also set location of the pgbackrest executable on the remote server.

configuration for the backup server backupsrv

[db01]
db-path=/var/lib/postgresql/10/main
db-port=5432
db-host=db01
retention-full=1

[global]
repo-path=/var/lib/postgresql/pgbackrest
backup-user=postgres
backup-cmd=/usr/bin/pgbackrest

We still have the stanza db01 but along with the data area there are also the database host and port. The parameter retention-full tells pgbackrest the amount of full backups should be kept before the expiration.

The global section lists the repo-path location and the the backup-user and backup-cmd necessary to access the remote server if needed.

We are now ready to initialise the stanza db01 on backupsrv.

postgres@backupsrv:~$ mkdir /var/lib/postgresql/pgbackrest
postgres@backupsrv:~$ pgbackrest stanza-create --stanza db01 --log-level-console=info
2018-01-27 21:08:10.999 P00   INFO: stanza-create command begin 1.27: --db1-host=db01 --db1-path=/var/lib/postgresql/10/main --db1-port=5432 --log-level-console=info --repo-path=/var/lib/postgresql/pgbackrest --stanza=db01
2018-01-27 21:08:12.050 P00   INFO: stanza-create command end: completed successfully

PostgreSQL configuration

We now need to configure PostgreSQL for the physical backup.

We need to change the wal_level to replica, enable the archive mode and set the archive command to pgbackrest specifying the stanza’s name and the command archive-push followed by the placeholder for the archived wal file %p.

We are also changing the max_wal_senders to 3. This will be useful if we want to setup a standby server with the streaming replication.

ALTER SYSTEM SET max_wal_senders=3;
ALTER SYSTEM SET wal_level='replica';
ALTER SYSTEM SET archive_mode='on';
ALTER SYSTEM SET listen_addresses = '*';
ALTER SYSTEM SET archive_command = 'pgbackrest --stanza=db01 archive-push %p';

We now shall restart postgres to apply the changes and verify that pgbackrest works properly with the check command.

postgres@db01:~$ pg_ctlcluster 10 main stop -m fast
postgres@db01:~$ pg_ctlcluster 10 main start
postgres@db01:~$ pgbackrest check --stanza db01 --log-level-console=info
2018-01-27 21:09:55.859 P00   INFO: check command begin 1.27: --backup-cmd=/usr/bin/pgbackrest --backup-host=backupsrv --backup-user=postgres --db1-path=/var/lib/postgresql/10/main --db1-port=5432 --log-level-console=info --repo-path=/var/lib/postgresql/pgbackrest --stanza=db01
2018-01-27 21:09:57.682 P00   INFO: WAL segment 000000010000000000000002 successfully stored in the archive at '/var/lib/postgresql/pgbackrest/archive/db01/10-1/0000000100000000/000000010000000000000002-803e4b17ddc85d8aa2263f44bb1085f4a229de40.gz'
2018-01-27 21:09:57.693 P00   INFO: check command end: completed successfully

If pgbackrest is correctly configured the previous command will work on db01 and backupsrv.

performing the backup

We are now ready to to backup the database. Let’s login on backupsrv as postgres user.

pgbackrest backup --stanza db01 --log-level-console=info

The last command of the shell cast is pgbackrest info, which displays the backup status with some useful statistics like the total backup size and the compressed size within the repository.

wrap up

This basic tutorial shows a very simple way to setup a backup on a remote server. The thin I love most of pgbackrest is that the repository is self contained and allows a great flexibility when running a restore.

In the next post we’ll see the different backup strategies offered by pgbackrest and how to perform a restore.

Thanks for reading

Framed, copyright Federico Campoli

Federico Campoli avatar
About Federico Campoli
Federico is a database administrator and an amateur python developer. He started his career as Oracle DBA in 2004 and fell in love with PostgreSQL in 2007.
comments powered by Disqus
Percona Live Europe 2018, Open Source Database Conference, Frankfurt 5-7 November 2018