PostgreSQL-Patroni高可用搭建部署

最新推荐文章于 2025-06-26 16:48:00 发布

侯晓阳丶

最新推荐文章于 2025-06-26 16:48:00 发布

阅读量2k

点赞数 10

CC 4.0 BY-SA版权

分类专栏： PostgreSQL 文章标签： postgresql 数据库 linux dba 可用性测试服务器

本文链接：https://blog.csdn.net/h_3369/article/details/141397371

PostgreSQL 专栏收录该内容

8 篇文章

订阅专栏

前言

Patroni高可用，算是PGSQL所有高可用插件中，比较牛掰的了，功能强大，支持自动拉起postgresql实例，将主库变为从库，支持经过watchdog防止脑裂，Patroni自身没有一致性协议，需要通过DCS(分布式配置存储)，如etcd、consul、zookeeper等，来提供一致性模型，Patroni再通过api连接etcd集群。向其插入键值对记录Patroni参数、数据库参数、主备信息以及连接信息，平常通过etcd对其它节点做心跳检测，通过从etcd获取数据，存储主备信息来判断各节点的状态对集群进行自动管理。

环境介绍

角色	IP	服务
Leader	172.22.138.220	PostgreSQL v14；Patroni
standby1	172.22.138.219	PostgreSQL v14；Patroni
standby2	172.22.138.218	PostgreSQL v14； Patroni

环境准备

1.三节点时间同步

root # yum install -y ntpdate
root # ntpdate ntp.aliyun.com

2.安装相关依赖包

root # yum install -y gcc python-devel epel-release
root # yum install -y gcc epel-release
root # yum install -y python-pip python-psycopg2 python-devel

安装PostgreSQL流复制

postgresql流复制的部署，这里不再赘述，需要搭建流复制的可以看我这篇文章：https://blog.csdn.net/h_3369/article/details/141358629?spm=1001.2014.3001.5501

安装etcd集群

1.使用yum命令安装etcd

root # yum install -y etcd

2.编写etcd配置文件

root # vim /etc/etcd/etcd.conf 
#leader
ETCD_NAME="node1"
ETCD_DATA_DIR="/data/etcd/data"
ETCD_LISTEN_CLIENT_URLS="http://127.0.0.1:2379,http://172.22.138.220:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://127.0.0.1:2379,http://172.22.138.220:2379"
ETCD_LISTEN_PEER_URLS="http://172.22.138.220:2380"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://172.22.138.220:2380"
ETCD_INITIAL_CLUSTER="node1=http://172.22.138.220:2380,node2=http://172.22.138.219:2380,node3=http://172.22.138.218:2380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_CLUSTER_STATE="new"

#standby1
ETCD_NAME="node2"
ETCD_DATA_DIR="/data/etcd/data"
ETCD_LISTEN_CLIENT_URLS="http://127.0.0.1:2379,http://172.22.138.219:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://127.0.0.1:2379,http://172.22.138.219:2379"
ETCD_LISTEN_PEER_URLS="http://172.22.138.219:2380"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://172.22.138.219:2380"
ETCD_INITIAL_CLUSTER="node1=http://172.22.138.220:2380,node2=http://172.22.138.219:2380,node3=http://172.22.138.218:2380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_CLUSTER_STATE="new"

#standby2
ETCD_NAME="node3"
ETCD_DATA_DIR="/data/etcd/data"
ETCD_LISTEN_CLIENT_URLS="http://127.0.0.1:2379,http://172.22.138.218:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://127.0.0.1:2379,http://172.22.138.218:2379"
ETCD_LISTEN_PEER_URLS="http://172.22.138.218:2380"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://172.22.138.218:2380"
ETCD_INITIAL_CLUSTER="node1=http://172.22.138.220:2380,node2=http://172.22.138.219:2380,node3=http://172.22.138.218:2380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_CLUSTER_STATE="new"

3.启动etcd集群

root # systemctl start etcd

安装Patroni

1.使用python的pip安装patroni和相关模块

root # pip3 install --upgrade pip
root # pip3 install --upgrade setuptools
root # pip3 install psycopg2-binary
root # pip3 install patroni[etcd]

#重点关注psycopg2-binary包三个节点版本号是否一致，不一致Patroni启动会报错
root #  pip3 list|grep psycopg2

