I see more and more questions about pgBackRest in a Patroni cluster on community channels. So, following yesterday’s post about Patroni on pure Raft, we’ll see in this post an example about how to setup pgBackRest in such cases.
To prepare this post, I followed most of the instructions given by Federico Campoli at PGDAY RUSSIA 2021 about Protecting your data with Patroni and pgbackrest. The video recording might even be found here.
pgBackRest repository location
The first decision you’ll have to take is to know where to store your pgBackRest repository. It can be any supported repo-type with or without a dedicated backup server (aka. repo-host). The most important thing to remember is that the repository should be equally reachable from all your PostgreSQL/Patroni nodes.
Based on that decision, you’ll have to think about how to schedule the backup command. With a repo-host that’s easy, because pgBackRest will be able to determine which PostgreSQL node is the primary (or a standby if you want to take the backup from a standby node). If you’re using a directly attached storage (i.e. NFS mount point or S3 bucket), the easiest solution might be to test the return of pg_is_in_recovery() in the cron job.
For the purpose of this demo setup, I’ll use a MinIO docker container with self-signed certificates and a bucket named pgbackrest already created.
Installation
pgBackRest installation and configuration
Install pgBackRest on all the PostgreSQL nodes:
$ sudo dnf install -y epel-release
$ sudo dnf install -y pgbackrest
$ pgbackrest version
pgBackRest 2.39
Then, configure /etc/pgbackrest.conf:
$ cat <<EOF | sudo tee /etc/pgbackrest.conf
[global]
repo1-type=s3
repo1-storage-verify-tls=n
repo1-s3-endpoint=192.168.121.1
repo1-s3-uri-style=path
repo1-s3-bucket=pgbackrest
repo1-s3-key=minioadmin
repo1-s3-key-secret=minioadmin
repo1-s3-region=eu-west-3
repo1-path=/repo1
repo1-retention-full=2
start-fast=y
log-level-console=info
log-level-file=debug
delta=y
process-max=2
[demo-cluster-1]
pg1-path=/var/lib/pgsql/14/data
pg1-port=5432
pg1-user=postgres
EOF
In the previous post, we defined the cluster name to use in the scope configuration of Patroni. We’ll reuse the same name for the stanza name.
Let’s initialize the stanza:
$ sudo -iu postgres pgbackrest --stanza=demo-cluster-1 stanza-create
INFO: stanza-create command begin 2.39: ...
INFO: stanza-create for stanza 'demo-cluster-1' on repo1
INFO: stanza-create command end: completed successfully (684ms)
Configure Patroni to use pgBackRest
Let’s adjust the archive_command in Patroni configuration:
$ sudo -iu postgres patronictl -c /etc/patroni.yml edit-config
## adjust the following lines
postgresql:
parameters:
archive_command: pgbackrest --stanza=demo-cluster-1 archive-push "%p"
archive_mode: "on"
$ sudo -iu postgres patronictl -c /etc/patroni.yml reload demo-cluster-1
Check that the archiving system is working:
$ sudo -iu postgres pgbackrest --stanza=demo-cluster-1 check
INFO: check command begin 2.39: ...
INFO: check repo1 configuration (primary)
INFO: check repo1 archive for WAL (primary)
INFO: WAL segment 000000010000000000000004 successfully archived to '...' on repo1
INFO: check command end: completed successfully (1083ms)
Take a first backup
Let’s not take our first full backup:
$ sudo -iu postgres pgbackrest --stanza=demo-cluster-1 backup --type=full
P00 INFO: backup command begin 2.39: ...
P00 INFO: execute non-exclusive pg_start_backup():
backup begins after the requested immediate checkpoint completes
P00 INFO: backup start archive = 000000010000000000000006, lsn = 0/6000028
P00 INFO: check archive for prior segment 000000010000000000000005
P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
P00 INFO: backup stop archive = 000000010000000000000006, lsn = 0/6000100
P00 INFO: check archive for segment(s) 000000010000000000000006:000000010000000000000006
P00 INFO: new backup label = 20220711-075256F
P00 INFO: full backup size = 25.2MB, file total = 957
P00 INFO: backup command end: completed successfully
All Patroni nodes should now be able to see the content of the pgBackRest repository:
$ pgbackrest info
stanza: demo-cluster-1
status: ok
cipher: none
db (current)
wal archive min/max (14): 000000010000000000000006/000000010000000000000006
full backup: 20220711-075256F
timestamp start/stop: 2022-07-11 07:52:56 / 2022-07-11 07:53:02
wal start/stop: 000000010000000000000006 / 000000010000000000000006
database size: 25.2MB, database backup size: 25.2MB
repo1: backup set size: 3.2MB, backup size: 3.2MB
The “restore” part
Now that the archiving system is working, we can configure the restore_command. Possibly, we could disable replication slots for Patroni too since we now have the archives as safety net for the replication.
Let’s edit the bootstrap configuration part:
$ sudo -iu postgres patronictl -c /etc/patroni.yml edit-config
## adjust the following lines
loop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:
parameters:
archive_command: pgbackrest --stanza=demo-cluster-1 archive-push "%p"
archive_mode: 'on'
recovery_conf:
recovery_target_timeline: latest
restore_command: pgbackrest --stanza=demo-cluster-1 archive-get %f "%p"
use_pg_rewind: false
use_slots: true
retry_timeout: 10
ttl: 30
$ sudo -iu postgres patronictl -c /etc/patroni.yml reload demo-cluster-1
To use pgBackRest for creating (or re-initializing) replicas, we need to adjust the Patroni configuration file.
On all your nodes, in /etc/patroni.yml, find the following part:
postgresql:
listen: "0.0.0.0:5432"
connect_address: "$MY_IP:5432"
data_dir: /var/lib/pgsql/14/data
bin_dir: /usr/pgsql-14/bin
pgpass: /tmp/pgpass0
authentication:
replication:
username: replicator
password: confidential
superuser:
username: postgres
password: my-super-password
rewind:
username: rewind_user
password: rewind_password
parameters:
unix_socket_directories: '/var/run/postgresql,/tmp'
and add:
create_replica_methods:
- pgbackrest
- basebackup
pgbackrest:
command: pgbackrest --stanza=demo-cluster-1 restore --type=none
keep_data: True
no_params: True
basebackup:
checkpoint: 'fast'
Don’t forget to reload the configuration:
$ sudo systemctl reload patroni
Create a replica using pgBackRest
Here is our current situation:
$ sudo -iu postgres patronictl -c /etc/patroni.yml list
+--------+-----------------+---------+---------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+ Cluster: demo-cluster-1 (7119014497759128647) ----+-----------+
| srv1 | 192.168.121.2 | Replica | running | 1 | 0 |
| srv2 | 192.168.121.254 | Leader | running | 1 | |
| srv3 | 192.168.121.89 | Replica | running | 1 | 0 |
+--------+-----------------+---------+---------+----+-----------+
We already have 2 running replicas. So we’ll need to stop Patroni on one node and remove its data directory to trigger a new replica creation:
$ sudo systemctl stop patroni
$ sudo rm -rf /var/lib/pgsql/14/data
$ sudo systemctl start patroni
$ sudo journalctl -u patroni.service -f
...
INFO: trying to bootstrap from leader 'srv2'
...
INFO: replica has been created using pgbackrest
INFO: bootstrapped from leader 'srv2'
...
INFO: no action. I am (srv3), a secondary, and following a leader (srv2)
As we can see from the logs above, the replica has successfully been created using pgBackRest:
$ sudo -iu postgres patronictl -c /etc/patroni.yml list
+--------+-----------------+---------+---------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+ Cluster: demo-cluster-1 (7119014497759128647) ----+-----------+
| srv1 | 192.168.121.2 | Replica | running | 1 | 0 |
| srv2 | 192.168.121.254 | Leader | running | 1 | |
| srv3 | 192.168.121.89 | Replica | running | 1 | 0 |
+--------+-----------------+---------+---------+----+-----------+
Now, let’s insert some data on srv2 and simulate an incident by suspending the VM network interface for a few seconds.
A failover will happen and the old primary will be completely out-of-sync and the replication will be lagging when adding new data on the primary:
$ sudo -iu postgres patronictl -c /etc/patroni.yml list
+--------+-----------------+---------+---------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+ Cluster: demo-cluster-1 (7119014497759128647) ----+-----------+
| srv1 | 192.168.121.2 | Leader | running | 2 | |
| srv2 | 192.168.121.254 | Replica | running | 1 | 188 |
| srv3 | 192.168.121.89 | Replica | running | 2 | 0 |
+--------+-----------------+---------+---------+----+-----------+
Since we didn’t configure pg_rewind, we’ll need to re-initialize the failing node manually:
$ sudo -iu postgres patronictl -c /etc/patroni.yml reinit demo-cluster-1 srv2
+--------+-----------------+---------+---------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+ Cluster: demo-cluster-1 (7119014497759128647) ----+-----------+
| srv1 | 192.168.121.2 | Leader | running | 2 | |
| srv2 | 192.168.121.254 | Replica | running | 1 | 188 |
| srv3 | 192.168.121.89 | Replica | running | 2 | 0 |
+--------+-----------------+---------+---------+----+-----------+
Are you sure you want to reinitialize members srv2? [y/N]: y
Success: reinitialize for member srv2
$ sudo -iu postgres patronictl -c /etc/patroni.yml list
+--------+-----------------+---------+---------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+ Cluster: demo-cluster-1 (7119014497759128647) ----+-----------+
| srv1 | 192.168.121.2 | Leader | running | 2 | |
| srv2 | 192.168.121.254 | Replica | running | 2 | 0 |
| srv3 | 192.168.121.89 | Replica | running | 2 | 0 |
+--------+-----------------+---------+---------+----+-----------+
The following trace shows that pgBackRest has successfully been used to re-initialize the old primary:
INFO: replica has been created using pgbackrest
INFO: bootstrapped from leader 'srv1'
...
INFO: Lock owner: srv1; I am srv2
INFO: establishing a new patroni connection to the postgres cluster
INFO: no action. I am (srv2), a secondary, and following a leader (srv1)
Conclusion
It is very easy and convenient to configure Patroni to use pgBackRest. Obviously, having backups is a good thing but being able to use those backups as source for the replica creation or re-initialization is even better.
As usual, the hardest part is to clearly define where to store the pgBackRest repository.
Tweet