PostgreSQL 高可用管理工具之xxx（pgsql高可用方案）

liuian 2025-07-06 14:04 18 浏览

Patroni & Etcd

环境说明

项目	说明	备注
操作系统	RockyLinux8
系统内核	5.14.0-362.8.1.el9_3.x86_64
Patroni	4.0.0	/opt/patroni/patroni
Etcd	3.5.15	/opt/etcd-3.5.15
PostgreSQL	14.9
主机IP	10.16.18.160～10.16.18.162

安装配置Etcd

Etcd 是一个可靠的分布式 key-value 存储系统，主要用于配置共享和服务注册和发现。Patroni主要使用其存储PostgreSQL集群的信息。

以下操作需要在三台机器上都要执行。

下载安装包

cd /opt
wget https://github.com/etcd-io/etcd/releases/download/v3.5.15/etcd-v3.5.15-linux-amd64.tar.gz

解压

tar xzf etcd-v3.5.15-linux-amd64.tar.gz

修改下目录名称

mv etcd-v3.5.15-linux-amd64 etcd-3.5.15

创建一个日志目录

mkdir -p /opt/etcd-3.5.15/logs

PG01上添加配置文件
/opt/etcd-3.5.15/etcd.conf

name: patroni01
data-dir: /opt/etcd-3.5.15
listen-peer-urls: http://10.16.18.160:2380
listen-client-urls: http://10.16.18.160:2379,http://127.0.0.1:2379

initial-cluster-state: new
initial-cluster-token: etcd-cluster
advertise-client-urls: http://10.16.18.160:2379
initial-advertise-peer-urls: http://10.16.18.160:2380
initial-cluster: patroni01=http://10.16.18.160:2380,patroni02=http://10.16.18.161:2380,patroni03=http://10.16.18.162:2380

如果添加enable-v2: true，那么对应的Patroni配置文件中应当是etcd，而不是etcd3。

PG02上添加配置文件
/opt/etcd-3.5.15/etcd.conf

name: patroni02
data-dir: /opt/etcd-3.5.15
listen-peer-urls: http://10.16.18.161:2380
listen-client-urls: http://10.16.18.161:2379,http://127.0.0.1:2379

initial-cluster-state: new
initial-cluster-token: etcd-cluster
advertise-client-urls: http://10.16.18.161:2379
initial-advertise-peer-urls: http://10.16.18.161:2380
initial-cluster: patroni01=http://10.16.18.160:2380,patroni02=http://10.16.18.161:2380,patroni03=http://10.16.18.162:2380

PG03上添加配置文件
/opt/etcd-3.5.15/etcd.conf

name: patroni03
data-dir: /opt/etcd-3.5.15
listen-peer-urls: http://10.16.18.162:2380
listen-client-urls: http://10.16.18.162:2379,http://127.0.0.1:2379

initial-cluster-state: new
initial-cluster-token: etcd-cluster
advertise-client-urls: http://10.16.18.162:2379
initial-advertise-peer-urls: http://10.16.18.162:2380
initial-cluster: patroni01=http://10.16.18.160:2380,patroni02=http://10.16.18.161:2380,patroni03=http://10.16.18.162:2380

在三台机器上配置系统服务
/usr/lib/systemd/system/etcd.service

[Unit]
Description=Etcd Server
Documentation=https://github.com/coreos/etcd
After=network.target
WorkingDirectory=/opt/etcd-3.5.15/etcd

[Service]
User=root
Type=notify
ExecStart=/opt/etcd-3.5.15/etcd --config-file /opt/etcd-3.5.15/etcd.conf --log-level error
Restart=on-failure
RestartSec=10s
LimitNOFILE=40000
StandardOutput=file:/opt/etcd-3.5.15/logs/etcd.log
StandardError=file:/opt/etcd-3.5.15/logs/etcd-error.log

[Install]
WantedBy=multi-user.target

在三台机器上启动etcd服务，初始化etcd数据库

systemct daemon-reload && systemctl start etcd

如果想设置为开机自启，加上systemctl enable etcd

查看节点的状态

/opt/etcd-3.5.15/etcdctl endpoint health --cluster -w table

[root@PG01 opt]# /opt/etcd-3.5.15/etcdctl endpoint health --cluster -w table
+--------------------------+--------+------------+-------+
|         ENDPOINT         | HEALTH |    TOOK    | ERROR |
+--------------------------+--------+------------+-------+
| http://10.16.18.160:2379 |   true | 1.018523ms |       |
| http://10.16.18.162:2379 |   true | 1.247401ms |       |
| http://10.16.18.161:2379 |   true | 1.384808ms |       |
+--------------------------+--------+------------+-------+

