PostgreSQL Patroni 架构使用 pgbackrest 创建备库

PostgreSQL Patroni ha 中,可以使用 pgbackrest or barman or WAL-E 等替代 basebackup 去创建备库，这篇文章我们介绍 在pg patroni集群中使用 pgbackrest 来创建备库.

1、介绍

集群信息：

postgres@pgcluster01[20:18:37]$ inctl list
+ Cluster: cndb (7559217642423819969) ----------+-----------+----+-----------+
| Member      | Host             | Role         | State     | TL | Lag in MB |
+-------------+------------------+--------------+-----------+----+-----------+
| pgcluster01 | 10.10.0.191:1622 | Sync Standby | streaming | 18 |         0 |
| pgcluster02 | 10.10.0.192:1622 | Leader       | running   | 18 |           |
+-------------+------------------+--------------+-----------+----+-----------+
repo: pgbackup196 10.10.0.196

默认情况下，重建备库是调用pg_basebackup 来创建，我们来看下创建过程：
我们直接删除了 集群中的备节点的数据目录 ，可以看到集群 10.10.0.191 节点已经 stop

此时我们看下后台进程：

ps -ef|grep pgbackrest && ps -ef|grep pg_basebackup && ps -ef|grep pg_rewind

主库的后台进程：

一般情况，重建备库会调用 pg_basebackup，此时压力全部给到了主库，试想一下如果能 让 pgbackrest 从repo备份仓库中 ,通过备份还原数据库 + 追加归档来创建备库，岂不是更好。不仅能提高效率，还减轻了主库压力。

我们来官方文档去看下相关参数：
patroni 官方文档：
https://patroni.readthedocs.io/en/latest/replica_bootstrap.html
官方文档内容：
Patroni 使用经过验证的成熟pg_basebackup方法来创建新的副本。它的缺点之一是需要一个正在运行的主节点。另一个缺点是备份数据缺乏“即时”压缩功能，也没有内置的过期备份文件清理机制。有些人更喜欢其他备份解决方案，例如WAL-E pgBackRest Barman，或者干脆自己编写脚本。为了满足所有这些使用场景，Patroni 支持运行自定义脚本来克隆新的副本。
可以看到patroni是可以支持pgbackrest接管pg数据库的。
这些脚本在配置块中进行配置postgresql：

postgresql:
    create_replica_methods:
        - <method name>
    <method name>:
        command: <command name>
        keep_data: True
        no_params: True
        no_leader: 1

pgbackrest

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'

2、配置

2.1 查看备份情况

配置前我们先看下我们的备份情况,我们今天正好有一份差异备份。

sudo -iu postgres /home/postgres/app/postgresql/bin/pgbackrest --stanza=cluster1 info

2.2 修改参数

需要增加配置
postgresql 模块

  recovery_conf:
    recovery_target_timeline: latest
    restore_command: '/home/postgres/app/postgresql/bin/pgbackrest --stanza=cluster1 archive-get %f "%p"'
  use_pg_rewind: true
  use_slots: true
create_replica_methods:
    - pgbackrest
    - basebackup
  pgbackrest:
    command: /home/postgres/app/postgresql/bin/pgbackrest --stanza=cluster1 --delta restore
    keep_data: true
    no_params: true
    no_master: 1
    config: /etc/pgbackrest/pgbackrest.conf

该create_replica_methods配置定义了可用的副本创建方法及其执行顺序。Patroni 会在第一个返回 0 的方法执行完毕后停止。每种方法都应该在配置文件中定义一个单独的部分，列出要执行的命令以及要传递给该命令的任何自定义参数。所有参数都将以特定 --name=value格式传递。除了用户定义的参数外，Patroni 还提供了一些集群特定的参数：

--scope
此副本属于哪个集群？
--datadir
副本数据目录的路径
--role
始终是“复制品”
--connstring
用于连接到集群成员（主副本或其他副本）以进行克隆的连接字符串。连接字符串中的用户可以执行 SQL 和复制协议命令。
如果定义了一个特殊no_leader参数，Patroni 可以在没有运行中的主节点或副本的情况下调用副本创建方法。在这种情况下，连接字符串中将传递一个空字符串。这对于从二进制备份恢复先前运行的集群非常有用。
如果定义了特殊keep_data参数，则会指示 Patroni 在调用 restore 之前不要清理 PGDATA 文件夹。
如果定义了特殊no_params参数，则可将参数传递给自定义命令。

几个参数：

