专注，勤学，慎思。戒骄戒躁，谦虚谨慎

just do it

导航

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

 

posted on 2025-09-26 14:33  MSSQL123  阅读(150)  评论(0)    收藏  举报