postgresql+patroni+etcd高可用安装

简介

Patroni 是一个用于管理 PostgreSQL 高可用性的开源工具。它基于 etcd、ZooKeeper 或 Consul 作为分布式一致性存储，以提供自动故障转移和集群管理功能。

Patroni 的核心功能

自动故障转移：当主节点失效时，Patroni 会自动选择一个新的主节点，并重新配置集群。

健康检查：持续监测 PostgreSQL 节点的健康状态，并在必要时执行恢复操作。

Leader 选举：通过分布式一致性存储（如 etcd、Consul、ZooKeeper）进行主节点选举。

简化的配置管理：提供灵活的 YAML 配置文件，支持热更新，无需重启服务。

负载均衡支持：与 HAProxy 或其他负载均衡器结合，提供读写分离能力。

API 控制：提供 RESTful API 供外部管理和监控集群状态。

Patroni 工作原理

集群初始化：

启动多个 PostgreSQL 节点，每个节点运行 Patroni 进程。Patroni 进程与 etcd/Consul/ZooKeeper 交互，以存储和检索集群状态信息。

主节点选举：

Patroni 通过分布式一致性存储（DCS）决定主节点。只有成功获得租约的节点才能成为 leader（主节点）。

健康检查：

Patroni 定期检查 PostgreSQL 实例的运行状态。如果主节点失效，会触发自动故障转移，将一个从节点提升为新主节点。

复制管理：

其他 PostgreSQL 节点作为从节点（standby），同步主节点数据。当 Patroni 发现主节点故障时，它会选出最新的从节点作为新的主节点，并重新配置集群。

外部交互：

通过 REST API 与 HAProxy、应用程序或其他管理工具交互，提供集群信息和控制操作。

集群规划

安装

基础操作所有节点关闭防火墙、postgres用户组添加、selinux临时关闭、hostname配置、IP映射文件hosts配置本文不再赘述。

IP映射

cat > /etc/hosts << EOF
10.0.0.131 vm131
10.0.0.132 vm132
10.0.0.133 vm133
EOF

时钟同步

生产使用时钟工具，这里只做同步服务器时区处理
sudo timedatectl set-timezone Asia/Shanghai

etcd安装

yum -y install etcd yamllint

vm131执行以下

cat > /etc/etcd/etcd.conf << EOF
ETCD_DATA_DIR="/var/lib/etcd/etcd01.etcd"
ETCD_LISTEN_PEER_URLS="http://10.0.0.131:2380"
ETCD_LISTEN_CLIENT_URLS="http://10.0.0.131:2379,http://127.0.0.1:2379"
ETCD_NAME="etcd01"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.0.0.131:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://10.0.0.131:2379"
ETCD_INITIAL_CLUSTER="etcd01=http://10.0.0.131:2380,etcd02=http://10.0.0.132:2380,etcd03=http://10.0.0.133:2380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_CLUSTER_STATE="new"
EOF

vm132执行以下

cat > /etc/etcd/etcd.conf << EOF
ETCD_DATA_DIR="/var/lib/etcd/etcd02.etcd"
ETCD_LISTEN_PEER_URLS="http://10.0.0.132:2380"
ETCD_LISTEN_CLIENT_URLS="http://10.0.0.132:2379,http://127.0.0.1:2379"
ETCD_NAME="etcd02"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.0.0.132:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://10.0.0.132:2379"
ETCD_INITIAL_CLUSTER="etcd01=http://10.0.0.131:2380,etcd02=http://10.0.0.132:2380,etcd03=http://10.0.0.133:2380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_CLUSTER_STATE="new"
EOF

vm133执行以下

cat > /etc/etcd/etcd.conf << EOF
ETCD_DATA_DIR="/var/lib/etcd/etcd03.etcd"
ETCD_LISTEN_PEER_URLS="http://10.0.0.133:2380"
ETCD_LISTEN_CLIENT_URLS="http://10.0.0.133:2379,http://127.0.0.1:2379"
ETCD_NAME="etcd03"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.0.0.133:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://10.0.0.133:2379"
ETCD_INITIAL_CLUSTER="etcd01=http://10.0.0.131:2380,etcd02=http://10.0.0.132:2380,etcd03=http://10.0.0.133:2380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_CLUSTER_STATE="new"
EOF

