How to configure a Patroni Cluster to use pgbackrest

Products

• VMware Tanzu Greenplum

Issue/Introduction

pgBackrest is the recommended tool to use for backup / restore in a VMware Tanzu Postgres database. Steps to install for single-instance Postgres are straightforward and can be found in the pgBackrest documentation: https://pgbackrest.org/user-guide.html

The setup gets a little more complicated if you try to install and configure pgBackrest in a HA Patroni cluster. This article will cover what is needed to get this up and running.

Q: What happens if the primary postgres host changes between backups?
A: pgBackrest will automatically determine which postgres instance is primary and will take the backup accordingly from there.

Note: In this setup we will be using a 2-node Patroni cluster with a dedicated host for the backup repository. This configuration is not a production setup and is meant to demonstrate the minimal settings to get the backup and restore working.

postgres_node_1 - 10.193.102.51 - pgBackrest repository
postgres_node_2 - 10.193.102.52 - Postgres instance 1
postgres_node_3 - 10.193.102.53 - Postgres instance 2

Environment

Product Version: 11.7

Resolution

Before installing pgBackrest, if not already configured, we need to make these changes to the Patroni yaml files:

• enable archive logging
• listen_addresses = '*'

postgresql:
  listen: "*:5532"

parameters:
    archive_mode: "on"
    archive_command: 'pgbackrest --stanza=main archive-push %p'

Changing listen to "0.0.0.0:5532" will also achieve the same thing. Restart Patroni in both hosts for the changes to take effect. To verify, you should be able to login with localhost now:

[postgres@postgres_node_2 ~]$ psql -h localhost -p 5532
psql (11.7 (VMware Postgres 11.7.3))
Type "help" for help.

postgres=# show listen_addresses;
 listen_addresses 
------------------
 *
(1 row)

postgres=# show archive_command;
             archive_command              
------------------------------------------
 pgbackrest --stanza=main archive-push %p
(1 row)

1. Install these prerequisite packages on all 3 hosts using sudo or root user:

yum install perl perl-Time-HiRes perl-Digest-SHA perl-JSON-PP

Note: pgbackrest is shipped with VMware Postgres so there's no need to install it again.

2. As root user, create these directories and files in all 3 hosts:

mkdir -p /etc/pgbackrest
mkdir -p /etc/pgbackrest/conf.d
mkdir -p /var/log/pgbackrest
touch /etc/pgbackrest/pgbackrest.conf
chmod 640 /etc/pgbackrest/pgbackrest.conf
chmod 770 /var/log/pgbackrest
chown postgres:postgres /etc/pgbackrest/pgbackrest.conf
chown postgres:postgres /var/log/pgbackrest

Note: For simplicity, we are using postgres user for the dedicated repository, but it is recommended to create and use a separate pgbackrest user in the dedicated repository host (10.193.102.51).

3. Exchange ssh keys to allow password-less login between all 3 hosts. We will assume in this article that .ssh directory does not exist in postgres user's home directory:

mkdir .ssh
chmod 700 .ssh
ssh-keygen -f /home/postgres/.ssh/id_rsa -t rsa -b 4096 -N ""
cd .ssh
touch authorized_keys
chmod 640 authorized_keys

Now copy the contents of ~/.ssh/id_rsa.pub into ~/.ssh/authorized_keys on the other 2 hosts that you want to ssh into. Each host will have it's own id_rsa.pub. The step above needs to be done on all 3 hosts. It is very important that these specific permissions on the files and .ssh folder are set otherwise it won't work.

4. On the repository host as postgres user (10.193.102.51), create repository location and configure pgbackrest.conf:

mkdir -p /var/lib/pgbackrest
chmod 750 /var/lib/pgbackrest

/etc/pgbackrest/pgbackrest.conf:

[main]
pg1-path=/home/postgres/data
pg1-port=5532
pg1-host=10.193.102.52
pg1-socket-path=/home/postgres/data
pg2-path=/home/postgres/data
pg2-port=5532
pg2-host=10.193.102.53
pg2-socket-path=/home/postgres/data

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
start-fast=y

This file defines where the postgres instances are located, how it is archived, and how it is backed up. It is known as a stanza. In our example "main" is going to be our stanza name when we create it later. Here are the options we are using:

pg1-path - postgres data directory
pg1-path-socket-path - Location of .s.PGSQL.5532 socket file (show unix_socket_directories)
repo1-path - location of the archive and backup files
repo1-retention-fill - number of full backups to retain
start-fast - immediate checkpoint is requested and the backup will start more quickly.

Also note that pg parameters are indexed by postgres instance number. In our example we have 2 postgres instances so we have two sets of parameters. You can also specify multiple repo[n]_path locations, but that is beyond the scope of this article. 

5. On each postgres host (10.193.102.52 and 10.193.102.53), configure the pgbackrest.conf:

