PostgreSQL高可用实战：Patroni集群故障转移与连接池配置

PostgreSQL高可用架构的核心是主从复制配合自动故障转移。Patroni作为Zalando开源的PostgreSQL高可用管理工具，基于etcd/ZooKeeper/Consul一致性存储实现Leader选举和自动切换，配合PgBouncer连接池减少连接开销。本文给出Patroni集群搭建、故障转移和读写分离的完整配置。

Patroni架构与etcd集群搭建

Patroni采用分布式一致性协议管理集群状态。每个PostgreSQL节点运行一个Patroni进程，通过etcd协调主从角色。架构组件：

┌─────────────────────────────────────────────┐
│                   客户端                     │
│           PgBouncer (端口6432)               │
│         /              \                     │
│    PG-Primary       PG-Replica               │
│   Patroni            Patroni                 │
│      │                  │                    │
│   etcd集群 (3节点)                            │
│   etcd-1  etcd-2  etcd-3                    │
└─────────────────────────────────────────────┘

搭建3节点etcd集群，Patroni依赖etcd存储集群元数据：

# 节点1: 192.168.1.10
# /etc/etcd/etcd.conf
name: 'etcd-1'
data-dir: '/var/lib/etcd'
listen-peer-urls: 'http://192.168.1.10:2380'
listen-client-urls: 'http://192.168.1.10:2379,http://127.0.0.1:2379'
initial-cluster-state: 'new'
initial-cluster-token: 'pg-ha-cluster'
initial-cluster: 'etcd-1=http://192.168.1.10:2380,etcd-2=http://192.168.1.11:2380,etcd-3=http://192.168.1.12:2380'
initial-advertise-peer-urls: 'http://192.168.1.10:2380'
advertise-client-urls: 'http://192.168.1.10:2379'

# 启动etcd（三个节点都执行）
systemctl start etcd
systemctl enable etcd

# 验证集群健康
etcdctl --endpoints=http://192.168.1.10:2379 \
  --endpoints=http://192.168.1.11:2379 \
  --endpoints=http://192.168.1.12:2379 \
  endpoint health

Patroni主从复制与自动故障转移配置

每个PostgreSQL节点安装Patroni，配置文件定义集群参数、复制信息和故障转移策略。

# /etc/patroni/patroni.yml （节点1示例）
scope: pg-cluster
namespace: /pg-ha/
name: pg-node-1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 192.168.1.10:8008

etcd:
  hosts: 192.168.1.10:2379,192.168.1.11:2379,192.168.1.12:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 20
    maximum_lag_on_failover: 1048576
    synchronous_mode: true
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_level: replica
        hot_standby: "on"
        max_wal_senders: 10
        max_replication_slots: 10
        wal_keep_size: 1024
        archive_mode: "on"
        archive_command: 'aws s3 cp %p s3://pg-wal-archive/%f'

  initdb:
    - encoding: UTF8
    - data-checksums
    - locale: C.UTF-8

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.10:5432
  data_dir: /var/lib/postgresql/data
  bin_dir: /usr/lib/postgresql/15/bin

  authentication:
    replication:
      username: replicator
      password: '<replication-password>'
    superuser:
      username: postgres
      password: '<superuser-password>'

  pg_hba:
    - host replication replicator 192.168.1.0/24 md5
    - host all all 0.0.0.0/0 md5

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false

三个节点配置基本相同，区别在name、connect_address和data_dir。启动Patroni：

# 节点1执行（初始化集群，自动成为Leader）
systemctl start patroni

# 查看集群状态
patronictl -c /etc/patroni/patroni.yml list
# +---------+--------------+---------+----------+
# | Member  | Host         | Role    | State    |
# | pg-node-1| 192.168.1.10| Leader  | running  |
# +---------+--------------+---------+----------+

# 节点2、3执行（自动加入集群，成为Replica）
systemctl start patroni

patronictl -c /etc/patroni/patroni.yml list
# | pg-node-1| 192.168.1.10| Leader  | running   |
# | pg-node-2| 192.168.1.11| Replica | streaming |
# | pg-node-3| 192.168.1.12| Replica | streaming |

故障转移验证——手动模拟主节点宕机：

# 停止节点1
systemctl stop patroni

# 等待30秒（TTL租约过期），观察集群状态
patronictl -c /etc/patroni/patroni.yml list
# 节点2自动提升为Leader：
# | pg-node-2| 192.168.1.11| Leader  | running   |
# | pg-node-3| 192.168.1.12| Replica | streaming |

# 恢复节点1（pg_rewind自动追赶新主）
systemctl start patroni
# | pg-node-1| 192.168.1.10| Replica | streaming |

PgBouncer连接池部署与读写分离

PostgreSQL的每个连接对应一个后端进程，连接数过多会导致内存膨胀和上下文切换开销。PgBouncer作为轻量级连接池，在客户端和PostgreSQL之间复用连接：

# /etc/pgbouncer/pgbouncer.ini
[databases]
postgres = host=192.168.1.10 port=5432 dbname=postgres
postgres_ro = host=192.168.1.11 port=5432 dbname=postgres

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600
query_wait_timeout = 30

结合HAProxy实现读写分离和Leader自动发现。HAProxy通过Patroni REST API的/master和/replica端点动态路由请求：

# /etc/haproxy/haproxy.cfg
frontend pg_write_frontend
    bind *:5432
    default_backend pg_write_backend

frontend pg_read_frontend
    bind *:5433
    default_backend pg_read_backend

# 写请求：只路由到Leader节点
backend pg_write_backend
    option httpchk
    http-check expect status 200
    default-server inter 3s fall 3 rise 2
    server pg-1 192.168.1.10:6432 check port 8008
    server pg-2 192.168.1.11:6432 check port 8008
    server pg-3 192.168.1.12:6432 check port 8008

# 读请求：路由到所有节点
backend pg_read_backend
    balance roundrobin
    option httpchk GET /replica
    http-check expect status 200
    server pg-1 192.168.1.10:6432 check port 8008
    server pg-2 192.168.1.11:6432 check port 8008
    server pg-3 192.168.1.12:6432 check port 8008

Patroni的8008端点返回HTTP状态码区分节点角色：/master返回200表示Leader，503表示非Leader；/replica返回200表示Replica。HAProxy据此实现故障转移后的自动路由切换。

高可用集群监控与健康检查

Prometheus通过postgres_exporter采集数据库指标，patroni_exporter采集集群状态指标。关键告警规则：

# 复制延迟告警
- alert: PGReplicationLagHigh
  expr: pg_replication_lag_seconds > 30
  for: 2m
  labels:
    severity: warning
  annotations:
    summary: "PostgreSQL复制延迟超过30秒"

# 集群无Leader告警
- alert: PatroniNoLeader
  expr: patroni_cluster_unlocked == 1
  for: 1m
  labels:
    severity: critical
  annotations:
    summary: "Patroni集群无Leader，正在故障转移"

# 连接池满告警
- alert: PgBouncerPoolExhausted
  expr: pgbouncer_pools_active_clients / pgbouncer_pools_pool_size > 0.9
  for: 1m
  labels:
    severity: warning
  annotations:
    summary: "PgBouncer连接池使用率超过90%"

这套架构的关键参数调优点：maximum_lag_on_failover决定故障转移时数据一致性底线，值越小RPO越低但越容易触发误切换；synchronous_mode开启后写性能下降但保证零数据丢失；PgBouncer的transaction模式不支持会话级特性（如临时表、SET命令），需要根据应用SQL特性选择session模式作为折中。生产环境建议至少3节点部署，避免脑裂。