参数解释：
 ETCD_DATA_DIR="/var/lib/etcd/etcd03.etcd"  ##指定 etcd 数据存储的目录
 ETCD_LISTEN_PEER_URLS="http://10.0.0.133:2380" ##指定 etcd 集群内部通信（Peer 通信） 的监听地址，即该节点用于与其他 etcd 节点交互的地址。
 ETCD_LISTEN_CLIENT_URLS="http://10.0.0.133:2379,http://127.0.0.1:2379" ##指定 etcd 对外提供服务的监听地址（即 Client 通信）。
 ETCD_NAME="etcd03" ##设置当前 etcd 节点的名称，需保证在集群中唯一。
 ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.0.0.133:2380" ##指定 当前 etcd 节点 向 其他 etcd 节点 广播的 Peer 通信地址。必须与 ETCD_LISTEN_PEER_URLS 保持一致。
 ETCD_ADVERTISE_CLIENT_URLS="http://10.0.0.133:2379" ##指定 当前 etcd 节点 向 外部客户端 广播的访问地址。
 ETCD_INITIAL_CLUSTER="etcd01=http://10.0.0.131:2380,etcd02=http://10.0.0.132:2380,etcd03=http://10.0.0.133:2380" ## 定义 etcd 集群的所有成员
 ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster" ##指定 etcd 集群的唯一标识符，用于防止多个 etcd 集群相互干扰。
 ETCD_INITIAL_CLUSTER_STATE="new" ##指定 etcd 集群的初始化状态："new"：表示正在创建新的 etcd 集群。"existing"：用于加入一个已经存在的 etcd 集群（避免错误地重新初始化）

