Patroni + Etcd实现高可用PostgreSQL
简介
Patroni + Etcd实现PostgeSQL-HA，主从复制使用PG自带的流复制，通过HAProxy也可实现读写分离。
Github：https://github.com/zalando/patroni
Patroni文档：https://patroni.readthedocs.io/en/latest/
Python库：https://pypi.org/project/patroni/
一、主机清单
| 主机名 | IP地址 | 备注 |
|---|---|---|
| pg-m | 192.168.100.101 | PG+Etcd+Patroni |
| pg-s1 | 192.168.100.102 | PG+Etcd+Patroni |
| pg-s2 | 192.168.100.103 | PG+Etcd+Patroni |
二、安装PG
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
systemctl enable postgresql-13
三、安装配置Etcd
1. 安装Etcd
cd /opt
wget -c https://github.com/etcd-io/etcd/releases/download/v3.4.7/etcd-v3.4.7-linux-amd64.tar.gz
tar xvf etcd-v3.4.7-linux-amd64.tar.gz
cd etcd-v3.4.7-linux-amd64
mv etcd* /usr/local/bin/
etcd -version
cd /opt
rm -rf etcd-v3.4.7-linux-amd64*
mkdir -p /opt/etcd
mkdir -p /data/etcd
2. 创建Etcd配置文件
server01
vi /opt/etcd/conf.yml
name: etcd01
data-dir: /data/etcd
initial-advertise-peer-urls: http://192.168.100.101:2380
listen-peer-urls: http://192.168.100.101:2380
listen-client-urls: http://192.168.100.101:2379,http://127.0.0.1:2379
advertise-client-urls: http://192.168.100.101:2379
initial-cluster-token: etcd-cluster-pg
initial-cluster: etcd01=http://192.168.100.101:2380,etcd02=http://192.168.100.102:2380,etcd03=http://192.168.100.103:2380
initial-cluster-state: new
enable-v2: true
server02
vi /opt/etcd/conf.yml
name: etcd02
data-dir: /data/etcd
initial-advertise-peer-urls: http://192.168.100.102:2380
listen-peer-urls: http://192.168.100.102:2380
listen-client-urls: http://192.168.100.102:2379,http://127.0.0.1:2379
advertise-client-urls: http://192.168.100.102:2379
initial-cluster-token: etcd-cluster-pg
initial-cluster: etcd01=http://192.168.100.101:2380,etcd02=http://192.168.100.102:2380,etcd03=http://192.168.100.103:2380
initial-cluster-state: new
enable-v2: true
server03
vi /opt/etcd/conf.yml
name: etcd03
data-dir: /data/etcd
initial-advertise-peer-urls: http://192.168.100.103:2380
listen-peer-urls: http://192.168.100.103:2380
listen-client-urls: http://192.168.100.103:2379,http://127.0.0.1:2379
advertise-client-urls: http://192.168.100.103:2379
initial-cluster-token: etcd-cluster-pg
initial-cluster: etcd01=http://192.168.100.101:2380,etcd02=http://192.168.100.102:2380,etcd03=http://192.168.100.103:2380
initial-cluster-state: new
enable-v2: true
3. 配置服务并启动
vi /usr/lib/systemd/system/etcd.service
[Unit]
Description=etcd
After=network.target
[Service]
Type=notify
ExecStart=/usr/local/bin/etcd --config-file=/opt/etcd/conf.yml
[Install]
WantedBy=multi-user.target
----------------------------------------------------------------------------
依次启动server01，02，03
systemctl daemon-reload
systemctl start etcd.service
systemctl status etcd.service
systemctl enable etcd.service
4. 检查安装结果
curl http://192.168.100.101:2379/version
curl http://192.168.100.102:2379/version
curl http://192.168.100.103:2379/version
curl http://192.168.100.101:2379/v2/members
etcd常用命令
# 查看所有数据
etcdctl get / --prefix --keys-only
# 清除所有的数据
etcdctl del / --prefix
四、安装配置Patroni
1. 安装python3
yum install -y gcc libffi-devel zlib* openssl-devel
wget -c https://www.python.org/ftp/python/3.8.2/Python-3.8.2.tar.xz
tar xf Python-3.8.2.tar.xz
cd Python-3.8.2
./configure
make && make install
rm -f /usr/bin/python
ln -s /usr/local/bin/python3 /usr/bin/python
python --version
修复python升级后yum报错问题
File "/usr/bin/yum", line 30
except KeyboardInterrupt, e:
^
SyntaxError: invalid syntax
--------------------------------------
vi /usr/bin/yum
vi /usr/libexec/urlgrabber-ext-down
第一行改为
#!/usr/bin/python2.7
--------------------------------------
2. 安装配置patroni
配置PG命令的环境变量
vi + /etc/profile
export PATH=$PATH:/usr/pgsql-13/bin/
source /etc/profile
防止报错，安装pg-devel
yum install -y centos-release-scl-rh
yum localinstall -y postgresql13-devel-13.6-1PGDG.rhel7.x86_64.rpm
安装patroni
python -m pip install --upgrade pip
pip3 install patroni[etcd] -i https://mirrors.aliyun.com/pypi/simple/
pip3 install psycopg2-binary -i https://mirrors.aliyun.com/pypi/simple/
pip3 install patroni -i https://mirrors.aliyun.com/pypi/simple/
注意：如果还是安装报错，可以重新编译安装Python
3. 配置partroni
server01
mkdir /opt/patroni
vi /opt/patroni/postgresql.yaml
scope: pg_ha
name: pg_s01
restapi:
listen: 0.0.0.0:8008
connect_address: 127.0.0.1:8008
etcd:
hosts:
- 192.168.100.101:2379
- 192.168.100.102:2379
- 192.168.100.103:2379
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
wal_level: replica
hot_standby: "on"
max_connections: 800
max_worker_processes: 8
wal_keep_segments: 8
max_wal_senders: 10
max_replication_slots: 10
max_prepared_transactions: 0
max_locks_per_transaction: 64
wal_log_hints: "on"
track_commit_timestamp: "off"
archive_mode: "on"
archive_timeout: 1800s
archive_command: mkdir -p ../wal_archive && test ! -f ../wal_archive/%f && cp %p ../wal_archive/%f
recovery_conf:
restore_command: cp ../wal_archive/%f %p
initdb:
- encoding: UTF8
- data-checksums
pg_hba:
- host replication replica 192.168.100.0/24 md5
- host all all 0.0.0.0/0 md5
users:
replica:
password: [密码]
options:
- replication
dba:
password: [密码]
options:
- createrole
- createdb
postgresql:
listen: 0.0.0.0:5432
connect_address: 192.168.100.101:5432
data_dir: /data/postgresql
bin_dir: /usr/pgsql-13/bin
config_dir: /data/postgresql
pgpass: /opt/patroni/pgpass
authentication:
replication:
username: replica
password: [密码]
superuser:
username: dba
password: [密码]
rewind: # Has no effect on postgres 10 and lower
username: dba
password: [密码]
parameters:
unix_socket_directories: '/tmp'
watchdog:
mode: off # Allowed values: off, automatic, required
device: /dev/watchdog
safety_margin: 5
server02
mkdir /opt/patroni
vi /opt/patroni/postgresql.yaml
scope: pg_ha
name: pg_s02
restapi:
listen: 0.0.0.0:8008
connect_address: 127.0.0.1:8008
etcd:
hosts:
- 192.168.100.101:2379
- 192.168.100.102:2379
- 192.168.100.103:2379
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
standby_cluster:
host: 192.168.100.101
port: 2379
primary_slot_name: patroni
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
wal_level: replica
hot_standby: "on"
max_connections: 800
max_worker_processes: 8
wal_keep_segments: 8
max_wal_senders: 10
max_replication_slots: 10
max_prepared_transactions: 0
max_locks_per_transaction: 64
wal_log_hints: "on"
track_commit_timestamp: "off"
archive_mode: "on"
archive_timeout: 1800s
initdb:
- encoding: UTF8
pg_hba:
- host replication replica 192.168.100.0/24 md5
- host all all 0.0.0.0/0 md5
users:
replica:
password: [密码]
options:
- replication
dba:
password: [密码]
options:
- createrole
- createdb
postgresql:
listen: 0.0.0.0:5432
connect_address: 192.168.100.102:5432
data_dir: /data/postgresql
bin_dir: /usr/pgsql-13/bin
config_dir: /data/postgresql
pgpass: /opt/patroni/pgpass
authentication:
replication:
username: replica
password: [密码]
superuser:
username: dba
password: [密码]
rewind: # Has no effect on postgres 10 and lower
username: dba
password: [密码]
parameters:
unix_socket_directories: '/tmp'
watchdog:
mode: off # Allowed values: off, automatic, required
device: /dev/watchdog
safety_margin: 5
server03
mkdir /opt/patroni
vi /opt/patroni/postgresql.yaml
scope: pg_ha
name: pg_s03
restapi:
listen: 0.0.0.0:8008
connect_address: 127.0.0.1:8008
etcd:
hosts:
- 192.168.100.101:2379
- 192.168.100.102:2379
- 192.168.100.103:2379
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
standby_cluster:
host: 192.168.100.101
port: 2379
primary_slot_name: patroni
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
wal_level: replica
hot_standby: "on"
max_connections: 800
max_worker_processes: 8
wal_keep_segments: 8
max_wal_senders: 10
max_replication_slots: 10
max_prepared_transactions: 0
max_locks_per_transaction: 64
wal_log_hints: "on"
track_commit_timestamp: "off"
archive_mode: "on"
archive_timeout: 1800s
initdb:
- encoding: UTF8
pg_hba:
- host replication replica 192.168.100.0/24 md5
- host all all 0.0.0.0/0 md5
users:
replica:
password: [密码]
options:
- replication
dba:
password: [密码]
options:
- createrole
- createdb
postgresql:
listen: 0.0.0.0:5432
connect_address: 192.168.100.103:5432
data_dir: /data/postgresql
bin_dir: /usr/pgsql-13/bin
config_dir: /data/postgresql
pgpass: /opt/patroni/pgpass
authentication:
replication:
username: replica
password: [密码]
superuser:
username: dba
password: [密码]
rewind: # Has no effect on postgres 10 and lower
username: dba
password: [密码]
parameters:
unix_socket_directories: '/tmp'
port: 5433
watchdog:
mode: off # Allowed values: off, automatic, required
device: /dev/watchdog
safety_margin: 5
4. 启动patroni
server01操作
systemctl disable postgresql-13
systemctl stop postgresql-13
mkdir /data/postgresql
chown -R postgres:postgres /data
chown -R postgres:postgres /opt/patroni
su - postgres
patroni /opt/patroni/postgresql.yaml
server02和server03操作
systemctl disable postgresql-13
systemctl stop postgresql-13
mkdir /data/postgresql
chown -R postgres:postgres /data
chown -R postgres:postgres /opt/patroni
chmod 750 /data/postgresql/
su - postgres
patroni /opt/patroni/postgresql.yaml
以上前台运行无报错后，依次结束server03,server02,server01
5. 配置patroni服务单元
# vi /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/local/bin/patroni /opt/patroni/postgresql.yaml
ExecReload=/bin/kill -s HUP $MAINPID
KillMode=process
TimeoutSec=30
Restart=no
[Install]
WantedBy=multi-user.target
启动patroni服务
systemctl start patroni
systemctl status patroni
systemctl enable patroni
6. patroni常用操作
# 查看集群列表
patronictl -c /opt/patroni/postgresql.yaml list
patronictl -d etcd://127.0.0.1:2379 list pg_ha
# 清除集群信息
patronictl -c /opt/patroni/postgresql.yml remove pg_ha
# 故障转移，手动选择主节点
patronictl -d etcd://127.0.0.1:2379 failover
五、连接使用
1. psql连接
sudo -u postgres psql
或者
psql postgres --host=localhost --port=5432 --username=dba
主节点上创建用户
create user [用户] with password '[密码]';
create database [数据库] owner [用户];
grant all on database [数据库] to [用户];
2. JDBC方式连接集群
jdbc:postgresql://node1:5432,node2:5432,node3:5432/postgres?targetServerType=master
3. HAProxy代理方式
# 通过请求8008端口获取主节点和只读节点
########PostgreSQL#################
# 连接master节点
listen pgsql_master
bind *:15432
option httpchk
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server pg_s1 192.168.100.101:5432 maxconn 100 check port 8008
server pg_s2 192.168.100.102:5432 maxconn 100 check port 8008
server pg_s3 192.168.100.103:5432 maxconn 100 check port 8008
# 连接只读节点
listen pgsql_read
bind *:25432
option httpchk GET /replica
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server pg_s1 192.168.100.101:5432 maxconn 100 check port 8008
server pg_s2 192.168.100.102:5432 maxconn 100 check port 8008
server pg_s3 192.168.100.103:5432 maxconn 100 check port 8008
SQL查询主节点
select inet_server_addr(),inet_server_port();
七、PG常见操作
1. 流复制状态查看
# 主数据库查看流复制状态
psql postgres --host=localhost --port=5432 --username=dba
postgres=# \x
postgres=# select * from pg_stat_replication;
# 从库查看流复制状态
postgres=# select * from pg_stat_wal_receiver;
2. 集群管理
# 查看集群列表
patronictl -c /opt/patroni/postgresql.yaml list
# 故障转移，手动选择主节点
patronictl -d etcd://127.0.0.1:2379 failover
3. 修改PostgreSQL参数
# 修改最大连接数，需要重启才能生效
patronictl edit-config -p 'max_connections=300'
patronictl restart pgsql
八、参考文章
https://www.opsdash.com/blog/postgres-getting-started-patroni.html