PostgreSQL patroni 高可用 3：patroni 运维

博客园 - MSSQL123

ubuntu24下 ext4和xfs类型磁盘以及PostgreSQL实例性能对比测试 PostgreSQL 18 新特性 skip scan，一个鸡肋的功能 PostgreSQL 17 流复制中开启逻辑复制槽同步，以及逻辑槽的故障转移记一次PostgreSQL交叉表crosstab行转列导致的OOM TiDB 扩容与缩容 PostgreSQL 高可用集群 patroni 自动故障转移测试 Ubuntu 20 环境下 patroni 自动化安装，一分钟快速搭建 patroni 集群 Ubuntu 20 环境下 pg_auto_failover 自动化安装，一分钟快速搭建pg_auto_failover集群 PostgreSQL的消息队列扩展pgmq 磁盘IO延迟和队列深度的关系 TiDB 最小拓扑架构集群安装 PostgreSQL 逻辑复制中的同步和异步模式以及其表现 prometheus 的 altermanager Silences静默告警，优雅地抑制告警 “世界上百分之99的博士和他们的论文都是垃圾”，原本以为这个观点偏激 pg_auto_failover 在多种场景下自动故障转移的验证 pgbouncer连接池设置与压力测试的最大连接数测试 pg_auto_failover 自动故障转移参数博文阅读密码验证 - 博客园 pg_auto_failover 高可用中，PostgreSQL实例配置文件的加载步骤 pg_auto_failover集群monitor节点的高可用 prometheus监控Linux Server node_exporter代理安装和配置 prometheus监控windows window_exporter代理安装和配置 Prometheus 和 Grafana 监控 PostgreSQL 记一次MySQL binlog日志导致磁盘空间占满的问题 SQLServer 2019 标准版在虚拟机上无法充分利用CPU的问题诊断 Windows Failover Cluster集群中的EventId 1196错误日志 pg_auto_failover 环境变量导致的show命令错误 SqlServer 事务复制（transaction replication）的复制位点信息 SqlServer 事务复制的两个参数immediate_sync，allow_anonymous MySQL，SqlServer，PostgreSQL中，如何实现锁定一张表 PostgreSQL pg_auto_failover 高可用 2：pg_auto_failover集群运维 PostgreSQL pg_auto_failover 高可用 1：pg_auto_failover集群搭建 PostgreSQL patroni 高可用 4：HAProxy和Keepalived实现读写分离

MSSQL123 · 2025-09-26 · via 博客园 - MSSQL123

PostgreSQL patroni 高可用 3：patroni 运维

PostgreSQL ptroni的高可用架构图如下所示，本文完成如下架构图中红色标记内的patroni安装和配置。

图片来源于：https://docs.percona.com/postgresql/12/solutions/high-availability.html#architecture-layout

1，patronictl 查看集群状态

patronictl -c /usr/local/pgsql16/patroni/patroni.yml list，修改参数后会显示Pending restart和Pending restart reason 列

#patroni集群状态查看
root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
+ Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
| Member   | Host                 | Role    | State     | TL | Lag in MB |
+----------+----------------------+---------+-----------+----+-----------+
| ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  5 |         0 |
| ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  5 |         0 |
| ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  5 |           |
+----------+----------------------+---------+-----------+----+-----------+





#修改相关参数之后
root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
+ Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+-----------------+----------------------------+
| Member   | Host                 | Role    | State     | TL | Lag in MB | Pending restart | Pending restart reason     |
+----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+
| ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  2 |         0 | *               | max_connections: 100->120  |
|          |                      |         |           |    |           |                 | shared_buffers: 1GB->512MB |
+----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+
| ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  2 |         0 | *               | max_connections: 100->120  |
|          |                      |         |           |    |           |                 | shared_buffers: 1GB->512MB |
+----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+
| ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  2 |           | *               | max_connections: 100->120  |
|          |                      |         |           |    |           |                 | shared_buffers: 1GB->512MB |
+----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+

2，patronictl 暂停auto failover

