動態

詳情 返回 返回

PostgreSQL patroni 高可用 2：patroni安裝和配置 - 動態 詳情

PostgreSQL patroni高可用

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

 


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

image
圖片來源於：https://docs.percona.com/postgresql/12/solutions/high-availability.html#architecture-layout

1，服務器環境

Ubuntu08：192.168.152.115
Ubuntu09：192.168.152.116
Ubuntu10：192.168.152.117
 

2，patroni依賴包安裝

patroni是一個python開發的中間件，需要依賴python3環境以及python庫
Patroni[etcd] 表示Patroni 使用 etcd 作為分佈式配置存儲 (DCS)。 

apt update
apt install -y libpq-dev python3-dev gcc
pip3 install -U pip setuptools wheel importlib_metadata
pip3 install -U psycopg2
pip3 install -U psycopg2-binary
pip3 install -U patroni[etcd]
安裝目錄
root@ubuntu08:/usr/local# whereis patroni
patroni: /usr/local/bin/patroni

 

3，創建patroni目錄

mkdir /usr/local/pgsql16/patroni
該目錄一定要授權給postgres用户，因為patroni是用postgres用户運行的，否則會導致後續無權限寫入
chown -R postgres:postgres /usr/local/pgsql16/patroni
chmod 700 -R /usr/local/pgsql16/patroni

 

4，編譯patroni配置文件patroni.yml

1，本地初已安裝的PostgreSQL服務需手動停止，並且移除數據文件目錄，
2，patroni啓動會自動初始化數據庫，以及創建postgres用户，其中以patroni.yml參數文件中指定的參數為準。
3，etcd3節點，一定要指定etcd3，默認是etcd2，默認情況會報錯
4，scope: pg_cluster_wy_prod，PostgreSQL實例的cluster_name參數，集羣中所有節點的cluster_name都會設置為該名字
     namespace: /service/ ，etcd的前綴名字，類似一個命名空間的名字
5，patroni.yml是靠空格縮進的，而不是tab鍵，整個參數文件中，任何一行，如果多一個或者少一個空格，都會導致無法啓動，這一點非常逆天，該死的yml配置，希望早日放棄這種逆天的配置格式。
  筆者在這裏卡了很久，而且實際測試中看，patroni.yml並不支持中文備註，如下配置文件中的中文註釋僅供參考，搭建環境是需移除
 

參考如下配置文件，patroni啓動的時候，會以如下文件為模板，將相關配置項寫入ectd數據庫中，同時會初始化一個數據庫。
 

需要注意的是：
在etcd的搭建過程中，etcd的配置文件中，是定義了集羣內所有成員的，也即如下
ETCD_INITIAL_CLUSTER="etcd01=http://192.168.152.115:2380,etcd02=http://192.168.152.116:2380,etcd03=http://192.168.152.117:2380"，但是在patroni的配置文件，中並沒有指定PostgreSQL的主從信息，為什麼patroni啓動後，從節點會自動從主節點複製數據？

Patroni 節點啓動時流程如下：
1，Patroni節點啓動後，向DCS（這裏是 etcd）註冊自己的信息（名字、角色、狀態）。
2，Patroni 查詢 DCS，看當前集羣有沒有 leader（primary）。
  如果已有 leader → 本節點會自動配置成 replica，並從 leader 獲取數據。
  如果沒有 leader → 通過選舉，本節點可能成為新的 leader。
 
 
Ubuntu08上的patroni配置文件，位置：/usr/local/pgsql16/patroni
scope: pg_cluster_wy_prod
namespace: /service/
name: ubuntu08

restapi:
  listen: 192.168.152.115:8008
  connect_address: 192.168.152.115:8008

# 這裏一定要是etcd3
etcd3:
  host: 192.168.152.115:2379

log:
  level: INFO
  traceback_level: ERROR
  dir: /usr/local/pgsql16/patroni/
  file_num: 10
  file_size: 104857600


bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    max_timelines_history: 0
    master_start_timeout: 300
    master_stop_timeout: 0
    synchronous_mode: false
    postgresql:
      use_pg_rewind: true
      pg_hba:
      - host replication repl_user 0.0.0.0/0 md5
      - host all all 0.0.0.0/0 md5
      use_slots: true
      parameters:
        wal_level: hot_standby
        hot_standby: "on"
        max_connections: 100
        max_worker_processes: 8
        wal_keep_segments: 8
        max_wal_senders: 10
        max_replication_slots: 10
        max_prepared_transactions: 0
        max_locks_per_transaction: 64
        wal_log_hints: "on"
        track_commit_timestamp: "off"
        archive_mode: "on"
        archive_timeout: 1800s
        archive_command: test ! -f /usr/local/pgsql16/pg_wal_arch/%f && cp %p /usr/local/pgsql16/pg_wal_arch/%f
        
        log_destination: stderr
        logging_collector: "on"
        log_directory: "log"
        log_filename: "postgresql-%Y-%m-%d_%H%M%S.log"
        log_file_mode: "0600"
        log_rotation_age: "1d"
        log_min_duration_statement: 1
        log_autovacuum_min_duration: 0
        log_checkpoints: "on"
        log_connections: "on"
        log_disconnections: "on"
        log_hostname: "on"
        log_line_prefix: "%m [%p] user:%u,db:%d,app:%a,host:%h "
        log_lock_waits: "on"
        log_recovery_conflict_waits: "on"
        log_statement: "ddl"
        log_replication_commands: "on"
        log_temp_files: 1
        log_timezone: "Asia/Shanghai"
        
  # some desired options for 'initdb'
  initdb:  # Note: It needs to be a list (some options need values, others are switches)
  - encoding: UTF8
  - data-checksums


postgresql:
  listen: 192.168.152.115:9000
  connect_address: 192.168.152.115:9000
  data_dir: /usr/local/pgsql16/pg9000/data
  bin_dir: /usr/local/pgsql16/server/bin
  #config_dir: 
  pgpass: /home/postgres/.pgpass
  # 用户名和密碼可以自定義
  authentication:
    replication:
      username: repl_user
      password: repl_user_123456
    superuser:
      username: postgres
      password: postgres_pwd
    rewind:  # Has no effect on postgres 10 and lower
      username: rewind_user
      password: rewind_pwd
  parameters:
    unix_socket_directories: '..'  # parent directory of data_dir


tags:
    # failover_priority: 1
    # sync_priority: 1
    noloadbalance: false
    clonefrom: false
    nostream: false
 
 
 

Ubuntu09上的patroni配置文件，位置：/usr/local/pgsql16/patroni

Ubuntu09上的patroni配置文件 
scope: pg_cluster_wy_prod
namespace: /service/
name: ubuntu09

restapi:
  listen: 192.168.152.116:8008
  connect_address: 192.168.152.116:8008

etcd3:
  host: 192.168.152.116:2379

log:
  level: INFO
  traceback_level: ERROR
  dir: /usr/local/pgsql16/patroni/
  file_num: 10
  file_size: 104857600


bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    max_timelines_history: 0
    master_start_timeout: 300
    master_stop_timeout: 0
    synchronous_mode: false
    postgresql:
      use_pg_rewind: true
      pg_hba:
      - host replication repl_user 0.0.0.0/0 md5
      - host all all 0.0.0.0/0 md5
      use_slots: true
      parameters:
        wal_level: hot_standby
        hot_standby: "on"
        max_connections: 100
        max_worker_processes: 8
        wal_keep_segments: 8
        max_wal_senders: 10
        max_replication_slots: 10
        max_prepared_transactions: 0
        max_locks_per_transaction: 64
        wal_log_hints: "on"
        track_commit_timestamp: "off"
        archive_mode: "on"
        archive_timeout: 1800s
        archive_command: test ! -f /usr/local/pgsql16/pg_wal_arch/%f && cp %p /usr/local/pgsql16/pg_wal_arch/%f
        log_destination: stderr
        logging_collector: "on"
        log_directory: "log"
        log_filename: "postgresql-%Y-%m-%d_%H%M%S.log"
        log_file_mode: "0600"
        log_rotation_age: "1d"
        log_min_duration_statement: 1
        log_autovacuum_min_duration: 0
        log_checkpoints: "on"
        log_connections: "on"
        log_disconnections: "on"
        log_hostname: "on"
        log_line_prefix: "%m [%p] user:%u,db:%d,app:%a,host:%h "
        log_lock_waits: "on"
        log_recovery_conflict_waits: "on"
        log_statement: "ddl"
        log_replication_commands: "on"
        log_temp_files: 1
        log_timezone: "Asia/Shanghai"
        
  # some desired options for 'initdb'
  initdb:  # Note: It needs to be a list (some options need values, others are switches)
  - encoding: UTF8
  - data-checksums


postgresql:
  listen: 192.168.152.116:9000
  connect_address: 192.168.152.116:9000
  data_dir: /usr/local/pgsql16/pg9000/data
  bin_dir: /usr/local/pgsql16/server/bin
  #config_dir: 
  pgpass: /home/postgres/.pgpass
  authentication:
    replication:
      username: repl_user
      password: repl_user_123456
    superuser:
      username: postgres
      password: postgres_pwd
    rewind:  # Has no effect on postgres 10 and lower
      username: rewind_user
      password: rewind_user_pwd
  parameters:
    unix_socket_directories: '..'  # parent directory of data_dir


tags:
    # failover_priority: 1
    # sync_priority: 1
    noloadbalance: false
    clonefrom: false
    nostream: false

 

Ubuntu10上的patroni配置文件，位置：/usr/local/pgsql16/patroni

Ubuntu10上的patroni配置文件 
scope: pg_cluster_wy_prod
namespace: /service/
name: ubuntu10

restapi:
  listen: 192.168.152.117:8008
  connect_address: 192.168.152.117:8008

etcd3:
  host: 192.168.152.117:2379

log:
  level: INFO
  traceback_level: ERROR
  dir: /usr/local/pgsql16/patroni/
  file_num: 10
  file_size: 104857600


bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    max_timelines_history: 0
    master_start_timeout: 300
    master_stop_timeout: 0
    synchronous_mode: false
    postgresql:
      use_pg_rewind: true
      pg_hba:
      - host replication repl_user 0.0.0.0/0 md5
      - host all all 0.0.0.0/0 md5
      use_slots: true
      parameters:
        wal_level: hot_standby
        hot_standby: "on"
        max_connections: 100
        max_worker_processes: 8
        wal_keep_segments: 8
        max_wal_senders: 10
        max_replication_slots: 10
        max_prepared_transactions: 0
        max_locks_per_transaction: 64
        wal_log_hints: "on"
        track_commit_timestamp: "off"
        archive_mode: "on"
        archive_timeout: 1800s
        archive_command: test ! -f /usr/local/pgsql16/pg_wal_arch/%f && cp %p /usr/local/pgsql16/pg_wal_arch/%f
        log_destination: stderr
        logging_collector: "on"
        log_directory: "log"
        log_filename: "postgresql-%Y-%m-%d_%H%M%S.log"
        log_file_mode: "0600"
        log_rotation_age: "1d"
        log_min_duration_statement: 1
        log_autovacuum_min_duration: 0
        log_checkpoints: "on"
        log_connections: "on"
        log_disconnections: "on"
        log_hostname: "on"
        log_line_prefix: "%m [%p] user:%u,db:%d,app:%a,host:%h "
        log_lock_waits: "on"
        log_recovery_conflict_waits: "on"
        log_statement: "ddl"
        log_replication_commands: "on"
        log_temp_files: 1
        log_timezone: "Asia/Shanghai"
        
  # some desired options for 'initdb'
  initdb:  # Note: It needs to be a list (some options need values, others are switches)
  - encoding: UTF8
  - data-checksums


