登录/ 注册
 找回密码
 立即注册
关闭

CSDN热搜

程序园 精品问答 技术交流 资源下载
返回列表 发新帖
首页 › 业界区 › 安全 ›PostgreSQL patroni 高可用 3：patroni 运维

PostgreSQL patroni 高可用 3：patroni 运维

僚娥 2025-9-26 18:23:55
PostgreSQL patroni高可用
PostgreSQL patroni 高可用 1：ectd 安装和配置
PostgreSQL patroni 高可用 2：patroni安装和配置
PostgreSQL patroni 高可用 3：patroni 运维
 
 PostgreSQL patroni 高可用 3：patroni 运维 PostgreSQL ptroni的高可用架构图如下所示，本文完成如下架构图中红色标记内的patroni安装和配置。
1.png

图片来源于：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 列
  1. #patroni集群状态查看
  2. root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
  3. + Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
  4. | Member   | Host                 | Role    | State     | TL | Lag in MB |
  5. +----------+----------------------+---------+-----------+----+-----------+
  6. | ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  5 |         0 |
  7. | ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  5 |         0 |
  8. | ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  5 |           |
  9. +----------+----------------------+---------+-----------+----+-----------+
  15. #修改相关参数之后
  16. root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
  17. + Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+-----------------+----------------------------+
  18. | Member   | Host                 | Role    | State     | TL | Lag in MB | Pending restart | Pending restart reason     |
  19. +----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+
  20. | ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  2 |         0 | *               | max_connections: 100->120  |
  21. |          |                      |         |           |    |           |                 | shared_buffers: 1GB->512MB |
  22. +----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+
  23. | ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  2 |         0 | *               | max_connections: 100->120  |
  24. |          |                      |         |           |    |           |                 | shared_buffers: 1GB->512MB |
  25. +----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+
  26. | ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  2 |           | *               | max_connections: 100->120  |
  27. |          |                      |         |           |    |           |                 | shared_buffers: 1GB->512MB |
  28. +----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+
复制代码
 

2，patronictl 暂停auto failover