某些情况下，比如重启服务器等运维措施实施的时候，此时并不想改变数据库的主从关系，就需要暂停auto failover，命令为patronictl pause暂停auto failover

-- 禁用 auto failover，如果没有启动 patroni，执行 patronictl pause 会失败
patronictl -c /usr/local/pgsql16/patroni/patroni.yml pause

root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml pause
Success: cluster management is paused
root@ubuntu08:/usr/local/pgsql16/patroni# patronictl list
root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml show-config
loop_wait: 10
master_start_timeout: 300
master_stop_timeout: 0
max_timelines_history: 0
maximum_lag_on_failover: 1048576
#备注行：启用之后，用show-config看到pause: true配置项
pause: true
postgresql:
  parameters:
    archive_command: test ! -f /usr/local/pgsql16/pg_wal_arch/%f && cp %p /usr/local/pgsql16/pg_wal_arch/%f
    archive_mode: 'on'
    archive_timeout: 1800s
    hot_standby: 'on'
    log_autovacuum_min_duration: 0
    log_checkpoints: 'on'
    log_connections: 'on'
    log_destination: stderr
    log_directory: log
    log_disconnections: 'on'
    log_file_mode: '0600'
    log_filename: postgresql-%Y-%m-%d_%H%M%S.log
    log_hostname: 'on'
    log_line_prefix: '%m [%p] user:%u,db:%d,app:%a,host:%h '
    log_lock_waits: 'on'
    log_min_duration_statement: 1
    log_recovery_conflict_waits: 'on'
    log_replication_commands: 'on'
    log_rotation_age: 1d
    log_statement: ddl
    log_temp_files: 1
    log_timezone: Asia/Shanghai
    logging_collector: 'on'
    max_connections: 100
    max_locks_per_transaction: 64
    max_prepared_transactions: 0
    max_replication_slots: 10
    max_wal_senders: 10
    max_worker_processes: 8
    shared_buffers: 1024MB
    track_commit_timestamp: 'off'
    wal_keep_segments: 8
    wal_level: hot_standby
    wal_log_hints: 'on'
    work_mem: 32MB
  pg_hba:
  - host replication repl_user 0.0.0.0/0 md5
  - host all all 0.0.0.0/0 md5
  use_pg_rewind: true
  use_slots: true
retry_timeout: 10
synchronous_mode: false
ttl: 30

# 启用 auto failover
 patronictl -c /usr/local/pgsql16/patroni/patroni.yml resume
# 启用之后，用show-config看不到pause: true
root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml resume
Success: cluster management is resumed
root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml show-config
loop_wait: 10
master_start_timeout: 300
master_stop_timeout: 0
max_timelines_history: 0
maximum_lag_on_failover: 1048576
postgresql:
  parameters:
    archive_command: test ! -f /usr/local/pgsql16/pg_wal_arch/%f && cp %p /usr/local/pgsql16/pg_wal_arch/%f
    archive_mode: 'on'
    archive_timeout: 1800s
    hot_standby: 'on'
    log_autovacuum_min_duration: 0
    log_checkpoints: 'on'
    log_connections: 'on'
    log_destination: stderr
    log_directory: log
    log_disconnections: 'on'
    log_file_mode: '0600'
    log_filename: postgresql-%Y-%m-%d_%H%M%S.log
    log_hostname: 'on'
    log_line_prefix: '%m [%p] user:%u,db:%d,app:%a,host:%h '
    log_lock_waits: 'on'
    log_min_duration_statement: 1
    log_recovery_conflict_waits: 'on'
    log_replication_commands: 'on'
    log_rotation_age: 1d
    log_statement: ddl
    log_temp_files: 1
    log_timezone: Asia/Shanghai
    logging_collector: 'on'
    max_connections: 100
    max_locks_per_transaction: 64
    max_prepared_transactions: 0
    max_replication_slots: 10
    max_wal_senders: 10
    max_worker_processes: 8
    shared_buffers: 1024MB
    track_commit_timestamp: 'off'
    wal_keep_segments: 8
    wal_level: hot_standby
    wal_log_hints: 'on'
    work_mem: 32MB
  pg_hba:
  - host replication repl_user 0.0.0.0/0 md5
  - host all all 0.0.0.0/0 md5
  use_pg_rewind: true
  use_slots: true
