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

呆呆的私房菜 2024-09-09

823

Whoami：5年+金融、政府、医疗领域工作经验的DBA
Certificate：OCP、PCP
Skill：Oracle、Mysql、PostgreSQL
Platform：CSDN、墨天伦、公众号（呆呆的私房菜）

阅读本文可以了解基于Patroni + etcd + 流复制的方式来实现PostgreSQL数据库高可用的相关内容，包含Patroni高可用架构实现的基本原理、搭建部署及主备切换等。

Patroni概述

Patroni 是一个用于自动化 PostgreSQL 数据库的设置、维护和高可用性管理的开源工具。
它通常与分布式键值存储系统（如 etcd、Consul、ZooKeeper 或 Kubernetes）一起使用，以确保数据库在主节点发生故障时能够持续提供服务。
Patroni 通过实现流复制、故障转移、配置变更和节点监控等功能，来增强 PostgreSQL 的高可用性。

Patroni基本原理

Patroni的基本实现原理是利用分布式配置存储（DCS）来协调和管理PostgreSQL集群中的多个节点，确保集群的高可用性和故障转移。它通过监控每个节点的健康状况，自动进行故障转移，以及在主节点故障时选举新的主节点，从而实现数据库的持续可用性。

分布式配置存储（DCS）集成：Patroni与etcd、Consul、ZooKeeper或Kubernetes等DCS集成，使用它们来存储集群状态和配置信息，确保集群中的所有节点都能访问到最新的集群信息；
健康检查：Patroni定期对每个PostgreSQL实例进行健康检查，包括检查数据库是否在运行、是否能够连接、复制延迟等，以确保它们能够正常工作；
故障转移：当主节点出现故障时，Patroni会自动触发故障转移过程，通过DCS中的共识算法在剩余的从节点中选举出新的主节点，并更新DCS中的相关信息；
配置管理：Patroni允许管理员通过DCS更新集群配置，这些更改会自动同步到所有节点，确保集群配置的一致性；
服务发现：应用程序可以通过查询DCS来发现当前的主节点，从而实现对数据库的读写操作，而无需直接与Patroni交互；
自动故障恢复：Patroni能够在故障节点恢复后自动将其重新加入集群，根据配置决定其作为新的主节点或从节点；
备份和恢复支持：Patroni支持与备份工具（如pgBackRest、WAL-E等）集成，提供集群备份和恢复的功能；
REST API：Patroni提供了REST API，允许用户通过patronictl工具或其他客户端程序进行集群管理和监控；
防止脑裂：Patroni通过与Linux看门狗（watchdog）集成，确保在出现网络分区或其他问题时，不会有两个节点同时认为自己是主节点，从而避免脑裂问题；
级联复制和同步复制：Patroni支持级联复制，允许从节点从另一个从节点而不是主节点复制数据，以及同步复制，确保数据在主节点和至少一个从节点上都写入后才认为是成功。

Patroni高可用架构搭建

高可用搭建环境IP规划：

系统类型	类型	主机名	IP地址
在线库集群	主库	pg01	192.168.56.11
从库	pg02	192.168.56.22
从库	pg03	192.168.56.33
VIP		192.168.56.66

高可用集群软件版本规划：

软件类型	软件版本	用途
RHEL	7.6	操作系统
Patroni	1.6	集群自动切换管理软件
Etcd	3.3.11	集群仲裁软件
PostgreSQL	13.4	数据库软件

1. PostgreSQL主备部署

# 如无特殊说明，下列命令所有节点都执行
1. 主机名配置
su - root
cat >> etc/hosts <<EOF
192.168.56.11 pg01
192.168.56.22 pg02
192.168.56.33 pg03
EOF


2. 操作系统配置
su - root
useradd postgres
echo "postgres#2024" | passwd --stdin postgres


cat >> /etc/sysctl.conf <<EOF
kernel.shmmax = 4294967296
kernel.shmall = 2097152
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144    
net.core.rmem_max = 4194304    
net.core.wmem_default = 262144    
net.core.wmem_max = 4194304
fs.file-max = 6815744
EOF