某些情况下，比如重启服务器等运维措施实施的时候，此时并不想改变数据库的主从关系，就需要暂停auto failover，命令为patronictl pause暂停auto failover
  1. -- 禁用 auto failover，如果没有启动 patroni，执行 patronictl pause 会失败
  2. patronictl -c /usr/local/pgsql16/patroni/patroni.yml pause
  4. root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml pause
  5. Success: cluster management is paused
  6. root@ubuntu08:/usr/local/pgsql16/patroni# patronictl list
  7. root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml show-config
  8. loop_wait: 10
  9. master_start_timeout: 300
  10. master_stop_timeout: 0
  11. max_timelines_history: 0
  12. maximum_lag_on_failover: 1048576
  13. #备注行：启用之后，用show-config看到pause: true配置项
  14. pause: true
  15. postgresql:
  16.   parameters:
  17.     archive_command: test ! -f /usr/local/pgsql16/pg_wal_arch/%f && cp %p /usr/local/pgsql16/pg_wal_arch/%f
  18.     archive_mode: 'on'
  19.     archive_timeout: 1800s
  20.     hot_standby: 'on'
  21.     log_autovacuum_min_duration: 0
  22.     log_checkpoints: 'on'
  23.     log_connections: 'on'
  24.     log_destination: stderr
  25.     log_directory: log
  26.     log_disconnections: 'on'
  27.     log_file_mode: '0600'
  28.     log_filename: postgresql-%Y-%m-%d_%H%M%S.log
  29.     log_hostname: 'on'
  30.     log_line_prefix: '%m [%p] user:%u,db:%d,app:%a,host:%h '
  31.     log_lock_waits: 'on'
  32.     log_min_duration_statement: 1
  33.     log_recovery_conflict_waits: 'on'
  34.     log_replication_commands: 'on'
  35.     log_rotation_age: 1d
  36.     log_statement: ddl
  37.     log_temp_files: 1
  38.     log_timezone: Asia/Shanghai
  39.     logging_collector: 'on'
  40.     max_connections: 100
  41.     max_locks_per_transaction: 64
  42.     max_prepared_transactions: 0
  43.     max_replication_slots: 10
  44.     max_wal_senders: 10
  45.     max_worker_processes: 8
  46.     shared_buffers: 1024MB
  47.     track_commit_timestamp: 'off'
  48.     wal_keep_segments: 8
  49.     wal_level: hot_standby
  50.     wal_log_hints: 'on'
  51.     work_mem: 32MB
  52.   pg_hba:
  53.   - host replication repl_user 0.0.0.0/0 md5
  54.   - host all all 0.0.0.0/0 md5
  55.   use_pg_rewind: true
  56.   use_slots: true
  57. retry_timeout: 10
  58. synchronous_mode: false
  59. ttl: 30
  61. # 启用 auto failover
  62. patronictl -c /usr/local/pgsql16/patroni/patroni.yml resume
  63. # 启用之后，用show-config看不到pause: true
  64. root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml resume
  65. Success: cluster management is resumed
  66. root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml show-config
  67. loop_wait: 10
  68. master_start_timeout: 300
  69. master_stop_timeout: 0
  70. max_timelines_history: 0
  71. maximum_lag_on_failover: 1048576
  72. postgresql:
  73.   parameters:
  74.     archive_command: test ! -f /usr/local/pgsql16/pg_wal_arch/%f && cp %p /usr/local/pgsql16/pg_wal_arch/%f
  75.     archive_mode: 'on'
  76.     archive_timeout: 1800s
  77.     hot_standby: 'on'
  78.     log_autovacuum_min_duration: 0
  79.     log_checkpoints: 'on'
  80.     log_connections: 'on'
  81.     log_destination: stderr
  82.     log_directory: log
  83.     log_disconnections: 'on'
  84.     log_file_mode: '0600'
  85.     log_filename: postgresql-%Y-%m-%d_%H%M%S.log
  86.     log_hostname: 'on'
  87.     log_line_prefix: '%m [%p] user:%u,db:%d,app:%a,host:%h '
  88.     log_lock_waits: 'on'
  89.     log_min_duration_statement: 1
  90.     log_recovery_conflict_waits: 'on'
  91.     log_replication_commands: 'on'
  92.     log_rotation_age: 1d
  93.     log_statement: ddl
  94.     log_temp_files: 1
  95.     log_timezone: Asia/Shanghai
  96.     logging_collector: 'on'
  97.     max_connections: 100
  98.     max_locks_per_transaction: 64
  99.     max_prepared_transactions: 0
  100.     max_replication_slots: 10
  101.     max_wal_senders: 10
  102.     max_worker_processes: 8
  103.     shared_buffers: 1024MB
  104.     track_commit_timestamp: 'off'
  105.     wal_keep_segments: 8
  106.     wal_level: hot_standby
  107.     wal_log_hints: 'on'
  108.     work_mem: 32MB
  109.   pg_hba:
  110.   - host replication repl_user 0.0.0.0/0 md5
  111.   - host all all 0.0.0.0/0 md5
  112.   use_pg_rewind: true
  113.   use_slots: true
  114. retry_timeout: 10
  115. synchronous_mode: false
  116. ttl: 30
复制代码
 
3，patronictl 重启集群