retry_timeout: 10
synchronous_mode: false
ttl: 30

3，patronictl 重启集群

在某些参数修改后，需要重启的情况下，重启命令为patronictl -c /usr/local/pgsql16/patroni/patroni.yml restart pg_cluster_wy_prod

可在任意节点执行上述命令重启集群的PostgreSQL实例，重启过程中不会故障转移，各节点的主从身份保持不变

root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
+ Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+-----------------+----------------------------+
| Member   | Host                 | Role    | State     | TL | Lag in MB | Pending restart | Pending restart reason     |
+----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+
| ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  2 |         0 | *               | max_connections: 100->120  |
|          |                      |         |           |    |           |                 | shared_buffers: 1GB->512MB |
+----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+
| ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  2 |         0 | *               | max_connections: 100->120  |
|          |                      |         |           |    |           |                 | shared_buffers: 1GB->512MB |
+----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+
| ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  2 |           | *               | max_connections: 100->120  |
|          |                      |         |           |    |           |                 | shared_buffers: 1GB->512MB |
+----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+
root@ubuntu08:/usr/local/pgsql16/patroni#
root@ubuntu08:/usr/local/pgsql16/patroni#
root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml restart pg_cluster_wy_prod
+ Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+-----------------+----------------------------+
| Member   | Host                 | Role    | State     | TL | Lag in MB | Pending restart | Pending restart reason     |
+----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+
| ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  2 |         0 | *               | max_connections: 100->120  |
|          |                      |         |           |    |           |                 | shared_buffers: 1GB->512MB |
+----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+
| ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  2 |         0 | *               | max_connections: 100->120  |
|          |                      |         |           |    |           |                 | shared_buffers: 1GB->512MB |
+----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+
| ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  2 |           | *               | max_connections: 100->120  |
|          |                      |         |           |    |           |                 | shared_buffers: 1GB->512MB |
+----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+
When should the restart take place (e.g. 2025-09-26T08:49)  [now]:
Are you sure you want to restart members ubuntu08, ubuntu09, ubuntu10? [y/N]: y
Restart if the PostgreSQL version is less than provided (e.g. 9.5.2)  []:
Success: restart on member ubuntu08
Success: restart on member ubuntu09
Success: restart on member ubuntu10
root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
+ Cluster: pg_cluster_wy_prod (7553485872297570126) --+----+-----------+
| Member   | Host                 | Role    | State   | TL | Lag in MB |
+----------+----------------------+---------+---------+----+-----------+
| ubuntu08 | 192.168.152.115:9000 | Replica | running |  2 |         0 |
| ubuntu09 | 192.168.152.116:9000 | Replica | running |  2 |         0 |
| ubuntu10 | 192.168.152.117:9000 | Leader  | running |  2 |           |
+----------+----------------------+---------+---------+----+-----------+
root@ubuntu08:/usr/local/pgsql16/patroni#

4，patronictl 查看参数

patronictl -c /usr/local/pgsql16/patroni/patroni.yml show-config查看PostgreSQL参数设置，需要注意的是，查看出来的参数是从ectd存储中读取出来的，而不是配置文件本身的内容，配置文件自身的内容会在启动的时候写入到etcd。

另一个细节是：如果通过edit-config增加相关参数，编辑完成后用show-config再次查看，相关的参数在编辑模式中显示的位置是会变化的，再次说明，edit-config编辑参数最终写入到了ectd中，显示的时候从ectd中读取出来，而不是patroni.yml文件本身，这一点非常容易误解。

