rocky8.10 部署Patroni + etcd+postgreql + haproxy 三台主机实现postgreql 高可用

节点规划（3 台）

主机名	IP	角色
emporerlinux-pg1	10.126.106.4	PostgreSQL + Patroni + etcd
emporerlinux-pg2	10.126.106.5	PostgreSQL + Patroni + etcd
emporerlinux-pg3	10.126.106.6	PostgreSQL + Patroni + etcd

pgsql 安装：

dnf install -y epel-release
dnf install -y vim wget curl jq python3-pip
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
dnf -qy module disable postgresql
dnf install -y postgresql15 postgresql15-server

etcd安装：

cd /opt
wget https://github.com/etcd-io/etcd/releases/download/v3.5.12/etcd-v3.5.12-linux-amd64.tar.gz
tar -xf etcd-v3.5.12-linux-amd64.tar.gz
cp etcd-v3.5.12-linux-amd64/etcd /usr/local/bin/
cp etcd-v3.5.12-linux-amd64/etcdctl /usr/local/bin/
chmod +x /usr/local/bin/etcd*
etcd --version
useradd -r -s /sbin/nologin etcd

mkdir -p /var/lib/etcd
mkdir -p /etc/etcd

chown -R etcd:etcd /var/lib/etcd /etc/etcd

etcd配置:

pg2/pg3 对应修改ETC_NAME,和IP地址

[root@emporerlinux-pg1 opt]# vi /etc/etcd/etcd.conf
[root@emporerlinux-pg1 opt]# cat /etc/etcd/etcd.conf
ETCD_NAME=pg-etcd-1
ETCD_DATA_DIR="/var/lib/etcd"

ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.126.108.4:2380"
ETCD_LISTEN_PEER_URLS="http://10.126.108.4:2380"

ETCD_ADVERTISE_CLIENT_URLS="http://10.126.108.4:2379"
ETCD_LISTEN_CLIENT_URLS="http://0.0.0.0:2379"

ETCD_INITIAL_CLUSTER="pg-etcd-1=http://10.126.108.4:2380,pg-etcd-2=http://10.126.108.5:2380,pg-etcd-3=http://10.126.108.6:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="pg-etcd-cluster"

二进制安装方式可以创建一个systemd

vi /etc/systemd/system/etcd.service
[Unit]
Description=etcd
Documentation=https://etcd.io
After=network.target

[Service]
Type=notify
User=etcd
Group=etcd

EnvironmentFile=/etc/etcd/etcd.conf

ExecStart=/usr/local/bin/etcd

Restart=always
RestartSec=10s
LimitNOFILE=40000

[Install]
WantedBy=multi-user.target

systemctl daemon-reexec
systemctl daemon-reload
systemctl enable --now etcd

验证etcd:

[root@emporerlinux-pg1 opt]# etcdctl --endpoints=http://10.126.108.4:2379,http://10.126.108.5:2379,http://10.126.108.6:2379 endpoint health
http://10.126.108.4:2379 is healthy: successfully committed proposal: took = 1.649567ms
http://10.126.108.6:2379 is healthy: successfully committed proposal: took = 1.881361ms
http://10.126.108.5:2379 is healthy: successfully committed proposal: took = 4.291538ms
[root@emporerlinux-pg1 opt]# etcdctl --endpoints=http://10.126.108.4:2379,http://10.126.108.5:2379,http://10.126.108.6:2379 member list
6762f36b8c8f829, started, pg-etcd-1, http://10.126.108.4:2380, http://10.126.108.4:2379, false
2c93ab1e006ad2d4, started, pg-etcd-3, http://10.126.108.6:2380, http://10.126.108.6:2379, false
76731e7e12a7f16b, started, pg-etcd-2, http://10.126.108.5:2380, http://10.126.108.5:2379, false
[root@emporerlinux-pg1 opt]# etcdctl --endpoints=http://10.126.108.4:2379,http://10.126.108.5:2379,http://10.126.108.6:2379 put /foo "bar"
OK
[root@emporerlinux-pg1 opt]# etcdctl --endpoints=http://10.126.108.4:2379,http://10.126.108.5:2379,http://10.126.108.6:2379 get /foo
/foo
bar
[root@emporerlinux-pg1 opt]# etcdctl --endpoints=http://10.126.108.4:2379,http://10.126.108.5:2379,http://10.126.108.6:2379 endpoint status -w table
+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
|         ENDPOINT         |        ID        | VERSION | DB SIZE | IS LEADER | IS LEARNER | RAFT TERM | RAFT INDEX | RAFT APPLIED INDEX | ERRORS |
+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
| http://10.126.108.4:2379 |  6762f36b8c8f829 |  3.5.12 |   20 kB |      true |      false |         2 |         16 |                 16 |        |
| http://10.126.108.5:2379 | 76731e7e12a7f16b |  3.5.12 |   20 kB |     false |      false |         2 |         16 |                 16 |        |
| http://10.126.108.6:2379 | 2c93ab1e006ad2d4 |  3.5.12 |   20 kB |     false |      false |         2 |         16 |                 16 |        |
+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
[root@emporerlinux-pg1 opt]#

安装 Patroni

dnf install -y python3-pip
pip3 install patroni[etcd]

