PostgreSQL 流复制配主从：读写分离 + 自动 failover

起因

单 PG 写多读多，CPU 经常 80%+。临时方案是加内存 + SSD，但读 query
还是抢主库 CPU。
PG 自带的 streaming replication 几乎免费——配一个 standby 把读流量
分过去，写仍走主。

整体架构

    应用 (写)              应用 (读)
       ↓                      ↓
   主库 (primary)  ----→  从库 (standby)
                  WAL 流

主库正常处理读写
从库实时 replay 主库 WAL，几乎实时同步（毫秒级延迟）
从库只读，可以服务 SELECT 查询
主库挂了 → 提升从库为新主

解决方案

1. 主库配置

/etc/postgresql/16/main/postgresql.conf：

wal_level = replica
max_wal_senders = 5
wal_keep_size = 1GB        # 保留多少 WAL 给落后的 standby 追
hot_standby = on

/etc/postgresql/16/main/pg_hba.conf 允许复制连接：

host replication replicator <standby_ip>/32 scram-sha-256

创建复制用户：

CREATE USER replicator REPLICATION LOGIN PASSWORD 'strong-pass';

sudo systemctl restart postgresql。

2. 从库初始化（pg_basebackup）

从库全空状态（删 data 目录或新机器）：

sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/16/main/*

sudo -u postgres pg_basebackup \
  -h <primary_ip> -U replicator -p 5432 \
  -D /var/lib/postgresql/16/main \
  -Fp -Xs -P -R

-R 自动生成 postgresql.auto.conf + standby.signal，
让这个 instance 启动后就是 standby。

sudo systemctl start postgresql

# 校验
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
# t   (true，是 standby)

3. 在主库上看复制状态

SELECT client_addr, state, sent_lsn, write_lsn, replay_lsn,
       sync_state, pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;
--  client_addr | state     | ... | lag_bytes
--  10.0.0.2    | streaming | ... | 0

state=streaming + lag_bytes 接近 0 = 健康。

4. 应用读写分离

最简单：用两个连接池。

# Django settings
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': 'primary.db.local',
        'NAME': 'myapp', 'USER': 'app', 'PASSWORD': '...',
    },
    'replica': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': 'standby.db.local',
        'NAME': 'myapp', 'USER': 'app', 'PASSWORD': '...',
    },
}

DATABASE_ROUTERS = ['myapp.routers.PrimaryReplicaRouter']

# routers.py
class PrimaryReplicaRouter:
    def db_for_read(self, model, **hints):
        return 'replica'
    def db_for_write(self, model, **hints):
        return 'default'
    def allow_relation(self, obj1, obj2, **hints):
        return True
    def allow_migrate(self, db, app_label, **hints):
        return db == 'default'

读 query 自动走 replica，写走 primary。

Node / Python 其它框架同理：分两个连接池，业务代码按操作类型选。

5. 同步 vs 异步复制

默认异步：主库 commit 立刻返回，WAL 后台 stream 到 standby。
代价：主挂时 standby 可能差几秒数据。

切同步（commit 等 standby 确认）：

# postgresql.conf
synchronous_standby_names = 'standby1'
synchronous_commit = on

代价：standby 慢 / 挂时主库写阻塞。生产建议 quorum：

synchronous_standby_names = 'ANY 1 (standby1, standby2, standby3)'

3 个 standby 任一确认即可——既保证 RPO=0 又有容错。

6. 自动 failover：repmgr / Patroni

PG 自带不做"主挂了自动提升 standby"。需要外部工具：

repmgr：简单成熟
Patroni：基于 etcd / Consul，K8s 友好
pg_auto_failover：Citus 出品

最简 repmgr：

sudo apt install postgresql-16-repmgr

# 注册主库
sudo -u postgres repmgr -f /etc/repmgr.conf primary register

# 注册 standby
sudo -u postgres repmgr -f /etc/repmgr.conf standby register

# 启动 daemon（监控 + 自动 failover）
sudo systemctl enable --now repmgrd

主挂后 repmgrd 30 秒内提升某 standby 为新主，更新所有节点配置。

应用层用 PgBouncer + 监听 repmgr 事件改 backend 指向新主。
或者用 HAProxy 在前面做 health check：

backend pg_primary
    option pgsql-check user healthcheck
    server primary primary.db:5432 check
    server standby1 standby1.db:5432 check backup

backup 表示 standby 在 primary down 时才接流量。

7. 监控复制延迟

-- 主库看每个 standby 的延迟（bytes）
SELECT application_name,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) / 1024 / 1024 AS lag_mb
FROM pg_stat_replication;

-- standby 上看延迟（秒）
SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) AS lag_seconds;

Prometheus postgres_exporter 自动暴露这些指标。
告警阈值：lag > 30 秒 → warning，> 5 分钟 → critical。

8. logical replication（不同 schema 选择性复制）

streaming replication 是物理（整库 / 所有表 / 同版本）。
logical replication（PG 10+）按表选择性复制，可跨版本：

-- 主库
CREATE PUBLICATION mypub FOR TABLE users, posts;

-- 订阅端（可以是另一台 PG，不要求版本一致）
CREATE SUBSCRIPTION mysub
  CONNECTION 'host=primary user=replicator dbname=myapp password=...'
  PUBLICATION mypub;

用于：跨版本升级（旧版做 publication，新版做 subscription，同步后切流量）、
ETL（把 production 部分表 logical 复制到分析库）。

效果

我们配主从后：

主库 CPU 80% → 35%（读流量去 standby）
报表 query（重 read）不再影响业务写性能
主库挂过一次，repmgr 28 秒 failover，业务无感知
监控显示复制延迟稳定 < 100ms

与其它扩容方案对比

	物理复制（流）	逻辑复制	读写分离中间件	Citus / 分库
复杂度	低	中	中	高
适合	读扩容 / HA	跨版本 / 部分表	多主	横向扩容 PB 级
跨主版本	❌	✅	N/A	✅
自动 failover	需 repmgr/Patroni	难	✅	✅

踩过的坑

standby IP 防火墙：5432 端口必须从主库 → standby、standby →
主库双向通（standby 需要拉 WAL）。
wal_keep_size 太小：standby 落后超过这个大小后 WAL 被回收，
standby 无法追上 → 必须 full re-base。生产至少 1-10 GB。或者用
replication slot（slot 让 PG 保留 WAL 直到 slot 消费完）：
sql SELECT pg_create_physical_replication_slot('slot_standby1');
standby 配 primary_slot_name='slot_standby1'。
standby 上跑长 query 阻塞复制：standby 默认会 cancel 长 query
让复制优先。要避免就调 max_standby_streaming_delay = 30s（query
能跑多久）。
switchover 后没清旧主：旧主重启后会变 "split brain"（同时两个
主）。一定先 demote 旧主或者关掉 PG service。
同步复制 standby 全挂主库 hang：synchronous + 没 standby
时主库 write 阻塞等。设 synchronous_commit = local 或者
quorum ANY 1 避免单点。