etcd patroni pgsql 实现数据库高可用
目录
1.机器规划
| 节点名称 | 系统名称 | IP地址 | OS | 节点角色 |
|---|---|---|---|---|
| PGSQL1 | pgsql1 | 172.30.3.15/vip=172.30.3.250 | Rocky Linux release 9.2 | PostgreSQL、ETCD、Patroni |
| PGSQL2 | pgsql2 | 172.30.3.16/vip=172.30.3.250 | Rocky Linux release 9.2 | PostgreSQL、ETCD、Patroni |
| PGSQL3 | pgsql3 | 172.30.3.17/vip=172.30.3.250 | Rocky Linux release 9.2 | PostgreSQL、ETCD、Patroni |
2.节点优化
2.1修改主机名
#pg_node2、pg_node3对应修改
hostnamectl set-hostname "pg_node1"
设置名称解析
echo 172.30.3.15 pgsql1 >> /etc/hosts
echo 172.30.3.16 pgsql2 >> /etc/hosts
echo 172.30.3.17 pgsql3 >> /etc/hosts
2.2关闭selinux
sed -i 's/SELINUX=.*/SELINUX=disabled/g' /etc/selinux/config
2.3配置防火墙
firewall-cmd --add-port=5432/tcp --permanent
firewall-cmd --add-port=8008/tcp --permanent
firewall-cmd --add-port=2379/tcp --permanent
firewall-cmd --add-port=2380/tcp --permanent
firewall-cmd --reload
firewall-cmd --list-all
2.5配置主机同步时间
yum -y install chrony
systemctl start chronyd
systemctl enable chronyd
2.6安装需要的包
yum -y install gcc epel-release wget readline* zlib* bzip2 gcc-c++ openssl-devel python-pip python-psycopg2 python-devel lrzsz jq
2.7重启服务器
reboot
3.安装配置etcd(三个节点都安装)
1.下载etcd.tar包
curl -O https://github.com/etcd-io/etcd/releases/download/v3.5.0/etcd-v3.5.0-linux-amd64.tar.gz
2.解压
tar xf etcd-v3.5.0-linux-amd64.tar.gz -C /opt
3.重命令
mv etcd-v3.5.0-linux-amd64 etcd-v3.5.0
4. 创建命令连接
cd /opt/etcd-v3.5.0
cp etcd* /usr/local/bin
5.配置systemctl 启动
[root@pgnode1 ~]# cat /usr/lib/systemd/system/etcd.service
[Unit]
Description=Etcd Server
After=network.target
After=network-online.target
Wants=network-online.target
[Service]
Type=notify
WorkingDirectory=/opt/etcd-v3.5.0/
#User=postgres
ExecStart=/opt/etcd-v3.5.0/etcd --config-file=/etc/etcd/conf.yml
Restart=on-failure
LimitNOFILE=65536
[Install]
WantedBy=multi-user.target
6.在PGSQL1节点上创建ETCD配置文件：
[root@pgnode1 etcd]# pwd
/etc/etcd
[root@pgnode1 etcd]# cat conf.yml
name: etcd-1
data-dir: /opt/etcd-v3.5.0/data
listen-client-urls: http://172.30.3.15:2379,http://127.0.0.1:2379
advertise-client-urls: http://172.30.3.15:2379 #,http://127.0.0.1:2379
listen-peer-urls: http://172.30.3.15:2380
initial-advertise-peer-urls: http://172.30.3.15:2380
initial-cluster: etcd-1=http://172.30.3.15:2380,etcd-2=http://172.30.3.16:2380,etcd-3=http://172.30.3.17:2380
initial-cluster-token: etcd-cluster-token
initial-cluster-state: new
[root@pgnode1 etcd]#
在PGSQL2节点上创建ETCD配置文件：
[root@pgnode2 opt]# cat /etc/etcd/conf.yml
name: etcd-2
data-dir: /opt/etcd-v3.5.0/data
listen-client-urls: http://172.30.3.16:2379,http://127.0.0.1:2379
advertise-client-urls: http://172.30.3.16:2379 #,http://127.0.0.1:2379
listen-peer-urls: http://172.30.3.16:2380
initial-advertise-peer-urls: http://172.30.3.16:2380
initial-cluster: etcd-1=http://172.30.3.15:2380,etcd-2=http://172.30.3.16:2380,etcd-3=http://172.30.3.17:2380
initial-cluster-token: etcd-cluster-token
initial-cluster-state: new
[root@pgnode2 opt]#
在PGSQL3节点上创建ETCD配置文件：
[root@pgnode3 ~]# cat /etc/etcd/conf.yml
name: etcd-3
data-dir: /opt/etcd-v3.5.0/data
listen-client-urls: http://172.30.3.17:2379,http://127.0.0.1:2379
advertise-client-urls: http://172.30.3.17:2379 #,http://127.0.0.1:2379
listen-peer-urls: http://172.30.3.17:2380
initial-advertise-peer-urls: http://172.30.3.17:2380
initial-cluster: etcd-1=http://172.30.3.15:2380,etcd-2=http://172.30.3.16:2380,etcd-3=http://172.30.3.17:2380
initial-cluster-token: etcd-cluster-token
initial-cluster-state: new
[root@pgnode3 ~]#
7.启动ETCD，并设置自启动：
systemctl start etcd
systemctl enable etcd
systemctl status etcd
8.在任意节点上查看ETCD成员：
etcdctl member list
4.安装数据库
1.安装数据库pgsql16
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql16-server
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
# sudo systemctl enable postgresql-16
# sudo systemctl start postgresql-16
2.配置环境变量：
su - postgres
修改.bash_profile，添加如下内容：
[root@pgnode1 ~]# su - postgres
Last login: Wed Sep 27 13:31:57 HKT 2023 on pts/3
export PATH=$PATH:/usr/pgsql-16/bin
[postgres@pgnode1 ~]$ ll -a
total 32
drwx------ 3 postgres postgres 109 Sep 27 15:33 .
drwxr-xr-x. 23 root root 4096 Sep 26 14:55 ..
drwx------ 4 postgres postgres 51 Sep 27 13:37 16
-rw------- 1 postgres postgres 843 Sep 27 15:33 .bash_history
-rwx------ 1 postgres postgres 302 Sep 27 13:35 .bash_profile
-rw------- 1 postgres postgres 29 Sep 27 14:53 pgpass
-rw------- 1 postgres postgres 391 Sep 27 14:12 .psql_history
-rw------- 1 postgres postgres 11206 Sep 27 14:36 .viminfo
[postgres@pgnode1 ~]$ cat .bash_profile
[ -f /etc/profile ] && source /etc/profile
PGDATA=/var/lib/pgsql/16/data
export PGDATA
export PATH=$PATH:/usr/pgsql-16/bin
# If you want to customize your settings,
# Use the file below. This is not overridden
# by the RPMS.
[ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile
# 在PGSQL1节点上配置远程登录和复制权限：(一定要写* ，不然配置的vip 无法生效)
修改vim /var/lib/pgsql/16/data/postgresql.conf：
listen_addresses = '*'
修改/var/lib/pgsql/16/data/pg_hba.conf，修改添加如下内容：
[root@pgnode1 16]# egrep -v "^#|^$" /var/lib/pgsql/16/data/pg_hba.conf
local all all peer
host all all 127.0.0.1/32 scram-sha-256
#下面为添加内容
host all all 172.30.0.0/16 scram-sha-256
host all all ::1/128 scram-sha-256
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
#下面为添加内容
host replication replica 172.30.3.0/24 md5
在PGSQL1节点上修改数据库密码：
su - postgres
psql -U postgres
ALTER USER postgres WITH ENCRYPTED PASSWORD '111111';
\du
\q
在PGSQL1节点上创建复制用户：
su - postgres
psql
CREATE USER replica WITH REPLICATION PASSWORD '111111';
\du
\q
在PGSQL1节点上创建槽位：
su - postgres
psql
SELECT * FROM pg_replication_slots;
SELECT * FROM pg_create_physical_replication_slot('pgsql_slot1');
SELECT * FROM pg_create_physical_replication_slot('pgsql_slot2');
SELECT * FROM pg_create_physical_replication_slot('pgsql_slot3');
SELECT * FROM pg_replication_slots;
在PGSQL2和PGSQL3节点上备份PGSQL1节点数据：
su - postgres
pg_basebackup -h pgsql1 -U replica -D /var/lib/pgsql/16/data -P -v
操作记录：
[postgres@pgnode1 data]$ psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
Is the server running locally and accepting connections on that socket?
[postgres@pgnode1 data]$ psql
psql (16.0)
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
postgres=# ALTER USER postgres WITH ENCRYPTED PASSWORD '111111';
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
postgres=# CREATE USER replica WITH REPLICATION PASSWORD '111111';
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
replica | Replication
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
postgres=# SELECT * FROM pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status
| safe_wal_size | two_phase | conflicting
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------
+---------------+-----------+-------------
(0 rows)
postgres=# SELECT * FROM pg_create_physical_replication_slot('pgsql_slot1');
slot_name | lsn
-------------+-----
pgsql_slot1 |
(1 row)
postgres=# SELECT * FROM pg_create_physical_replication_slot('pgsql_slot2');
slot_name | lsn
-------------+-----
pgsql_slot2 |
(1 row)
postgres=# SELECT * FROM pg_create_physical_replication_slot('pgsql_slot3');
slot_name | lsn
-------------+-----
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 | confirmed_flush_lsn | wal_statu
s | safe_wal_size | two_phase | conflicting
-------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+----------
--+---------------+-----------+-------------
pgsql_slot1 | | physical | | | f | f | | | | | |
| | f |
pgsql_slot2 | | physical | | | f | f | | | | | |
| | f |
pgsql_slot3 | | physical | | | f | f | | | | | |
| | f |
(3 rows)
postgres=# \q
[postgres@pgnode1 data]$ sudo mkdir -p /etc/patroni
[postgres@pgnode1 data]$ sudo chown -R postgres:postgres /etc/patroni
[postgres@pgnode1 data]$ cd /etc/patroni/
[postgres@pgnode1 patroni]$ ll
total 0
[postgres@pgnode1 patroni]$ vim /etc/patroni/patroni.yml
[postgres@pgnode1 patroni]$ cat /etc/patroni/patroni.yml
scope: pgsql16
namespace: /pgsql/
name: pgsql_slot1
restapi:
listen: 172.30.3.15:8008
connect_address: 172.30.3.15:8008
etcd3:
hosts: 172.30.3.15:2379,172.30.3.16:2379,172.30.3.17: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"
postgresql:
listen: 0.0.0.0:5432
connect_address: 172.30.3.15:5432
data_dir: /var/lib/pgsql/16/data
bin_dir: /usr/pgsql-16/bin
authentication:
replication:
username: replica
password: 111111
superuser:
username: postgres
password: 111111
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
[postgres@pgnode1 patroni]$ vim /etc/patroni/patroni.yml
[postgres@pgnode1 patroni]$ vim /etc/patroni/patroni_callback.sh
[postgres@pgnode1 patroni]$ ll /etc/patroni/patroni_callback.sh
-rw-r--r-- 1 postgres postgres 777 Sep 27 14:36 /etc/patroni/patroni_callback.sh
[postgres@pgnode1 patroni]$ chmod 755 /etc/patroni/patroni_callback.sh
5.安装配置patroni
5.1指定国内镜像安装
pip install patroni[etcd] -i https://mirrors.aliyun.com/pypi/simple/
pip install psycopg2-binary -i https://mirrors.aliyun.com/pypi/simple/
验证Patroni是否安装成功：
patroni --version
5.2在PGSQL1节点上创建Patroni配置文件：
su - postgres
sudo mkdir -p /etc/patroni
sudo chown -R postgres:postgres /etc/patroni
[root@pgnode1 etcd]# cat /etc/patroni/patroni.yml
scope: pgsql16
namespace: /pgsql/
name: pgsql_slot1
restapi:
listen: 172.30.3.15:8008
connect_address: 172.30.3.15:8008
etcd3:
hosts: 172.30.3.15:2379,172.30.3.16:2379,172.30.3.17: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"
postgresql:
listen: 0.0.0.0:5432
connect_address: 172.30.3.15:5432
data_dir: /var/lib/pgsql/16/data
bin_dir: /usr/pgsql-16/bin
authentication:
replication:
username: replica
password: 111111
superuser:
username: postgres
password: 111111
basebackup:
#max-rate: 100M
checkpoint: fast
callbacks: # 本次配置没有使用haproxy+keepalived实现VIP切换和负载均衡，因为callbacks方式更快速，对系统资源消耗更小，操作更简单，脚本后面提供
on_start: /bin/bash /etc/patroni/patroni_callback.sh # patroni服务启动时候的触发的操作
on_stop: /bin/bash /etc/patroni/patroni_callback.sh # patroni服务停止时候触发的操作
on_role_change: /bin/bash /etc/patroni/patroni_callback.sh # patroni服务角色切换时触发的操作
watchdog: # 使用linux自带的软件watchdog监控patroni的服务持续性
mode: automatic # Allowed values: off, automatic, required
device: /dev/watchdog # watchdog设备，/dev/watchdog和/dev/watchdog0等同，可能存在兼容性区别
safety_margin: 5
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
[root@pgnode1 etcd]#
5.3在PGSQL2节点上创建Patroni配置文件：
[root@pgnode2 opt]# cat /etc/patroni/patroni.yml
scope: pgsql16
namespace: /pgsql/
name: pgsql_slot2
restapi:
listen: 172.30.3.16:8008
connect_address: 172.30.3.16:8008
etcd3:
hosts: 172.30.3.15:2379,172.30.3.16:2379,172.30.3.17: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"
postgresql:
listen: 0.0.0.0:5432
connect_address: 172.30.3.16:5432
data_dir: /var/lib/pgsql/16/data
bin_dir: /usr/pgsql-16/bin
authentication:
replication:
username: replica
password: 111111
superuser:
username: postgres
password: 111111
basebackup:
#max-rate: 100M
checkpoint: fast
callbacks: # 本次配置没有使用haproxy+keepalived实现VIP切换和负载均衡，因为callbacks方式更快速，对系统资源消耗更小，操作更简单，脚本后面提供
on_start: /bin/bash /etc/patroni/patroni_callback.sh # patroni服务启动时候的触发的操作
on_stop: /bin/bash /etc/patroni/patroni_callback.sh # patroni服务停止时候触发的操作
on_role_change: /bin/bash /etc/patroni/patroni_callback.sh # patroni服务角色切换时触发的操作
watchdog: # 使用linux自带的软件watchdog监控patroni的服务持续性
mode: automatic # Allowed values: off, automatic, required
device: /dev/watchdog # watchdog设备，/dev/watchdog和/dev/watchdog0等同，可能存在兼容性区别
safety_margin: 5
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
[root@pgnode2 opt]#
5.4在PGSQL3节点上创建Patroni配置文件：
[root@pgnode3 ~]# cat /etc/patroni/patroni.yml
scope: pgsql16
namespace: /pgsql/
name: pgsql_slot3
restapi:
listen: 172.30.3.17:8008
connect_address: 172.30.3.17:8008
etcd3:
hosts: 172.30.3.15:2379,172.30.3.16:2379,172.30.3.17: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"
postgresql:
listen: 0.0.0.0:5432
connect_address: 172.30.3.17:5432
data_dir: /var/lib/pgsql/16/data
bin_dir: /usr/pgsql-16/bin
authentication:
replication:
username: replica
password: 111111
superuser:
username: postgres
password: 111111
basebackup:
#max-rate: 100M
checkpoint: fast
callbacks: # 本次配置没有使用haproxy+keepalived实现VIP切换和负载均衡，因为callbacks方式更快速，对系统资源消耗更小，操作更简单，脚本后面提供
on_start: /bin/bash /etc/patroni/patroni_callback.sh # patroni服务启动时候的触发的操作
on_stop: /bin/bash /etc/patroni/patroni_callback.sh # patroni服务停止时候触发的操作
on_role_change: /bin/bash /etc/patroni/patroni_callback.sh # patroni服务角色切换时触发的操作
watchdog: # 使用linux自带的软件watchdog监控patroni的服务持续性
mode: automatic # Allowed values: off, automatic, required
device: /dev/watchdog # watchdog设备，/dev/watchdog和/dev/watchdog0等同，可能存在兼容性区别
safety_margin: 5
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
5.5跳转到6 配置VIP
5.6在所有节点上配置systemd管理Patroni：
[root@pgnode3 ~]# cat /usr/lib/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
EnvironmentFile=-/etc/patroni/patroni_env.conf
# 使用watchdog进行服务监控
ExecStartPre=-/usr/bin/sudo /sbin/modprobe softdog
# 使用postgres用户管理，需要sudo
ExecStartPre=-/usr/bin/sudo /bin/chown postgres /dev/watchdog
# 注意纠正patroni命令的路径
ExecStart=/usr/local/bin/patroni /etc/patroni/patroni.yml
ExecReload=/bin/kill -s HUP \$MAINPID
KillMode=process
TimeoutSec=30
Restart=no
[Install]
WantedBy=multi-user.target
[root@pgnode3 ~]#
6.配置patroni_callback ——VIP
[root@pgnode3 ~]# cat /etc/patroni/patroni_callback.sh
#!/bin/bash
readonly action=$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 $action $role $scope"
case $action in
on_stop)
sudo ip addr del 172.30.3.250/24 dev ens3 label ens3:1
;;
on_start)
;;
on_role_change)
if [[ $role == 'master' ]]; then
# 绑定VIP
sudo ip addr add 172.30.3.250/24 brd 172.30.3.255 dev ens3 label ens3:1
# 监测VIP冲突,并屏蔽冲突的IP
sudo arping -q -A -c 1 -I ens3 172.30.3.250
else
sudo ip addr del 172.30.3.250/24 dev ens3 label ens3:1
fi
;;
*)
usage
;;
esac
[root@pgnode3 ~]chmod u+x /etc/patroni/patroni_callback.sh
[root@pgnode3 ~]chown postgres:postgres /etc/patroni/patroni_callback.sh
7.watchdog进行patroni服务监控
# 安装软件，linux内置功能
yum install -y watchdog
# 初始化watchdog字符设备
modprobe softdog
# 修改/dev/watchdog设备权限
chmod 666 /dev/watchdog
# 启动watchdog服务
systemctl start watchdog
systemctl enable watchdog
8.在所有节点上启动Patroni，并设置自启动：
systemctl start patroni
systemctl enable patroni
systemctl status patroni
8.验证集群可用性
PGSQL1节点为Leader节点
通过虚IP的5432端口连接数据库：
psql -U postgres -h 172.30.3.250 -p 5432
[root@pgnode1 etcd]# psql -U postgres -h 172.30.3.250 -p 5432
Password for user postgres:
psql (16.0)
Type "help" for help.
postgres=# exit
[root@pgnode1 etcd]# psql -U postgres -h 172.30.3.250 -p 5432
Password for user postgres:
psql (16.0)
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
replica | Replication
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
postgres=# exit
在任意节点上查看Patroni集群状态：
[root@pgnode1 etcd]# patronictl -c /etc/patroni/patroni.yml list
+ Cluster: pgsql16 (7283377285291017307) ---------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+-------------+-------------+---------+-----------+----+-----------+
| pgsql_slot1 | 172.30.3.15 | Replica | running | 1 | 32 |
| pgsql_slot2 | 172.30.3.16 | Replica | running | 1 | 32 |
| pgsql_slot3 | 172.30.3.17 | Leader | running | 2 | |
+-------------+-------------+---------+-----------+----+-----------+
创建数据库和表：
\c db
CREATE TABLE tb (
id int NOT NULL,
name varchar(255) NULL,
PRIMARY KEY (id)
);
插入数据
INSERT INTO tb (id,name) VALUES (1,'MySQL');
查看数据：
SELECT * FROM tb;
\q
关闭PGSQL1节点，模拟节点故障
在任意健康节点上查看Patroni集群状态：
[root@pgnode1 etcd]# patronictl -c /etc/patroni/patroni.yml list
+ Cluster: pgsql16 (7283377285291017307) ---------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+-------------+-------------+---------+-----------+----+-----------+
| pgsql_slot2 | 172.30.3.16 | Replica | running | 1 | 32 |
| pgsql_slot3 | 172.30.3.17 | Leader | running | 2 | |
+-------------+-------------+---------+-----------+----+-----------+