How to use pgbackrest to bootstrap or add replica to HA Patroni

Products

• VMware Tanzu Greenplum

Issue/Introduction

By default, Patroni uses initdb to create a new Postgres instance (bootstrapping) or pg_basebackup to create a new replica based on whether $PGDATA location already exists. It is also possible to configure Patroni to use pgBackrest to manage a cluster from backup sets. This scenario becomes very useful if you want to create test instances on the fly from a dedicated repository.

This article builds on the configs in this article: How to configure a Patroni Cluster to use pgbackrest. If pgbackrest hasn't been configured yet, then please go through that article first.

Note: In this article, we will attempt to restore a backup from a cluster that stored its backup in stanza=main. Later on, we will create another stanza in order to add replicas after the new cluster is bootstrapped. We will be using PGDATA=/var/lib/pgsql/data_backrest as the new data directory for the newly bootstrapped cluster.

Environment

Product Version: 11.7

Resolution

We will do the following with these additional assumptions:

• Patroni and postgres have not yet been created.
• Targeted $PGDATA should not exist at this point.
• You have access to the dedicated repository of another cluster in which a full backup was taken (stanza=main).
• 110.193.102.51 = the dedicated repository
• 192.168.99.110, 192.168.99.111, 192.168.99.112 = new postgres hosts
• etcd / zookeeper is already configured and working

/etc/pgbackrest/pgbackrest.conf should be set to the following in these hosts:

Dedicated repository:

[test]
pg1-path=/var/lib/pgsql/data_backrest
pg1-port=5532
pg1-host=192.168.99.110
pg1-socket-path=/var/lib/pgsql/data_backrest
pg2-path=/var/lib/pgsql/data_backrest
pg2-port=5532
pg2-host=192.168.99.111
pg2-socket-path=/var/lib/pgsql/data_backrest
pg3-path=/var/lib/pgsql/data_backrest
pg3-port=5532
pg3-host=192.168.99.112
pg3-socket-path=/var/lib/pgsql/data_backrest