root@ubuntu08:/usr/local/pgsql16/patroni#  patronictl -c /usr/local/pgsql16/patroni/patroni.yml  show-config
loop_wait: 10
master_start_timeout: 300
master_stop_timeout: 0
max_timelines_history: 0
maximum_lag_on_failover: 1048576
postgresql:
  parameters:
    archive_command: test ! -f /usr/local/pgsql16/pg_wal_arch/%f && cp %p /usr/local/pgsql16/pg_wal_arch/%f
    archive_mode: 'on'
    archive_timeout: 1800s
    hot_standby: 'on'
    log_autovacuum_min_duration: 0
    log_checkpoints: 'on'
    log_connections: 'on'
    log_destination: stderr
    log_directory: log
    log_disconnections: 'on'
    log_file_mode: '0600'
    log_filename: postgresql-%Y-%m-%d_%H%M%S.log
    log_hostname: 'on'
    log_line_prefix: '%m [%p] user:%u,db:%d,app:%a,host:%h '
    log_lock_waits: 'on'
    log_min_duration_statement: 1
    log_recovery_conflict_waits: 'on'
    log_replication_commands: 'on'
    log_rotation_age: 1d
    log_statement: ddl
    log_temp_files: 1
    log_timezone: Asia/Shanghai
    logging_collector: 'on'
    max_connections: 100
    max_locks_per_transaction: 64
    max_prepared_transactions: 0
    max_replication_slots: 10
    max_wal_senders: 10
    max_worker_processes: 8
    shared_buffers: 1024MB
    track_commit_timestamp: 'off'
    wal_keep_segments: 8
    wal_level: hot_standby
    wal_log_hints: 'on'
    work_mem: 32MB
  pg_hba:
  - host replication repl_user 0.0.0.0/0 md5
  - host all all 0.0.0.0/0 md5
  use_pg_rewind: true
  use_slots: true
retry_timeout: 10
synchronous_mode: false
ttl: 30

5，修改参数

5.1 edit-config方式修改参数1

patronictl -c /usr/local/pgsql16/patroni/patroni.yml edit-config

Ubuntu下edit-config以nano方式编辑参数文件，编辑完成后需要（Ctrl + O（写入文件）→ Enter（确认） → Ctrl + X（保存并退出））

root@ubuntu08:/usr/local/pgsql16/patroni#  patronictl -c /usr/local/pgsql16/patroni/patroni.yml edit-config
---
+++
@@ -28,13 +28,13 @@
     log_temp_files: 1
     log_timezone: Asia/Shanghai
     logging_collector: 'on'
-    max_connections: 100
+    max_connections: 120
     max_locks_per_transaction: 64
     max_prepared_transactions: 0
     max_replication_slots: 10
     max_wal_senders: 10
     max_worker_processes: 8
-    shared_buffers: 1024MB
+    shared_buffers: 512MB
     track_commit_timestamp: 'off'
     wal_keep_segments: 8
     wal_level: hot_standby

Apply these changes? [y/N]: y
Configuration changed
root@ubuntu08:/usr/local/pgsql16/patroni#

5.2 edit-config方式修改参数2

patronictl -c /usr/local/pgsql16/patroni/patroni.yml edit-config -s 'postgresql.parameters.max_connections=300'

root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
+ Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
| Member   | Host                 | Role    | State     | TL | Lag in MB |
+----------+----------------------+---------+-----------+----+-----------+
| ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  2 |         0 |
| ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  2 |         0 |
| ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  2 |           |
+----------+----------------------+---------+-----------+----+-----------+
root@ubuntu08:/usr/local/pgsql16/patroni#   patronictl -c /usr/local/pgsql16/patroni/patroni.yml edit-config -s 'postgresql.parameters.max_connections=300'
---
+++
@@ -28,7 +28,7 @@
     log_temp_files: 1
     log_timezone: Asia/Shanghai
     logging_collector: 'on'
-    max_connections: 120
+    max_connections: 300
     max_locks_per_transaction: 64
     max_prepared_transactions: 0
     max_replication_slots: 10