postgresql:
  listen: 192.168.152.117:9000
  connect_address: 192.168.152.117:9000
  data_dir: /usr/local/pgsql16/pg9000/data
  bin_dir: /usr/local/pgsql16/server/bin
  #config_dir: 
  pgpass: /home/postgres/.pgpass
  authentication:
    replication:
      username: repl_user
      password: repl_user_123456
    superuser:
      username: postgres
      password: postgres_pwd
    rewind:  # Has no effect on postgres 10 and lower
      username: rewind_user
      password: rewind_user_pwd
  parameters:
    unix_socket_directories: '..'  # parent directory of data_dir


tags:
    # failover_priority: 1
    # sync_priority: 1
    noloadbalance: false
    clonefrom: false
    nostream: false

 

4.1 相關參數

scope: pg_cluster_wy_prod

1，該參數會註冊到etcd的key中
2，在patroni的配置中，可以在這裏體現出來
  scope: 參數為pg_cluster_wy_prod
  namespace:參數為 /service/
root@ubuntu08:/usr/local# etcdctl get /service --prefix --keys-only
{"level":"warn","ts":"2025-09-22T16:47:36.675953+0800","caller":"flags/flag.go:94","msg":"unrecognized environment variable","environment-variable":"ETCDCTL_API=3"}
/service/pg_cluster_wy_prod/config
/service/pg_cluster_wy_prod/history
/service/pg_cluster_wy_prod/initialize
/service/pg_cluster_wy_prod/leader
/service/pg_cluster_wy_prod/members/ubuntu08
/service/pg_cluster_wy_prod/members/ubuntu09
/service/pg_cluster_wy_prod/members/ubuntu10
/service/pg_cluster_wy_prod/status

 

5，編輯patroni systemctl服務文件

patroni的systemctl 啓動文件，各個節點都一樣

[Unit]
Description=patroni
After=network.target remote-fs.target nss-lookup.target etcd.service #一定要等到etcd啓動之後再啓動patroni
Requires=etcd.service
 
[Service]
Type=forking
User=postgres
Group=postgres
Environment="PGHOME=/usr/local/pgsql16/server"
Environment="PGDATA=/usr/local/pgsql16/pg9000/data"
Environment="PGPORT=9000"
Environment="LD_LIBRARY_PATH=/usr/local/pgsql16/server/lib"
Environment="PATH=/usr/local/pgsql16/server/bin:/usr/local/bin"
ExecStart=/bin/bash -c "patroni /usr/local/pgsql16/patroni/patroni.yml >> /usr/local/pgsql16/patroni/patroni.log 2>&1 &"
ExecReload=/bin/kill -s HUP $MAINPID
ExecStop=/usr/bin/killall patroni
KillMode=process
TimeoutSec=30
Restart=no

[Install]
WantedBy=multi-user.target

 

6，啓動partoni

設置自動啓動，並啓動服務，檢查集羣狀態，patronictl -c /usr/local/pgsql16/patroni/patroni.yml list

systemctl daemon-reload
systemctl start patroni
systemctl enable patroni