1） keep_data: true # keep_data: true + no_master: 1= 增量恢复，不检查主库状态； keep_data: false + no_master: 1= 完整恢复，完全独立
2） no_params: true # no_params: true 不使用主库的 postgresql.conf
3） no_master: 1 的含义：但实际含义是：在恢复过程中不连接主库。
实际作用：当设置为 1或 true时 （推荐）（ no_master: 0 # 或 false），告诉 Patroni：“使用 pgBackRest 恢复时，不需要连接到当前的主库获取额外信息”

2.3 暂停集群（禁止故障转移）

修改参数后，需要重启patroni服务，为了避免故障转移

inctl pause

2.4 重启 patroni

systemctl restart patroni
systemctl status patroni

2.5 恢复集群（启用故障转移）查看集群状态

inctl resume

3 创建过程

1）直接删除备库pgdata,看到 pgbackrest 已经开始从repo仓库中恢复、重建备库

2） 来看下主库的后台进程

3） repo仓库后台进程

pgbackrest完全接管了 pg patroni 集群中的备份恢复，重建备库也不需要依赖主库执行 pg_basebackup 去重建,一下命令：

/home/postgres/app/postgresql/bin/pg_basebackup --pgdata=/data/pgdata -X stream --dbname=dbname=postgres user=repuser host=10.10.0.192 port=1622

3.2 我们来看下 重建备库过程, 备库创建完成、并且加入集群

备库创建有两个过程

1）creating replica：pgbackrest从备份仓库repo中restore数据库，相当于 pgbackrest --stanza=cluster1 --type=standby restore
2）in archive recovery：日志恢复，拷贝备份中、归档的wal日志，恢复到最新，依赖 recovery_target_timeline: latest 参数。

3.3 测试 reinit

postgres@pgcluster02[21:06:50]$ inctl reinit cndb pgcluster02 
+ Cluster: cndb (7559217642423819969) ----------+-----------+----+-----------+
| Member      | Host             | Role         | State     | TL | Lag in MB |
+-------------+------------------+--------------+-----------+----+-----------+
| pgcluster01 | 10.10.0.191:1622 | Leader       | running   | 19 |           |
| pgcluster02 | 10.10.0.192:1622 | Sync Standby | streaming | 19 |         0 |
+-------------+------------------+--------------+-----------+----+-----------+
Are you sure you want to reinitialize members pgcluster02? [y/N]: y
Success: reinitialize for member pgcluster02
postgres@pgcluster02[21:07:30]$ ps -ef|grep pgbackrest && ps -ef|grep pg_basebackup && ps -ef|grep pg_rewind
postgres  534397  524792 29 21:07 ?        00:00:00 /home/postgres/app/postgresql/bin/pgbackrest --stanza=cluster1 --delta restore --config=/etc/pgbackrest/pgbackrest.conf
postgres  534400  534397  0 21:07 ?        00:00:00 /home/postgres/app/postgresql/bin/pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --exec-id=534397-097e44b9 --log-level-console=off --log-level-file=off --log-level-stderr=error --process=1 --remote-type=repo --stanza=cluster1 restore:local
postgres  534402  525048  0 21:07 pts/0    00:00:00 grep pgbackrest
postgres  534404  525048  0 21:07 pts/0    00:00:00 grep pg_basebackup
postgres  534406  525048  0 21:07 pts/0    00:00:00 grep pg_rewind
postgres@pgcluster02[21:07:31]$ ps -ef|grep pgbackrest && ps -ef|grep pg_basebackup && ps -ef|grep pg_rewind
postgres  534397  524792 14 21:07 ?        00:00:00 /home/postgres/app/postgresql/bin/pgbackrest --stanza=cluster1 --delta restore --config=/etc/pgbackrest/pgbackrest.conf
postgres  534400  534397 88 21:07 ?        00:00:00 /home/postgres/app/postgresql/bin/pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --exec-id=534397-097e44b9 --log-level-console=off --log-level-file=off --log-level-stderr=error --process=1 --remote-type=repo --stanza=cluster1 restore:local
postgres  534409  525048  0 21:07 pts/0    00:00:00 grep pgbackrest
postgres  534411  525048  0 21:07 pts/0    00:00:00 grep pg_basebackup
postgres  534413  525048  0 21:07 pts/0    00:00:00 grep pg_rewind
postgres@pgcluster02[21:07:32]$ inctl list
+ Cluster: cndb (7559217642423819969) -----+------------------+----+-----------+
| Member      | Host             | Role    | State            | TL | Lag in MB |
+-------------+------------------+---------+------------------+----+-----------+
| pgcluster01 | 10.10.0.191:1622 | Leader  | running          | 19 |           |
| pgcluster02 | 10.10.0.192:1622 | Replica | creating replica |    |   unknown |
+-------------+------------------+---------+------------------+----+-----------+
postgres@pgcluster02[21:08:15]$ ps -ef|grep pgbackrest && ps -ef|grep pg_basebackup && ps -ef|grep pg_rewind
postgres  534397  524792  0 21:07 ?        00:00:00 /home/postgres/app/postgresql/bin/pgbackrest --stanza=cluster1 --delta restore --config=/etc/pgbackrest/pgbackrest.conf
postgres  534400  534397 74 21:07 ?        00:04:13 /home/postgres/app/postgresql/bin/pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --exec-id=534397-097e44b9 --log-level-console=off --log-level-file=off --log-level-stderr=error --process=1 --remote-type=repo --stanza=cluster1 restore:local
postgres  534415  534400  8 21:07 ?        00:00:28 /usr/bin/ssh -o LogLevel=error -o Compression=no -o PasswordAuthentication=no postgres@pgbackup196 /home/postgres/app/postgresql/bin/pgbackrest --exec-id=534397-097e44b9 --log-level-console=off --log-level-file=off --log-level-stderr=error --pg1-path=/data/pgdata --process=1 --remote-type=repo --repo=1 --stanza=cluster1 restore:remote
postgres  535038  525048  0 21:13 pts/0    00:00:00 grep pgbackrest
postgres  535040  525048  0 21:13 pts/0    00:00:00 grep pg_basebackup
postgres  535042  525048  0 21:13 pts/0    00:00:00 grep pg_rewind
postgres@pgcluster02[21:25:53]$ ps -ef|grep pgbackrest && ps -ef|grep pg_basebackup && ps -ef|grep pg_rewind
postgres  537041  525048  0 21:26 pts/0    00:00:00 grep pgbackrest
postgres  537043  525048  0 21:26 pts/0    00:00:00 grep pg_basebackup
postgres  537045  525048  0 21:26 pts/0    00:00:00 grep pg_rewind
postgres@pgcluster02[21:26:12]$  inctl list
+ Cluster: cndb (7559217642423819969) ----------+-----------+----+-----------+
| Member      | Host             | Role         | State     | TL | Lag in MB |
+-------------+------------------+--------------+-----------+----+-----------+
| pgcluster01 | 10.10.0.191:1622 | Leader       | running   | 19 |           |
| pgcluster02 | 10.10.0.192:1622 | Sync Standby | streaming | 19 |         0 |
+-------------+------------------+--------------+-----------+----+-----------+

