postgresql高可用集群搭建
Patroni是一款非常值得推荐的PostgreSQL高可用工具。不仅简单易用而且功能强大。
- 支持自动failover和按需switchover
- 支持一个和多个备节点
- 支持级联复制
- 支持同步复制，异步复制
- 支持同步复制下备库故障时自动降级为异步复制（功效类似于MySQL的半同步，但是更加智能）
- 支持控制指定节点是否参与选主，是否参与负载均衡以及是否可以成为同步备机
- 支持通过pg_rewind自动修复旧主
- 支持多种方式初始化集群和重建备机，包括pg_basebackup和支持wal_e，pgBackRest，barman等备份工具的自定义脚本
- 支持自定义外部callback脚本
- 支持REST API
- 支持通过watchdog防止脑裂
- 支持k8s，docker等容器化环境部署
- 支持多种常见DCS(Distributed Configuration Store)存储元数据，包括etcd，ZooKeeper，Consul，Kubernetes
下面详细介绍基于patroni搭建postgresql高可用
实验环境
机器和vip资源
Postgresql
node1: 10.20.192.81
node2: 10.20.192.82
Etcd
VIP
读写vip：10.20.192.101
只读vip：10.20.192.102
环境准备
时钟同步
yum install -y ntpdate
ntpdate time.windows.com && hwclock -w
关闭防火墙
setenforce 0
sed -i.bak "s/SELINUX=enforcing/SELINUX=permissive/g" /etc/selinux/config
systemctl disable firewalld.service
systemctl stop firewalld.service
iptables -F
etcd部署
2节点etcd集群需要所有节点接受投票才能选举成功（N=2），只要有一个节点发生故障，etcd集群就会变成不可用状态，因为这时不可能选举成功。因此2节点集群的容错能力不如单节点集群，所以只在node1上部署单节点etcd。部署步骤如下：
安装需要的包：
yum install -y gcc python-devel epel-release
安装etcd：
yum install -y etcd
编辑etcd配置文件/etc/etcd/etcd.conf，参考配置如下：
[Member]
ETCD_DATA_DIR="/var/lib/etcd/default.etcd"
ETCD_LISTEN_PEER_URLS="http://10.20.192.81:2380"
ETCD_LISTEN_CLIENT_URLS="http://0.0.0.0:2379"
ETCD_NAME="etcd01"
[Clustering]
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.20.192.82:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://0.0.0.0:2379"
ETCD_INITIAL_CLUSTER="etcd01=http://10.20.192.81:2380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_CLUSTER_STATE="new"
启动etcd
systemctl start etcd
# 设置etc自启动
systemctl enable etcd
etcd不可访问影响
当Patroni无法访问etcd时，将不能确认自己所处的角色。为了防止这种状态下产生脑裂，如果本机的PG是主库，Patroni会把PG降级为备库。如果集群中所有Patroni节点都无法访问etcd，集群中将全部都是备库，业务无法写入数据。
为了防止etcd集群故障带来的严重影响，可以考虑设置超大的retry_timeout参数，比如10000天，同时通过同步复制模式防止脑裂。
retry_timeout:864000000
synchronous_mode:true
于PostgreSQL操作，目前似乎只有调用GET /patroni的REST API时会重试，而且最多只重试1次，所以把retry_timeout调大不会带来其他副作用。
patroni部署
在需要运行postgresql的实例上安装相关软件
安装PostgreSQL13
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y postgresql13-server postgresql13-contrib
安装Patroni
yum install -y gcc epel-release
yum install -y python-pip python-psycopg2 python-devel
pip install --upgrade pip
pip install --upgrade setuptools
pip install patroni[etcd]
创建PostgreSQL数据目录
mkdir -p /pgsql/data
chown postgres:postgres -R /pgsql
chmod -R 700 /pgsql/data
创建Patroni service配置文件/etc/systemd/system/patroni.service
[Unit]
Description=Runners to orchestrate a high-availability PostgreSQL
After=syslog.target network.target
[Service]
Type=simple
User=postgres
Group=postgres
#StandardOutput=syslog
ExecStart=/usr/bin/patroni /etc/patroni.yml
ExecReload=/bin/kill -s HUP $MAINPID
KillMode=process
TimeoutSec=30
Restart=no
[Install]
WantedBy=multi-user.target
创建Patroni配置文件/etc/patroni.yml，以下是node1配置示例
scope: cn
namespace: /service/
name: pg1
restapi:
listen: 0.0.0.0:8008
connect_address: 10.20.192.81:8008
etcd:
hosts: 10.20.192.81:2379
bootstrap:
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: "0.0.0.0"
port: 5432
wal_level: logical
hot_standby: "on"
wal_keep_segments: 1000
max_wal_senders: 10
max_replication_slots: 10
wal_log_hints: "on"
max_connections: "100"
max_prepared_transactions: "100"
shared_preload_libraries: "citus,timescaledb"
citus.node_conninfo: "sslmode=prefer"
citus.replication_model: streaming
citus.task_assignment_policy: round-robin
initdb:
- encoding: UTF8
- locale: C
- lc-ctype: zh_CN.UTF-8
- data-checksums
pg_hba:
- host replication repl 0.0.0.0/0 md5
- host all all 0.0.0.0/0 md5
postgresql:
listen: 0.0.0.0:5432
connect_address: 10.20.192.81:5432
data_dir: /var/lib/pgsql/13/data
bin_dir: /usr/pgsql-13/bin
authentication:
replication:
username: repl
password: "123456"
superuser:
username: postgres
password: "123456"
basebackup:
max-rate: 100M
checkpoint: fast
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
其他PG节点的patroni.yml需要相应修改下面3个参数
name
node1~node2分别设置pg1~pg2
restapi.connect_address
根据各自节点IP设置
postgresql.connect_address
根据各自节点IP设置
启动Patroni
systemctl start patroni
初次启动Patroni时，Patroni会初始创建PostgreSQL实例和用户。
[root@localhost ~]# systemctl status patroni
● patroni.service - Runners to orchestrate a high-availability PostgreSQL
Loaded: loaded (/etc/systemd/system/patroni.service; disabled; vendor preset: disabled)
Active: active (running) since Thu 2021-03-25 22:42:39 CST; 6h ago
Main PID: 8273 (patroni)
CGroup: /system.slice/patroni.service
├─ 8273 /usr/bin/python /usr/bin/patroni /etc/patroni.yml
├─10067 /usr/pgsql-13/bin/postgres -D /var/lib/pgsql/13/data --config-file=/var/lib/pgsql/13/data/postgresql.conf --listen_addresses=0.0.0.0 --max_worker_processes=8 -...
├─10074 postgres: cn: logger
├─10082 postgres: cn: checkpointer
├─10083 postgres: cn: background writer
├─10084 postgres: cn: stats collector
├─10117 postgres: cn: postgres postgres 127.0.0.1(36696) idle
├─10145 postgres: cn: walwriter
├─10146 postgres: cn: autovacuum launcher
├─10147 postgres: cn: TimescaleDB Background Worker Launcher
├─10148 postgres: cn: logical replication launcher
├─10168 postgres: cn: walsender repl 10.20.192.82(34922) streaming 0/B6533E8
├─10271 postgres: cn: Citus Maintenance Daemon: 14174/10
├─10620 postgres: cn: postgres postgres 10.20.192.81(57262) idle
├─11018 postgres: cn: postgres postgres 10.20.192.101(39970) idle
├─11661 SGZg6zXX
├─12459 postgres: cn: postgres postgres 10.20.192.84(39256) idle
└─13323 postgres: cn: postgres postgres 10.20.192.83(41290) idle
查看集群状态
[root@localhost ~]# patronictl -c /etc/patroni.yml list
+ Cluster: cn (6943596690876391300) --------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+--------+--------------+---------+---------+----+-----------+
| pg1 | 10.20.192.81 | Leader | running | 5 | |
| pg2 | 10.20.192.82 | Replica | running | 5 | 0.0 |
+--------+--------------+---------+---------+----+-----------+
设置postgres拥有免密的sudoer权限
echo 'postgres ALL=(ALL) NOPASSWD: ALL'> /etc/sudoers.d/postgres
vip
Patroni支持用户配置在特定事件发生时触发回调脚本。因此我们可以配置一个回调脚本，在主备切换后动态加载VIP。
准备加载VIP的回调脚本/var/lib/pgsql/patroni_callback.sh
#!/bin/bash
RWVIP=10.20.192.101
RVIP=10.20.192.102
GATEWAY=10.20.192.1
DEV=ens3
action=$1
role=$2
cluster=$3
log()
{
echo "patroni_callback: $*"|logger
}
load_vip()
{
ip a|grep -w ${DEV}|grep -w $1 >/dev/null
if [ $? -eq 0 ] ;then
log "vip exists, skip load vip"
else
sudo ip addr add $1/32 dev ${DEV} >/dev/null
rc=$?
if [ $rc -ne 0 ] ;then
log "fail to add vip $1 at dev ${DEV} rc=$rc"
exit 1
fi
log "added vip $1 at dev ${DEV}"
arping -U -I ${DEV} -s $1 ${GATEWAY} -c 5 >/dev/null
rc=$?
if [ $rc -ne 0 ] ;then
log "fail to call arping to gateway ${GATEWAY} rc=$rc"
exit 1
fi
log "called arping to gateway ${GATEWAY}"
fi
}
unload_vip()
{
ip a|grep -w ${DEV}|grep -w $1 >/dev/null
if [ $? -eq 0 ] ;then
sudo ip addr del $1/32 dev ${DEV} >/dev/null
rc=$?
if [ $rc -ne 0 ] ;then
log "fail to delete vip $1 at dev ${DEV} rc=$rc"
exit 1
fi
log "deleted vip $1 at dev ${DEV}"
else
log "vip not exists, skip delete vip"
fi
}
log "patroni_callback start args:'$*'"
case $action in
on_start|on_restart|on_role_change)
case $role in
master)
unload_vip ${RVIP}
load_vip ${RWVIP}
;;
replica)
unload_vip ${RWVIP}
load_vip ${RVIP}
;;
*)
log "wrong role '$role'"
exit 1
;;
esac
;;
*)
log "wrong action '$action'"
exit 1
;;
esac
修改Patroni配置文件/etc/patroni.yml，配置回调函数
postgresql:
...
callbacks:
on_start: /bin/bash /var/lib/pgsql/patroni_callback.sh
on_restart: /bin/bash /var/lib/pgsql/patroni_callback.sh
on_role_change: /bin/bash /var/lib/pgsql/patroni_callback.sh
所有节点的Patroni配置文件都修改后，重新加载Patroni配置文件
patronictl reload pgsql
执行switchover后，可以看到VIP发生了漂移
/var/log/messages:
Sep 5 21:32:24 localvm postgres: loadvip: loadvip start args:'on_role_change master pgsql'
Sep 5 21:32:24 localvm systemd: Started Session c7 of user root.
Sep 5 21:32:24 localvm postgres: loadvip: added vip 10.20.192.101 at dev ens3
Sep 5 21:32:25 localvm patroni: 2021-03-25 21:32:25,415 INFO: Lock owner: pg1; I am pg1
Sep 5 21:32:25 localvm patroni: 2021-03-25 21:32:25,431 INFO: no action. i am the leader with the lock
Sep 5 21:32:28 localvm postgres: loadvip: called arping to gateway 10.20.192.101