PostgreSQL patroni 高可用 2：patroni安装和配置
PostgreSQL patroni高可用PostgreSQL patroni 高可用 1：ectd 安装和配置
PostgreSQL patroni 高可用 2：patroni安装和配置
PostgreSQL patroni 高可用 3：patroni 运维
PostgreSQL patroni 高可用 2：patroni 安装 PostgreSQL ptroni的高可用架构图如下所示，本文完成如下架构图中红色标记内的patroni安装和配置。
图片来源于：https://docs.percona.com/postgresql/12/solutions/high-availability.html#architecture-layout
1，服务器环境
Ubuntu08：192.168.152.115Ubuntu09：192.168.152.116Ubuntu10：192.168.152.117 2，patroni依赖包安装
patroni是一个python开发的中间件，需要依赖python3环境以及python库
Patroni 表示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
安装目录
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/patroniscope: 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 启动文件，各个节点都一样
Description=patroni
After=network.target remote-fs.target nss-lookup.target etcd.service #一定要等到etcd启动之后再启动patroni
Requires=etcd.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
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: Starting patroni...
Sep 22 14:27:29 ubuntu08 systemd: 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并不是修改前的值，而是默认值，我一直以为搞错了，试了三次才发现是这个规律 <img>。 方法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参数的新增或者修改不会记录类似修改前后的值？
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！ 前排留名，哈哈哈
页:
[1]