前言
Patroni高可用,算是PGSQL所有高可用插件中,比较牛掰的了,功能强大,支持自动拉起postgresql实例,将主库变为从库,支持经过watchdog防止脑裂,Patroni自身没有一致性协议,需要通过DCS(分布式配置存储),如etcd、consul、zookeeper等,来提供一致性模型,Patroni再通过api连接etcd集群。向其插入键值对记录Patroni参数、数据库参数、主备信息以及连接信息,平常通过etcd对其它节点做心跳检测,通过从etcd获取数据,存储主备信息来判断各节点的状态对集群进行自动管理。
环境介绍
角色 | IP | 服务 |
---|---|---|
Leader | 172.22.138.220 | PostgreSQL v14;Patroni |
standby1 | 172.22.138.219 | PostgreSQL v14;Patroni |
standby2 | 172.22.138.218 | PostgreSQL v14; Patroni |
环境准备
1.三节点时间同步
root # yum install -y ntpdate
root # ntpdate ntp.aliyun.com
2.安装相关依赖包
root # yum install -y gcc python-devel epel-release
root # yum install -y gcc epel-release
root # yum install -y python-pip python-psycopg2 python-devel
安装PostgreSQL流复制
postgresql流复制的部署,这里不再赘述,需要搭建流复制的可以看我这篇文章:https://blog.csdn.net/h_3369/article/details/141358629?spm=1001.2014.3001.5501
安装etcd集群
1.使用yum命令安装etcd
root # yum install -y etcd
2.编写etcd配置文件
root # vim /etc/etcd/etcd.conf
#leader
ETCD_NAME="node1"
ETCD_DATA_DIR="/data/etcd/data"
ETCD_LISTEN_CLIENT_URLS="http://127.0.0.1:2379,http://172.22.138.220:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://127.0.0.1:2379,http://172.22.138.220:2379"
ETCD_LISTEN_PEER_URLS="http://172.22.138.220:2380"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://172.22.138.220:2380"
ETCD_INITIAL_CLUSTER="node1=http://172.22.138.220:2380,node2=http://172.22.138.219:2380,node3=http://172.22.138.218:2380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_CLUSTER_STATE="new"
#standby1
ETCD_NAME="node2"
ETCD_DATA_DIR="/data/etcd/data"
ETCD_LISTEN_CLIENT_URLS="http://127.0.0.1:2379,http://172.22.138.219:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://127.0.0.1:2379,http://172.22.138.219:2379"
ETCD_LISTEN_PEER_URLS="http://172.22.138.219:2380"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://172.22.138.219:2380"
ETCD_INITIAL_CLUSTER="node1=http://172.22.138.220:2380,node2=http://172.22.138.219:2380,node3=http://172.22.138.218:2380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_CLUSTER_STATE="new"
#standby2
ETCD_NAME="node3"
ETCD_DATA_DIR="/data/etcd/data"
ETCD_LISTEN_CLIENT_URLS="http://127.0.0.1:2379,http://172.22.138.218:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://127.0.0.1:2379,http://172.22.138.218:2379"
ETCD_LISTEN_PEER_URLS="http://172.22.138.218:2380"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://172.22.138.218:2380"
ETCD_INITIAL_CLUSTER="node1=http://172.22.138.220:2380,node2=http://172.22.138.219:2380,node3=http://172.22.138.218:2380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_CLUSTER_STATE="new"
3.启动etcd集群
root # systemctl start etcd
安装Patroni
1.使用python的pip安装patroni和相关模块
root # pip3 install --upgrade pip
root # pip3 install --upgrade setuptools
root # pip3 install psycopg2-binary
root # pip3 install patroni[etcd]
#重点关注psycopg2-binary包三个节点版本号是否一致,不一致Patroni启动会报错
root # pip3 list|grep psycopg2
2.编辑Patroni的配置文件
root # vim /etc/patroni.yml
# Leader节点
scope: pgsql
namespace: /service/
name: pa-pg-1
restapi:
listen: 0.0.0.0:8008
connect_address: 172.22.138.220:8008
etcd:
host: 172.22.138.220:2379
bootstrap:
dcs:
ttl: 30
loop_wait: 10 # 循环更新领导者密钥过程中的休眠时间
retry_timeout: 10 # etcd和PostgreSQL操作重试的超时时间(以秒为单位)
maximum_lag_on_failover: 1048576 # 如果Master和Replicate之间的字节数延迟大于此值,那么Replicate将不参与新的领导者选举
master_start_timeout: 300
synchronous_mode: false # 是否打开同步复制模式
postgresql: # PostgreSQL的配置,是否使用pg_rewind,是否使用复制插槽,还有PostgreSQL参数等信息
use_pg_rewind: true
use_slots: true
parameters:
listen_addresses: "0.0.0.0"
port: 5432
wal_level: replica
hot_standby: "on"
wal_keep_segments: 100
max_wal_senders: 10
max_replication_slots: 10
wal_log_hints: "on"
postgresql:
listen: 0.0.0.0:5432 # 设置postgresql.conf参数listen_addresses和port
connect_address: 172.22.138.220:5432 # 从其他节点和应用程序访问Postgres的地址和端口
data_dir: /data/pgsql/data # 集群的数据目录的存放路径
bin_dir: /data/pgsql/base/bin # PostgreSQL二进制文件存放路径
authentication: # 定义用于复制的用户,超级用户
replication:
username: replica
password: "replica"
superuser:
username: postgres
password: "123456"
basebackup:
max-rate: 100M
checkpoint: fast
tags:
nofailover: false # 不参与选主
noloadbalance: false # 不参与负载均衡
clonefrom: false
nosync: false # 也不作为同步备库
# Standby1节点
scope: pgsql
namespace: /service/
name: pa-pg-1
restapi:
listen: 0.0.0.0:8008
connect_address: 172.22.138.219:8008
etcd:
host: 172.22.138.219:2379
bootstrap:
dcs:
ttl: 30
loop_wait: 10 # 循环更新领导者密钥过程中的休眠时间
retry_timeout: 10 # etcd和PostgreSQL操作重试的超时时间(以秒为单位)
maximum_lag_on_failover: 1048576 # 如果Master和Replicate之间的字节数延迟大于此值,那么Replicate将不参与新的领导者选举
master_start_timeout: 300
synchronous_mode: false # 是否打开同步复制模式
postgresql: # PostgreSQL的配置,是否使用pg_rewind,是否使用复制插槽,还有PostgreSQL参数等信息
use_pg_rewind: true
use_slots: true
parameters:
listen_addresses: "0.0.0.0"
port: 5432
wal_level: replica
hot_standby: "on"
wal_keep_segments: 100
max_wal_senders: 10
max_replication_slots: 10
wal_log_hints: "on"
postgresql:
listen: 0.0.0.0:5432 # 设置postgresql.conf参数listen_addresses和port
connect_address: 172.22.138.219:5432 # 从其他节点和应用程序访问Postgres的地址和端口
data_dir: /data/pgsql/data # 集群的数据目录的存放路径
bin_dir: /data/pgsql/base/bin # PostgreSQL二进制文件存放路径
authentication: # 定义用于复制的用户,超级用户
replication:
username: replica
password: "replica"
superuser:
username: postgres
password: "123456"
basebackup:
max-rate: 100M
checkpoint: fast
tags:
nofailover: false # 不参与选主
noloadbalance: false # 不参与负载均衡
clonefrom: false
nosync: false # 也不作为同步备库
# Standby2节点
scope: pgsql
namespace: /service/
name: pa-pg-1
restapi:
listen: 0.0.0.0:8008
connect_address: 172.22.138.218:8008
etcd:
host: 172.22.138.218:2379
bootstrap:
dcs:
ttl: 30
loop_wait: 10 # 循环更新领导者密钥过程中的休眠时间
retry_timeout: 10 # etcd和PostgreSQL操作重试的超时时间(以秒为单位)
maximum_lag_on_failover: 1048576 # 如果Master和Replicate之间的字节数延迟大于此值,那么Replicate将不参与新的领导者选举
master_start_timeout: 300
synchronous_mode: false # 是否打开同步复制模式
postgresql: # PostgreSQL的配置,是否使用pg_rewind,是否使用复制插槽,还有PostgreSQL参数等信息
use_pg_rewind: true
use_slots: true
parameters:
listen_addresses: "0.0.0.0"
port: 5432
wal_level: replica
hot_standby: "on"
wal_keep_segments: 100
max_wal_senders: 10
max_replication_slots: 10
wal_log_hints: "on"
postgresql:
listen: 0.0.0.0:5432 # 设置postgresql.conf参数listen_addresses和port
connect_address: 172.22.138.218:5432 # 从其他节点和应用程序访问Postgres的地址和端口
data_dir: /data/pgsql/data # 集群的数据目录的存放路径
bin_dir: /data/pgsql/base/bin # PostgreSQL二进制文件存放路径
authentication: # 定义用于复制的用户,超级用户
replication:
username: replica
password: "replica"
superuser:
username: postgres
password: "123456"
basebackup:
max-rate: 100M
checkpoint: fast
tags:
nofailover: false # 不参与选主
noloadbalance: false # 不参与负载均衡
clonefrom: false
nosync: false # 也不作为同步备库
3.配置Patroni的service启动文件
root # vim /etc/systemd/system/patroni.service
[Unit]
Description=patroni - a high-availability PostgreSQL
Documentation=https://patroni.readthedocs.io/en/latest/index.html
After=syslog.target network.target etcd.target
Wants=network-online.target
[Service]
Type=simple
User=postgres
Group=postgres
PermissionsStartOnly=true
ExecStart=/usr/local/bin/patroni /etc/patroni.yml
ExecReload=/bin/kill -HUP $MAINPID
LimitNOFILE=65536
KillMode=process
KillSignal=SIGINT
Restart=on-abnormal
RestartSec=30s
TimeoutSec=0
[Install]
WantedBy=multi-user.target
4.授权Patroni的配置文件并启动Patroni,启动顺序必须是:主库>从库1==从库2!
#postgres用户没有sudo权限的话记得配置sudo
root # echo 'postgres ALL=(ALL) NOPASSWD: ALL'> /etc/sudoers.d/postgres
root # chown postgres./etc/patroni.yml
root # systemctl start patroni
Patroni日常检查
1.使用Patroni命令查看集群状态
root # patronictl -c /etc/patroni.yml list
2.Patroni日志默认输出到 /var/log/messages
root # less /var/log/messages
Aug 20 12:23:14 DB-test2 systemd: Started patroni - a high-availability PostgreSQL.
Aug 20 12:23:14 DB-test2 systemd-logind: New session 886 of user root.
Aug 20 12:23:14 DB-test2 systemd: Started Session 886 of user root.
Aug 20 12:23:14 DB-test2 systemd-logind: Removed session 886.
Aug 20 12:23:15 DB-test2 patroni: postgresql parameter listen_addresses=0.0.0.0 failed validation, defaulting to None
Aug 20 12:23:15 DB-test2 patroni: postgresql parameter port=5432 failed validation, defaulting to None
Aug 20 12:23:15 DB-test2 patroni: 2024-08-15 12:23:15,225 INFO: No PostgreSQL configuration items changed, nothing to reload.
Aug 20 12:23:15 DB-test2 patroni: localhost:5432 - accepting connections
Aug 15 12:23:15 DB-test2 patroni: 2024-08-15 12:23:15,233 INFO: establishing a new patroni heartbeat connection to postgres
Aug 20 12:23:15 DB-test2 patroni: 2024-08-15 12:23:15,254 INFO: no action. I am (pa-pg-2), a secondary, and following a leader (pa-pg-1)
Aug 20 12:23:16 DB-test2 systemd-logind: New session 887 of user root.
Aug 20 12:23:16 DB-test2 systemd: Started Session 887 of user root.
Aug 20 12:23:16 DB-test2 systemd-logind: Removed session 887.
Aug 20 12:23:16 DB-test2 patroni: 2024-08-15 12:23:16,423 INFO: no action. I am (pa-pg-2), a secondary, and following a leader (pa-pg-1)