cat >> etc/security/limits.conf <<EOF
postgres soft nproc unlimited
postgres hard nproc unlimited
postgres soft nofile 100000
postgres hard nofile 100000
postgres soft stack unlimited
postgres hard stack unlimited
postgres soft core unlimited
postgres hard core unlimited
postgres soft memlock unlimited
postgres hard memlock unlimited
EOF


3. 主备互信创建
# 数据库wal需要使用scp命令集中归档至同一存储节点，因此需要对3个数据库节点的postgres用户之间配置互信；
# 本次wal归档节点使用PostgreSQL主节点，即wal归档至 192.168.56.11 的/data/pgwal/archive_wals目录下。
su - postgres
ssh-keygen -t rsa
ssh-copy-id pg01
ssh-copy-id pg02
ssh-copy-id pg03


4. 创建数据目录
su - root
mkdir -p pg/{pghome,patroni}
mkdir -p data/{pgwal,pgdata,pgbak}
mkdir -p data/pgwal/archive_wals
mkdir -p data/pgdata/pg13
chown -R postgres:postgres pg
chown -R postgres:postgres data/{pgwal,pgdata,pgbak}
chmod -R 700 pg
chmod -R 700 data/{pgwal,pgdata,pgbak}


5. 配置环境变量
su - postgres
cat >> /home/postgres/.bash_profile
export PGHOME=/pg/pghome
export PGDATA=/data/pgdata/pg13
export PGPORT=5432
export PGDATABASE=postgres
export LD_LIBRARY_PATH=\$PGHOME/lib:\$LD_LIBRARY_PATH
export PATH=\$PGHOME/bin:\$PATH
EOF
source .bashrc


6. 安装依赖包
yum -y install wget gcc gcc-c++  epel-release llvm5.0 llvm5.0-devel clang libicu-devel perl-ExtUtils-Embed readline readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel openldap-devel systemd-devel tcl-devel python-devel


7. 源码安装数据库
# 上传PostgreSQL13.4源码安装包至/data目录，使用postgres用户解压并编译安装
su - postgres
cd data
tar zxvf postgresql-13.4.tar.gz
cd postgresql-13.4
./configure --prefix=/pg/pghome --with-pgport=5432
./configure --prefix=/pg/pghome --with-pgport=5432
gmake world
gmake install
cd contrib 
make && make instal


8. 主库初始化并修改配置文件（主库执行）
su - postgres
initdb -D data/pgdata/pg13 -E UTF8 --locale=zh_CN.UTF-8
cat >> $PGDATA/pg_hba.conf <<EOF
echo "host all all 0.0.0.0/0 md5"
echo "host replication replicator 192.168.56.11/24 md5"
echo "host replication replicator 192.168.56.22/24 md5"
echo "host replication replicator 192.168.56.33/24 md5"
EOF


cat >> $PGDATA/postgresql.conf <<EOF
archive_command = 'scp %p 192.168.56.11:/data/pgwal/archive_wals/%f'
archive_mode = 'on'
archive_timeout = '1800s'
cluster_name = 'PGCluster'
hot_standby = 'on'
listen_addresses = '0.0.0.0'
max_connections = '2000'
max_locks_per_transaction = '64'
max_prepared_transactions = '0'
max_replication_slots = '10'
max_wal_senders = '50'
max_worker_processes = '8'
port = '5432'
track_commit_timestamp = 'off'
wal_keep_size = 16GB
wal_level = 'replica'
wal_log_hints = 'on'
hba_file = '/data/pgdata/pg13/pg_hba.conf'
ident_file = '/data/pgdata/pg13/pg_ident.conf'
restore_command = 'scp 192.168.56.11:/data/pgwal/archive_wals/%f %p'
recovery_target_timeline = 'latest'
EOF


pg_ctl start
psql -U postgres -c "ALTER USER postgres WITH PASSWORD 'postgres#2024';"
psql -U postgres -c "create user replicator replication login encrypted password 'replicator#2024';"