[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

All Postgres hosts:

[test]
pg1-path=/var/lib/pgsql/data_backrest
pg1-socket-path=/var/lib/pgsql/data_backrest
pg1-port=5532

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

Bootstrapping a new Patroni Cluster

In the Patroni yaml file, you will have to add this to the primary Patroni node. You can add it to the yaml files of all the Patroni nodes for completeness, but it will only be used in one of the hosts:

bootstrap:
  method: pgbackrest
  pgbackrest:
      command: /var/lib/pgsql/custom_bootstrap.sh
      keep_existing_recovery_conf: True
      no_params: False
      recovery_conf:
          recovery_target_timeline: latest
          recovery_target_action: promote
          restore_command: pgbackrest --stanza=main --pg1-path=/var/lib/pgsql/data_backrest archive-get %f %p

Create /var/lib/pgsql/custom_bootstrap.sh:

#!/bin/sh
mkdir -p /var/lib/pgsql/data_backrest
pgbackrest --stanza=main --pg1-path=/var/lib/pgsql/data_backrest --log-level-console=info --delta restore 

Note: You only have to add --pg1-path if your new PGDATA does not match the path from the old path which in our case was /home/postgres/data.

Add execution permission to the script:

chmod +x /var/lib/pgsql/custom_bootstrap.sh 

Startup Patroni:

patroni postgres_backrest_0.yml > patroni_backrest_0.log 2>&1

Checking the patroni_backrest_0.log, we see the following:

2020-08-08 19:00:20,790 INFO: Selected new etcd server http://192.168.99.112:2379
2020-08-08 19:00:20,795 INFO: No PostgreSQL configuration items changed, nothing to reload.
2020-08-08 19:00:20,801 INFO: Lock owner: None; I am postgresql1
2020-08-08 19:00:20,807 INFO: trying to bootstrap a new cluster
2020-08-08 19:00:20,808 INFO: Running custom bootstrap script: /var/lib/pgsql/custom_bootstrap.sh
2020-08-08 19:00:20.817 P00   INFO: restore command begin 2.23: --delta --log-level-console=info --log-level-file=detail --pg1-path=/var/lib/pgsql/data_backrest --repo1-host=192.168.99.109 --repo1-host-user=postgres --stanza=prod
WARN: --delta or --force specified but unable to find 'PG_VERSION' or 'backup.manifest' in '/var/lib/pgsql/data_backrest' to confirm that this is a valid $PGDATA directory.  --delta and --force have been disabled and if any files exist in the destination directories the restor
e will be aborted.
2020-08-08 19:00:21.179 P00   INFO: restore backup set 20200808-184402F
2020-08-08 19:00:21.179 P00   INFO: remap data directory to '/var/lib/pgsql/data_backrest'
2020-08-08 19:00:21.542 P01   INFO: restore file /var/lib/pgsql/data_backrest/pg_log/postgresql-2020-07-24_000000.log (3.8MB, 8%) checksum 6b47282f5b46515ea509bc8eef90a7d5783d5094
2020-08-08 19:00:21.566 P01   INFO: restore file /var/lib/pgsql/data_backrest/base/16387/16391 (3.5MB, 17%) checksum aae2c08d13294ae409b8098cd8d30c63417035ee
2020-08-08 19:00:21.588 P01   INFO: restore file /var/lib/pgsql/data_backrest/base/16387/16388 (3.5MB, 25%) checksum 55c941682dfced6b3185eb4beaa730620ac0300f
2020-08-08 19:00:21.595 P01   INFO: restore file /var/lib/pgsql/data_backrest/base/16387/1255 (608KB, 26%) checksum d60bbdabd07c989cc4fd7b4b2b03d5e61499808d
....
2020-08-08 19:00:23.859 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2020-08-08 19:00:24.179 P00   INFO: restore command end: completed successfully (3363ms)
2020-08-08 19:00:24.383 UTC [4473] LOG:  listening on IPv4 address "0.0.0.0", port 5532
2020-08-08 19:00:24.383 UTC [4473] LOG:  listening on IPv6 address "::", port 5532
2020-08-08 19:00:24.384 UTC [4473] LOG:  listening on Unix socket "./.s.PGSQL.5532"
2020-08-08 19:00:24.392 UTC [4473] LOG:  redirecting log output to logging collector process
2020-08-08 19:00:24.392 UTC [4473] HINT:  Future log output will appear in directory "pg_log".
2020-08-08 19:00:24,394 INFO: postmaster pid=4473
localhost:5532 - rejecting connections
localhost:5532 - rejecting connections
localhost:5532 - rejecting connections
localhost:5532 - accepting connections
2020-08-08 19:00:26,431 INFO: establishing a new patroni connection to the postgres cluster
2020-08-08 19:00:26,440 INFO: waiting for end of recovery after bootstrap
2020-08-08 19:00:36,458 INFO: running post_bootstrap
server signaled
2020-08-08 19:00:37,486 WARNING: Could not activate Linux watchdog device: "Can't open watchdog device: [Errno 2] No such file or directory: '/dev/watchdog'"
2020-08-08 19:00:37,487 INFO: establishing a new patroni connection to the postgres cluster
2020-08-08 19:00:37,500 INFO: initialized a new cluster
2020-08-08 19:00:47,483 INFO: Lock owner: postgresql1; I am postgresql1
2020-08-08 19:00:47,491 INFO: Lock owner: postgresql1; I am postgresql1

We see that the restore was successful and Patroni has acquired the leader lock. You can login to psql and check if your data is there. As of this step you have successfully bootstrapped a single node Patroni instance.

psql -h localhost -p 5532

Adding a replica:

1. Create a new stanza called "test" for the Patroni instance in the previous step in the dedicated repository:

pgbackrest --stanza=test --log-level-console=info stanza-create

2. Create a full backup:

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

In your Postgres replica host, add the following section in your yaml file:

postgresql:
  create_replica_methods:
        - pgbackrest
  pgbackrest:
      command: /bin/pgbackrest --pg1-path=/var/lib/pgsql/data --stanza=test --delta restore
      keep_data: True
      no_params: True

In order for the replica to create successfully, we have to create an empty data directory:

mkdir -p /var/lib/pgsql/data_backrest
chmod 700 data_backrest
patroni postgres_backrest_1.yml > patroni_backrest_1.log 2>&1 &

After starting Patroni, check patroni_backrest_1.log to make sure it recognizes itself as the secondary:

2020-08-08 18:55:17,033 INFO: Selected new etcd server http://192.168.99.112:2379
2020-08-08 18:55:17,038 INFO: No PostgreSQL configuration items changed, nothing to reload.
2020-08-08 18:55:17,045 INFO: Lock owner: postgresql1; I am postgresql2
2020-08-08 18:55:17,049 INFO: trying to bootstrap from leader 'postgresql1'
WARN: --delta or --force specified but unable to find 'PG_VERSION' or 'backup.manifest' in '/var/lib/pgsql/data_backrest' to confirm that this is a valid $PGDATA directory.  --delta and --force have been disabled and if any files exist in the destination directories the restore will be aborted.
2020-08-08 18:55:20,294 INFO: replica has been created using pgbackrest
2020-08-08 18:55:20,295 INFO: bootstrapped from leader 'postgresql1'
2020-08-08 18:55:20.505 UTC [5454] LOG:  listening on IPv4 address "0.0.0.0", port 5532
2020-08-08 18:55:20.505 UTC [5454] LOG:  listening on IPv6 address "::", port 5532
2020-08-08 18:55:20.507 UTC [5454] LOG:  listening on Unix socket "./.s.PGSQL.5532"
2020-08-08 18:55:20.519 UTC [5454] LOG:  redirecting log output to logging collector process
2020-08-08 18:55:20.519 UTC [5454] HINT:  Future log output will appear in directory "pg_log".
2020-08-08 18:55:20,523 INFO: postmaster pid=5454
localhost:5532 - rejecting connections
localhost:5532 - rejecting connections
localhost:5532 - rejecting connections
localhost:5532 - rejecting connections
localhost:5532 - rejecting connections
localhost:5532 - rejecting connections
localhost:5532 - rejecting connections
localhost:5532 - accepting connections
2020-08-08 18:55:26,623 INFO: Lock owner: postgresql1; I am postgresql2
2020-08-08 18:55:26,623 INFO: does not have lock
2020-08-08 18:55:26,623 INFO: establishing a new patroni connection to the postgres cluster
2020-08-08 18:55:26,638 INFO: no action.  i am a secondary and i am following a leader
2020-08-08 18:55:31,745 INFO: Lock owner: postgresql1; I am postgresql2
2020-08-08 18:55:31,745 INFO: does not have lock

The final state should look like this:

[postgres@postgres-1 ~]$ patronictl -c postgres2_node1_pgbackrest3.yml  list
+-------------------+-------------+---------------------+--------+---------+----+-----------+
|      Cluster      |    Member   |         Host        |  Role  |  State  | TL | Lag in MB |
+-------------------+-------------+---------------------+--------+---------+----+-----------+
| patroni_backrest3 | postgresql1 | 192.168.99.110:5532 | Leader | running |  8 |           |
| patroni_backrest3 | postgresql2 | 192.168.99.111:5532 |        | running |  8 |         0 |
+-------------------+-------------+---------------------+--------+---------+----+-----------+

Additional info: It is possible to configure Patroni to use pgBackrest as the primary method but have it fall back to pg_basebackup if it fails. You would just make the postgresql section look like this:

postgresql:
    create_replica_methods:
        - pgbackrest
        - basebackup
    pgbackrest:
        command: /usr/bin/pgbackrest --stanza=<scope> --delta restore
        keep_data: True
        no_params: True
    basebackup:
        max-rate: '100M'