动态

详情 返回 返回

PostgreSQL patroni 高可用 3：patroni 運維 - 动态 详情

PostgreSQL patroni高可用

PostgreSQL patroni 高可用 1：ectd 安裝和配置
PostgreSQL patroni 高可用 2：patroni安裝和配置
PostgreSQL patroni 高可用 3：patroni 運維
PostgreSQL patroni 高可用 4：HAProxy和Keepalived實現讀寫分離

 

 
PostgreSQL patroni 高可用 3：patroni 運維
 
PostgreSQL ptroni的高可用架構圖如下所示，本文完成如下架構圖中紅色標記內的patroni安裝和配置。

image

圖片來源於：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#

 

postgresql , postgresql
点赞

Add a new 评论

Some HTML is okay.