9. 创建备库（只需要在备库执行）
su - postgres
pg_basebackup -D $PGDATA -Fp -Xs -v -P -h 192.168.56.11 -p 5432 -U replicator
cat >> $PGDATA/standby.signal <<EOF
standby_mode = 'on'
EOF


cat >> $PGDATA/postgresql.auto.conf <<EOF
primary_conninfo='application_name=pg01 host=192.168.56.11 port=5432 user=replicator password=replicator'            
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on
recovery_target_timeline = 'latest'
EOF
pg_ctl start


10. 检查主备状态
主库查看同步状态
select * from pg_stat_replication;
备库查看同步状态
select * from pg_stat_wal_receiver;

2. Patroni安装部署

# 如无特殊说明，下列命令所有节点都执行
1. 依赖安装，注意python版本要求2.7以上
yum install -y haproxy postgresql-devel* watchdog libnfnetlink python-devel libevent-devel libdbi* openssl-devel bzip2-devel *bsddb* sqlite-devel gdbm-devel


2. pip安装
wget https://mirrors.aliyun.com/pypi/packages/ce/ea/9b445176a65ae4ba22dce1d93e4b5fe182f953df71a145f557cffaffc1bf/pip-19.3.1.tar.gz
tar -xf pip-19.3.1.tar.gz
cd pip-19.3.1
python  setup.py install


3. setuptools安装
wget https://mirrors.aliyun.com/pypi/packages/54/28/c45d8b54c1339f9644b87663945e54a8503cfef59cf0f65b3ff5dd17cf64/setuptools-44.0.2-py2.py3-none-any.whl
pip install setuptools-44.0.2-py2.py3-none-any.whl


4. 下载所有patroni用到的pip包
wget http://mirrors.163.com/pypi/packages/97/2a/b854019bcb9b925cd10ff245dbc9448a82fe7fdb40127e5cf1733ad0765c/psycopg2_binary-2.8.4-cp27-cp27mu-manylinux1_x86_64.whl
wget https://mirrors.163.com/pypi/packages/b4/40/a9837291310ee1ccc242ceb6ebfd9eb21539649f193a7c8c86ba15b98539/urllib3-1.25.7-py2.py3-none-any.whl
wget http://mirrors.163.com/pypi/packages/65/26/32b8464df2a97e6dd1b656ed26b2c194606c16fe163c695a992b36c11cdf/six-1.13.0-py2.py3-none-any.whl
wget http://mirrors.163.com/pypi/packages/fa/37/45185cb5abbc30d7257104c434fe0b07e5a195a6847506c074527aa599ec/Click-7.0-py2.py3-none-any.whl
wget http://mirrors.163.com/pypi/packages/ef/99/53bd1ac9349262f59c1c421d8fcc2559ae8a5eeffed9202684756b648d33/tzlocal-2.0.0-py2.py3-none-any.whl
wget http://mirrors.163.com/pypi/packages/d4/70/d60450c3dd48ef87586924207ae8907090de0b306af2bce5d134d78615cb/python_dateutil-2.8.1-py2.py3-none-any.whl
wget http://mirrors.163.com/pypi/packages/3f/d0/59bc5f1c6c4d4b498c41d8ce7052ee9e9d68be19e16038a55252018a6c4d/python_consul-1.1.0-py2.py3-none-any.whl
wget http://mirrors.163.com/pypi/packages/e7/f9/f0b53f88060247251bf481fa6ea62cd0d25bf1b11a87888e53ce5b7c8ad2/pytz-2019.3-py2.py3-none-any.whl
wget http://mirrors.163.com/pypi/packages/ec/d3/3aa0e7213ef72b8585747aa0e271a9523e713813b9a20177ebe1e939deb0/dnspython-1.16.0-py2.py3-none-any.whl
pip install psycopg2_binary-2.8.4-cp27-cp27mu-manylinux1_x86_64.whl
pip install urllib3-1.25.7-py2.py3-none-any.whl
pip install six-1.13.0-py2.py3-none-any.whl
pip install Click-7.0-py2.py3-none-any.whl
pip install pytz-2019.3-py2.py3-none-any.whl
pip install tzlocal-2.0.0-py2.py3-none-any.whl
pip install python_dateutil-2.8.1-py2.py3-none-any.whl
pip install dnspython-1.16.0-py2.py3-none-any.whl
pip install urllib3-1.26.6-py2.py3-none-any.whl 
pip install idna-2.10-py2.py3-none-any.whl 
pip install chardet-4.0.0-py2.py3-none-any.whl 
pip install certifi-2021.5.30-py2.py3-none-any.whl 
pip install requests-2.26.0-py2.py3-none-any.whl 
pip install python_consul-1.1.0-py2.py3-none-any.whl
pip install backports.functools_lru_cache-1.6.4-py2.py3-none-any.whl 
pip install wcwidth-0.2.5-py2.py3-none-any.whl 
pip install prettytable-1.0.1-py2.py3-none-any.whl