vi /etc/pgbackrest/pgbackrest.conf
[main]
pg1-path=/home/postgres/data
pg1-socket-path=/home/postgres/data
pg1-port=5532

[global]
log-level-file=detail
repo1-host=10.193.102.51
repo1-host-user=postgres

6. Create the stanza on the repository host (only needs to be done on this host):

[postgres@postgres_node_1 ~]$ pgbackrest --stanza=main --log-level-console=info stanza-create
2020-06-29 21:25:44.990 P00   INFO: stanza-create command begin 2.23: --log-level-console=info --pg1-host=10.193.102.52 --pg2-host=10.193.102.53 --pg1-path=/home/postgres/data --pg2-path=/home/postgres/data --pg1-port=5532 --pg2-port=5532 --pg1-socket-path=/home/postgres/data --pg2-socket-path=/home/postgres/data --repo1-path=/var/lib/pgbackrest --stanza=main
2020-06-29 21:25:46.547 P00   INFO: stanza-create command end: completed successfully (1558ms)

7. Check the config on each of the two postgres hosts:

[postgres@postgres_node_1 ~]$ patronictl -c postgres2.yml list
+---------------+-------------+--------------------+--------+---------+----+-----------+
|    Cluster    |    Member   |        Host        |  Role  |  State  | TL | Lag in MB |
+---------------+-------------+--------------------+--------+---------+----+-----------+
| patroni_test2 | postgresql1 | 10.193.102.52:5532 | Leader | running | 15 |           |
| patroni_test2 | postgresql2 | 10.193.102.53:5532 |        | running | 15 |         0 |
+---------------+-------------+--------------------+--------+---------+----+-----------+


[postgres@postgres_node_2 ~]$ pgbackrest --stanza=main --log-level-console=info check
2020-06-29 21:26:53.635 P00   INFO: check command begin 2.23: --log-level-console=info --log-level-file=detail --pg1-path=/home/postgres/data --pg1-port=5532 --pg1-socket-path=/home/postgres/data --repo1-host=10.193.102.51 --repo1-host-user=postgres --stanza=main
2020-06-29 21:26:54.910 P00   INFO: WAL segment 0000000F000000000000001F successfully archived to '/var/lib/pgbackrest/archive/main/11-1/0000000F00000000/0000000F000000000000001F-4de42917877fb7bad0bd26634b7c8a4da15ff544.gz'
2020-06-29 21:26:55.010 P00   INFO: check command end: completed successfully (1376ms)

[postgres@postgres_node_3 ~]$ pgbackrest --stanza=main --log-level-console=info check
2020-06-29 21:26:58.727 P00   INFO: check command begin 2.23: --log-level-console=info --log-level-file=detail --pg1-path=/home/postgres/data --pg1-port=5532 --pg1-socket-path=/home/postgres/data --repo1-host=10.193.102.51 --repo1-host-user=postgres --stanza=main
2020-06-29 21:26:59.404 P00   INFO: switch wal not performed because this is a standby
2020-06-29 21:26:59.504 P00   INFO: check command end: completed successfully (778ms)

Note the difference in output. The WAL log will only be archived from the primary.

8. Finally, let's create a backup (must be run from repository host):