Apply these changes? [y/N]: y
Configuration changed
root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list #备注：patronictl list执行后，这里一开始不显示需要重启，过几秒中才能看到
+ Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+-----------------+---------------------------+
| Member   | Host                 | Role    | State     | TL | Lag in MB | Pending restart | Pending restart reason    |
+----------+----------------------+---------+-----------+----+-----------+-----------------+---------------------------+
| ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  2 |         0 | *               | max_connections: 120->300 |
| ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  2 |         0 | *               | max_connections: 120->300 |
| ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  2 |           | *               | max_connections: 120->300 |
+----------+----------------------+---------+-----------+----+-----------+-----------------+---------------------------+

patronictl -c /usr/local/pgsql16/patroni/patroni.yml edit-config，该命令会以nano编辑器的方式打开patroni.yml配置文件，修改后:

1，edit-config的方式修改配置，不是修改patroni.yml这个物理配置文件自身，而是操作etcd数据库的方式，将数据写入ectd数据库，在任何一个节点都可以修改，并且会同步至其他节点

2，如果参数有误或者格式不对，保存的时候（Ctrl + O → Enter → Ctrl + X）会直接报错

3，如果正常保存，不会重写patroni.yml这个物理文件自身，也就是说即便修改成功，这个文件也不会变

4，修改后该参数不会自动更新或保存在postgresql.auto.conf配置文件中

5，如果正常保存，会自动重写pg数据库实例目录下的patroni.dynamic.json文件

6，如果正常保存，会保存在ectd数据库中，如下修改"shared_buffers"， "old_value"始终是默认值，而不是字面意思上修改前的值我擦我试了好几次才发现这个规律

"shared_buffers": {

"old_value": "128MB",

"new_value": "1GB"

}

7，修改后的参数会下发到所有的PostgreSQL实例，其他PostgreSQL实例下，同样会自动重写patroni.dynamic.json文件

8，patroni的日志中，会自动记录修改参数的日志：Changed shared_buffers from '128MB' to '512MB' (restart might be required)

关于修改参数的patronictl edit-config，语法上是patronictl 指定一个配置文件修改，修改后实际上修改的etcd的中的记录，然后etcd中的new_value记录的会分发到etcd的所有节点，但是其old_value并不是修改前的值，而是默认值，我一直以为搞错了，试了三次才发现是这个规律。

5.3 PostgreSQL 的 ALTER SYSTEM SET 命令

--修改work_mem
ALTER SYSTEM SET work_mem TO '8MB';
SELECT pg_reload_conf();

通过ALTER SYSTEM SET parameter='***'的方式修改

这种方式修改后，

1，可以再任意节点执行修改

2，执行成功后，会将参数保存在postgresql.auto.conf配置文件中，

3，这种方式的修改，只会对当前节点生效，*不会*同步至其他节点

“alter system set”修改的参数会优先于“patronictl edit-config”修改的加载，也就是说postgresql.auto.conf优先级会高于patroni.dynamic.json

6，patroni切换PostgreSQL主从

6.1 patronictl switchover

patronictl switchover：手动故障转移测试，它仅在集群健康(有leader)时才起作用，并允许在指定时间安排切换。patronictl switchover允许在集群中的任意节点执行切换操作