在某些参数修改后，需要重启的情况下，重启命令为patronictl -c /usr/local/pgsql16/patroni/patroni.yml restart pg_cluster_wy_prod可在任意节点执行上述命令重启集群的PostgreSQL实例，重启过程中不会故障转移，各节点的主从身份保持不变
  1. root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
  2. + Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+-----------------+----------------------------+
  3. | Member   | Host                 | Role    | State     | TL | Lag in MB | Pending restart | Pending restart reason     |
  4. +----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+
  5. | ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  2 |         0 | *               | max_connections: 100->120  |
  6. |          |                      |         |           |    |           |                 | shared_buffers: 1GB->512MB |
  7. +----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+
  8. | ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  2 |         0 | *               | max_connections: 100->120  |
  9. |          |                      |         |           |    |           |                 | shared_buffers: 1GB->512MB |
  10. +----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+
  11. | ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  2 |           | *               | max_connections: 100->120  |
  12. |          |                      |         |           |    |           |                 | shared_buffers: 1GB->512MB |
  13. +----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+
  14. root@ubuntu08:/usr/local/pgsql16/patroni#
  15. root@ubuntu08:/usr/local/pgsql16/patroni#
  16. root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml restart pg_cluster_wy_prod
  17. + Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+-----------------+----------------------------+
  18. | Member   | Host                 | Role    | State     | TL | Lag in MB | Pending restart | Pending restart reason     |
  19. +----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+
  20. | ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  2 |         0 | *               | max_connections: 100->120  |
  21. |          |                      |         |           |    |           |                 | shared_buffers: 1GB->512MB |
  22. +----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+
  23. | ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  2 |         0 | *               | max_connections: 100->120  |
  24. |          |                      |         |           |    |           |                 | shared_buffers: 1GB->512MB |
  25. +----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+
  26. | ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  2 |           | *               | max_connections: 100->120  |
  27. |          |                      |         |           |    |           |                 | shared_buffers: 1GB->512MB |
  28. +----------+----------------------+---------+-----------+----+-----------+-----------------+----------------------------+
  29. When should the restart take place (e.g. 2025-09-26T08:49)  [now]:
  30. Are you sure you want to restart members ubuntu08, ubuntu09, ubuntu10? [y/N]: y
  31. Restart if the PostgreSQL version is less than provided (e.g. 9.5.2)  []:
  32. Success: restart on member ubuntu08
  33. Success: restart on member ubuntu09
  34. Success: restart on member ubuntu10
  35. root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
  36. + Cluster: pg_cluster_wy_prod (7553485872297570126) --+----+-----------+
  37. | Member   | Host                 | Role    | State   | TL | Lag in MB |
  38. +----------+----------------------+---------+---------+----+-----------+
  39. | ubuntu08 | 192.168.152.115:9000 | Replica | running |  2 |         0 |
  40. | ubuntu09 | 192.168.152.116:9000 | Replica | running |  2 |         0 |
  41. | ubuntu10 | 192.168.152.117:9000 | Leader  | running |  2 |           |
  42. +----------+----------------------+---------+---------+----+-----------+
  43. 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文件本身，这一点非常容易误解。
  1. root@ubuntu08:/usr/local/pgsql16/patroni#  patronictl -c /usr/local/pgsql16/patroni/patroni.yml  show-config
  2. loop_wait: 10
  3. master_start_timeout: 300
  4. master_stop_timeout: 0
  5. max_timelines_history: 0
  6. maximum_lag_on_failover: 1048576
  7. postgresql:
  8.   parameters:
  9.     archive_command: test ! -f /usr/local/pgsql16/pg_wal_arch/%f && cp %p /usr/local/pgsql16/pg_wal_arch/%f
  10.     archive_mode: 'on'
  11.     archive_timeout: 1800s
  12.     hot_standby: 'on'
  13.     log_autovacuum_min_duration: 0
  14.     log_checkpoints: 'on'
  15.     log_connections: 'on'
  16.     log_destination: stderr
  17.     log_directory: log
  18.     log_disconnections: 'on'
  19.     log_file_mode: '0600'
  20.     log_filename: postgresql-%Y-%m-%d_%H%M%S.log
  21.     log_hostname: 'on'
  22.     log_line_prefix: '%m [%p] user:%u,db:%d,app:%a,host:%h '
  23.     log_lock_waits: 'on'
  24.     log_min_duration_statement: 1
  25.     log_recovery_conflict_waits: 'on'
  26.     log_replication_commands: 'on'
  27.     log_rotation_age: 1d
  28.     log_statement: ddl
  29.     log_temp_files: 1
  30.     log_timezone: Asia/Shanghai
  31.     logging_collector: 'on'
  32.     max_connections: 100
  33.     max_locks_per_transaction: 64
  34.     max_prepared_transactions: 0
  35.     max_replication_slots: 10
  36.     max_wal_senders: 10
  37.     max_worker_processes: 8
  38.     shared_buffers: 1024MB
  39.     track_commit_timestamp: 'off'
  40.     wal_keep_segments: 8
  41.     wal_level: hot_standby
  42.     wal_log_hints: 'on'
  43.     work_mem: 32MB
  44.   pg_hba:
  45.   - host replication repl_user 0.0.0.0/0 md5
  46.   - host all all 0.0.0.0/0 md5
  47.   use_pg_rewind: true
  48.   use_slots: true
  49. retry_timeout: 10
  50. synchronous_mode: false
  51. ttl: 30
复制代码
 
5，修改参数

5.1  edit-config方式修改参数1

