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
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
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
backupsrv we need to setup the cross ssh login without password for the os user postgres.
Now we can configure pgbackrest on both database and backup server.
The default configuration file is
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.
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
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
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.
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