root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
+ Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
| Member   | Host                 | Role    | State     | TL | Lag in MB |
+----------+----------------------+---------+-----------+----+-----------+
| ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  3 |         0 |
| ubuntu09 | 192.168.152.116:9000 | Leader  | running   |  3 |           |
| ubuntu10 | 192.168.152.117:9000 | Replica | streaming |  3 |         0 |
+----------+----------------------+---------+-----------+----+-----------+
root@ubuntu08:/usr/local/pgsql16/patroni#
root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml switchover
Current cluster topology
+ Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
| Member   | Host                 | Role    | State     | TL | Lag in MB |
+----------+----------------------+---------+-----------+----+-----------+
| ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  3 |         0 |
| ubuntu09 | 192.168.152.116:9000 | Leader  | running   |  3 |           |
| ubuntu10 | 192.168.152.117:9000 | Replica | streaming |  3 |         0 |
+----------+----------------------+---------+-----------+----+-----------+
Primary [ubuntu09]:																							#1，这里是提示当前主节点为Ubuntu09，不需要任何输入，回车即可
Candidate ['ubuntu08', 'ubuntu10'] []: ubuntu08																#2，这里列举出2个从节点，可以选择输入其中一个从节点提升为主节点，也可以不选择（默认选择）
When should the switchover take place (e.g. 2025-09-26T09:08 )  [now]:										#3，提示当前直接执行切换，回车即可
Are you sure you want to switchover cluster pg_cluster_wy_prod, demoting current leader ubuntu09? [y/N]: y	#4，输入y确认执行
2025-09-26 08:09:02.30730 Successfully switched over to "ubuntu08"
+ Cluster: pg_cluster_wy_prod (7553485872297570126) --+----+-----------+
| Member   | Host                 | Role    | State   | TL | Lag in MB |
+----------+----------------------+---------+---------+----+-----------+
| ubuntu08 | 192.168.152.115:9000 | Leader  | running |  3 |           |
| ubuntu09 | 192.168.152.116:9000 | Replica | stopped |    |   unknown |
| ubuntu10 | 192.168.152.117:9000 | Replica | running |  3 |         0 |
+----------+----------------------+---------+---------+----+-----------+
root@ubuntu08:/usr/local/pgsql16/patroni#
root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
+ Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
| Member   | Host                 | Role    | State     | TL | Lag in MB |
+----------+----------------------+---------+-----------+----+-----------+
| ubuntu08 | 192.168.152.115:9000 | Leader  | running   |  4 |           |
| ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  4 |         0 |
| ubuntu10 | 192.168.152.117:9000 | Replica | streaming |  4 |         0 |
+----------+----------------------+---------+-----------+----+-----------+
root@ubuntu08:/usr/local/pgsql16/patroni#

6.2 patonictl failover

patonictl failover 允许在没有健康节点时执行手动 failover ，也就是节点故障了，不能switchover了，只能用failover来切换。patronictl failover允许在集群中的任意节点执行切换操作

root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml failover
Current cluster topology
+ Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
| Member   | Host                 | Role    | State     | TL | Lag in MB |
+----------+----------------------+---------+-----------+----+-----------+
| ubuntu08 | 192.168.152.115:9000 | Leader  | running   |  4 |           |
| ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  4 |         0 |
| ubuntu10 | 192.168.152.117:9000 | Replica | streaming |  4 |         0 |
+----------+----------------------+---------+-----------+----+-----------+
Candidate ['ubuntu09', 'ubuntu10'] []: ubuntu10																	#1：这里列举出来从节点，输入从节点主机名
Are you sure you want to failover cluster pg_cluster_wy_prod, demoting current leader ubuntu08? [y/N]: y		#2：这里输入y，执行切换确认
2025-09-26 08:15:47.24592 Successfully failed over to "ubuntu10"
+ Cluster: pg_cluster_wy_prod (7553485872297570126) --+----+-----------+
| Member   | Host                 | Role    | State   | TL | Lag in MB |
+----------+----------------------+---------+---------+----+-----------+
| ubuntu08 | 192.168.152.115:9000 | Replica | stopped |    |   unknown |
| ubuntu09 | 192.168.152.116:9000 | Replica | running |  4 |         0 |
| ubuntu10 | 192.168.152.117:9000 | Leader  | running |  4 |           |
+----------+----------------------+---------+---------+----+-----------+
root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
+ Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
| Member   | Host                 | Role    | State     | TL | Lag in MB |
+----------+----------------------+---------+-----------+----+-----------+
| ubuntu08 | 192.168.152.115:9000 | Replica | stopped   |    |   unknown |
| ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  4 |         0 |
| ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  5 |           |
+----------+----------------------+---------+-----------+----+-----------+
root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
+ Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
| Member   | Host                 | Role    | State     | TL | Lag in MB |
+----------+----------------------+---------+-----------+----+-----------+
| ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  5 |         0 |
| ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  5 |         0 |
| ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  5 |           |
+----------+----------------------+---------+-----------+----+-----------+
root@ubuntu08:/usr/local/pgsql16/patroni#

7，重启PostgreSQL实例