patronictl -c /usr/local/pgsql16/patroni/patroni.yml edit-configUbuntu下edit-config以nano方式编辑参数文件，编辑完成后需要（Ctrl + O（写入文件）→ Enter（确认） → Ctrl + X（保存并退出））
  1. root@ubuntu08:/usr/local/pgsql16/patroni#  patronictl -c /usr/local/pgsql16/patroni/patroni.yml edit-config
  2. ---
  3. +++
  4. @@ -28,13 +28,13 @@
  5.      log_temp_files: 1
  6.      log_timezone: Asia/Shanghai
  7.      logging_collector: 'on'
  8. -    max_connections: 100
  9. +    max_connections: 120
  10.      max_locks_per_transaction: 64
  11.      max_prepared_transactions: 0
  12.      max_replication_slots: 10
  13.      max_wal_senders: 10
  14.      max_worker_processes: 8
  15. -    shared_buffers: 1024MB
  16. +    shared_buffers: 512MB
  17.      track_commit_timestamp: 'off'
  18.      wal_keep_segments: 8
  19.      wal_level: hot_standby
  21. Apply these changes? [y/N]: y
  22. Configuration changed
  23. 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'
  1. root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
  2. + Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
  3. | Member   | Host                 | Role    | State     | TL | Lag in MB |
  4. +----------+----------------------+---------+-----------+----+-----------+
  5. | ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  2 |         0 |
  6. | ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  2 |         0 |
  7. | ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  2 |           |
  8. +----------+----------------------+---------+-----------+----+-----------+
  9. root@ubuntu08:/usr/local/pgsql16/patroni#   patronictl -c /usr/local/pgsql16/patroni/patroni.yml edit-config -s 'postgresql.parameters.max_connections=300'
  10. ---
  11. +++
  12. @@ -28,7 +28,7 @@
  13.      log_temp_files: 1
  14.      log_timezone: Asia/Shanghai
  15.      logging_collector: 'on'
  16. -    max_connections: 120
  17. +    max_connections: 300
  18.      max_locks_per_transaction: 64
  19.      max_prepared_transactions: 0
  20.      max_replication_slots: 10
  22. Apply these changes? [y/N]: y
  23. Configuration changed
  24. root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list #备注：patronictl list执行后，这里一开始不显示需要重启，过几秒中才能看到
  25. + Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+-----------------+---------------------------+
  26. | Member   | Host                 | Role    | State     | TL | Lag in MB | Pending restart | Pending restart reason    |
  27. +----------+----------------------+---------+-----------+----+-----------+-----------------+---------------------------+
  28. | ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  2 |         0 | *               | max_connections: 120->300 |
  29. | ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  2 |         0 | *               | max_connections: 120->300 |
  30. | ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  2 |           | *               | max_connections: 120->300 |
  31. +----------+----------------------+---------+-----------+----+-----------+-----------------+---------------------------+