3.4 测试下swichover

postgres@pgcluster02[20:17:49]$ inctl switchover
Current cluster topology
+ Cluster: cndb (7559217642423819969) ----------+-----------+----+-----------+
| Member      | Host             | Role         | State     | TL | Lag in MB |
+-------------+------------------+--------------+-----------+----+-----------+
| pgcluster01 | 10.10.0.191:1622 | Sync Standby | streaming | 18 |         0 |
| pgcluster02 | 10.10.0.192:1622 | Leader       | running   | 18 |           |
+-------------+------------------+--------------+-----------+----+-----------+
Primary [pgcluster02]: 
Candidate ['pgcluster01'] []: 
When should the switchover take place (e.g. 2026-02-02T21:17 )  [now]: 
Are you sure you want to switchover cluster cndb, demoting current leader pgcluster02? [y/N]: y
2026-02-02 20:17:57.83154 Successfully switched over to "pgcluster01"
+ Cluster: cndb (7559217642423819969) -----+---------+----+-----------+
| Member      | Host             | Role    | State   | TL | Lag in MB |
+-------------+------------------+---------+---------+----+-----------+
| pgcluster01 | 10.10.0.191:1622 | Leader  | running | 18 |           |
| pgcluster02 | 10.10.0.192:1622 | Replica | stopped |    |   unknown |
+-------------+------------------+---------+---------+----+-----------+
postgres@pgcluster02[20:17:57]$ ps -ef|grep pgbackrest && ps -ef|grep pg_basebackup && ps -ef|grep pg_rewind
postgres  529300  525048  0 20:17 pts/0    00:00:00 grep pgbackrest
postgres  529302  525048  0 20:17 pts/0    00:00:00 grep pg_basebackup
postgres  529304  525048  0 20:17 pts/0    00:00:00 grep pg_rewind
postgres@pgcluster02[20:17:58]$ ps -ef|grep pgbackrest && ps -ef|grep pg_basebackup && ps -ef|grep pg_rewind
postgres  529314  525048  0 20:17 pts/0    00:00:00 grep pgbackrest
postgres  529316  525048  0 20:17 pts/0    00:00:00 grep pg_basebackup
postgres  529318  525048  0 20:17 pts/0    00:00:00 grep pg_rewind
postgres@pgcluster02[20:17:59]$ ps -ef|grep pgbackrest && ps -ef|grep pg_basebackup && ps -ef|grep pg_rewind
postgres  529324  529323  0 20:17 ?        00:00:00 /home/postgres/app/postgresql/bin/pgbackrest --stanza=cluster1 archive-get 00000013.history pg_wal/RECOVERYHISTORY
postgres  529325  529324  0 20:17 ?        00:00:00 /usr/bin/ssh -o LogLevel=error -o Compression=no -o PasswordAuthentication=no postgres@pgbackup196 /home/postgres/app/postgresql/bin/pgbackrest --exec-id=529324-62615551 --log-level-console=off --log-level-file=off --log-level-stderr=error --pg1-path=/data/pgdata --process=0 --remote-type=repo --repo=1 --stanza=cluster1 archive-get:remote
postgres  529327  525048  0 20:17 pts/0    00:00:00 grep pgbackrest
postgres  529329  525048  0 20:17 pts/0    00:00:00 grep pg_basebackup
postgres  529331  525048  0 20:17 pts/0    00:00:00 grep pg_rewind
postgres@pgcluster02[20:18:00]$ ps -ef|grep pgbackrest && ps -ef|grep pg_basebackup && ps -ef|grep pg_rewind
postgres  529337  529323  0 20:17 ?        00:00:00 /home/postgres/app/postgresql/bin/pgbackrest --stanza=cluster1 archive-get 000000130000006B00000013 pg_wal/RECOVERYXLOG
postgres  529338  529337  1 20:17 ?        00:00:00 /usr/bin/ssh -o LogLevel=error -o Compression=no -o PasswordAuthentication=no postgres@pgbackup196 /home/postgres/app/postgresql/bin/pgbackrest --exec-id=529337-d3c140cb --log-level-console=off --log-level-file=off --log-level-stderr=error --pg1-path=/data/pgdata --process=0 --remote-type=repo --repo=1 --stanza=cluster1 archive-get:remote
postgres  529344  525048  0 20:18 pts/0    00:00:00 grep pgbackrest
postgres  529346  525048  0 20:18 pts/0    00:00:00 grep pg_basebackup
postgres  529348  525048  0 20:18 pts/0    00:00:00 grep pg_rewind
postgres@pgcluster02[20:18:01]$ ps -ef|grep pgbackrest && ps -ef|grep pg_basebackup && ps -ef|grep pg_rewind
postgres  529359  525048  0 20:18 pts/0    00:00:00 grep pgbackrest
postgres  529361  525048  0 20:18 pts/0    00:00:00 grep pg_basebackup
postgres  529364  525048  0 20:18 pts/0    00:00:00 grep pg_rewind
postgres@pgcluster02[20:18:02]$ inctl list
+ Cluster: cndb (7559217642423819969) -----+----------+----+-----------+
| Member      | Host             | Role    | State    | TL | Lag in MB |
+-------------+------------------+---------+----------+----+-----------+
| pgcluster01 | 10.10.0.191:1622 | Leader  | running  | 19 |           |
| pgcluster02 | 10.10.0.192:1622 | Replica | starting |    |   unknown |
+-------------+------------------+---------+----------+----+-----------+
postgres@pgcluster02[20:18:07]$ inctl list
+ Cluster: cndb (7559217642423819969) ----------+-----------+----+-----------+
| Member      | Host             | Role         | State     | TL | Lag in MB |
+-------------+------------------+--------------+-----------+----+-----------+
| pgcluster01 | 10.10.0.191:1622 | Leader       | running   | 19 |           |
| pgcluster02 | 10.10.0.192:1622 | Sync Standby | streaming | 19 |         0 |
+-------------+------------------+--------------+-----------+----+-----------+

手动切换正常，switchover 并没有使用 pg_rewind 去同步主备数据。而是直接调用了pgbackrest应用增量wal日志去同步主备数据。
此时 pgbackrest 已完全接管了pg的备份恢复，替代了patroni 集群中pg的basebackup 和 pg_rewind 去创建备库，同步主备数据。

目前这个配置我已在生产上测试验证了一段时间，目前还是很稳定的。目前学习pg中，有感兴趣的朋友可以一起学习交流。