patronictl -c /usr/local/pgsql16/patroni/patroni.yml restart cluster_name（集群名） hostname（主机名）

root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml restart pg_cluster_wy_prod ubuntu08
+ Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
| Member   | Host                 | Role    | State     | TL | Lag in MB |
+----------+----------------------+---------+-----------+----+-----------+
| ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  5 |         0 |
| ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  5 |         0 |
| ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  5 |           |
+----------+----------------------+---------+-----------+----+-----------+
When should the restart take place (e.g. 2025-09-26T09:20)  [now]:
Are you sure you want to restart members ubuntu08? [y/N]: y
Restart if the PostgreSQL version is less than provided (e.g. 9.5.2)  []:
Success: restart on member ubuntu08
root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
+ Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
| Member   | Host                 | Role    | State     | TL | Lag in MB |
+----------+----------------------+---------+-----------+----+-----------+
| ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  5 |         0 |
| ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  5 |         0 |
| ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  5 |           |
+----------+----------------------+---------+-----------+----+-----------+
root@ubuntu08:/usr/local/pgsql16/patroni#

8，重新加载patroni的配置文件

在修改patroni的参数的时候，类似于PostgreSQL中执行select pg_reload_conf()，需要重新加载参数，patronictl -c /usr/local/pgsql16/patroni/patroni.yml reload pg_cluster_wy_prod

root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml reload pg_cluster_wy_prod
+ Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
| Member   | Host                 | Role    | State     | TL | Lag in MB |
+----------+----------------------+---------+-----------+----+-----------+
| ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  5 |         0 |
| ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  5 |         0 |
| ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  5 |           |
+----------+----------------------+---------+-----------+----+-----------+
Are you sure you want to reload members ubuntu08, ubuntu09, ubuntu10? [y/N]: y
Reload request received for member ubuntu08 and will be processed within 10 seconds
Reload request received for member ubuntu09 and will be processed within 10 seconds
Reload request received for member ubuntu10 and will be processed within 10 seconds
root@ubuntu08:/usr/local/pgsql16/patroni#

9，重新初始化节点

当从节点损坏，需要重新初始化的情况下，执行命令为patronictl reinit pg_cluster_wy_prod。
任意节点上执行，会删除（需要初始化的）输入的从节点的数据目录，然后重新从主节点初始化PostgreSQL实例

root@ubuntu08:/usr/local/pgsql16/patroni#
root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml reinit pg_cluster_wy_prod
+ Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
| Member   | Host                 | Role    | State     | TL | Lag in MB |
+----------+----------------------+---------+-----------+----+-----------+
| ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  5 |         0 |
| ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  5 |         0 |
| ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  5 |           |
+----------+----------------------+---------+-----------+----+-----------+
Which member do you want to reinitialize [ubuntu09, ubuntu08]? []: ubuntu08    #输入需要重新初始化的主机
Are you sure you want to reinitialize members ubuntu08? [y/N]: y               #确认
Success: reinitialize for member ubuntu08
root@ubuntu08:/usr/local/pgsql16/patroni#
root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
+ Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
| Member   | Host                 | Role    | State     | TL | Lag in MB |
+----------+----------------------+---------+-----------+----+-----------+
| ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  5 |         0 |
| ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  5 |         0 |
| ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  5 |           |
+----------+----------------------+---------+-----------+----+-----------+
root@ubuntu08:/usr/local/pgsql16/patroni#
root@ubuntu08:/usr/local/pgsql16/patroni#

此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。原文来自 — 版权归原作者所有。

推荐订阅源

博客园 - MSSQL123

1，patronictl 查看集群状态

2，patronictl 暂停auto failover

3，patronictl 重启集群

4，patronictl 查看参数

5，修改参数

5.1 edit-config方式修改参数1

5.2 edit-config方式修改参数2

5.3 PostgreSQL 的 ALTER SYSTEM SET 命令

6，patroni切换PostgreSQL主从

6.1 patronictl switchover

6.2 patonictl failover

7，重启PostgreSQL实例

8，重新加载patroni的配置文件

9，重新初始化节点