复制代码
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并不是修改前的值，而是默认值，我一直以为搞错了，试了三次才发现是这个规律 <img>。  5.3 PostgreSQL 的 ALTER SYSTEM SET 命令
  1. --修改work_mem
  2. ALTER SYSTEM SET work_mem TO '8MB';
  3. 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允许在集群中的任意节点执行切换操作
  1. root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
  2. + Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
  3. | Member   | Host                 | Role    | State     | TL | Lag in MB |
  4. +----------+----------------------+---------+-----------+----+-----------+
  5. | ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  3 |         0 |
  6. | ubuntu09 | 192.168.152.116:9000 | Leader  | running   |  3 |           |
  7. | ubuntu10 | 192.168.152.117:9000 | Replica | streaming |  3 |         0 |
  8. +----------+----------------------+---------+-----------+----+-----------+
  9. root@ubuntu08:/usr/local/pgsql16/patroni#
  10. root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml switchover
  11. Current cluster topology
  12. + Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
  13. | Member   | Host                 | Role    | State     | TL | Lag in MB |
  14. +----------+----------------------+---------+-----------+----+-----------+
  15. | ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  3 |         0 |
  16. | ubuntu09 | 192.168.152.116:9000 | Leader  | running   |  3 |           |
  17. | ubuntu10 | 192.168.152.117:9000 | Replica | streaming |  3 |         0 |
  18. +----------+----------------------+---------+-----------+----+-----------+
  19. Primary [ubuntu09]:                                                                                                                                                                                        #1，这里是提示当前主节点为Ubuntu09，不需要任何输入，回车即可
  20. Candidate ['ubuntu08', 'ubuntu10'] []: ubuntu08                                                                                                                                #2，这里列举出2个从节点，可以选择输入其中一个从节点提升为主节点，也可以不选择（默认选择）
  21. When should the switchover take place (e.g. 2025-09-26T09:08 )  [now]:                                                                                #3，提示当前直接执行切换，回车即可
  22. Are you sure you want to switchover cluster pg_cluster_wy_prod, demoting current leader ubuntu09? [y/N]: y        #4，输入y确认执行
  23. 2025-09-26 08:09:02.30730 Successfully switched over to "ubuntu08"
  24. + Cluster: pg_cluster_wy_prod (7553485872297570126) --+----+-----------+
  25. | Member   | Host                 | Role    | State   | TL | Lag in MB |
  26. +----------+----------------------+---------+---------+----+-----------+
  27. | ubuntu08 | 192.168.152.115:9000 | Leader  | running |  3 |           |
  28. | ubuntu09 | 192.168.152.116:9000 | Replica | stopped |    |   unknown |
  29. | ubuntu10 | 192.168.152.117:9000 | Replica | running |  3 |         0 |
  30. +----------+----------------------+---------+---------+----+-----------+
  31. root@ubuntu08:/usr/local/pgsql16/patroni#
  32. root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
  33. + Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
  34. | Member   | Host                 | Role    | State     | TL | Lag in MB |
  35. +----------+----------------------+---------+-----------+----+-----------+
  36. | ubuntu08 | 192.168.152.115:9000 | Leader  | running   |  4 |           |
  37. | ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  4 |         0 |
  38. | ubuntu10 | 192.168.152.117:9000 | Replica | streaming |  4 |         0 |
  39. +----------+----------------------+---------+-----------+----+-----------+
  40. root@ubuntu08:/usr/local/pgsql16/patroni#
复制代码
6.2 patonictl failover 

patonictl failover 允许在没有健康节点时执行手动 failover ，也就是节点故障了，不能switchover了，只能用failover来切换 。patronictl failover允许在集群中的任意节点执行切换操作
  1. root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml failover
  2. Current cluster topology
  3. + Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
  4. | Member   | Host                 | Role    | State     | TL | Lag in MB |
  5. +----------+----------------------+---------+-----------+----+-----------+
  6. | ubuntu08 | 192.168.152.115:9000 | Leader  | running   |  4 |           |
  7. | ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  4 |         0 |
  8. | ubuntu10 | 192.168.152.117:9000 | Replica | streaming |  4 |         0 |
  9. +----------+----------------------+---------+-----------+----+-----------+
  10. Candidate ['ubuntu09', 'ubuntu10'] []: ubuntu10                                                                                                                                        #1：这里列举出来从节点，输入从节点主机名
  11. Are you sure you want to failover cluster pg_cluster_wy_prod, demoting current leader ubuntu08? [y/N]: y                #2：这里输入y，执行切换确认
  12. 2025-09-26 08:15:47.24592 Successfully failed over to "ubuntu10"
  13. + Cluster: pg_cluster_wy_prod (7553485872297570126) --+----+-----------+
  14. | Member   | Host                 | Role    | State   | TL | Lag in MB |
  15. +----------+----------------------+---------+---------+----+-----------+
  16. | ubuntu08 | 192.168.152.115:9000 | Replica | stopped |    |   unknown |
  17. | ubuntu09 | 192.168.152.116:9000 | Replica | running |  4 |         0 |
  18. | ubuntu10 | 192.168.152.117:9000 | Leader  | running |  4 |           |
  19. +----------+----------------------+---------+---------+----+-----------+
  20. root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
  21. + Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
  22. | Member   | Host                 | Role    | State     | TL | Lag in MB |
  23. +----------+----------------------+---------+-----------+----+-----------+
  24. | ubuntu08 | 192.168.152.115:9000 | Replica | stopped   |    |   unknown |
  25. | ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  4 |         0 |
  26. | ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  5 |           |
  27. +----------+----------------------+---------+-----------+----+-----------+
  28. root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
  29. + Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
  30. | Member   | Host                 | Role    | State     | TL | Lag in MB |
  31. +----------+----------------------+---------+-----------+----+-----------+
  32. | ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  5 |         0 |
  33. | ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  5 |         0 |
  34. | ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  5 |           |
  35. +----------+----------------------+---------+-----------+----+-----------+
  36. root@ubuntu08:/usr/local/pgsql16/patroni#