2.编辑Patroni的配置文件

root # vim /etc/patroni.yml
# Leader节点
scope: pgsql
namespace: /service/
name: pa-pg-1
restapi:
  listen: 0.0.0.0:8008
  connect_address: 172.22.138.220:8008
etcd:
  host: 172.22.138.220:2379
bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10                                               # 循环更新领导者密钥过程中的休眠时间
    retry_timeout: 10                                   # etcd和PostgreSQL操作重试的超时时间（以秒为单位）
    maximum_lag_on_failover: 1048576    # 如果Master和Replicate之间的字节数延迟大于此值，那么Replicate将不参与新的领导者选举
    master_start_timeout: 300
    synchronous_mode: false                             # 是否打开同步复制模式
    postgresql:                                                 # PostgreSQL的配置，是否使用pg_rewind，是否使用复制插槽，还有PostgreSQL参数等信息
      use_pg_rewind: true
      use_slots: true
      parameters:
        listen_addresses: "0.0.0.0"
        port: 5432
        wal_level: replica
        hot_standby: "on"
        wal_keep_segments: 100
        max_wal_senders: 10
        max_replication_slots: 10
        wal_log_hints: "on"
postgresql:
  listen: 0.0.0.0:5432                                  # 设置postgresql.conf参数listen_addresses和port
  connect_address: 172.22.138.220:5432   # 从其他节点和应用程序访问Postgres的地址和端口
  data_dir: /data/pgsql/data                                 # 集群的数据目录的存放路径
  bin_dir: /data/pgsql/base/bin                    # PostgreSQL二进制文件存放路径
  authentication:                                               # 定义用于复制的用户，超级用户
    replication:
      username: replica
      password: "replica"
    superuser:
      username: postgres
      password: "123456"
  basebackup:
    max-rate: 100M
    checkpoint: fast
tags:
    nofailover: false                                   # 不参与选主
    noloadbalance: false                                # 不参与负载均衡
    clonefrom: false
    nosync: false                                               # 也不作为同步备库

# Standby1节点
scope: pgsql
namespace: /service/
name: pa-pg-1
restapi:
  listen: 0.0.0.0:8008
  connect_address: 172.22.138.219:8008
etcd:
  host: 172.22.138.219:2379
bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10                                               # 循环更新领导者密钥过程中的休眠时间
    retry_timeout: 10                                   # etcd和PostgreSQL操作重试的超时时间（以秒为单位）
    maximum_lag_on_failover: 1048576    # 如果Master和Replicate之间的字节数延迟大于此值，那么Replicate将不参与新的领导者选举
    master_start_timeout: 300
    synchronous_mode: false                             # 是否打开同步复制模式
    postgresql:                                                 # PostgreSQL的配置，是否使用pg_rewind，是否使用复制插槽，还有PostgreSQL参数等信息
      use_pg_rewind: true
      use_slots: true
      parameters:
        listen_addresses: "0.0.0.0"
        port: 5432
        wal_level: replica
        hot_standby: "on"
        wal_keep_segments: 100
        max_wal_senders: 10
        max_replication_slots: 10
        wal_log_hints: "on"
postgresql:
  listen: 0.0.0.0:5432                                  # 设置postgresql.conf参数listen_addresses和port
  connect_address: 172.22.138.219:5432   # 从其他节点和应用程序访问Postgres的地址和端口
  data_dir: /data/pgsql/data                                 # 集群的数据目录的存放路径
  bin_dir: /data/pgsql/base/bin                    # PostgreSQL二进制文件存放路径
  authentication:                                               # 定义用于复制的用户，超级用户
    replication:
      username: replica
      password: "replica"
    superuser:
      username: postgres
      password: "123456"
  basebackup:
    max-rate: 100M
    checkpoint: fast
tags:
    nofailover: false                                   # 不参与选主
    noloadbalance: false                                # 不参与负载均衡
    clonefrom: false
    nosync: false                                               # 也不作为同步备库

# Standby2节点
scope: pgsql
namespace: /service/
name: pa-pg-1
restapi:
  listen: 0.0.0.0:8008
  connect_address: 172.22.138.218:8008
etcd:
  host: 172.22.138.218:2379
bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10                                               # 循环更新领导者密钥过程中的休眠时间
    retry_timeout: 10                                   # etcd和PostgreSQL操作重试的超时时间（以秒为单位）
    maximum_lag_on_failover: 1048576    # 如果Master和Replicate之间的字节数延迟大于此值，那么Replicate将不参与新的领导者选举
    master_start_timeout: 300
    synchronous_mode: false                             # 是否打开同步复制模式
    postgresql:                                                 # PostgreSQL的配置，是否使用pg_rewind，是否使用复制插槽，还有PostgreSQL参数等信息
      use_pg_rewind: true
      use_slots: true
      parameters:
        listen_addresses: "0.0.0.0"
        port: 5432
        wal_level: replica
        hot_standby: "on"
        wal_keep_segments: 100
        max_wal_senders: 10
        max_replication_slots: 10
        wal_log_hints: "on"
postgresql:
  listen: 0.0.0.0:5432                                  # 设置postgresql.conf参数listen_addresses和port
  connect_address: 172.22.138.218:5432   # 从其他节点和应用程序访问Postgres的地址和端口
  data_dir: /data/pgsql/data                                 # 集群的数据目录的存放路径
  bin_dir: /data/pgsql/base/bin                    # PostgreSQL二进制文件存放路径
  authentication:                                               # 定义用于复制的用户，超级用户
    replication:
      username: replica
      password: "replica"
    superuser:
      username: postgres
      password: "123456"
  basebackup:
    max-rate: 100M
    checkpoint: fast
tags:
    nofailover: false                                   # 不参与选主
    noloadbalance: false                                # 不参与负载均衡
    clonefrom: false
    nosync: false                                               # 也不作为同步备库

3.配置Patroni的service启动文件

root # vim /etc/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 /etc/patroni.yml
ExecReload=/bin/kill -HUP $MAINPID
LimitNOFILE=65536
KillMode=process
KillSignal=SIGINT
Restart=on-abnormal
RestartSec=30s
TimeoutSec=0
[Install]
WantedBy=multi-user.target

4.授权Patroni的配置文件并启动Patroni，启动顺序必须是：主库>从库1==从库2！

#postgres用户没有sudo权限的话记得配置sudo
root # echo 'postgres        ALL=(ALL)       NOPASSWD: ALL'> /etc/sudoers.d/postgres
root # chown postgres./etc/patroni.yml
root # systemctl start patroni

Patroni日常检查

1.使用Patroni命令查看集群状态

root # patronictl -c /etc/patroni.yml list

2.Patroni日志默认输出到 /var/log/messages

root # less /var/log/messages
Aug 20 12:23:14 DB-test2 systemd: Started patroni - a high-availability PostgreSQL.
Aug 20 12:23:14 DB-test2 systemd-logind: New session 886 of user root.
Aug 20 12:23:14 DB-test2 systemd: Started Session 886 of user root.
Aug 20 12:23:14 DB-test2 systemd-logind: Removed session 886.
Aug 20 12:23:15 DB-test2 patroni: postgresql parameter listen_addresses=0.0.0.0 failed validation, defaulting to None
Aug 20 12:23:15 DB-test2 patroni: postgresql parameter port=5432 failed validation, defaulting to None
Aug 20 12:23:15 DB-test2 patroni: 2024-08-15 12:23:15,225 INFO: No PostgreSQL configuration items changed, nothing to reload.
Aug 20 12:23:15 DB-test2 patroni: localhost:5432 - accepting connections
Aug 15 12:23:15 DB-test2 patroni: 2024-08-15 12:23:15,233 INFO: establishing a new patroni heartbeat connection to postgres
Aug 20 12:23:15 DB-test2 patroni: 2024-08-15 12:23:15,254 INFO: no action. I am (pa-pg-2), a secondary, and following a leader (pa-pg-1)
Aug 20 12:23:16 DB-test2 systemd-logind: New session 887 of user root.
Aug 20 12:23:16 DB-test2 systemd: Started Session 887 of user root.
Aug 20 12:23:16 DB-test2 systemd-logind: Removed session 887.
Aug 20 12:23:16 DB-test2 patroni: 2024-08-15 12:23:16,423 INFO: no action. I am (pa-pg-2), a secondary, and following a leader (pa-pg-1)