yamlint 对etcd.conf的yaml的格式进行检查
yamllint /etc/etcd/etcd.conf
如果当前的repo没有此包，可以执行以下指令对其进行更新
bash <(curl -sSL https://linuxmirrors.cn/main.sh)

## 警告需要在开头增加--- 符号 非error提示可以忽略
### vm131 第九行第33列结尾有多余空格，需要删掉
  1:1       warning  missing document start "---"  (document-start)
  9:33      error    trailing spaces  (trailing-spaces)

启动etcd

systemctl start etcd
systemctl status etcd
## 检查以下etcd启动正常的情况开启自启动
systemctl enable etcd

检查etcd健康状态

etcdctl --endpoints=http://10.0.0.131:2379,http://10.0.0.132:2379,http://10.0.0.133:2379   cluster-health
etcdctl cluster-health
etcdctl member list     

postgresql 安装

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

##以下只需要在主节点初始化数据库即可

sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

sudo systemctl start postgresql-15
sudo systemctl status postgresql-15

最佳配置参数

echo -e "host    all             all             all          scram-sha-256\nhost    replication     all             all          scram-sha-256" | sudo tee -a /var/lib/pgsql/15/data/pg_hba.conf
 cat /var/lib/pgsql/15/data/pg_hba.conf |tail -n 5

echo -e "listen_addresses='*'\nwal_keep_size = 1024" | sudo tee -a /var/lib/pgsql/15/data/postgresql.conf
cat /var/lib/pgsql/15/data/postgresql.conf |tail -n 5


systemctl restart  postgresql-15.service 
systemctl status  postgresql-15.service 

创建流复制用户

su - postgres 
psql 
ALTER USER postgres WITH ENCRYPTED PASSWORD 'postgres';
CREATE USER repluser  REPLICATION PASSWORD 'repluser123';

patroni安装

回到root用户下所有节点执行以下步骤,确保网络正常python为3.6以上版本，指令依次执行。

yum install -y python3  
##以下3.6选择和python版本一致的主版本号
curl https://bootstrap.pypa.io/pip/3.6/get-pip.py -o get-pip.py
python3 get-pip.py
pip install psycopg2-binary -i  https://mirrors.aliyun.com/pypi/simple/
pip install patroni[etcd] -i https://mirrors.aliyun.com/pypi/simple/
patroni --version
pip list 

patroni配置

– vm131

cat > ~postgres/patroni.yaml << EOF
---
scope: pg_patroni
namespace: /patroni_metadata/
name: patroni1
restapi:
  listen: 10.0.0.131:8008
  connect_address: 10.0.0.131:8008
etcd:
  host: 10.0.0.131:2379
bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    initialize: false
    
    # 存入 DCS 的 initdb 参数
    initdb:
      - encoding: UTF8
      - data-checksums

    # 存入 DCS 的 PostgreSQL 配置
    postgresql:
      use_slots: true
      use_pg_rewind: true
      
      # 存入 DCS 的 pg_hba 配置
      pg_hba:
        - local all all trust
        - host all all all scram-sha-256
        - host replication all all scram-sha-256

      # 存入 DCS 的 postgresql 参数
      parameters:
        listen_addresses: "*"
        wal_level: logical
        hot_standby: on
        max_connections: 100
        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
postgresql:
  listen: '*'
  connect_address: 10.0.0.131:5432
  data_dir: /var/lib/pgsql/15/data
  bin_dir: /usr/pgsql-15/bin
  authentication:
    replication:
      username: repluser
      password: repluser123
    superuser:
      username: postgres
      password: postgres
  parameters:
    listen_addresses: '*'
    port: 5432
    max_connections: 200
    superuser_reserved_connections: 10
    shared_buffers: 512MB
    wal_level: logical
    hot_standby: on
    max_wal_senders: 10
    max_replication_slots: 10
    wal_log_hints: on
  replica:
    primary_slot_name: patroni1
tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false
callbacks:
  on_start: /var/lib/pgsql/vip_manager.sh
  on_stop: /var/lib/pgsql/vip_manager.sh
  on_role_change: /var/lib/pgsql/vip_manager.sh
EOF

– vm132

cat > ~postgres/patroni.yaml << EOF
---
scope: pg_patroni
namespace: /patroni_metadata/
name: patroni2
restapi:
  listen: 10.0.0.132:8008
  connect_address: 10.0.0.132:8008
etcd:
  host: 10.0.0.132:2379
bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    initialize: false
    # 存入 DCS 的 initdb 参数
    initdb:
      - encoding: UTF8
      - data-checksums
    # 存入 DCS 的 PostgreSQL 配置
    postgresql:
      use_slots: true
      use_pg_rewind: true
      # 存入 DCS 的 pg_hba 配置
      pg_hba:
        - local all all trust
        - host all all all scram-sha-256
        - host replication all all scram-sha-256
      # 存入 DCS 的 postgresql 参数
      parameters:
        listen_addresses: "*"
        wal_level: logical
        hot_standby: on
        max_connections: 100
        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
postgresql:
  listen: '*'
  connect_address: 10.0.0.132:5432
  data_dir: /var/lib/pgsql/15/data
  bin_dir: /usr/pgsql-15/bin
  authentication:
    replication:
      username: repluser
      password: repluser123
    superuser:
      username: postgres
      password: postgres
  parameters:
    listen_addresses: '*'
    port: 5432
    max_connections: 200
    superuser_reserved_connections: 10
    shared_buffers: 512MB
    wal_level: logical
    hot_standby: on
    max_wal_senders: 10
    max_replication_slots: 10
    wal_log_hints: on
  replica:
    primary_slot_name: patroni2
tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false
callbacks:
  on_start: /var/lib/pgsql/vip_manager.sh
  on_stop: /var/lib/pgsql/vip_manager.sh
  on_role_change: /var/lib/pgsql/vip_manager.sh
EOF

– vm133

cat > ~postgres/patroni.yaml << EOF
---
scope: pg_patroni
namespace: /patroni_metadata/
name: patroni3

restapi:
  listen: 10.0.0.133:8008
  connect_address: 10.0.0.133:8008

etcd:
  host: 10.0.0.133:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    initialize: false
    # 存入 DCS 的 initdb 参数
    initdb:
      - encoding: UTF8
      - data-checksums

    # 存入 DCS 的 PostgreSQL 配置
    postgresql:
      use_slots: true
      use_pg_rewind: true
      # 存入 DCS 的 pg_hba 配置
      pg_hba:
        - local all all trust
        - host all all all scram-sha-256
        - host replication all all scram-sha-256

      # 存入 DCS 的 postgresql 参数
      parameters:
        listen_addresses: "*"
        wal_level: logical
        hot_standby: on
        max_connections: 100
        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

postgresql:
  listen: '*'
  connect_address: 10.0.0.133:5432
  data_dir: /var/lib/pgsql/15/data
  bin_dir: /usr/pgsql-15/bin

  authentication:
    replication:
      username: repluser
      password: repluser123
    superuser:
      username: postgres
      password: postgres

  parameters:
    listen_addresses: '*'
    port: 5432
    max_connections: 200
    superuser_reserved_connections: 10
    shared_buffers: 512MB
    wal_level: logical
    hot_standby: on
    max_wal_senders: 10
    max_replication_slots: 10
    wal_log_hints: on
  replica:
    primary_slot_name: patroni3

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: true
EOF

yaml格式效验

yamllint  ~postgres/patroni.yaml 
## 调整为不再有error即可

守护基础配置

cat > /usr/lib/systemd/system/patroni.service << EOF
[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 /var/lib/pgsql/patroni.yaml
ExecReload=/bin/kill -HUP $MAINPID
LimitNOFILE=65536
KillMode=process
KillSignal=SIGINT
Restart=on-abnormal
RestartSec=30s
TimeoutSec=0

[Install]
WantedBy=multi-user.target
EOF

检查启动状态，先拉起主节点patroni，停留几秒，状态正常。再依次拉起其他节点

systemctl daemon-reload 
systemctl start patroni
systemctl enable patroni
systemctl status patroni
patronictl -c /var/lib/pgsql/patroni.yaml list

常见错误

出现以下错误，说明dcs已经对该节点存储过一次数据库的信息

journalctl -u patroni --no-pager -n 50

 CRITICAL: system ID mismatch, node patroni1 belongs to a different cluster: 7486515195984684289 != 7486517408449177422

使用以下指令清理DCS中的元数据

etcdctl rm --recursive /patroni_metadata/

etcdctl del --prefix /patroni_metadata/

patronictl -c /var/lib/pgsql/patroni.yaml remove pg_patroni

/patroni_metadata/ 是 ~postgres/patroni.yaml 文件中namespace:参数的值

创建callback脚本

cat > ~postgres/vip_manager.sh << EOF
#!/bin/bash

VIP=10.0.0.134
VIPBRD=10.0.0.255
VIPNETMASK=255.255.255.0
VIPNETMASKBIT=24
VIPDEV=ens33
VIPLABEL=1

PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/sbin:/usr/sbin

function usage() {
    echo "Usage: -bash <on_start|on_stop|on_role_change> <role> <scope>"
    exit 1
}

function addvip(){
    echo "$(date '+%Y-%m-%d %H:%M:%S') INFO: Adding VIP $VIP to $VIPDEV"
    sudo /sbin/ip addr add "$VIP/$VIPNETMASKBIT" brd "$VIPBRD" dev "$VIPDEV" label "$VIPDEV:$VIPLABEL" || echo "Failed to add VIP"
    sudo /usr/sbin/arping -q -A -c 1 -I "$VIPDEV" "$VIP" || echo "Failed to send ARP"
}

function delvip(){
    echo "$(date '+%Y-%m-%d %H:%M:%S') INFO: Deleting VIP $VIP from $VIPDEV"
    sudo /sbin/ip addr del "$VIP/$VIPNETMASKBIT" dev "$VIPDEV" label "$VIPDEV:$VIPLABEL" || echo "Failed to delete VIP"
    sudo /usr/sbin/arping -q -A -c 1 -I "$VIPDEV" "$VIP" || echo "Failed to send ARP"
}

echo "$(date '+%Y-%m-%d %H:%M:%S') WARNING: patroni callback"

case "$1" in
    on_stop)
        delvip
        ;;
    on_start)
        ;;
    on_role_change)
        if [[ "$2" == 'master' ]]; then
            addvip
        elif [[ "$2" == 'slave' ]] || [[ "$2" == 'replica' ]] || [[ "$2" == 'logical' ]]; then
            delvip
        fi
        ;; 
    *)
        usage
        ;;
esac
EOF
##对以上脚本授予执行权限
 
##授予 postgres  sudo权限
chmod +x ~postgres/vip_manager.sh 

给postgres用户授权ip 指令权限

echo "postgres ALL=(ALL) NOPASSWD: $(which ip), $(which arping)" | sudo tee -a /etc/sudoers > /dev/null
cat /etc/sudoers 

检查集群状态

patronictl -c patroni.yaml list 

patroni 主从切换

patronictl -c patroni.yaml switchover pg_patroni --leader patroni1 --candidate patroni2 --force