PostgreSQL高可用流复制与读写分离配置实战

PostgreSQL流复制（Streaming Replication）是基于WAL（Write-Ahead Log）日志的物理复制技术，主库将WAL日志实时传输到备库重放，实现数据同步。相比逻辑复制，流复制延迟更低、一致性更强，是PostgreSQL高可用架构的核心方案，配合PgBouncer连接池可实现读写分离和故障自动切换。

流复制架构与主库配置

流复制分为同步和异步两种模式。异步模式下主库提交事务后立即返回，不等备库确认；同步模式下主库等待至少一个备库确认收到WAL日志后才返回提交成功。生产环境通常使用同步模式保证数据零丢失。

主库（Primary）配置文件postgresql.conf关键参数：

# postgresql.conf (Primary)
listen_addresses = '*'
port = 5432
max_connections = 200
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
wal_keep_size = 1024

synchronous_standby_names = 'FIRST 1 (standby1, standby2)'

shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 64MB
maintenance_work_mem = 512MB
wal_buffers = 16MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB

archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
archive_timeout = 300

log_min_messages = warning
log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '
log_statement = 'ddl'
log_replication_commands = on

主库pg_hba.conf添加复制权限：

# pg_hba.conf (Primary)
host    replication  replicator  192.168.1.0/24   md5
host    all          all         192.168.1.0/24   md5
host    replication  replicator  192.168.1.21/32  md5
host    replication  replicator  192.168.1.22/32  md5

创建复制专用用户：

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'StrongRepPass2026';

SELECT pg_create_physical_replication_slot('standby1_slot');

备库初始化与流复制启动

使用pg_basebackup从主库创建基准备份，自动配置流复制：

# 在备库服务器执行
pg_basebackup   -h 192.168.1.20   -p 5432   -U replicator   -D /var/lib/postgresql/data   -Fp   -Xs   -P   -R   -S standby1_slot   -C

pg_basebackup的-R参数会自动生成standby.signal文件并写入primary_conninfo。手动配置备库：

# postgresql.auto.conf (Standby)
primary_conninfo = 'host=192.168.1.20 port=5432 user=replicator password=StrongRepPass2026 application_name=standby1'
primary_slot_name = 'standby1_slot'
hot_standby = on
hot_standby_feedback = on

# touch /var/lib/postgresql/data/standby.signal

启动备库后验证复制状态：

-- 在主库执行
SELECT 
    client_addr,
    application_name,
    state,
    sync_state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;

-- 在备库执行
SELECT 
    pg_is_in_recovery() AS is_standby,
    pg_last_wal_receive_lsn() AS receive_lsn,
    pg_last_wal_replay_lsn() AS replay_lsn,
    pg_last_xact_replay_timestamp() AS last_replay_time;

-- 计算复制延迟
SELECT 
    sent_lsn - replay_lsn AS replication_lag_bytes
FROM pg_stat_replication
WHERE application_name = 'standby1';

PgBouncer连接池与读写分离配置

PgBouncer是轻量级连接池，减少PostgreSQL连接创建开销。配合读写分离路由，应用层连接PgBouncer，读取请求路由到备库，写入请求路由到主库。

# pgbouncer.ini
[databases]
primary = host=192.168.1.20 port=5432 dbname=appdb
standby1 = host=192.168.1.21 port=5432 dbname=appdb
standby2 = host=192.168.1.22 port=5432 dbname=appdb
appdb = host=192.168.1.20 port=5432 dbname=appdb

[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 = 300
query_wait_timeout = 120
client_idle_timeout = 0
server_lifetime = 3600
server_connect_timeout = 15

server_check_query = select 1
server_check_delay = 10

应用层通过不同数据源名称路由读写请求：

package main

import (
    "database/sql"
    "fmt"
    "math/rand"
    
    _ "github.com/lib/pq"
)

type DBRouter struct {
    primary  *sql.DB
    standbys []*sql.DB
}

func NewDBRouter() *DBRouter {
    primary, _ := sql.Open("postgres", 
        "host=pgbouncer port=6432 dbname=primary user=app password=pass sslmode=disable")
    
    standbys := make([]*sql.DB, 2)
    for i, name := range []string{"standby1", "standby2"} {
        db, _ := sql.Open("postgres",
            fmt.Sprintf("host=pgbouncer port=6432 dbname=%s user=app password=pass sslmode=disable", name))
        standbys[i] = db
    }
    
    return &DBRouter{primary: primary, standbys: standbys}
}

func (r *DBRouter) Writer() *sql.DB {
    return r.primary
}

func (r *DBRouter) Reader() *sql.DB {
    idx := rand.Intn(len(r.standbys))
    return r.standbys[idx]
}

Patroni自动故障切换与高可用管理

Patroni是PostgreSQL高可用管理工具，基于分布式一致性（etcd/Consul/ZooKeeper）实现Leader选举和自动Failover。当主库故障时，Patroni在秒级内将备库提升为新主库并更新服务路由。

# patroni.yml
namespace: /postgres/
name: node1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 192.168.1.20:8008

etcd:
  hosts: 192.168.1.100:2379,192.168.1.101:2379,192.168.1.102: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
        synchronous_commit: on

  initdb:
    - encoding: UTF8
    - data-checksums

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.20:5432
  data_dir: /var/lib/postgresql/data
  bin_dir: /usr/lib/postgresql/15/bin
  authentication:
    replication:
      username: replicator
      password: StrongRepPass2026
    superuser:
      username: postgres
      password: SuperSecretPass
  parameters:
    shared_buffers: 4GB
    effective_cache_size: 12GB

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

Patroni配合HAProxy暴露统一的虚拟IP，应用连接该VIP即可实现透明故障切换——当主库发生变化时，HAProxy健康检查会自动将写请求路由到新的Patroni Leader节点。这种架构在生产环境中可实现RPO=0（零数据丢失）和RTO小于30秒的高可用目标。