搭建PostgreSQL高可用集群（基于Patroni+Etcd）

搭建PostgreSQL高可用集群（基于Patroni+Etcd）

此文以PostgreSQL 10版本为例！

如未指定，下述命令在所有节点执行！

系统资源及组件规划

节点名称	系统名称	CPU/内存	网卡	磁盘	IP地址	OS	节点角色
PGSQL1	pgsql1	2C/4G	ens33	128G	192.168.0.11	CentOS7	PostgreSQL、ETCD、Patroni
PGSQL2	pgsql2	2C/4G	ens33	128G	192.168.0.12	CentOS7	PostgreSQL、ETCD、Patroni
PGSQL3	pgsql3	2C/4G	ens33	128G	192.168.0.13	CentOS7	PostgreSQL、ETCD、Patroni

二、系统软件安装与设置

1、安装基本软件

yum -y install vim lrzsz bash-completion

2、设置名称解析

echo 192.168.0.11 pgsql1 >> /etc/hosts
echo 192.168.0.12 pgsql2 >> /etc/hosts
echo 192.168.0.13 pgsql3 >> /etc/hosts

3、设置NTP

yum -y install chrony

systemctl start chronyd
systemctl enable chronyd
systemctl status chronyd

chronyc sources

4、设置SELinux、防火墙

systemctl stop firewalld
systemctl disable firewalld
setenforce 0
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

三、搭建PostgreSQL高可用集群

1、部署ETCD集群

安装ETCD：

yum -y install 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.0.11:2380"
ETCD_LISTEN_CLIENT_URLS="http://127.0.0.1:2379,http://192.168.0.11:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.0.11:2380"
ETCD_INITIAL_CLUSTER="etcd1=http://192.168.0.11:2380,etcd2=http://192.168.0.12:2380,etcd3=http://192.168.0.13:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.0.11: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.0.12:2380"
ETCD_LISTEN_CLIENT_URLS="http://127.0.0.1:2379,http://192.168.0.12:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.0.12:2380"
ETCD_INITIAL_CLUSTER="etcd1=http://192.168.0.11:2380,etcd2=http://192.168.0.12:2380,etcd3=http://192.168.0.13:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.0.12: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.0.13:2380"
ETCD_LISTEN_CLIENT_URLS="http://127.0.0.1:2379,http://192.168.0.13:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.0.13:2380"
ETCD_INITIAL_CLUSTER="etcd1=http://192.168.0.11:2380,etcd2=http://192.168.0.12:2380,etcd3=http://192.168.0.13:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.0.13:2379"
EOF

启动ETCD，并设置自启动：

systemctl start etcd
systemctl enable etcd
systemctl status etcd

在任意节点上查看ETCD状态：

etcdctl cluster-health

在任意节点上查看ETCD成员：

etcdctl member list

2、部署PostgreSQL

配置YUM源：

参考地址：https://www.postgresql.org/download

yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

安装PostgreSQL：

yum -y install postgresql10-server

配置环境变量：

su - postgres

修改.bash_profile，添加如下内容：

export PATH=$PATH:/usr/pgsql-10/bin

在PGSQL1节点上初始化PostgreSQL：

/usr/pgsql-10/bin/postgresql-10-setup initdb

在PGSQL1节点上配置远程登录和复制权限：

修改/var/lib/pgsql/10/data/postgresql.conf：

listen_addresses = '*'

修改/var/lib/pgsql/10/data/pg_hba.conf，修改添加如下内容：

# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 0.0.0.0/0 md5

# replication privilege.
host replication repluser 192.168.0.0/24 md5

在PGSQL1节点上启动PostgreSQL：

su - postgres
pg_ctl start
pg_ctl status

在PGSQL1节点上修改数据库密码：

su - postgres
psql -U postgres
ALTER USER postgres WITH ENCRYPTED PASSWORD '111111';
\du
\q

在PGSQL1节点上创建复制用户：

su - postgres
psql
CREATE USER repluser 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 repluser -D /var/lib/pgsql/10/data -P -v

3、部署Patroni

安装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

安装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

验证Patroni是否安装成功：

patroni --version

在PGSQL1节点上创建Patroni配置文件：

mkdir -p /usr/patroni/conf
cat > /usr/patroni/conf/patroni_postgresql.yml << EOF
scope: pgsql10
namespace: /pgsql/
name: pgsql_slot1
restapi:
  listen: 192.168.0.11:8008
  connect_address: 192.168.0.11:8008