root@ubuntu08:/usr/local# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
+ Cluster: pg_cluster_wy_prod (7552796412810163150) +-----------+
| Member   | Host           | Role   | State   | TL | Lag in MB |
+----------+----------------+--------+---------+----+-----------+
| ubuntu08 | 127.0.0.1:9000 | Leader | running |  1 |           |
+----------+----------------+--------+---------+----+-----------+
root@ubuntu08:/usr/local#
root@ubuntu08:/usr/local# systemctl status patroni
● patroni.service - patroni
     Loaded: loaded (/etc/systemd/system/patroni.service; enabled; vendor preset: enabled)
     Active: active (running) since Mon 2025-09-22 14:27:29 CST; 42min ago
   Main PID: 623548 (patroni)
      Tasks: 13 (limit: 4550)
     Memory: 95.6M
     CGroup: /system.slice/patroni.service
             ├─623548 /usr/bin/python3 /usr/local/bin/patroni /usr/local/pgsql16/patroni/patroni.yml
             ├─623575 /usr/local/pgsql16/server/bin/postgres -D /usr/local/pgsql16/pg9000/data --config-file=/usr/local/pgsql16/pg9000/data/postgresql.conf --listen_addresses=127.0.0.1 --port=9000 --cluster>
             ├─623577 postgres: pg_cluster_wy_prod: checkpointer
             ├─623578 postgres: pg_cluster_wy_prod: background writer
             ├─623580 postgres: pg_cluster_wy_prod: walwriter
             ├─623581 postgres: pg_cluster_wy_prod: autovacuum launcher
             ├─623583 postgres: pg_cluster_wy_prod: logical replication launcher
             └─623588 postgres: pg_cluster_wy_prod: postgres postgres 127.0.0.1(48710) idle

Sep 22 14:27:29 ubuntu08 systemd[1]: Starting patroni...
Sep 22 14:27:29 ubuntu08 systemd[1]: Started patroni.




--查看patroni集羣狀態
root@ubuntu08:/usr/local# patronictl -c /usr/local/pgsql16/patroni/patroni.yml list
+ Cluster: pg_cluster_wy_prod (7552796412810163150) +-----------+
| Member   | Host           | Role   | State   | TL | Lag in MB |
+----------+----------------+--------+---------+----+-----------+
| ubuntu08 | 127.0.0.1:9000 | Leader | running |  1 |           |
+----------+----------------+--------+---------+----+-----------+
root@ubuntu08:/usr/local#
root@ubuntu08:/usr/local#

patroni環境變量配置，這樣不需要patronictl 每次都加上-c /usr/local/pgsql16/patroni/patroni.yml 來指定配置文件

為了方便日常操作，添加以下內容到/etc/profile中

vi /etc/profile
export PATRONICTL_CONFIG_FILE=/home/postgres/patroni/patroni.yml

source /etc/profile

 

7，patroni環境下修改PostgreSQL配置文件

這部分非常非常非常重要，很容易誤解，筆者在這裏花了一個多小時的時間，來測試各種場景。

方法1 patronictl edit-config

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並不是修改前的值，而是默認值，我一直以為搞錯了，試了三次才發現是這個規律 。
 

方法2 ALTER SYSTEM SET 命令

通過ALTER SYSTEM SET parameter='***'的方式修改
這種方式修改後，
1，可以再任意節點執行修改
2，執行成功後，會將參數保存在postgresql.auto.conf配置文件中，
3，這種方式的修改，只會對當前節點生效，*不會*同步至其他節點
“方法2”修改的參數會優先於“方式1”修改的加載，也就是説postgresql.auto.conf優先級會高於patroni.dynamic.json 
--在從節點上執行
SHOW listen_addresses;
listen_addresses|
----------------+
192.168.152.115 |
--查看當前work_mem
show work_mem;
work_mem|
--------+
4MB     |
--修改work_mem
ALTER SYSTEM SET work_mem TO '8MB';
SELECT pg_reload_conf();
 
通過patronictl edit-config來修改PostgreSQL的參數，遇到以下一個問題，原本修改記錄是記錄在etcd中的（再次説了edit-config的方式修改配置，不是修改patroni.yml這個物理配置文件自身，而是操作etcd數據庫），但是work_mem參數的修改就不會記錄在ectd中，還是有緩存？
 
work_mem參數的新增或者修改不會記錄類似修改前後的值？
image
 
image

 

postgresql , postgresql
點贊

Add a new 評論

Some HTML is okay.