PostgreSQL patroni 高可用 3：patroni 运维
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 pauseroot@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/%farchive_mode: 'on'archive_timeout: 1800shot_standby: 'on'log_autovacuum_min_duration: 0log_checkpoints: 'on'log_connections: 'on'log_destination: stderrlog_directory: loglog_disconnections: 'on'log_file_mode: '0600'log_filename: postgresql-%Y-%m-%d_%H%M%S.loglog_hostname: 'on'log_line_prefix: '%m [%p] user:%u,db:%d,app:%a,host:%h 'log_lock_waits: 'on'log_min_duration_statement: 1log_recovery_conflict_waits: 'on'log_replication_commands: 'on'log_rotation_age: 1dlog_statement: ddllog_temp_files: 1log_timezone: Asia/Shanghailogging_collector: 'on'max_connections: 100max_locks_per_transaction: 64max_prepared_transactions: 0max_replication_slots: 10max_wal_senders: 10max_worker_processes: 8shared_buffers: 1024MBtrack_commit_timestamp: 'off'wal_keep_segments: 8wal_level: hot_standbywal_log_hints: 'on'work_mem: 32MBpg_hba:- host replication repl_user 0.0.0.0/0 md5- host all all 0.0.0.0/0 md5use_pg_rewind: trueuse_slots: true
retry_timeout: 10
synchronous_mode: false
ttl: 30# 启用 auto failoverpatronictl -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/%farchive_mode: 'on'archive_timeout: 1800shot_standby: 'on'log_autovacuum_min_duration: 0log_checkpoints: 'on'log_connections: 'on'log_destination: stderrlog_directory: loglog_disconnections: 'on'log_file_mode: '0600'log_filename: postgresql-%Y-%m-%d_%H%M%S.loglog_hostname: 'on'log_line_prefix: '%m [%p] user:%u,db:%d,app:%a,host:%h 'log_lock_waits: 'on'log_min_duration_statement: 1log_recovery_conflict_waits: 'on'log_replication_commands: 'on'log_rotation_age: 1dlog_statement: ddllog_temp_files: 1log_timezone: Asia/Shanghailogging_collector: 'on'max_connections: 100max_locks_per_transaction: 64max_prepared_transactions: 0max_replication_slots: 10max_wal_senders: 10max_worker_processes: 8shared_buffers: 1024MBtrack_commit_timestamp: 'off'wal_keep_segments: 8wal_level: hot_standbywal_log_hints: 'on'work_mem: 32MBpg_hba:- host replication repl_user 0.0.0.0/0 md5- host all all 0.0.0.0/0 md5use_pg_rewind: trueuse_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/%farchive_mode: 'on'archive_timeout: 1800shot_standby: 'on'log_autovacuum_min_duration: 0log_checkpoints: 'on'log_connections: 'on'log_destination: stderrlog_directory: loglog_disconnections: 'on'log_file_mode: '0600'log_filename: postgresql-%Y-%m-%d_%H%M%S.loglog_hostname: 'on'log_line_prefix: '%m [%p] user:%u,db:%d,app:%a,host:%h 'log_lock_waits: 'on'log_min_duration_statement: 1log_recovery_conflict_waits: 'on'log_replication_commands: 'on'log_rotation_age: 1dlog_statement: ddllog_temp_files: 1log_timezone: Asia/Shanghailogging_collector: 'on'max_connections: 100max_locks_per_transaction: 64max_prepared_transactions: 0max_replication_slots: 10max_wal_senders: 10max_worker_processes: 8shared_buffers: 1024MBtrack_commit_timestamp: 'off'wal_keep_segments: 8wal_level: hot_standbywal_log_hints: 'on'work_mem: 32MBpg_hba:- host replication repl_user 0.0.0.0/0 md5- host all all 0.0.0.0/0 md5use_pg_rewind: trueuse_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: 1log_timezone: Asia/Shanghailogging_collector: 'on'
- max_connections: 100
+ max_connections: 120max_locks_per_transaction: 64max_prepared_transactions: 0max_replication_slots: 10max_wal_senders: 10max_worker_processes: 8
- shared_buffers: 1024MB
+ shared_buffers: 512MBtrack_commit_timestamp: 'off'wal_keep_segments: 8wal_level: hot_standbyApply 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: 1log_timezone: Asia/Shanghailogging_collector: 'on'
- max_connections: 120
+ max_connections: 300max_locks_per_transaction: 64max_prepared_transactions: 0max_replication_slots: 10Apply 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#