Patroni is one of the most used high availability (HA) solutions with the PostgreSQL database. It uses a Distributed Configuration Store (DCS) to keep the configuration in a centralized location available for all nodes making it an easy-to-use and reliable HA solution available in the market today.
On the other hand, pgBackRest is a backup solution that helps in taking not only the FULL backup but also incremental and differential backup. This is one of the most used backup tool used for PostgreSQL databases.
In the previous blogs, we have discussed how to set up PostgreSQL HA with Patroni, and how to configure pgBackRest. Both these open source tools have been saviors for DBAs for achieving high availability and performing restoration in ample ways. In this blog, we will integrate both of these tools and understand how they can work together to reduce the server load.
Scenarios:
Pre-configured setup:
For the purpose of testing these scenarios, the below configurations will be used throughout this blog:
Patroni Nodes:
|
1 |
+ Cluster: prod (7171021941707843784) ----+-----------+<br>| Member | Host | Role | State | TL | Lag in MB |<br>+--------+-------+---------+---------+----+-----------+<br>| node1 | node1 | Leader | running | 1 | |<br>| node2 | node2 | Replica | running | 1 | 0 |<br>+--------+-------+---------+---------+----+-----------+ |
Patronictl edit-config shows below:
|
1 |
loop_wait: 10<br>maximum_lag_on_failover: 1048576<br>postgresql:<br> create_replica_methods:<br> - pgbackrest<br> - basebackup<br> parameters:<br> archive_command: pgbackrest --stanza=patroni archive-push %p<br> archive_mode: 'on'<br> archive_timeout: 120s<br> hot_standby: 'on'<br> listen_addresses: '*'<br> logging_collector: 'on'<br> max_replication_slots: 10<br> max_wal_senders: 10<br> pg_hba:<br> - host all all 0.0.0.0/0 md5<br> - host replication all 0.0.0.0/0 md5<br> - local all postgres peer<br> wal_level: replica<br> wal_log_hints: 'on'<br> pgbackrest:<br> command: /usr/bin/pgbackrest --stanza=patroni --log-level-file=detail --delta restore<br> keep_data: true<br> no_params: true<br> recovery_conf:<br> restore_command: pgbackrest --stanza=patroni archive-get %f %p<br> use_pg_rewind: true<br> use_slots: true<br>retry_timeout: 10<br>ttl: 30<br> |
Note: Please check the highlighted sections, which are specific for rebuilding nodes using pgBackRest backup.
Apart from Patroni, we will need a Backup Repo host where pgBackRest has been configured. It can be on a dedicated server or one of the DB hosts. However, it is recommended to use a dedicated server as in case DB goes down, we have a separate server to make the life of the DBAs easier.
Let’s test the scenarios one by one:
The main advantage of using pgBackRest instead of pgBaseBackup while reinitializing the node is that it’ll reduce the load from the leader node. This will not make any difference if the DB size is smaller. However, this feature is very useful in case the DB size is huge and it takes hours or days to build the node. This will divert the resource utilization on the dedicated backup host instead of the primary server, which anyways is busy fulfilling the majority of the requests coming to the database.
Let us try to understand how we can rebuild the node using backup.
Many times, we are unable to start the secondary nodes after failover or switchover. To handle this situation, Patroni allows us to reinitialize the database cluster, which will create/rebuild the node by wiping the data directory. In the background, it will copy all the contents of the data directory from the Primary Server and re-create the desired node.
Please make the changes in the Patroni configuration/yml file and reload the configuration, as shown previously. To reinitialize the Patroni replica node, the reinit command is used as below:
|
1 |
ubuntu@192.168.0.1:~$ patronictl -c /etc/patroni/node1.yml reinit prod<br>+ Cluster: prod (7171021941707843784) ----+-----------+<br>| Member | Host | Role | State | TL | Lag in MB |<br>+--------+-------+---------+---------+----+-----------+<br>| node1 | node1 | Leader | running | 1 | |<br>| node2 | node2 | Replica | running | 1 | 0 |<br>+--------+-------+---------+---------+----+-----------+<br>Which member do you want to reinitialize [node1, node2]? []: node2<br>Are you sure you want to reinitialize members node2? [y/N]: y<br>Success: reinitialize for member node2 |
On the replica node, we can notice in the top command that it is rebuilding the node using pgBackRest backup and not pgBackRest. Ideally, Patroni uses pgBaseBackup in case create_replica_methods is not used which increases the load on the leader node.
|
1 |
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND<br> 12939 postgres 20 0 306440 268140 4 S 34.9 27.1 8871:14 GkwP468a<br> 791850 postgres 20 0 218692 29544 26820 S 8.9 3.0 0:00.28 /usr/lib/postgresql/14/bin/postgres -D /var/lib/postgresql/14/main --config-file=/var/lib/postgresql/14/main/postgresql.conf --listen_ad+<br> 791881 postgres 20 0 60980 12696 10628 S 3.2 1.3 0:00.10 pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=patroni archive-get 00000013.history pg_wal/RECOVERYHISTORY<br> 791874 postgres 20 0 218692 8032 5260 S 2.2 0.8 0:00.07 postgres: prod: startup<br> 791827 postgres 20 0 7760 3516 3212 R 1.3 0.4 0:00.04 bash<br> 784973 postgres 20 0 23316 212 0 S 0.3 0.0 0:18.42 tracepath |
In case it is using pgBackRest, then it will create a restore file mentioning pgBackRest command as below:
|
1 |
2022-12-15 15:41:29.070 P00 INFO: restore command begin 2.41: --config=/etc/pgbackrest/pgbackrest.conf --delta --exec-id=791815-82f4ea68 --log-level-console=info --log-level-file=detail --pg1-path=/var/lib/postgresql/14/main --process-max=2 --repo1-host=192.168.0.5 --repo1-host-user=postgres --repo1-path=/pgrdbackups --stanza=patroni<br>2022-12-15 15:41:30.800 P00 INFO: repo1: restore backup set 20221213-154604F, recovery will start at 2022-12-13 15:46:04<br>2022-12-15 15:41:30.877 P00 DETAIL: check '/var/lib/postgresql/14/main' exists<br>2022-12-15 15:41:30.877 P00 DETAIL: remove 'global/pg_control' so cluster will not start if restore does not complete<br>2022-12-15 15:41:30.918 P00 INFO: remove invalid files/links/paths from '/var/lib/postgresql/14/main'<br>2022-12-15 15:41:30.919 P00 DETAIL: remove invalid file '/var/lib/postgresql/14/main/backup_label.old'<br>2022-12-15 15:41:31.841 P00 DETAIL: remove invalid file '/var/lib/postgresql/14/main/base/13761/pg_internal.init'<br>2022-12-15 15:41:31.920 P00 DETAIL: remove invalid file '/var/lib/postgresql/14/main/global/pg_internal.init' |
In case the bootstrap section contains code to rebuild the node using pgBackRest, then while adding the node in the already existing Patroni cluster, the first time building of the new node will use pgBackRest backup instead of pgBaseBackup. Also, point-in-time recovery can be done using the bootstrap section. This will help in not only reducing the load from the leader node but also the backup node will help restore the data with comparatively lesser resource utilization.
To configure the same, please use the below in the Patroni configuration file:
|
1 |
bootstrap:<br> method: <custom_bootstrap_method_name><br> <custom_bootstrap_method_name>:<br> command: <path_to_custom_bootstrap_script> [param1 [, ...]]<br> keep_existing_recovery_conf: True/False<br> no_params: True/False<br> recovery_conf:<br> recovery_target_action: promote<br> recovery_target_timeline: <PITR_Time><br> restore_command: <method_specific_restore_command> |
In this example, the below section has been added to Patroni config, which will build the node by performing point-in-time recovery using the time stamp mentioned.
|
1 |
bootstrap:<br> method: pitr_restore_by_pgbackrest<br> pitr_restore_by_pgbackrest:<br> command: 'pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=patroni --log-level-file=detail --link-all --type=time<br> --target="2022-12-13 15:46:04" restore'<br> keep_existing_recovery_conf: True<br> no_params: True<br> recovery_conf:<br> recovery_target_action: "promote"<br> recovery_target_time: "2022-12-13 15:46:04"<br> restore_command: 'pgbackrest -config=/etc/rdba/pgbackrest/pgbackrest.conf --stanza=patroni --log-level-file=detail archive-get %f "%p"'<br> recovery_target_inclusive: true |
When the node is being built, one can see in the TOP processes that pgBackRest is being used instead of base backup and Patroni Status when the node is being built:
|
1 |
● patroni.service - PostgreSQL high-availability manager<br> Loaded: loaded (/lib/systemd/system/patroni.service; enabled; vendor preset: enabled)<br> Drop-In: /etc/systemd/system/patroni.service.d<br> └─override.conf<br> Active: active (running) since Mon 2022-12-19 19:06:16 UTC; 3s ago<br> Main PID: 2094 (patroni)<br> Tasks: 11 (limit: 1143)<br> Memory: 114.6M<br> CPU: 941ms<br> CGroup: /system.slice/patroni.service<br> ├─2094 /usr/bin/python3 /usr/bin/patroni /etc/patroni/db2.yml<br> ├─2100 /usr/bin/pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=patroni --log-level-file=detail --delta restore<br> ├─2102 /usr/bin/pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --exec-id=2100-9618fafd --log-level-console=off --log-level-file=off --log-level-stderr=error --process=1 --remote-type=repo --stanza=p><br> ├─2103 /usr/bin/pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --exec-id=2100-9618fafd --log-level-console=off --log-level-file=off --log-level-stderr=error --process=2 --remote-type=repo --stanza=p><br> ├─2104 ssh -o LogLevel=error -o Compression=no -o PasswordAuthentication=no postgres@192.168.0.3 "/usr/bin/pgbackrest --exec-id=2100-9618fafd --log-level-console=off --log-level-file=off --log-level-st><br> └─2105 ssh -o LogLevel=error -o Compression=no -o PasswordAuthentication=no [email protected] "/usr/bin/pgbackrest --exec-id=2100-9618fafd --log-level-console=off --log-level-file=off --log-level-st><br>Dec 19 19:06:16 ip-192-168-0-2 systemd[1]: Started PostgreSQL high-availability manager.<br>Dec 19 19:06:16 ip-192-168-0-2 patroni[2094]: 2022-12-19 19:06:16,415 INFO: Selected new etcd server http://192.168.0.1:2379<br>Dec 19 19:06:16 ip-192-168-0-2 patroni[2094]: 2022-12-19 19:06:16,434 INFO: No PostgreSQL configuration items changed, nothing to reload.<br>Dec 19 19:06:16 ip-192-168-0-2 patroni[2094]: 2022-12-19 19:06:16,551 INFO: Lock owner: node3; I am db2<br>Dec 19 19:06:16 ip-192-168-0-2 patroni[2094]: 2022-12-19 19:06:16,633 INFO: trying to bootstrap from leader 'node3'<br>Dec 19 19:06:16 ip-192-168-0-2 patroni[2100]: 2022-12-19 19:06:16.645 P00 INFO: restore command begin 2.41: --config=/etc/pgbackrest/pgbackrest.conf --delta --exec-id=2100-9618fafd --log-level-console=info --log-l><br>Dec 19 19:06:16 ip-192-168-0-2 patroni[2100]: WARN: --delta or --force specified but unable to find 'PG_VERSION' or 'backup.manifest' in '/var/lib/postgresql/14/main' to confirm that this is a valid $PGDATA director<br>Dec 19 19:06:17 ip-192-168-0-2 patroni[2100]: 2022-12-19 19:06:17.361 P00 INFO: repo1: restore backup set 20221213-154604F, <b>recovery</b> <b>will</b> <b>start</b> <b>at</b> <b>2022-12-13</b> <b>15</b><b>:46:04</b> |
Notice that it is using delta restore which means it will automatically identify which files are required to restore and only those will be restored making the whole process faster.
Also, the log file (by default – /var/log/pgbackrest) will contain the restore date and time as below:
|
1 |
-------------------PROCESS START-------------------<br>2022-12-19 19:06:16.645 P00 INFO: restore command begin 2.41: --config=/etc/pgbackrest/pgbackrest.conf --delta --exec-id=2100-9618fafd --log-level<br>-console=info --log-level-file=detail --pg1-path=/var/lib/postgresql/14/main --process-max=2 --repo1-host=192.168.0.3 --repo1-host-user=postgres -<br>-repo1-path=/pgrdbackups --stanza=patroni<br>2022-12-19 19:06:16.646 P00 WARN: --delta or --force specified but unable to find 'PG_VERSION' or 'backup.manifest' in '/var/lib/postgresql/14/mai<br>n' to confirm that this is a valid $PGDATA directory. --delta and --force have been disabled and if any files exist in the destination directories<br>the restore will be aborted.<br>2022-12-19 19:06:17.361 P00 INFO: repo1: restore backup set 20221213-154604F, recovery will start at 2022-12-13 15:46:04<br>2022-12-19 19:06:17.361 P00 DETAIL: check '/var/lib/postgresql/14/main' exists<br>2022-12-19 19:06:17.362 P00 DETAIL: create path '/var/lib/postgresql/14/main/base'<br>2022-12-19 19:06:17.362 P00 DETAIL: create path '/var/lib/postgresql/14/main/base/1'<br>2022-12-19 19:06:17.362 P00 DETAIL: create path '/var/lib/postgresql/14/main/base/13760'<br>2022-12-19 19:06:17.362 P00 DETAIL: create path '/var/lib/postgresql/14/main/base/13761' |
This blog majorly focuses on integrating the pgBackRest and Patroni, however, one can use other backup tools like WAL_E or BARMAN to rebuild the nodes. More information on such configuration can be found in the Patroni documentation.
Patroni and pgBackRest solutions work best when integrated, which helps in reducing the load from the Primary DB Server. This integration, where the node is reinitialized using pgBackRest, makes the optimum utilization of the dedicated backup server. If the Patroni setup is already configured and the dedicated backup host is also available, then making a few configuration changes in patroni.yml can work like wonders.