wget http://mirrors.163.com/pypi/packages/32/80/8c0fbf433eb9e09eb025bb4f942881cfcf2e343db0750fd50873b4722b43/patroni-1.6.1.tar.gz
wget http://mirrors.163.com/pypi/packages/73/93/4f8213fbe66fc20cb904f35e6e04e20b47b85bee39845cc66a0bcf5ccdcb/psutil-5.6.7.tar.gz
wget http://mirrors.163.com/pypi/packages/69/6c/301876940e760a8b46c1caacf08c298f511f517c70eec32e43f38e9cc6f5/cdiff-1.0.tar.gz
wget http://mirrors.163.com/pypi/packages/a1/da/616a4d073642da5dd432e5289b7c1cb0963cc5dde23d1ecb8d726821ab41/python-etcd-0.4.5.tar.gz
pip install psutil-5.6.7.tar.gz
pip install cdiff-1.0.tar.gz
pip install python-etcd-0.4.5.tar.gz
pip install patroni-1.6.1.tar.gz


5. 检查python配置文件
如果使用python2安装PG13以上版本检查下面文件是否含有wal_keep_segments 参数，如果有需要注释掉。
vi /lib/python2.7/site-packages/patroni/postgresql/config.py

‍

3. Patroni配置

# 编辑patroni配置文件pg.yml，写入etcd、PostgreSQL相关信息。
# 3个数据库节点的patroni配置文件略有不同，需要注意修改。
1. 主节点配置如下：
cat > /pg/patroni/pg.yml <<EOF
scope: PGCluster
namespace: /pgsql/
name: db1
restapi:
  listen: 192.168.56.11:8008
  connect_address: 192.168.56.11:8008
etcd:
  hosts: 192.168.56.11:2379,192.168.56.22:2379,192.168.56.33:2379
bootstrap:
  # this section will be written into Etcd:/<namespace>/<scope>/config after initializing new cluster
  # and all other cluster members will use it as a global configuration
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    master_start_timeout: 300
    synchronous_mode: true
    synchronous_standby_names: "ANY 1 (pg01,pg02,pg03)"
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        listen_addresses: "*"
        port: 5432
        wal_level: hot_standby
        hot_standby: "on"
        max_connections: 2000
        wal_keep_size: 16GB
        max_wal_senders: 50
        max_replication_slots: 10
        wal_log_hints: "on"
        archive_mode: "on"
        archive_timeout: 1800s
        archive_command: scp %p 192.168.56.11:/data/pgwal/archive_wals/%f
      recovery_conf:
        restore_command: scp 192.168.56.11:/data/pgwal/archive_wals/%f %p
postgresql:
  callbacks:
    on_start: /pg/patroni/patroni_callback.sh
    on_stop: /pg/patroni/patroni_callback.sh
    on_role_change: /pg/patroni/patroni_callback.sh
  listen: 0.0.0.0:5432
  connect_address: 192.168.56.11:5432
  data_dir: /data/pgdata/pg13
  bin_dir: /pg/pghome/bin
