PostgreSQL patroni高可用

PostgreSQL patroni 高可用 1：ectd 安装和配置
PostgreSQL patroni 高可用 2：patroni安装和配置
PostgreSQL patroni 高可用 3：patroni 运维

需要注意的是：
在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启动后，从节点会自动从主节点复制数据？

scope: pg_cluster_wy_prodnamespace: /service/name: ubuntu08restapi:  listen: 192.168.152.115:8008  connect_address: 192.168.152.115:8008# 这里一定要是etcd3etcd3:  host: 192.168.152.115:2379log:  level: INFO  traceback_level: ERROR  dir: /usr/local/pgsql16/patroni/  file_num: 10  file_size: 104857600bootstrap:  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-checksumspostgresql:  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_dirtags:    # failover_priority: 1    # sync_priority: 1    noloadbalance: false    clonefrom: false    nostream: false

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

scope: pg_cluster_wy_prodnamespace: /service/name: ubuntu09restapi:  listen: 192.168.152.116:8008  connect_address: 192.168.152.116:8008etcd3:  host: 192.168.152.116:2379log:  level: INFO  traceback_level: ERROR  dir: /usr/local/pgsql16/patroni/  file_num: 10  file_size: 104857600bootstrap:  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-checksumspostgresql:  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_dirtags:    # failover_priority: 1    # sync_priority: 1    noloadbalance: false    clonefrom: false    nostream: false

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

scope: pg_cluster_wy_prodnamespace: /service/name: ubuntu10restapi:  listen: 192.168.152.117:8008  connect_address: 192.168.152.117:8008etcd3:  host: 192.168.152.117:2379log:  level: INFO  traceback_level: ERROR  dir: /usr/local/pgsql16/patroni/  file_num: 10  file_size: 104857600bootstrap:  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-checksumspostgresql:  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_dirtags:    # failover_priority: 1    # sync_priority: 1    noloadbalance: false    clonefrom: false    nostream: false

4.1 相关参数

scope: pg_cluster_wy_prod

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=patroniAfter=network.target remote-fs.target nss-lookup.target etcd.service #一定要等到etcd启动之后再启动patroniRequires=etcd.service [Service]Type=forkingUser=postgresGroup=postgresEnvironment="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 $MAINPIDExecStop=/usr/bin/killall patroniKillMode=processTimeoutSec=30Restart=no[Install]WantedBy=multi-user.target

6，启动partoni

设置自动启动，并启动服务，检查集群状态，patronictl -c /usr/local/pgsql16/patroni/patroni.yml list

systemctl daemon-reloadsystemctl start patronisystemctl enable patroniroot@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) idleSep 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/profileexport PATRONICTL_CONFIG_FILE=/home/postgres/patroni/patroni.ymlsource /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，这种方式的修改，只会对当前节点生效，*不会*同步至其他节点

--在从节点上执行SHOW listen_addresses;listen_addresses|----------------+192.168.152.115 |--查看当前work_memshow work_mem;work_mem|--------+4MB     |--修改work_memALTER SYSTEM SET work_mem TO '8MB';SELECT pg_reload_conf();