etcd:
  host: 192.168.0.11: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: 192.168.0.11:5432
  data_dir: /var/lib/pgsql/10/data
  bin_dir: /usr/pgsql-10/bin
  authentication:
    replication:
      username: repluser
      password: 111111
    superuser:
      username: postgres
      password: 111111
tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false
EOF

在PGSQL2节点上创建Patroni配置文件：

mkdir -p /usr/patroni/conf
cat > /usr/patroni/conf/patroni_postgresql.yml << EOF
scope: pgsql10
namespace: /pgsql/
name: pgsql_slot2
restapi:
  listen: 192.168.0.12:8008
  connect_address: 192.168.0.12:8008
etcd:
  host: 192.168.0.12: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: 192.168.0.12:5432
  data_dir: /var/lib/pgsql/10/data
  bin_dir: /usr/pgsql-10/bin
  authentication:
    replication:
      username: repluser
      password: 111111
    superuser:
      username: postgres
      password: 111111
tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false
EOF

在PGSQL3节点上创建Patroni配置文件：

mkdir -p /usr/patroni/conf
cat > /usr/patroni/conf/patroni_postgresql.yml << EOF
scope: pgsql10
namespace: /pgsql/
name: pgsql_slot3
restapi:
  listen: 192.168.0.13:8008
  connect_address: 192.168.0.13:8008
etcd:
  host: 192.168.0.13: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: 192.168.0.13:5432
  data_dir: /var/lib/pgsql/10/data
  bin_dir: /usr/pgsql-10/bin
  authentication:
    replication:
      username: repluser
      password: 111111
    superuser:
      username: postgres
      password: 111111
tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false
EOF

在所有节点上配置systemd管理Patroni：

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=postgres
Group=postgres
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

在所有节点上启动Patroni，并设置自启动：

systemctl start patroni
systemctl enable patroni
systemctl status patroni

在任意节点上查看Patroni集群状态：

patronictl -c /usr/patroni/conf/patroni_postgresql.yml list

在任意节点上查看ETCD信息：

etcdctl ls /pgsql/pgsql10
etcdctl get /pgsql/pgsql10/members/pgsql_slot1
etcdctl get /pgsql/pgsql10/members/pgsql_slot2
etcdctl get /pgsql/pgsql10/members/pgsql_slot3

4、部署Keepalived

安装Keepalived：

yum -y install 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 111111
    }
    virtual_ipaddress {
        192.168.0.10/24
    }
    track_script {
        check_haproxy
    }
}
EOF

在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 111111
    }
    virtual_ipaddress {
        192.168.0.10/24
    }
    track_script {
        check_haproxy
    }
}
EOF

在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 111111
    }
    virtual_ipaddress {
        192.168.0.10/24
    }
    track_script {
        check_haproxy
    }
}
EOF

在所有节点上创建检查脚本：

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

启动Keepalived，并设置自启动：

systemctl start keepalived
systemctl enable keepalived
systemctl status keepalived

在PGSQL1节点上查看Keepalived工作状态：

ip addr

在ens33网卡绑定了192.168.0.10虚拟IP

5、部署HAProxy

安装HAProxy：

yum -y install haproxy

在所有节点上创建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.0.11:5432 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
    server pgsql2 192.168.0.12:5432 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
    server pgsql3 192.168.0.13:5432 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.0.11:5432 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
    server pgsql2 192.168.0.12:5432 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
    server pgsql3 192.168.0.13:5432 maxconn 1000 check port 8008 inter 5000 rise 2 fall 2
EOF

启动HAProxy，并设置自启动：

systemctl start haproxy
systemctl enable haproxy
systemctl status haproxy

6、PostgreSQL故障演示

在任意节点上查看Patroni集群状态：

patronictl -c /usr/patroni/conf/patroni_postgresql.yml list

PGSQL1节点为Leader节点

通过虚IP的5000端口连接数据库：

psql -U postgres -h 192.168.0.10 -p 5000

创建数据库和表：

CREATE DATABASE db;

\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集群状态：

patronictl -c /usr/patroni/conf/patroni_postgresql.yml list

此时PGSQL3节点为Leader节点

通过虚IP的5000端口连接数据库：

psql -U postgres -h 192.168.0.10 -p 5000

插入数据：

\c db

INSERT INTO tb (id,name) VALUES (2,'Redis');

查看数据：

SELECT * FROM tb;

\q

数据库读写正常