#  config_dir: /etc/postgresql/9.6/main
  authentication:
    replication:
      username: replicator
      password: Postgres%clover
    superuser:
      username: postgres
      password: Postgres%clover
watchdog:
  mode: automatic # Allowed values: off, automatic, required
  device: /dev/watchdog
  safety_margin: 5
tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false


2. 从节点1配置如下：
cat > /pg/patroni/pg.yml <<EOF
scope: PGCluster
namespace: /pgsql/
name: db2
restapi:
  listen: 192.168.56.22:8008
  connect_address: 192.168.56.22:8008
etcd:
  hosts: 192.168.56.11:2379,192.168.56.22:2379,192.168.56.33:2379
bootstrap:
  # this section will be written into Etcd:/<namespace>/<scope>/config after initializing new cluster
  # and all other cluster members will use it as a global configuration
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    master_start_timeout: 300
    synchronous_mode: true
    synchronous_standby_names: "ANY 1 (pg01,pg02,pg03)"
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        listen_addresses: "*"
        port: 5432
        wal_level: hot_standby
        hot_standby: "on"
        max_connections: 2000
        wal_keep_size: 16GB
        max_wal_senders: 50
        max_replication_slots: 10
        wal_log_hints: "on"
        archive_mode: "on"
        archive_timeout: 1800s
        archive_command: scp %p 192.168.56.22:/data/pgwal/archive_wals/%f
      recovery_conf:
        restore_command: scp 192.168.56.22:/data/pgwal/archive_wals/%f %p
postgresql:
  callbacks:
    on_start: /bin/sh /pg/patroni/patroni_callback.sh
    on_stop: /bin/sh /pg/patroni/patroni_callback.sh
    on_role_change: /bin/sh /pg/patroni/patroni_callback.sh
  listen: 0.0.0.0:5432
  connect_address: 192.168.56.22:5432
  data_dir: /data/pgdata/pg13
  bin_dir: /pg/pghome/bin
#  config_dir: /etc/postgresql/9.6/main
  authentication:
    replication:
      username: replicator
      password: Postgres%clover
    superuser:
      username: postgres
      password: Postgres%clover
watchdog:
  mode: automatic # Allowed values: off, automatic, required
  device: /dev/watchdog
  safety_margin: 5
tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false
EOF


3. 从节点2配置如下：
cat > /pg/patroni/pg.yml <<EOF
scope: PGCluster
namespace: /pgsql/
name: db3
restapi:
  listen: 192.168.56.33:8008
  connect_address: 192.168.56.33:8008
etcd:
  hosts: 192.168.56.11:2379,192.168.56.22:2379,192.168.56.33:2379
bootstrap:
  # this section will be written into Etcd:/<namespace>/<scope>/config after initializing new cluster
  # and all other cluster members will use it as a global configuration
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    master_start_timeout: 300
    synchronous_mode: true
    synchronous_standby_names: "ANY 1 (pg01,pg02,pg03)"
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        listen_addresses: "*"
        port: 5432
        wal_level: hot_standby
        hot_standby: "on"
        max_connections: 2000
        wal_keep_size: 16GB
        max_wal_senders: 50
        max_replication_slots: 10
        wal_log_hints: "on"
        archive_mode: "on"
        archive_timeout: 1800s
        archive_command: scp %p 192.168.56.33:/data/pgwal/archive_wals/%f
      recovery_conf:
        restore_command: scp 192.168.56.33:/data/pgwal/archive_wals/%f %p
postgresql:
  callbacks:
    on_start: /pg/patroni/patroni_callback.sh
    on_stop: /pg/patroni/patroni_callback.sh
    on_role_change: /pg/patroni/patroni_callback.sh
  listen: 0.0.0.0:5432
  connect_address: 192.168.56.33:5432
  data_dir: /data/pgdata/pg13
  bin_dir: /pg/pghome/bin
#  config_dir: /etc/postgresql/9.6/main
  authentication:
    replication:
      username: replicator
      password: Postgres%clover
    superuser:
      username: postgres
      password: Postgres%clover
watchdog:
  mode: automatic # Allowed values: off, automatic, required
  device: /dev/watchdog
  safety_margin: 5
tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false
EOF

4. vip配置

PostgreSQL高可用环境下，主节点可以是3个数据库节点中的任意一个。
因此，如果应用通过物理IP访问主库，每当主备切换时，都需要人工调整应用访问数据库的连接串，这显然很不友好。
因此，需要引入VIP，即主库虚拟IP，以此访问主库。


1. 添加postgres用户为sudoer，以root用户执行：
su - root
visudo 
postgres ALL=(ALL) NOPASSWD:ALL


2. 配置脚本
cat > /pg/patroni/patroni_callback.sh <<EOF
#!/bin/bash
readonly cb_name=$1
readonly role=$2
readonly scope=$3
VIP=192.168.56.66
VIPBRD=192.168.56.255
VIPNETMASK=255.255.255.0
VIPNETMASKBIT=22
#VIPifconfig 
VIPDEV=em1
VIPLABEL=1
function usage() {
    echo "Usage: $0 <on_start|on_stop|on_role_change> <role> <scope>";
    exit 1;
}
function addvip(){
    echo "`date +%Y-%m-%d\ %H:%M:%S,%3N` INFO: /sbin/ip addr add ${VIP}/${VIPNETMASKBIT} brd ${VIPBRD} dev ${VIPDEV} label ${VIPDEV}:${VIPLABEL}"
    sudo /sbin/ip addr add ${VIP}/${VIPNETMASKBIT} brd ${VIPBRD} dev ${VIPDEV} label ${VIPDEV}:${VIPLABEL}
    sudo /usr/sbin/arping -q -A -c 1 -I ${VIPDEV}:${VIPLABEL} ${VIP}
    #sudo /sbin/iptables -F
}
function delvip(){
    echo "`date +%Y-%m-%d\ %H:%M:%S,%3N` INFO: sudo /sbin/ip addr del ${VIP}/${VIPNETMASKBIT} dev ${VIPDEV} label ${VIPDEV}:${VIPLABEL}"
    sudo /sbin/ip addr del ${VIP}/${VIPNETMASKBIT} dev ${VIPDEV} label ${VIPDEV}:${VIPLABEL}
    sudo /usr/sbin/arping -q -A -c 1 -I ${VIPDEV} ${VIP}
    #sudo /sbin/iptables -F
    
}
echo "`date +%Y-%m-%d\ %H:%M:%S,%3N` WARNING: patroni callback $cb_name $role $scope"
case $cb_name in
    on_stop)
        delvip
        ;;
    on_start)
        ;;
    on_role_change)
        if [[ $role == 'master' ]]; then
            addvip
        elif [[ $role == 'slave' ]]||[[ $role == 'replica' ]]||[[ $role == 'logical' ]]; then
            delvip
        fi
        ;; 
    *)
        usage
        ;;
esac
EOF


chmod +x /pg/patroni/patroni_callback.sh

5. 启动Patroni

# 启动前提：
# PostgreSQL流复制正常，且etcd也正常运行的情况下
1. 手工启动3个数据库节点的patroni程序
su – postgres
nohup patroni /pg/patroni/pg.yml & >> /pg/patroni/patroni.log 2>&1 &


2. 配置开机启动
su – postgres 
crontab -e
@reboot patroni /pg/patroni/pg.yml & >> /pg/patroni/patroni.log 2>&1

Patroni高可用测试

1. 查看集群状态
su - postgres
patronictl -c /pg/patroni/pg.yml list 


2. 主备切换
patronictl -c /pg/patroni/pg.yml switchover


3. 故障切换
模拟节点故障，关掉主节点，同步备库将自动升级为主库；
原主节点恢复后，将自动降级为备库。重启其他节点同理。
reboot
patronictl -c /pg/patroni/pg.yml list

本文内容就到这啦，阅读完本篇，相信你对PostgreSQL高可用架构patroni相关知识有了一定的认识了吧！我们下篇再见！

点击上方公众号，关注我吧！

postgresql 数据库集群高可用 etcd

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

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

评论