看到输出结果如上说明成功了，否则查看日志文件
/opt/etcd-3.5.15/logs/etcd-error.log查找原因。

网上有些配置文件示例是下面这样的，这种配置是环境变量的方式，不能通过etcd --config-file的方式执行。

#[Member]
ETCD_NAME="patroni01"
ETCD_DATA_DIR="/opt/etcd-3.5.15/data"
ETCD_LISTEN_PEER_URLS="http://10.16.18.160:2380"
ETCD_LISTEN_CLIENT_URLS="http://10.16.18.160:2379,http://127.0.0.1:2379"

#[Clustering]
ETCD_ENABLE_V2=true
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_ADVERTISE_CLIENT_URLS="http://10.16.18.160:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.16.18.160:2380"
ETCD_INITIAL_CLUSTER="patroni01=http://10.16.18.160:2380,patroni02=http://10.16.18.161:2380,patroni03=http://10.16.18.162:2380"

与其对应的是在
/usr/lib/systemd/system/etcd.service中增加EnvironmentFile

[Service]
EnvironmentFile=-/opt/etcd-v3.5.0/config/etcd.conf

安装配置Patroni

以下操作三台机器上都要执行。

安装Python3

需要先安装Python3，网上类似的文章很多，这里不再赘述，或者直接使用中启乘数提供的编译好的Python包（是给他们CLup软件用的），我之前装过，所以这里不再安装Python环境。

这里给一个安装示例，只有el7和el8的版本

cd /opt
wget https://gitee.com/csudata/csupy3.9.16/releases/download/1.0/csupy3.9.16.el8.tar.xz
tar xf csupy3.9.16.el8.tar.xz

el7的话替换下el8即可。

如果是其他的操作系统，又不想折腾编译Python3，也可以装一个CLup的开源版本（另一款PostgreSQL高可用软件，带有Web界面，建议有时间的话可以装一套用用），软件安装时会自动安装一个Python3的环境。

其安装命令如下，参考文档：
https://www.csudata.com/clup/manual/5.x/10147

wget -qO /tmp/clup.sh --no-check-certificate https://get.csudata.com/csuinst/clup.sh && bash /tmp/clup.sh openclup install

在其中一套安装即可，如果想要试用CLup的话在其他机器上安装其Agent端，要不就是直接tar一下Python3环境，然后scp过去再解压。

上面的tar包解压或是安装CLup后的Python环境如下

.
├── csupy3.9.16
└── csu_pyenv

csupy3.9.16：Python3的软件目录
csu_pyenv：安装了工具包的虚拟环境

需要先添加下环境变量~/.bashrc

export PATH=/opt/csupy3.9.16/bin:$PATH
export LD_LIBRARY_PATH=/opt/csupy3.9.16/lib:$LD_LIBRARY_PATH

注意替换路径为自己的实际路径。

安装Patroni

Patroni安装比较简单，直接pip安装，下面还需安装psycopg2-binary（Python 连接PostgreSQL的库）

pip3 install psycopg2-binary -i https://mirrors.aliyun.com/pypi/simple/
pip3 install patroni[etcd] -i https://mirrors.aliyun.com/pypi/simple/

配置Patroni

创建目录

mkdir -p /opt/patroni/conf
mkdir -p /opt/patroni/logs

PG01上添加配置文件
/opt/patroni/conf/patroni_pg.yml

scope: pg14-cluster
name: patroni01
# log
log:
  level: INFO
  traceback_level: ERROR
  dir: /opt/patroni/logs
  file_num: 10
  # file_size: 26214400
restapi:
  listen: 10.16.18.160:8008
  connect_address: 10.16.18.160:8008
etcd3:
  hosts: 10.16.18.160:2379,10.16.18.161:2379,10.16.18.162:2379

bootstrap:
  # this section will be written into Etcd:/<namespace>/<scope>/config after initializing new cluster
  # and all other cluster members will use it as a `global configuration`
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    master_start_timeout: 300
    synchronous_mode: false
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        listen_addresses: "*"
        port: 5414
        wal_level: logical
        hot_standby: "on"
        max_wal_senders: 10
        max_replication_slots: 10
        wal_log_hints: "on"

postgresql:
  listen: 10.16.18.160:5414
  connect_address: 10.16.18.160:5414
  database: template1
  data_dir: /data/pgdata14
  bin_dir: /usr/csupg-14.9/bin
  pgpass: /home/pg14/.pgpass
  #callbacks:
  #on_start:/u01/app/halo/product/shield/patroni/scripts/patroni_callback.sh
  #on_stop:/u01/app/halo/product/shield/patroni/scripts/patroni_callback.sh
  #on_role_change:/u01/app/halo/product/shield/patroni/scripts/patroni_callback.sh
  
  authentication:
    replication:
      username: postgres
      password: postgres
    superuser:
      username: postgres
      password: postgres
    rewind:
      username: postgres
      password: postgres

