基于Patroni+etcd+流复制搭建PostgreSQL高可用

IT那活儿 2022-10-29

3161

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

Patroni方案简介

Patroni是一个基于zk、etcd、consul等的pg ha模板，可以使用python来创建和定制高可用性解决方案。Patroni使用分布式key-value数据库作为数据存储，主节点故障时进行主节点重新选举。通过PG内置的流复制，支持同步和异步复制。

由于数据库信息记录在ETCD中，通过增加部署实例数可以避免脑裂，且该方案自动化程度较高，可以自动初始化PG实例；当STANDBY实例关闭后，支持自动尝试拉起；支持当主库宕机后自动选取新主库。

ETCD是一个基于RAFT协议的分布式Key-Value数据库，基于Go语言编写，Patroni监控本地的PG数据库状态，并将相关信息写入ETCD。每个Patroni都能读写ETCD的KEY，从而获取其他主机的PG数据库实例信息。

部署环境

操作系统：CentOS Linux 7.8
数据库版本：PostgreSQL 13.1
Python版本：Python3.8
ETCD版本：3.3
Patroni版本：2.0.1

部署规划：

主机名	IP	组件	备注
PG1	192.168.21.135	PostgreSQL、Patroni、ETCD	MASTER
PG2	192.168.21.136	PostgreSQL、Patroni、ETCD	STANDBY
PG3	192.168.21.137	PostgreSQL、Patroni、ETCD	STANDBY

环境准备

1. 安装PostgreSQL以及搭建流复制

本节内容不在此文展示。

2. 所有节点安装etcd

PG1：

Sudo yum install -y etcd
Vi etc/etcd/etcd.conf
ETCD_NAME="node1"
ETCD_DATA_DIR="/var/lib/etcd/node1.etcd"
ETCD_LISTEN_CLIENT_URLS="http://127.0.0.1:2379,http://192.168.21.135:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://127.0.0.1:2379,http://192.168.21.135:2379"
ETCD_LISTEN_PEER_URLS="http://192.168.21.135:2380"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.21.135:2380"
ETCD_INITIAL_CLUSTER="node1=http://192.168.21.135:2380,node2=http://192.168.21.136:2380,node3=http://192.168.21.137:2380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_CLUSTER_STATE="new"

PG2:

Sudo yum install -y etcd
Vi /etc/etcd/etcd.conf
ETCD_NAME="node2"
ETCD_DATA_DIR="/var/lib/etcd/node2.etcd"
ETCD_LISTEN_CLIENT_URLS="http://127.0.0.1:2379,http://192.168.21.136:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://127.0.0.1:2379,http://192.168.21.136:2379"
ETCD_LISTEN_PEER_URLS="http://192.168.21.136:2380"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.21.136:2380"
ETCD_INITIAL_CLUSTER="node1=http://192.168.21.135:2380,node2=http://192.168.21.136:2380,node3=http://192.168.21.137:2380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_CLUSTER_STATE="new"

PG3:

Sudo yum install -y etcd
Vi /etc/etcd/etcd.conf
ETCD_NAME="node3"
ETCD_DATA_DIR="/var/lib/etcd/node3.etcd"
ETCD_LISTEN_CLIENT_URLS="http://127.0.0.1:2379,http://192.168.21.137:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://127.0.0.1:2379,http://192.168.21.137:2379"
ETCD_LISTEN_PEER_URLS="http://192.168.21.137:2380"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.21.137:2380"
ETCD_INITIAL_CLUSTER="node1=http://192.168.21.135:2380,node2=http://192.168.21.136:2380,node3=http://192.168.21.137:2380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_CLUSTER_STATE="new"

3. 所有节点安装patroni

3.1 依赖包安装

注意：所有节点都需要安装：

Yum install -y libffi-devel libuuid libuuid-devel

3.2 安装python

注意：所有节点都需要安装。

Tar -xzvf python-3.8.7.tgz
Cd python-3.8.7
./configure
Make -j 7
Make -j 7 install

设置系统默认的Python版本：

Rm -rf /usr/bin/python
Ln -s /usr/local/bin/python3 /usr/bin/pyhon

注意：操作yum使用的python2版本，修改默认的python版本后，yum无法正常使用，解决：

vi /usr/bin/yum
修改#!/usr/bin/python 为#!/usr/bin/python2

3.3 安装patroni

Pip3 install psycopg2-binary
Pip3 install patroni[etcd]

3.4 创建patroni配置文件

PG1：

Mkdir /etc/patroni
Vi /etc/patroni/patroni.conf
scope: etcd_patroni_pg
namespace: /data/
name: pg1
restapi:
listen: 192.168.21.135:8008
connect_address: 192.168.21.135:8008
etcd:
host: 192.168.21.135: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_address: "*"
port: 5432
max_connections: 2000
hot_standby: "on"
max_locks_per_transactions: 128
max_prepared_transactions: 2000
max_replication_slots: 10
max_wal_sender: 10
max_worker_precesses: 256
track_commit_timestamp: "on"
wal_keep_size: 0
wal_level: replica
wal_log_hints: "on"
log_directory: "log"
log_destination: "stderr"
postgresql:
listen: 0.0.0.0:5432
connect_address: 192.168.21.135:5432
data_dir: /data/pgdata
bin_dir: /opt/pg13/bin
pgpass: /home/postgres/.pgpass
authentication:
replication:
username: replica
password: replica
superuser:
username: postgres
password: root
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false

PG2:

mkdir /etc/patroni
vim /etc/patroni/partoni.conf
scope: etcd_patroni_pg
namespace: /data/
name: pg2
restapi:
listen: 192.168.21.136:8008
connect_address: 192.168.21.136:8008
etcd:
host: 192.168.21.136: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_address: "*"
port: 5432
max_connections: 2000
hot_standby: "on"
max_locks_per_transactions: 128
max_prepared_transactions: 2000
max_replication_slots: 10
max_wal_sender: 10
max_worker_precesses: 256
track_commit_timestamp: "on"
wal_keep_size: 0
wal_level: replica
wal_log_hints: "on"
log_directory: "log"
log_destination: "stderr"
postgresql:
listen: 0.0.0.0:5432
connect_address: 192.168.21.136:5432
data_dir: /data/pgdata
bin_dir: /opt/pg13/bin
pgpass: /home/postgres/.pgpass
authentication:
replication:
username: replica
password: replica
superuser:
username: postgres
password: root
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false

PG3:

mkdir /etc/patroni
vim /etc/patroni/patroni.conf
scope: etcd_patroni_pg
namespace: /data/
name: pg3
restapi:
listen: 192.168.21.137:8008
connect_address: 192.168.21.137:8008
etcd:
host: 192.168.21.137: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_address: "*"
port: 5432
max_connections: 2000
hot_standby: "on"
max_locks_per_transactions: 128
max_prepared_transactions: 2000
max_replication_slots: 10
max_wal_sender: 10
max_worker_precesses: 256
track_commit_timestamp: "on"
wal_keep_size: 0
wal_level: replica
wal_log_hints: "on"
log_directory: "log"
log_destination: "stderr"
postgresql:
listen: 0.0.0.0:5432
connect_address: 192.168.21.137:5432
data_dir: /data/pgdata
bin_dir: /opt/pg13/bin
pgpass: /home/postgres/.pgpass
authentication:
replication:
username: replica
password: replica
superuser:
username: postgres
password: root
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false

3.5 将patroni加入服务开机启动

vi /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/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

3.6 启动集群

启动数据库：
Pg_ctl start $PGDATA
启动etcd：
Sudo systemctl start etcd
启动patroni:
Sudo systemctl start patroni

操作演示

1. 查看节点状态

Role：显示当前节点所属状态；
TL：显示当前集群的时间线；
Lag in MB:显示当前集群主备延迟大小。

2. 手工触发主备切换

切换后，新主变为pg2,在进行一次当前集群状态查看：

原始旧主库，自动切换为新主的备库，TL时间线变化。切换后，旧主库会自动跟随新主库。

3. 手工停止主节点Patroni

在次触发主备切换。

重新启动patroni组件后，集群状态：

Pg2自动跟随新主库pg3.

4. 手工停止主节点PG实例

停止主库的后，patroni组件很快就检测到主库宕机，然后尝试将主库重新启动。如果启动不成功，则触发主备切换。

绑定VIP

Patroni集群，在应用连接时，可以连接多个IP，在使用时，先判断当前实例是否为MASTER，如果是，则业务继续进行，否则需要尝试其他IP。PATRONI可以将VIP绑定在MASTER节点，当主备切换时，VIP漂移到新主节点。

VIP绑定实现：

1）修改patroni配置文件，每个节点都需要。

scope: etcd_patroni_pg1
namespace: /data/
name: pg1
restapi:
listen: 192.168.21.135:8008
connect_address: 192.168.21.135:8008
etcd:
host: 192.168.21.135: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_address: "*"
port: 5432
max_connections: 2000
hot_standby: "on"
max_locks_per_transaction: 128
max_prepared_transactions: 2000
max_replication_slots: 10
max_wal_sender: 10
max_worker_precesses: 256
track_commit_timestamp: "on"
wal_keep_size: 0
wal_level: replica
wal_log_hints: "on"
log_directory: "log"
log_destination: "stderr"
postgresql:
listen: 0.0.0.0:5432
connect_address: 192.168.21.135:5432
data_dir: /data/pgdata
bin_dir: /opt/pg13/bin
authentication:
replication:
username: replica
password: replica
superuser:
username: postgres
password: root
pgpass: /home/postgresql/.pgpass
callbacks:
on_start: /etc/patroni/patroni_callback.sh
on_stop: /etc/patroni/patroni_callback.sh
on_role_change: /etc/patroni/patroni_callback.sh
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false

注意：红色部分，为绑定VIP需要的shell脚本。

2）Shell脚本：

#!/bin/bash

readonly cb_name=$1
readonly role=$2
readonly scope=$3

function usage() {
echo "Usage: $0 <on_start|on_stop|on_role_change> <role> <scope>";
exit 1;
}

echo "this is patroni callback $cb_name $role $scope"

case $cb_name in
on_stop)
sudo ip addr del 192.168.21.250/24 dev ens33 label ens33:1
sudo arping -q -A -c 1 -I ens33 192.168.21.250
#sudo iptables -F
;;
on_start)
;;
on_role_change)
if [[ $role == 'master' ]]; then
sudo ip addr add 192.168.21.250/24 brd 192.168.21.255 dev ens33 label ens33:1
sudo arping -q -A -c 1 -I ens33 192.168.21.250
#sudo iptables -F
elif [[ $role == 'slave' ]]||[[ $role == 'replica' ]]||[[ $role == 'logical' ]]; then
sudo ip addr del 192.168.21.250/24 dev ens33 label ens33:1
sudo arping -q -A -c 1 -I enp0s8 192.168.21.250
#sudo iptables -F
fi
;;
*)
usage
;;
esac

本文作者：魏强（上海新炬王翦团队）

本文来源：“IT那活儿”公众号

文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。