[postgres@postgres_node_1 11-1]$ pgbackrest --log-level-console=info --stanza=main backup
2020-06-29 21:39:26.632 P00   INFO: backup command begin 2.23: --log-level-console=info --pg1-host=10.193.102.52 --pg2-host=10.193.102.53 --pg1-path=/home/postgres/data --pg2-path=/home/postgres/data --pg1-port=5532 --pg2-port=5532 --pg1-socket-path=/home/postgres/data --pg2-socket-path=/home/postgres/data --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=main --start-fast
WARN: no prior backup exists, incr backup has been changed to full
2020-06-29 21:39:28.113 P00   INFO: execute non-exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes
2020-06-29 21:39:28.818 P00   INFO: backup start archive = 0000000F0000000000000021, lsn = 0/21000028
WARN: exclude special file '/home/postgres/data/.s.PGSQL.5532' from backup
2020-06-29 21:39:29.545 P01   INFO: backup file 10.193.102.52:/home/postgres/data/base/13881/1255 (608KB, 2%) checksum 620bf66f88a71b955da0a18a61d66c1cf72e12e4
2020-06-29 21:39:29.600 P01   INFO: backup file 10.193.102.52:/home/postgres/data/base/13880/1255 (608KB, 5%) checksum 620bf66f88a71b955da0a18a61d66c1cf72e12e4
2020-06-29 21:39:29.632 P01   INFO: backup file 10.193.102.52:/home/postgres/data/base/1/1255 (608KB, 7%) checksum 620bf66f88a71b955da0a18a61d66c1cf72e12e4
2020-06-29 21:39:29.659 P01   INFO: backup file 10.193.102.52:/home/postgres/data/base/13881/2608 (448KB, 9%) checksum 9e4dd031c5008b4adff3d2959970bd0f8aef8626
2020-06-29 21:39:29.684 P01   INFO: backup file 10.193.102.52:/home/postgres/data/base/13880/2608 (448KB, 11%) checksum e4b9f0822f3db90062db9a7cc52582e2914d0494
....
2020-06-29 21:39:44.013 P01   INFO: backup file 10.193.102.52:/home/postgres/data/base/1/13733 (0B, 100%)
2020-06-29 21:39:44.020 P01   INFO: backup file 10.193.102.52:/home/postgres/data/base/1/13728 (0B, 100%)
2020-06-29 21:39:44.027 P01   INFO: backup file 10.193.102.52:/home/postgres/data/base/1/13723 (0B, 100%)
2020-06-29 21:39:44.046 P01   INFO: backup file 10.193.102.52:/home/postgres/data/base/1/13718 (0B, 100%)
2020-06-29 21:39:44.048 P00   INFO: full backup size = 23.6MB
2020-06-29 21:39:44.048 P00   INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2020-06-29 21:39:44.351 P00   INFO: backup stop archive = 0000000F0000000000000021, lsn = 0/21000130
2020-06-29 21:39:44.478 P00   INFO: check archive for segment(s) 0000000F0000000000000021:0000000F0000000000000021
2020-06-29 21:39:44.627 P00   INFO: new backup label = 20200629-213928F
2020-06-29 21:39:44.869 P00   INFO: backup command end: completed successfully (18238ms)
2020-06-29 21:39:44.869 P00   INFO: expire command begin 2.23: --log-level-console=info --pg1-host=10.193.102.52 --pg2-host=10.193.102.53 --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=main
2020-06-29 21:39:44.876 P00   INFO: full backup total < 2 - using oldest full backup for 11-1 archive retention
2020-06-29 21:39:45.177 P00   INFO: expire command end: completed successfully (308ms)

If we check the repository location, you will see the backup files:

[postgres@postgres_node_1 11-1]$ cd /var/lib/pgbackrest/
[postgres@postgres_node_1 pgbackrest]$ ls
archive  backup
[postgres@postgres_node_1 pgbackrest]$ cd backup
[postgres@postgres_node_1 backup]$ ls
main
[postgres@postgres_node_1 backup]$ cd main
[postgres@postgres_node_1 main]$ ls
20200629-213928F  backup.history  backup.info  backup.info.copy  latest
[postgres@postgres_node_1 main]$ cd 20200629-213928F/
[postgres@postgres_node_1 20200629-213928F]$ ls
backup.manifest  backup.manifest.copy  pg_data
[postgres@postgres_node_1 20200629-213928F]$ cd pg_data/
[postgres@postgres_node_1 pg_data]$ ls
backup_label.gz      global                   pg_dynshmem            pg_ident.conf.backup.gz  pg_logical    pg_replslot   pg_stat      pg_tblspc      pg_wal                   postgresql.base.conf.backup.gz  postgresql.conf.gz
base                 patroni.dynamic.json.gz  pg_hba.conf.backup.gz  pg_ident.conf.gz         pg_multixact  pg_serial     pg_stat_tmp  pg_twophase    pg_xact                  postgresql.base.conf.gz
current_logfiles.gz  pg_commit_ts             pg_hba.conf.gz         pg_log                   pg_notify     pg_snapshots  pg_subtrans  PG_VERSION.gz  postgresql.auto.conf.gz  postgresql.conf.backup.gz

Deleting a Stanza:
1. Stop pgbackrest. There is no active daemon for pgbackrest but this command will prevent any future backups from launching.

[postgres@postgres_node_1 ~]$ pgbackrest --stanza=main --log-level-console=info stop
2020-06-29 21:20:31.853 P00   INFO: stop command begin 2.23: --log-level-console=info --pg1-host=10.193.102.52 --pg2-host=10.193.102.53 --repo1-path=/var/lib/pgbackrest --stanza=main
2020-06-29 21:20:31.853 P00   INFO: stop command end: completed successfully (1ms)

2. Stop all Patroni nodes
3. Delete the stanza

postgres@postgres_node_1 ~]$ pgbackrest --stanza=main --log-level-console=info stanza-delete
2020-06-29 21:24:14.864 P00   INFO: stanza-delete command begin 2.23: --log-level-console=info --pg1-host=10.193.102.52 --pg2-host=10.193.102.53 --pg1-path=/home/postgres/data --pg2-path=/home/postgres/data --pg1-port=5532 --pg2-port=5532 --pg1-socket-path=/home/postgres/data --pg2-socket-path=/home/postgres/data --repo1-path=/var/lib/pgbackrest --stanza=main
2020-06-29 21:24:15.172 P00   INFO: stanza-delete command end: completed successfully (309ms)