watchdog:
  mode: off # Allowed values: off, automatic, required
  device: /dev/watchdog
  safety_margin: 5

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false

PG02上添加配置文件
/opt/patroni/conf/patroni_pg.yml

scope: pg14-cluster
name: patroni02
# log
log:
  level: INFO
  traceback_level: ERROR
  dir: /opt/patroni/logs
  file_num: 10
  # file_size: 26214400
restapi:
  listen: 10.16.18.161:8008
  connect_address: 10.16.18.161:8008
etcd3:
  hosts: 10.16.18.160:2379,10.16.18.161:2379,10.16.18.162:2379
  
bootstrap:
  # this section will be written into Etcd:/<namespace>/<scope>/config after initializing new cluster
  # and all other cluster members will use it as a `global configuration`
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    master_start_timeout: 300
    synchronous_mode: false
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        listen_addresses: "*"
        port: 5414
        wal_level: logical
        hot_standby: "on"
        max_wal_senders: 10
        max_replication_slots: 10
        wal_log_hints: "on"

postgresql:
  listen: 10.16.18.161:5414
  connect_address: 10.16.18.161:5414
  database: template1
  data_dir: /data/pgdata14
  bin_dir: /usr/csupg-14.9/bin
  pgpass: /home/pg14/.pgpass
  #callbacks:
  #on_start:/u01/app/halo/product/shield/patroni/scripts/patroni_callback.sh
  #on_stop:/u01/app/halo/product/shield/patroni/scripts/patroni_callback.sh
  #on_role_change:/u01/app/halo/product/shield/patroni/scripts/patroni_callback.sh
  
  authentication:
    replication:
      username: postgres
      password: postgres
    superuser:
      username: postgres
      password: postgres
    rewind:
      username: postgres
      password: postgres

watchdog:
  mode: off # Allowed values: off, automatic, required
  device: /dev/watchdog
  safety_margin: 5

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false