[root@emporerlinux-pg1 opt]# cat /etc/patroni.yml
scope: foreman-pg
name: pg1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.126.108.4:8008

etcd:
  hosts: 10.126.108.4:2379,10.126.108.5:2379,10.126.108.6:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_level: replica
        hot_standby: "on"
        max_wal_senders: 10
        max_replication_slots: 10

  initdb:
    - encoding: UTF8
    - data-checksums

  users:
    repl_user:
      password: replpass
      options:
        - replication
    postgres:
      password: superpass

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.126.108.4:5432
  data_dir: /var/lib/pgsql/15/data
  bin_dir: /usr/pgsql-15/bin
  authentication:
    replication:
      username: repl_user
      password: replpass
    superuser:
      username: postgres
      password: superpass

启动报错：
[root@emporerlinux-pg1 opt]# patroni /etc/patroni.yml
FATAL: Patroni requires psycopg2>=2.5.4, psycopg2-binary, or psycopg>=3.0.0
安装依赖：
[root@emporerlinux-pg1 opt]# dnf install -y python3-psycopg2

再次启动报错：
[root@emporerlinux-pg1 opt]# patroni /etc/patroni.yml
2025-12-17 12:08:15,478 ERROR: Failed to get list of machines from http://10.126.108.4:2379/v2: EtcdException('Bad response : 404 page not found\n',)
2025-12-17 12:08:15,479 ERROR: Failed to get list of machines from http://10.126.108.6:2379/v2: EtcdException('Bad response : 404 page not found\n',)
2025-12-17 12:08:15,480 ERROR: Failed to get list of machines from http://10.126.108.5:2379/v2: EtcdException('Bad response : 404 page not found\n',)
2025-12-17 12:08:15,480 INFO: waiting on etcd
启用etcd-v3:
[root@emporerlinux-pg1 opt]# cat /etc/patroni.yml
scope: foreman-pg
name: pg1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.126.108.4:8008

etcd3:
  hosts: 10.126.108.4:2379,10.126.108.5:2379,10.126.108.6:2379

创建patroni 服务启动文件

[root@emporerlinux-pg1 pgsql]# cat /etc/systemd/system/patroni.service
[Unit]
Description=Patroni PostgreSQL Cluster Service
After=network.target

[Service]
Type=simple
User=postgres
Group=postgres
Environment="PATRONI_CONFIG_FILE=/etc/patroni.yml"
ExecStart=/usr/local/bin/patroni $PATRONI_CONFIG_FILE
ExecReload=/bin/kill -HUP $MAINPID
Restart=on-failure
RestartSec=5s
LimitNOFILE=65535

# 指定工作目录为 postgres 可访问路径，避免 /root 权限问题
WorkingDirectory=/var/lib/pgsql

[Install]
WantedBy=multi-user.target

启动pg1 上的patroni 服务


systemctl daemon-reexec
systemctl daemon-reload
systemctl enable --now patroni

[root@emporerlinux-pg1 pgsql]# chmod 700 /var/lib/pgsql/15/data
[root@emporerlinux-pg1 pgsql]# chown -R postgres:postgres /var/lib/pgsql/15/data

检查patroni服务：

[root@emporerlinux-pg1 pgsql]# patronictl -c /etc/patroni.yml list
+ Cluster: foreman-pg (7584703474433756903) ----+-------------+-----+------------+-----+
| Member | Host         | Role   | State   | TL | Receive LSN | Lag | Replay LSN | Lag |
+--------+--------------+--------+---------+----+-------------+-----+------------+-----+
| pg1    | 10.126.108.4 | Leader | running |  2 |             |     |            |     |
+--------+--------------+--------+---------+----+-------------+-----+------------+-----+

pg2，启动patroni

[root@emporerlinux-pg1 pgsql]# patronictl -c /etc/patroni.yml list
+ Cluster: foreman-pg (7584703474433756903) --+----+-------------+-----+------------+-----+
| Member | Host         | Role    | State     | TL | Receive LSN | Lag | Replay LSN | Lag |
+--------+--------------+---------+-----------+----+-------------+-----+------------+-----+
| pg1    | 10.126.108.4 | Leader  | running   |  2 |             |     |            |     |
| pg2    | 10.126.108.5 | Replica | streaming |  2 |   0/3000060 |   0 |  0/3000060 |   0 |
+--------+--------------+---------+-----------+----+-------------+-----+------------+-----+

最后启动pg3


[root@emporerlinux-pg1 pgsql]# patronictl -c /etc/patroni.yml list
+ Cluster: foreman-pg (7584703474433756903) --+----+-------------+-----+------------+-----+
| Member | Host         | Role    | State     | TL | Receive LSN | Lag | Replay LSN | Lag |
+--------+--------------+---------+-----------+----+-------------+-----+------------+-----+
| pg1    | 10.126.108.4 | Leader  | running   |  2 |             |     |            |     |
| pg2    | 10.126.108.5 | Replica | streaming |  2 |   0/5000148 |   0 |  0/5000148 |   0 |
| pg3    | 10.126.108.6 | Replica | streaming |  2 |   0/5000148 |   0 |  0/5000148 |   0 |
+--------+--------------+---------+-----------+----+-------------+-----+------------+-----+