PostgreSQL 主从复制 & 读写分离实战：从零搭建高可用架构
上次我们聊了 PG 性能优化（文章 #26），从配置调优到索引优化，把单机性能榨了个遍。但单机再强也有天花板——挂了怎么办？读压力大了怎么办？
这一篇，我们解决这两个问题。主从复制负责高可用（挂了有人顶），读写分离负责扛读压力（别让报表查询拖垮主库）。
一、先搞清楚：你要的是什么？
很多同学一上来就跟着教程配，配完发现跟自己想要的不一样。我们先拉一下需求：
| 需求 | 用什么技术 | 是否必须 |
|---|---|---|
| 主库挂了能自动切换 | 流复制 + 监控工具（Patroni / repmgr） | ✅ 高可用必备 |
| 主库挂了，手动切过去也接受 | 流复制 + pg_ctl promote |
⚠️ 可接受停机 |
| 把查询请求分流到从库 | 读写分离中间件或应用层路由 | ✅ |
| 从库能查，但数据不能写 | Hot Standby（默认开启） | ✅ |
| 永远不丢数据 | 同步复制（synchronous_commit = on） | ⚠️ 有性能代价 |
| 允许丢几秒数据，但性能优先 | 异步复制（默认） | ✅ 大多数场景 |
老张点评： 99% 的生产环境用 异步流复制 + Hot Standby + 应用层读写分离 就够了。同步复制不加缓存的话，性能代价挺大，后面细说。
二、架构总览：一张图说清楚
┌─────────────────────────────────────────────────┐
│ 客户端应用 │
└────────────────────┬────────────────────────────┘
│
▼
┌─────────────────┐
│ 读写分离路由器 │ ← PgBouncer / ProxySQL / 应用层
│ (Read/Write │
│ Split Router) │
└────────┬────────┘
│
┌─────────┴─────────┐
▼ ▼
┌─────────────────┐ ┌─────────────────┐
│ Primary (主) │ │ Standby (从) │ ← 可多个
│ Read + Write │ │ Read Only │
│ wal_level=replica│ │ hot_standby=on │
└────────┬─────────┘ └─────────────────┘
│ WAL 流（TCP 连接）
└─────────────────────────────────►
streaming replication (pgoutput)
核心概念：
- Primary — 可读可写，产生 WAL 日志
- Standby — 只读，通过流复制接收并回放 WAL
- WAL（Write-Ahead Log） — PG 的预写日志，相当于 MySQL 的 binlog
- LSN（Log Sequence Number） — WAL 日志的位置标记，用来监控复制延迟
三、手把手配置流复制
3.1 环境准备
假设两台服务器：
| 角色 | IP | 数据目录 | PG 版本 |
|---|---|---|---|
| Primary | 192.168.1.10 | /var/lib/postgresql/16/main |
16 / 17 / 18 |
| Standby | 192.168.1.20 | /var/lib/postgresql/16/main |
同版本 ⚠️ |
⚠️ 重要：主从必须同大版本（16→16 或 17→17）。跨版本复制不支持。也建议保持小版本一致，非要混的话，从库先升级。
3.2 第一步：主库配置
① 创建复制用户
-- 在主库执行
CREATE ROLE repuser WITH REPLICATION LOGIN PASSWORD 'your_strong_password';
💡
REPLICATION权限不授权修改数据，只允许读取 WAL 流。比 SUPERUSER 安全得多。
② 修改 postgresql.conf
# ── 复制相关 ──
wal_level = replica # 必须！replica 或以上
max_wal_senders = 10 # 最多同时连接几个从库(含备份工具)
wal_keep_size = 1024 # 保留 1GB WAL，防止从库追不上时被回收
max_replication_slots = 10 # 复制槽数量
# ── 网络 ──
listen_addresses = '192.168.1.10' # 监听内网 IP
# ── 归档（推荐） ──
archive_mode = on
archive_command = 'cp %p /archive/%f' # 归档到共享目录（可选的，但推荐）
wal_level 参数说明（PG 15+ 之后简化了）：
minimal— 不产生足够复制信息，不能做流复制replica— 支持流复制和归档（PG 15+ 推荐，旧版叫hot_standby/archive）logical— 支持逻辑复制（更细粒度，可以复制指定表）
③ 配置 pg_hba.conf
# 允许从库连接复制伪数据库
# TYPE DATABASE USER ADDRESS METHOD
host replication repuser 192.168.1.20/32 scram-sha-256
④ 重启主库
sudo systemctl restart postgresql
# 或
pg_ctl reload # 只是重载配置的话 reload 就够了
3.3 第二步：创建基础备份
方法一：pg_basebackup（推荐）
在从库服务器上执行：
# 先清空从库数据目录
sudo -u postgres rm -rf /var/lib/postgresql/16/main/*
# 从主库拉取完整备份
sudo -u postgres pg_basebackup -h 192.168.1.10 -D /var/lib/postgresql/16/main \
-U repuser -P -v --wal-method=stream
关键参数：
| 参数 | 作用 |
|:----|:----|
| -h | 主库 IP |
| -D | 从库数据目录 |
| -U repuser | 复制用户 |
| -P -v | 显示进度 |
| --wal-method=stream | 备份过程中同步 WAL，避免漏数据 |
方法二：rsync（大库更稳）
如果数据库很大（TB 级），pg_basebackup 可能超时，可以用 rsync：
# 主库上
psql -c "SELECT pg_start_backup('full_backup', true);"
rsync -acv ${PGDATA}/ standby:/srv/pgsql/standby/ --exclude postmaster.pid
psql -c "SELECT pg_stop_backup();"
3.4 第三步：配置从库
① 创建 standby.signal 信号文件
PG 12+ 不再用 recovery.conf，而是通过信号文件标记：
sudo -u postgres touch /var/lib/postgresql/16/main/standby.signal
② 配置 postgresql.conf（从库）
# ── 复制连接 ──
primary_conninfo = 'host=192.168.1.10 port=5432 user=repuser password=your_strong_password'
primary_slot_name = 'standby1_slot' # 推荐使用复制槽
# ── 热备模式（允许只读查询） ──
hot_standby = on
# ── 防止从库查询冲突导致复制中断 ──
hot_standby_feedback = on
# ── WAL 接收 ──
wal_receiver_create_temp_slot = on # 如果没有预创建复制槽，自动创建
wal_receiver_status_interval = 5 # 每 5 秒向主库报告状态
③ 在主库创建复制槽
在主库执行：
SELECT pg_create_physical_replication_slot('standby1_slot');
复制槽的用处：即使从库断了，主库也不会删掉从库还没收到的 WAL。不加复制槽的话，从库断联太久就可能需要重新做 base backup。
④ 启动从库
sudo systemctl start postgresql
3.5 验证复制状态
在主库查看：
\x -- 扩展显示，方便阅读
SELECT * FROM pg_stat_replication;
输出示例：
-[ RECORD 1 ]----+------------------------------
pid | 12345
application_name | walreceiver
state | streaming ← ✅ 在流复制了
sync_state | async ← 异步模式
sent_lsn | 0/3002E120 ← 已发送的位置
write_lsn | 0/3002E120 ← 从库已写入
flush_lsn | 0/3002E120 ← 从库已刷盘
replay_lsn | 0/3002E120 ← 从库已回放
write_lag | 00:00:00.002145 ← 写入延迟 ~2ms
flush_lag | 00:00:00.003012 ← 刷盘延迟 ~3ms
replay_lag | 00:00:00.003521 ← 回放延迟 ~3ms
在从库查看：
-- 当前从库接收和回放位置
SELECT pg_is_in_recovery(); -- true = 是从库
SELECT pg_last_wal_receive_lsn(); -- 最后接收的 LSN
SELECT pg_last_wal_replay_lsn(); -- 最后回放的 LSN
SELECT pg_last_wal_replay_lsn() - pg_last_wal_receive_lsn() AS replay_lag_bytes;
-- 用进程查看也有信息
\x
SELECT * FROM pg_stat_wal_receiver;
延迟速查命令：
-- 主库执行：最后写入位置
SELECT pg_current_wal_lsn();
-- 从库执行：最后回放位置
SELECT pg_last_wal_replay_lsn();
-- 二者相减 ≈ 延迟字节数（可以用 pg_wal_lsn_diff()）
-- 在主库执行：
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS send_pending,
pg_wal_lsn_diff(sent_lsn, write_lsn) AS write_pending,
pg_wal_lsn_diff(sent_lsn, flush_lsn) AS flush_pending,
pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_pending
FROM pg_stat_replication;
四、复制模式怎么选？
4.1 异步复制（默认）
客户端 ──COMMIT──► 主库 ──WAL──► 从库
← 立即返回
- 主库提交完就返回客户端，WAL 异步发到从库
- ✅ 性能损耗极小（<5%）
- ❌ 主库挂了可能丢几秒数据
- ✅ 适合大多数生产场景
4.2 同步复制
客户端 ──COMMIT──► 主库 ──WAL──► 从库
← 等从库确认后才返回
配置方式：
# 主库 postgresql.conf
synchronous_standby_names = 'FIRST 1 (standby1, standby2)'
synchronous_commit = on # 默认就是 on
| synchronous_commit 值 | 从库确认到什么程度 | 安全性 |
|---|---|---|
on（默认） |
WAL 写入并刷盘 | ⭐⭐⭐ |
remote_write |
WAL 写入 OS 缓存，未刷盘 | ⭐⭐ |
remote_apply |
事务在从库已回放，查询可见 | ⭐⭐⭐⭐ |
local |
只等本地刷盘，不等从库 | ⭐（等同于异步） |
老张点评： 同步复制不是银弹。网络延迟有多大，写入变慢就有多少。如果主从在同一机房（<1ms 延迟），影响不大。跨机房的话，每个 INSERT 都等几十毫秒往返，高并发场景直接崩。
实用建议： 核心业务数据用同步（通过 session 级设置），普通数据用异步：
-- 在应用层，只对关键事务用同步
SET synchronous_commit TO remote_write;
INSERT INTO payments (...) VALUES (...);
SET synchronous_commit TO on; -- 恢复默认
4.3 级联复制
Primary ──► Standby A ──► Standby B ──► Standby C
↑ 上游 ↑ 下游
当你有很多从库时，可以让从库挂从库，减轻主库连接压力：
# Standby A（既是接收者也是发送者）
max_wal_senders = 10 # 需要配
hot_standby = on # 需要是 hot standby
# Standby B（指向 Standby A）
primary_conninfo = 'host=192.168.1.21 port=5432 user=repuser ...'
五、读写分离实战
5.1 方案选型
| 方案 | 适用场景 | 复杂程度 | 维护成本 |
|---|---|---|---|
| 应用层硬编码 | 小项目，就 1~2 个从库 | ⭐ | 低 |
| PgBouncer | 连接池 + 简单路由 | ⭐⭐ | 低 |
| ProxySQL + PG | 复杂路由、多规则 | ⭐⭐⭐ | 中 |
| Pgpool-II | 全功能（含连接池、负载均衡） | ⭐⭐⭐⭐ | 高 |
| 应用框架（ORM） | Rails、Django 等内置支持 | ⭐ | 低 |
老张推荐： 对大多数项目，应用层判断 + PgBouncer 连接池是最优解。ORM 可以配置多个数据源，代码里区分读写就行。不需要引入额外的单点故障。
5.2 方式一：应用层读写分离（推荐）
以 Python / SQLAlchemy 为例：
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
# 配置主库和从库
engines = {
'write': create_engine('postgresql://user:[email protected]:5432/dbname'),
'read': create_engine('postgresql://user:[email protected]:5432/dbname'),
}
class RoutingSession(Session):
def get_bind(self, mapper=None, clause=None, **kwargs):
if self._flushing or self.info.get('write'):
return engines['write']
return engines['read']
# 使用
db = Session(bind=engines['write']) # 写操作
db.info['write'] = True
# 读操作自动走从库
result = Session(bind=engines['read']).execute('SELECT ...')
5.3 方式二：PgBouncer 自动路由
PgBouncer 本身不做读写分离，但可以配置两个端口/数据库名来区分：
[databases]
# 主库（读写）
mydb_write = host=192.168.1.10 port=5432 dbname=mydb
# 从库（只读，可以配多个做负载均衡）
mydb_read = host=192.168.1.20 port=5432 dbname=mydb
host=192.168.1.21 port=5432 dbname=mydb
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 500
default_pool_size = 50
# 事务级连接池（用完就还，非常适合读写分离）
应用层连接：
写操作 → pgbouncer://user:pass@host:6432/mydb_write
读操作 → pgbouncer://user:pass@host:6432/mydb_read ← 自动负载均衡
5.4 方式三：ProxySQL + PG
ProxySQL 支持基于正则的路由规则：
-- 添加 PG 后端
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (0, '192.168.1.10', 5432); -- 写组
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '192.168.1.20', 5432); -- 读组
-- 设置路由规则：SELECT 走读组，其他走写组
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup)
VALUES (1, 1, '^SELECT', 1);
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup)
VALUES (2, 1, '^.*', 0);
LOAD MYSQL QUERY RULES TO RUN;
5.5 需要注意的问题
问题 1：主从延迟导致读到旧数据
用户写完立马刷新页面→读到的是从库数据，但从库还没回放完→用户以为自己操作失败了。
解决方案：
- 写后读一致性： 写完关键数据（如订单、支付），强制走主库读取
- session 级别标记： 写操作后 1 秒内，同一个 session 走主库
- 在 ORM 层处理： 事务结束后，下一个读请求延迟几毫秒或者走主库
问题 2：从库查询把从库打死
假设你有个日报表查询，一次跑 5 分钟。如果这种查询跑到从库上，从库复制延迟会飙到几分钟。
解决方案：
- 对从库设置
statement_timeout防止慢查询拖死复制 - 大查询单独连接一个专用的分析实例（replica 再 replica）
# 从库的 postgresql.conf 额外加
statement_timeout = '30s' # 从库上超时短的查询直接掐掉，不让它拖慢复制
idle_in_transaction_session_timeout = '60s'
问题 3：从库因为查询冲突卡住复制
当从库上有个长事务在跑 SELECT，而此时主库在 VACUUM，VACUUM 删掉的元组在从库上还没被读取，PG 就会卡住复制。
解决方案：
# 从库
hot_standby_feedback = on -- 告诉主库：别急着 vacuum 我还在读的元组
六、监控与告警
6.1 复制延迟监控
-- 主库：查看所有从库的延迟情况
SELECT application_name,
state,
sync_state,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag_human,
EXTRACT(EPOCH FROM write_lag) AS write_lag_seconds,
EXTRACT(EPOCH FROM replay_lag) AS replay_lag_seconds,
backend_start
FROM pg_stat_replication;
6.2 从库恢复状态
-- 从库执行
SELECT pg_is_in_recovery(), -- 是否处于恢复模式
pg_is_wal_replay_paused(), -- 复制是否暂停
pg_last_wal_receive_lsn(), -- 最后接收位置
pg_last_wal_replay_lsn(), -- 最后回放位置
pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(),
pg_last_wal_replay_lsn())) AS unsolved_bytes;
6.3 建议的告警阈值
| 指标 | 警告阈值 | 严重阈值 | 说明 |
|---|---|---|---|
replay_lag |
> 10 秒 | > 60 秒 | 从库落后太多 |
| 从库连接断开 | 断开 > 30 秒 | 断开 > 5 分钟 | 可能需重建复制 |
pg_wal 目录大小 |
> 10 GB | > 50 GB | WAL 堆积，复制可能有问题 |
| 复制槽堆积 | 任意槽 inactive > 1h | inactive > 24h | 可能撑爆磁盘 |
6.4 一键检查脚本
#!/bin/bash
# pg_replication_check.sh - 快速查看复制健康状态
PRIMARY_HOST="192.168.1.10"
STANDBY_HOST="192.168.1.20"
echo "=== 主库复制状态 ==="
psql -h $PRIMARY_HOST -c "
SELECT application_name, state, sync_state,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS lag
FROM pg_stat_replication;
"
echo ""
echo "=== 从库状态 ==="
psql -h $STANDBY_HOST -c "
SELECT pg_is_in_recovery(),
pg_last_wal_receive_lsn(),
pg_last_wal_replay_lsn();
"
echo ""
echo "=== 复制槽 ==="
psql -h $PRIMARY_HOST -c "
SELECT slot_name, slot_type, active,
pg_size_pretty(pg_wal_lsn_diff(COALESCE(restart_lsn,'0/0'), '0/0')) AS retained_wal
FROM pg_replication_slots;
"
七、故障切换与恢复
7.1 手动切换（主库挂了）
步骤：
# 在从库上执行
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/16/main/
# 或使用触发文件（PG 12 之前的方式）
touch /tmp/promote_trigger # 如果有 trigger_file 配置
从库被 promote 后会变成新的主库，开始接受写入。
7.2 原主库恢复后重新加入
原主库恢复后不能直接加回去（数据已经不一致了），需要重建：
# 在新主库上创建复制槽
psql -h 192.168.1.20 -c "SELECT pg_create_physical_replication_slot('old_primary');"
# 在旧主库上重新做 base backup
sudo -u postgres rm -rf /var/lib/postgresql/16/main/*
sudo -u postgres pg_basebackup -h 192.168.1.20 -D /var/lib/postgresql/16/main/ \
-U repuser -P -v --wal-method=stream
# 创建 standby.signal
sudo -u postgres touch /var/lib/postgresql/16/main/standby.signal
# 更新 primary_conninfo 指向新主库
# ...
7.3 从库断连后自动重新同步
如果启用了复制槽，从库连回来会自动从断点开始继续复制，无需重建。如果没启用复制槽，且 wal_keep_size 不够，从库落后太多 → WAL 已被回收 → 需要重新 base backup。
所以：一定要用复制槽！
-- 主库：创建复制槽（第一次配的时候做）
SELECT pg_create_physical_replication_slot('standby1_slot');
-- 从库 postgresql.conf（必须配置）
primary_slot_name = 'standby1_slot'
7.4 安全使用 repmgr/Patroni（生产推荐）
手动切换在大半夜出故障时是噩梦。生产环境建议上 Patroni：
| 工具 | 特点 |
|---|---|
| Patroni | 自动故障检测 + 自动切换，基于 DCS（etcd/consul） |
| repmgr | 轻量，事件驱动，手动/半自动切换 |
| pg_auto_failover | Citus 出品，简单易用 |
Patroni 的配置方式是另一个话题了，后面可以另写一篇专门讲。
八、常见问题速查
Q：复制延迟越来越大怎么办？
自查步骤：
1. 从库 CPU 是不是跑满了？→ 从库有慢查询占资源
2. 有没有长时间运行的只读事务？→ 加 hot_standby_feedback
3. 网络带宽够不够？→ 检查主库网卡流量
4. 从库磁盘 IO 能跟上吗？→ iostat 看一下
5. 是不是有索引重建/大批量写入？→ 考虑批量写时暂时不查从库
Q：pg_basebackup 报 "out of memory"
大库备份时 WAL 发送器内存不够。主库调大 wal_sender_buffer 相关参数，或改用 rsync 方式。
Q：从库连不上主库
排查：
1. ping 通吗？→ 防火墙/网络
2. pg_hba.conf 有 replication 条目吗？→ 检查
3. 密码对不对？→ 检查 .pgpass
4. listen_addresses 配置了吗？→ 主库只监听了 127.0.0.1？
Q：复制槽撑爆磁盘
检查 pg_replication_slots 中是否有不再活动的槽：
-- 查看所有槽
SELECT slot_name, slot_type, active,
pg_size_pretty(pg_wal_lsn_diff(COALESCE(restart_lsn,'0/0'), '0/0')) AS retained
FROM pg_replication_slots;
-- 删除已废弃的槽
SELECT pg_drop_replication_slot('dead_slot_name');
PG 17 引入了
max_slot_wal_keep_size限制复制槽最大保留的 WAL 量，超过后自动失效槽——建议设置：
max_slot_wal_keep_size = '10GB' -- 每个复制槽最多保留 10GB WAL
Q：从库能写数据吗？
不能。Hot Standby 模式下从库严格只读，连临时表都不能写：
postgres=# CREATE TABLE test (id int);
ERROR: cannot execute CREATE TABLE in a read-only transaction
Q：逻辑复制和物理复制哪个好？
| 物理复制 | 逻辑复制 | |
|---|---|---|
| 复制粒度 | 整个实例 | 指定表 |
| 版本要求 | 必须同版本 | 可跨大版本 |
| 数据一致性 | 完全一致 | 行级，可过滤 |
| 从库可写 | ❌ | ✅（subscriber 可写） |
| 典型场景 | 高可用、读写分离 | 数据迁移、实时数仓 |
一句话：高可用用物理复制，数据同步用逻辑复制。
总结
主从复制 + 读写分离是 PG 生产架构的基石，但并不复杂。记住这几个要点：
- wal_level = replica，配好 pg_hba，流复制就通了
- 复制槽必须用，不然从库挂了会要重新 base backup
- 异步复制够用了，同步复制只有银弹四象限里的特定场景才需要
- 读写分离最佳实践是应用层判断 + PgBouncer 池化，别引入无意义的中间件
- 监控延迟不只看「差了多少字节」，要看 replay_lag 的时间
最后，一切配置都在这了，你可以在测试环境走一遍，从 pg_basebackup 到 promote 来回切两轮，确保团队每个人都清楚故障切换的手动步骤——别等真的挂了才开始学。
下一篇预告：PG 主从复制进阶——Patroni + etcd 搭建自动故障切换集群，敬请期待！
本文由老张整理编写。如果你实操中遇到问题，欢迎留言讨论。