PG03上添加配置文件
/opt/patroni/conf/patroni_pg.yml`

scope: pg14-cluster
name: patroni03
# log
log:
  level: INFO
  traceback_level: ERROR
  dir: /opt/patroni/logs
  file_num: 10
  # file_size: 26214400
restapi:
  listen: 10.16.18.162:8008
  connect_address: 10.16.18.162:8008
etcd3:
  hosts: 10.16.18.160:2379,10.16.18.161:2379,10.16.18.162:2379
  
bootstrap:
  # this section will be written into Etcd:/<namespace>/<scope>/config after initializing new cluster
  # and all other cluster members will use it as a `global configuration`
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    master_start_timeout: 300
    synchronous_mode: false
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        listen_addresses: "*"
        port: 5414
        wal_level: logical
        hot_standby: "on"
        max_wal_senders: 10
        max_replication_slots: 10
        wal_log_hints: "on"

postgresql:
  listen: 10.16.18.162:5414
  connect_address: 10.16.18.162:5414
  database: template1
  data_dir: /data/pgdata14
  bin_dir: /usr/csupg-14.9/bin
  pgpass: /home/pg14/.pgpass
  #callbacks:
  #on_start:/u01/app/halo/product/shield/patroni/scripts/patroni_callback.sh
  #on_stop:/u01/app/halo/product/shield/patroni/scripts/patroni_callback.sh
  #on_role_change:/u01/app/halo/product/shield/patroni/scripts/patroni_callback.sh
  
  authentication:
    replication:
      username: postgres
      password: postgres
    superuser:
      username: postgres
      password: postgres
    rewind:
      username: postgres
      password: postgres

watchdog:
  mode: off # Allowed values: off, automatic, required
  device: /dev/watchdog
  safety_margin: 5

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false

系统服务
/usr/lib/systemd/system/patroni.service

[Unit]
Description=Patroni Cluster
After=syslog.target network.target

[Service]
Type=simple

User=pg14
Group=pg14
# Set PATH LD_LIBRARY_PATH, they will be passed to OS env variables when call pg_ctl by patroni
Environment="LD_LIBRARY_PATH=/usr/csupg-14.9/lib:/opt/csupy3.9.16/lib"
# Start the patroni process
ExecStart=/opt/csupy3.9.16/bin/patroni /opt/patroni/conf/patroni_pg.yml
# Send HUP to reload from patroni.yml
ExecReload=/bin/kill -s HUP $MAINPID
# only kill the patroni process, not it's children, so it will gracefully stop Halo
KillMode=process
# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=30
# Do not restart the service if it crashes, we want to manually inspect database on failure
Restart=no
  
[Install]
WantedBy=multi-user.target

注意替换Environment中的值为自己的实际路径。

修改文件属主

chown -R pg14:pg14 /opt/patroni

启动服务

systemctl daemon-reload && systemctl start patroni

查看集群的状态

patronictl -c /opt/patroni/conf/patroni_pg.yml list

这里Leader节点显示有些参数会在重启后生效，实际上是patroni在启动时会根据patroni的配置文件修改数据的参数。

剩下的两个Replica节点没有显示未生效的参数的原因是中间启动备节点的patroni有问题，后面重搭了备库，需要注意的是执行pg_basebackup后要修改下参数

# postgresql.conf
listen_addresses = 10.16.18.161

# postgresql.auto.conf
primary_conninfo="application_name=patroni02 ..."

注意：这里的地址换成自己的当前备库的实际地址，primary_conninfo中是增加application_name=，而不是配置成上面那样。

问题解决

etcd启动时报错

[root@PG01 logs]# /opt/etcd-3.5.15/etcd --config-file /opt/etcd-3.5.15/etcd.conf --log-level debug
{"level":"info","ts":"2024-08-30T10:00:16.034302+0800","caller":"etcdmain/etcd.go:73","msg":"Running: ","args":["/opt/etcd-3.5.15/etcd","--config-file","/opt/etcd-3.5.15/etcd.conf","--log-level","debug"]}
{"level":"warn","ts":"2024-08-30T10:00:16.034328+0800","caller":"etcdmain/etcd.go:75","msg":"failed to verify flags","error":"error unmarshaling JSON: while decoding JSON: json: cannot unmarshal string into Go struct field configYAML.log-outputs of type []string"}

检查配置文件，可能有些参数名称不对或者设置不对。

cluster id 不对

问题描述：

启动Patroni后日志中总是报错：

node patroni02 belongs to a different cluster: 7223643286119069904 ！=7384703923858607267

原因：

可能是旧的集群数据没有清理掉

解决版本：清理旧集群数据，所有节点都要做

停止Patroni服务
systemctl stop patroni
停止etcd服务
systemctl stop etcd
清理或者重命名旧集群数据
cd /etc/etcdxxx/
mv data data_old
启动etcd
systemctl start etcd
启动patroni
systemctl start patroni

postgresql case when

上一篇：PostgreSQL技术内幕17:PG分区表（pg数据库表分区）
下一篇：云贝数据 | PG快速构建海量数据方法

PostgreSQL 高可用管理工具之xxx（pgsql高可用方案）

Patroni & Etcd

安装配置Etcd

安装配置Patroni

安装Python3

安装Patroni

配置Patroni

问题解决

cluster id 不对

相关推荐

Python实现人事自动打卡，再也不会被批评

【验证码逆向专栏】vaptcha 手势验证码逆向分析

Psutil + Flask + Pyecharts + Bootstrap 开发动态可视化系统监控

一个解决支持HTML/CSS/JS网页转PDF(高质量)的终极解决方案

再见Swagger UI 国人开源了一款超好用的 API 文档生成框架，真香

网页转成pdf文件的经验分享网页转成pdf文件的经验分享怎么弄

C++ std::vector 简介

飞牛OS入门安装遇到问题，如何解决?

系统C盘清理:微信PC端文件清理，扩大C盘可用空间步骤

10款高性能NAS丨双十一必看，轻松搞定虚拟机、Docker、软路由

PostgreSQL 高可用管理工具之xxx（pgsql高可用方案）

Patroni & Etcd

安装配置Etcd

安装配置Patroni

安装Python3

安装Patroni

配置Patroni

问题解决

cluster id 不对

相关推荐

Python实现人事自动打卡，再也不会被批评

【验证码逆向专栏】vaptcha 手势验证码逆向分析

Psutil + Flask + Pyecharts + Bootstrap 开发动态可视化系统监控

一个解决支持HTML/CSS/JS网页转PDF(高质量)的终极解决方案

再见Swagger UI 国人开源了一款超好用的 API 文档生成框架，真香

网页转成pdf文件的经验分享 网页转成pdf文件的经验分享怎么弄

C++ std::vector 简介

飞牛OS入门安装遇到问题，如何解决?

系统C盘清理:微信PC端文件清理，扩大C盘可用空间步骤

10款高性能NAS丨双十一必看，轻松搞定虚拟机、Docker、软路由

网页转成pdf文件的经验分享网页转成pdf文件的经验分享怎么弄