搭建PostgreSQL高可用集群（基于Patroni+Etcd）
1.主机环境准备
| 节点名称 | 主机名 | 网卡 | IP地址 | OS | 安装软件 | 角色作用 |
|---|---|---|---|---|---|---|
| PGSQL1 | pgsql1 | ens33 | 192.168.198.162 | CentOS7 | PostgreSQL、ETCD、Patroni | 主数据库 |
| PGSQL2 | pgsql2 | ens33 | 192.168.198.163 | CentOS7 | PostgreSQL、ETCD、Patroni | 备份数据库1 |
| PGSQL3 | pgsql3 | ens33 | 192.168.198.159 | CentOS7 | PostgreSQL、ETCD、Patroni | 备份数据库2 |
2. 基本软件安装和设置
2.1 安装基本软件
yum -y install vim lrzsz bash-completion
2.2 设置名称解析
echo 192.168.198.162 pgsql1 >> /etc/hosts
echo 192.168.198.163 pgsql2 >> /etc/hosts
echo 192.168.198.159 pgsql3 >> /etc/hosts
2.3 设置NTP
yum -y install chrony
systemctl start chronyd
systemctl enable chronyd
systemctl status chronyd
chronyc sources
2.4 设置SELinux、防火墙
systemctl status firewalld
systemctl stop firewalld
systemctl disable firewalld
setenforce 0
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
3. 搭建PostgreSQL高可用集群
3.1 搭建ETCD集群
3.1.1 安装ETCD
yum -y install etcd
3.1.2 修改ETCD配置文件
在PGSQL1节点上修改ETCD配置文件：
cat > /etc/etcd/etcd.conf << EOF
ETCD_NAME="etcd1"
ETCD_DATA_DIR="/var/lib/etcd/etcd1"
ETCD_LISTEN_PEER_URLS="http://192.168.198.162:2380"
ETCD_LISTEN_CLIENT_URLS="http://127.0.0.1:2379,http://192.168.198.162:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.198.162:2380"
ETCD_INITIAL_CLUSTER="etcd1=http://192.168.198.162:2380,etcd2=http://192.168.198.163:2380,etcd3=http://192.168.198.159:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.198.162:2379"
EOF
在PGSQL2节点上创建ETCD配置文件：
cat > /etc/etcd/etcd.conf << EOF
ETCD_NAME="etcd2"
ETCD_DATA_DIR="/var/lib/etcd/etcd2"
ETCD_LISTEN_PEER_URLS="http://192.168.198.163:2380"
ETCD_LISTEN_CLIENT_URLS="http://127.0.0.1:2379,http://192.168.198.163:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.198.163:2380"
ETCD_INITIAL_CLUSTER="etcd1=http://192.168.198.162:2380,etcd2=http://192.168.198.163:2380,etcd3=http://192.168.198.159:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.198.163:2379"
EOF
在PGSQL3节点上创建ETCD配置文件：
cat > /etc/etcd/etcd.conf << EOF
ETCD_NAME="etcd3"
ETCD_DATA_DIR="/var/lib/etcd/etcd3"
ETCD_LISTEN_PEER_URLS="http://192.168.198.159:2380"
ETCD_LISTEN_CLIENT_URLS="http://127.0.0.1:2379,http://192.168.198.159:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.198.159:2380"
ETCD_INITIAL_CLUSTER="etcd1=http://192.168.198.162:2380,etcd2=http://192.168.198.163:2380,etcd3=http://192.168.198.159:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.198.159:2379"
EOF
3.1.3 启动ETCD
启动ETCD，并设置自启动：
systemctl start etcd
systemctl enable etcd
systemctl status etcd
3.1.4 查看集群状态
在任意节点上查看ETCD状态：
etcdctl cluster-health
在任意节点上查看ETCD成员：
etcdctl member list
3.2 部署PostgreSQL
3.1 安装
参照FlyingDB数据库的安装文档。版本为FlyingDB-13.13-8k-rhel7-x86_64。
3.2 环境变量配置
su - fbase
cat ~/.bashrc
export PGHOME=/usr/local/fbase/13.3
export PGPORT=8432
export PGDATA=/data/fbase/fbdata
export PGUSER=fbase
export PGHOST=localhost
export PGDATABASE=postgres
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
export PG_MASTER_OOM_SCORE_ADJ=1000
export PG_OOM_ADJUST_VALUE=0
export DATE=`date +'%Y%m%d%H%M'`
export LANG=en_US.utf8
3.3 启动
su - fbase
pg_ctl start
pg_ctl status
3.4 创建复制槽
在PGSQL1节点创建逻辑复制槽：
# 查看当前用户
SELECT CURRENT_USER;
# 在主库上执行，查看repuser的用户是否有REPLICATION的权限
SELECT rolname, rolsuper, rolreplication FROM pg_roles WHERE rolname = 'repuser';
# 在主库上执行，授予权限
GRANT CREATE_REPLICATION_SLOT ON DATABASE your_database TO repuser;
# 在主库上执行，切换用户
SET ROLE repuser;
# 查看复制槽
SELECT * FROM pg_replication_slots;
# 创建复制槽
SELECT pg_create_physical_replication_slot('pgsql_slot1');
SELECT pg_create_physical_replication_slot('pgsql_slot2');
SELECT pg_create_physical_replication_slot('pgsql_slot3');
# 查看是否创建成功
SELECT * FROM pg_replication_slots;
数据库操作结果：
postgres=> SELECT pg_create_physical_replication_slot('pgsql_slot1');
pg_create_physical_replication_slot
-------------------------------------
(pgsql_slot1,)
(1 row)
postgres=> SELECT pg_create_physical_replication_slot('pgsql_slot2');
pg_create_physical_replication_slot
-------------------------------------
(pgsql_slot2,)
(1 row)
postgres=> SELECT pg_create_physical_replication_slot('pgsql_slot3');
pg_create_physical_replication_slot
-------------------------------------
(pgsql_slot3,)
(1 row)
postgres=> SELECT * FROM pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirme
d_flush_lsn | wal_status | safe_wal_size
-------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------
------------+------------+---------------
pgsql_slot1 | | physical | | | f | f | | | | |
| |
pgsql_slot2 | | physical | | | f | f | | | | |
| |
pgsql_slot3 | | physical | | | f | f | | | | |
| |
(3 rows)
修改PGSQL3节点和PGSQL3节点的postgresql.conf文件。
pgsql2：
primary_conninfo = 'host=192.168.198.162 port=8432 user=repuser password=123456'
primary_slot_name = 'pgsql_slot1'
pgsql3：
primary_conninfo = 'host=192.168.198.162 port=8432 user=repuser password=123456'
primary_slot_name = 'pgsql_slot2'
重启pgsql2和pgsql3的postgresql数据库。
pg_ctl restart
查看pgsql1上的复制槽使用情况。
postgres=# SELECT * FROM pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirme
d_flush_lsn | wal_status | safe_wal_size
-------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------
------------+------------+---------------
pgsql_slot1 | | physical | | | f | t | 3680 | 489 | | 0/5000060 |
| reserved |
pgsql_slot2 | | physical | | | f | t | 3687 | 489 | | 0/5000060 |
| reserved |
pgsql_slot3 | | physical | | | f | f | | | | |
| |
(3 rows)
3.3 部署Patroni
3.3.1 安装前置软件
安装Python3：
yum install -y python3
安装pip：
curl https://bootstrap.pypa.io/pip/3.6/get-pip.py -o get-pip.py
python3 get-pip.py
3.3.2 安装Patroni
安装Patroni：
pip install psycopg2-binary -i https://pypi.tuna.tsinghua.edu.cn/simple
pip install patroni[etcd,consul] -i https://pypi.tuna.tsinghua.edu.cn/simple
3.3.3 验证是否安装
验证Patroni是否安装成功：
patroni --version
3.3.4 修改配置文件
3.3.4.1 在PGSQL1节点上创建Patroni配置文件
在PGSQL1节点上创建Patroni配置文件：
mkdir -p /usr/patroni/conf
cd /usr/patroni/conf
touch patroni_postgresql.yml
cat > /usr/patroni/conf/patroni_postgresql.yml << EOF
scope: pgsql13
namespace: /pgsql/
name: pgsql_slot1
restapi:
listen: 192.168.198.162:8008
connect_address: 192.168.198.162:8008
etcd:
host: 192.168.198.162: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: 8432
wal_level: logical
hot_standby: "on"
wal_keep_segments: 1000
max_wal_senders: 10
max_replication_slots: 10
wal_log_hints: "on"
postgresql:
listen: 0.0.0.0:8432
connect_address: 192.168.198.162:8432
data_dir: /data/fbase/fbdata
bin_dir: /usr/local/fbase/13.3/bin
authentication:
replication:
username: repuser
password: 123456
superuser:
username: fbase
password: 123456
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
EOF
3.3.4.2 在PGSQL2节点上创建Patroni配置文件
在PGSQL2节点上创建Patroni配置文件：
mkdir -p /usr/patroni/conf
cd /usr/patroni/conf
touch patroni_postgresql.yml
cat > /usr/patroni/conf/patroni_postgresql.yml << EOF
scope: pgsql13
namespace: /pgsql/
name: pgsql_slot2
restapi:
listen: 192.168.198.163:8008
connect_address: 192.168.198.163:8008
etcd:
host: 192.168.198.163: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: 8432
wal_level: logical
hot_standby: "on"
wal_keep_segments: 1000
max_wal_senders: 10
max_replication_slots: 10
wal_log_hints: "on"
postgresql:
listen: 0.0.0.0:8432
connect_address: 192.168.198.163:8432
data_dir: /data/fbase/fbdata
bin_dir: /usr/local/fbase/13.3/bin
authentication:
replication:
username: repuser
password: 123456
superuser:
username: fbase
password: 123456
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
EOF
3.3.4.3 在PGSQL3节点上创建Patroni配置文件：
在PGSQL3节点上创建Patroni配置文件：
mkdir -p /usr/patroni/conf
cd /usr/patroni/conf
touch patroni_postgresql.yml
cat > /usr/patroni/conf/patroni_postgresql.yml << EOF
scope: pgsql13
namespace: /pgsql/
name: pgsql_slot3
restapi:
listen: 192.168.198.159:8008
connect_address: 192.168.198.159:8008
etcd:
host: 192.168.198.159: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: 8432
wal_level: logical
hot_standby: "on"
wal_keep_segments: 1000
max_wal_senders: 10
max_replication_slots: 10
wal_log_hints: "on"
postgresql:
listen: 0.0.0.0:8432
connect_address: 192.168.198.159:8432
data_dir: /data/fbase/fbdata
bin_dir: /usr/local/fbase/13.3/bin
authentication:
replication:
username: repuser
password: 123456
superuser:
username: fbase
password: 123456
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
EOF
3.3.4.4 在所有节点上配置systemd管理Patroni
在所有节点上配置systemd管理Patroni：
touch /usr/lib/systemd/system/patroni.service
vim /usr/lib/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=fbase
Group=fbase
PermissionsStartOnly=true
ExecStart=/usr/local/bin/patroni /usr/patroni/conf/patroni_postgresql.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.3.5 启动Patroni
在所有节点上启动Patroni，并设置自启动：
systemctl start patroni
systemctl enable patroni
systemctl status patroni
3.3.6 检查Patroni状态
在任意节点上查看Patroni集群状态：
patronictl -c /usr/patroni/conf/patroni_postgresql.yml list
在任意节点上查看ETCD信息：
etcdctl ls /pgsql/pgsql13
etcdctl get /pgsql/pgsql13/members/pgsql_slot1
etcdctl get /pgsql/pgsql13/members/pgsql_slot2
etcdctl get /pgsql/pgsql13/members/pgsql_slot3
3.4 部署Keepalived
3.4.1 安装
安装Keepalived：
yum -y install keepalived
3.4.2 在PGSQL1节点上创建Keepalived配置文件
在PGSQL1节点上创建Keepalived配置文件：
cat > /etc/keepalived/keepalived.conf << EOF
global_defs {
router_id LVS_DEVEL
}
vrrp_script check_haproxy {
script "/etc/keepalived/check_haproxy.sh"
interval 2
weight 5
fall 3
rise 5
timeout 2
}
vrrp_instance VI_1 {
state Master
interface ens33
virtual_router_id 80
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 123456
}
virtual_ipaddress {
192.168.198.165/24
}
track_script {
check_haproxy
}
}
EOF
3.4.3 在PGSQL2节点上创建Keepalived配置文件
在PGSQL2节点上创建Keepalived配置文件：
cat > /etc/keepalived/keepalived.conf << EOF
global_defs {
router_id LVS_DEVEL
}
vrrp_script check_haproxy {
script "/etc/keepalived/check_haproxy.sh"
interval 2
weight 5
fall 3
rise 5
timeout 2
}
vrrp_instance VI_1 {
state Slave
interface ens33
virtual_router_id 80
priority 50
advert_int 1
authentication {
auth_type PASS
auth_pass 123456
}
virtual_ipaddress {
192.168.198.165/24
}
track_script {
check_haproxy
}
}
EOF
3.4.4 在PGSQL3节点上创建Keepalived配置文件
在PGSQL3节点上创建Keepalived配置文件：
cat > /etc/keepalived/keepalived.conf << EOF
global_defs {
router_id LVS_DEVEL
}
vrrp_script check_haproxy {
script "/etc/keepalived/check_haproxy.sh"
interval 2
weight 5
fall 3
rise 5
timeout 2
}
vrrp_instance VI_1 {
state Slave
interface ens33
virtual_router_id 80
priority 30
advert_int 1
authentication {
auth_type PASS
auth_pass 123456
}
virtual_ipaddress {
192.168.198.165/24
}
track_script {
check_haproxy
}
}
EOF
3.4.5 创建检查脚本
在所有节点上创建检查脚本：
vim /etc/keepalived/check_haproxy.sh
cat /etc/keepalived/check_haproxy.sh
#!/bin/bash
count=`ps aux | grep -v grep | grep haproxy | wc -l`
if [ $count -eq 0 ]; then
exit 1
else
exit 0
fi
赋予执行权限：
chmod a+x /etc/keepalived/check_haproxy.sh
3.4.6 启动Keepalived
启动Keepalived，并设置自启动：
systemctl start keepalived
systemctl enable keepalived
systemctl status keepalived
3.4.7 检查Keepalived状态
在PGSQL1节点上查看Keepalived工作状态：
ip addr
在ens33网卡绑定了192.168.198.165虚拟IP
3.5 部署HAProxy
3.5.1 安装HAProxy
安装HAProxy：
yum -y install haproxy
3.5.2 在所有节点创建配置文件
在所有节点上创建HAProxy配置文件：
cat > /etc/haproxy/haproxy.cfg << EOF
global
log 127.0.0.1 local2
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
maxconn 4000
user haproxy
group haproxy
daemon
stats socket /var/lib/haproxy/stats
defaults
mode tcp
log global
option tcplog
option dontlognull
option redispatch
retries 3
timeout queue 1m
timeout connect 10s
timeout client 1m
timeout server 1m
timeout check 10s
maxconn 3000
listen status
bind *:1080
mode http
log global
stats enable
stats refresh 30s
stats uri /
stats realm Private lands
stats auth admin:admin
listen master
bind *:5000
mode tcp
option tcplog
balance roundrobin
option httpchk OPTIONS /master
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server pgsql1 192.168.198.162:8432 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
server pgsql2 192.168.198.163:8432 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
server pgsql3 192.168.198.159:8432 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
listen replicas
bind *:5001
mode tcp
option tcplog
balance roundrobin
option httpchk OPTIONS /replica
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server pgsql1 192.168.198.162:8432 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
server pgsql2 192.168.198.163:8432 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
server pgsql3 192.168.198.159:8432 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
EOF
3.5.3 启动HAProxy
启动HAProxy，并设置自启动：
systemctl start haproxy
systemctl enable haproxy
systemctl status haproxy
4 PostgreSQL故障演示
4.1 故障演示前准备
在任意节点上查看Patroni集群状态：
patronictl -c /usr/patroni/conf/patroni_postgresql.yml list
PGSQL1节点为Leader节点
通过虚IP的5000端口连接数据库：
psql -U fbase -h 192.168.198.165 -p 5000
创建数据库和表：
CREATE DATABASE db;
\c db
CREATE TABLE tb (
id int NOT NULL,
name varchar(255) NULL,
PRIMARY KEY (id)
);
postgres=# CREATE DATABASE db;
CREATE DATABASE
postgres=# \c db
You are now connected to database "db" as user "fbase".
db=# CREATE TABLE tb (
db(# id int NOT NULL,
db(# name varchar(255) NULL,
db(# PRIMARY KEY (id)
db(# );
CREATE TABLE
插入数据：
INSERT INTO tb (id,name) VALUES (1,'MySQL');
db=# INSERT INTO tb (id,name) VALUES (1,'MySQL');
INSERT 0 1
查看数据：
SELECT * FROM tb;
\q
db=# SELECT * FROM tb;
id | name
----+-------
1 | MySQL
(1 row)
db=# \q
4.2 故障演示结果
关闭PGSQL1节点，模拟节点故障
在任意健康节点上查看Patroni集群状态：
patronictl -c /usr/patroni/conf/patroni_postgresql.yml list
此时PGSQL3节点为Leader节点。
[root@pgsql3 fbdata]# patronictl -c /usr/patroni/conf/patroni_postgresql.yml list
+ Cluster: pgsql13 (7399949065468504207) ------+---------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+-------------+----------------------+---------+---------+----+-----------+
| pgsql_slot1 | 192.168.198.162:8432 | Replica | running | 2 | 0 |
| pgsql_slot3 | 192.168.198.159:8432 | Leader | running | 3 | |
+-------------+----------------------+---------+---------+----+-----------+
通过虚IP的5000端口连接数据库：
psql -U fbase -h 192.168.198.165 -p 5000
插入数据：
\c db
INSERT INTO tb (id,name) VALUES (2,'Redis');
postgres=# \c db
You are now connected to database "db" as user "fbase".
db=#
db=# INSERT INTO tb (id,name) VALUES (2,'Redis');
INSERT 0 1
查看数据：
SELECT * FROM tb;
\q
db=# SELECT * FROM tb;
id | name
----+-------
1 | MySQL
2 | Redis
(2 rows)
db=# \q
数据库读写正常。
4.3 重启故障服务器
重新启动故障服务器，查看是否加入集群中。
[root@pgsql3 fbdata]# patronictl -c /usr/patroni/conf/patroni_postgresql.yml list
+ Cluster: pgsql13 (7399949065468504207) ------+-----------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+-------------+----------------------+---------+-----------+----+-----------+
| pgsql_slot1 | 192.168.198.162:8432 | Replica | streaming | 3 | 0 |
| pgsql_slot2 | 192.168.198.163:8432 | Replica | running | 3 | 0 |
| pgsql_slot3 | 192.168.198.159:8432 | Leader | running | 3 | |
+-------------+----------------------+---------+-----------+----+-----------+
PGSQL1节点加入作为备用服务器加入集群。
标签：PostgreSQL,conf,db,patroni,pgsql,Patroni,Etcd,节点 From： https://www.cnblogs.com/zreo2home/p/18353418