复制代码
 
7，重启PostgreSQL实例

patronictl -c /usr/local/pgsql16/patroni/patroni.yml restart cluster_name（集群名） hostname（主机名）
  1. root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml restart pg_cluster_wy_prod ubuntu08
  2. + Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
  3. | Member   | Host                 | Role    | State     | TL | Lag in MB |
  4. +----------+----------------------+---------+-----------+----+-----------+
  5. | ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  5 |         0 |
  6. | ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  5 |         0 |
  7. | ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  5 |           |
  8. +----------+----------------------+---------+-----------+----+-----------+
  9. When should the restart take place (e.g. 2025-09-26T09:20)  [now]:
  10. Are you sure you want to restart members ubuntu08? [y/N]: y
  11. Restart if the PostgreSQL version is less than provided (e.g. 9.5.2)  []:
  12. Success: restart on member ubuntu08
  13. root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
  14. + Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
  15. | Member   | Host                 | Role    | State     | TL | Lag in MB |
  16. +----------+----------------------+---------+-----------+----+-----------+
  17. | ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  5 |         0 |
  18. | ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  5 |         0 |
  19. | ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  5 |           |
  20. +----------+----------------------+---------+-----------+----+-----------+
  21. 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
  1. root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml reload pg_cluster_wy_prod
  2. + Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
  3. | Member   | Host                 | Role    | State     | TL | Lag in MB |
  4. +----------+----------------------+---------+-----------+----+-----------+
  5. | ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  5 |         0 |
  6. | ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  5 |         0 |
  7. | ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  5 |           |
  8. +----------+----------------------+---------+-----------+----+-----------+
  9. Are you sure you want to reload members ubuntu08, ubuntu09, ubuntu10? [y/N]: y
  10. Reload request received for member ubuntu08 and will be processed within 10 seconds
  11. Reload request received for member ubuntu09 and will be processed within 10 seconds
  12. Reload request received for member ubuntu10 and will be processed within 10 seconds
  13. root@ubuntu08:/usr/local/pgsql16/patroni#
复制代码
 

9，重新初始化节点

当从节点损坏，需要重新初始化的情况下，执行命令为patronictl reinit pg_cluster_wy_prod。
任意节点上执行，会删除（需要初始化的）输入的从节点的数据目录，然后重新从主节点初始化PostgreSQL实例
  1. root@ubuntu08:/usr/local/pgsql16/patroni#
  2. root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml reinit pg_cluster_wy_prod
  3. + Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
  4. | Member   | Host                 | Role    | State     | TL | Lag in MB |
  5. +----------+----------------------+---------+-----------+----+-----------+
  6. | ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  5 |         0 |
  7. | ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  5 |         0 |
  8. | ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  5 |           |
  9. +----------+----------------------+---------+-----------+----+-----------+
  10. Which member do you want to reinitialize [ubuntu09, ubuntu08]? []: ubuntu08    #输入需要重新初始化的主机
  11. Are you sure you want to reinitialize members ubuntu08? [y/N]: y               #确认
  12. Success: reinitialize for member ubuntu08
  13. root@ubuntu08:/usr/local/pgsql16/patroni#
  14. root@ubuntu08:/usr/local/pgsql16/patroni# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
  15. + Cluster: pg_cluster_wy_prod (7553485872297570126) ----+----+-----------+
  16. | Member   | Host                 | Role    | State     | TL | Lag in MB |
  17. +----------+----------------------+---------+-----------+----+-----------+
  18. | ubuntu08 | 192.168.152.115:9000 | Replica | streaming |  5 |         0 |
  19. | ubuntu09 | 192.168.152.116:9000 | Replica | streaming |  5 |         0 |
  20. | ubuntu10 | 192.168.152.117:9000 | Leader  | running   |  5 |           |
  21. +----------+----------------------+---------+-----------+----+-----------+
  22. root@ubuntu08:/usr/local/pgsql16/patroni#
  23. root@ubuntu08:/usr/local/pgsql16/patroni#
复制代码
 

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
patroniPostgreSQL高可用可用运维

使用道具 举报

相关推荐
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册
发帖
僚娥
2025-9-26 18:23:55

0

粉丝关注

16

主题发布

板块介绍填写区域，请于后台编辑
微信扫一扫