PostgreSQL 高可用集群部署与读写分离实战

2026年7月16日互联网

引言：数据库高可用的重要性

在现代互联网架构中，数据库往往是系统性能和可靠性的核心瓶颈。单机数据库一旦宕机，整个业务系统将完全不可用。PostgreSQL 作为功能最强大的开源关系型数据库之一，其高可用方案日益成熟。本文将详细介绍如何基于流复制和 Pgpool-II 搭建 PostgreSQL 高可用集群，并实现读写分离，确保数据库层的稳定运行。

一、PostgreSQL 流复制原理

PostgreSQL 的流复制是其高可用的基础。主库将 WAL（预写日志）实时传输到备库，备库持续回放日志，保持与主库的数据同步。流复制分为异步和同步两种模式：

# postgresql.conf (主库配置)
listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1024   # 保留 1GB WAL 日志
synchronous_commit = on
synchronous_standby_names = 'FIRST 1 (standby1)'

# pg_hba.conf 允许备库连接
host replication replicator 192.168.1.0/24 md5

创建专用复制用户，避免使用超级管理员账号进行复制操作：

-- 在主库执行
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'StrongPass2026';

-- 使用 pg_basebackup 初始化备库
-- 在备库服务器上执行：
pg_basebackup -h 192.168.1.10 -D /var/lib/postgresql/data \
  -U replicator -v -P -R -X stream
# -R 参数会自动生成 standby.signal 和 primary_conninfo 配置

二、同步复制与数据一致性

异步复制虽然性能更好，但在主库故障切换时可能丢失少量数据。同步复制确保事务在至少一个备库上写入后才向客户端返回成功，从而保证零数据丢失：

# 同步复制配置（主库 postgresql.conf）
synchronous_commit = on
synchronous_standby_names = 'FIRST 1 (standby1, standby2)'

# 备库 postgresql.auto.conf
primary_conninfo = 'host=192.168.1.10 port=5432 user=replicator password=StrongPass2026 application_name=standby1'
hot_standby = on
hot_standby_feedback = on

验证复制状态是运维中的关键操作：

-- 在主库查看复制状态
SELECT application_name, state, sync_state, 
       sent_lsn, write_lsn, flush_lsn, replay_lsn,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

-- 在备库查看接收状态
SELECT status, receive_start_lsn, receive_start_tli,
       synced_slot
FROM pg_replication_slot;

-- 检查备库是否处于恢复模式
SELECT pg_is_in_recovery();

三、使用 Pgpool-II 实现读写分离

Pgpool-II 是 PostgreSQL 生态中最常用的中间件之一，它位于应用和数据库之间，提供连接池、负载均衡、读写分离和自动故障切换功能。以下是核心配置：

# pgpool.conf 关键配置
listen_addresses = '*'
port = 9999

# 后端节点定义
backend_hostname0 = '192.168.1.10'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '192.168.1.11'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

# 读写分离
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'

# 故障检测
health_check_period = 10
health_check_timeout = 20
health_check_user = 'pgpool_check'
failover_command = '/etc/pgpool/failover.sh %d %P %H %R'

# 连接池
num_init_children = 32
max_pool = 4
connection_cache = on

四、自动故障切换与恢复

当主库故障时，Pgpool-II 会自动检测并将备库提升为新的主库。以下是故障切换脚本的核心逻辑：

#!/bin/bash
# failover.sh - 故障切换脚本
FALLING_NODE=$1   # 故障节点 ID
OLD_MASTER=$2      # 旧主库信息
NEW_MASTER=$3      # 新主库信息
NEW_MASTER_PORT=$4

if [ $FALLING_NODE -eq 0 ]; then
  # 主库故障，提升备库为新主库
  ssh -T postgres@$NEW_MASTER \
    "pg_ctl promote -D /var/lib/postgresql/data"
  echo "Failover completed: $NEW_MASTER is now primary"
fi

故障切换后，原主库恢复时需要重新作为备库加入集群。使用 pg_rewind 工具可以高效地同步增量数据，避免全量 basebackup：

# 在原主库上执行
pg_rewind --target-data-directory=/var/lib/postgresql/data \
  --source-server="host=192.168.1.11 port=5432 user=replicator password=StrongPass2026"

# 创建 standby.signal 并配置连接信息
touch /var/lib/postgresql/data/standby.signal
echo "primary_conninfo = 'host=192.168.1.11 port=5432 user=replicator'" \
  >> /var/lib/postgresql/data/postgresql.auto.conf

# 启动 PostgreSQL
pg_ctl start -D /var/lib/postgresql/data

五、监控与运维最佳实践

高可用集群的稳定运行离不开持续监控。以下是几个关键监控指标和对应的 SQL 查询：

-- 1. 复制延迟监控（备库执行）
SELECT EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp()) AS lag_seconds;

-- 2. 连接数监控
SELECT count(*) AS total, 
       count(*) FILTER (WHERE state = 'active') AS active,
       count(*) FILTER (WHERE state = 'idle') AS idle
FROM pg_stat_activity;

-- 3. WAL 生成速率
SELECT pg_walfile_name(pg_current_wal_lsn()) AS current_wal,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0')) AS total_wal_size;

-- 4. 数据库大小增长趋势
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database ORDER BY pg_database_size(datname) DESC;

总结

PostgreSQL 高可用集群的搭建是一个系统工程，涉及流复制配置、同步策略选择、中间件层读写分离、自动故障切换以及全方位监控。在实际生产环境中，还需要根据业务特点权衡一致性与性能、考虑跨机房部署的网络延迟、制定完善的备份与灾难恢复策略。建议在搭建完成后进行充分的故障演练，验证切换流程的可靠性，确保在真实故障发生时系统能够平滑降级并快速恢复。