PostgreSQL 逻辑复制实战：主从搭建、延迟监控与四个必踩的坑，附零停机迁移流程

  1.  Home
  2. Text  
    3. Share to：

为什么个人站也该关心 PostgreSQL 复制

大部分草根站长的技术栈是 MySQL/MariaDB，博客、论坛、CMS 一套走天下。但只要你哪天跑到一个用 PostgreSQL 的开源项目上——比如 Calibre-Web、Matomo、一些 Go/Rails 的 SaaS 模板——就会遇到「数据怎么备份、怎么迁到新机、怎么在读库上跑报表不拖累主库」这些现实问题。这些问题的标准答案，是 PostgreSQL 自带的逻辑复制（Logical Replication）。

它跟 MySQL 的 binlog 主从思路相通，但机制和你踩的坑完全不同。今天这篇从零讲透：逻辑复制是什么、怎么搭、怎么监控延迟、以及为什么你 90% 的情况下不该用它来做「定时备份」。内容全部基于 PostgreSQL 14+ 的原生逻辑复制，不需要任何插件。

物理复制 vs 逻辑复制，先分清

PostgreSQL 有两套复制机制，很多人一上来就搞混：

  • 物理复制（Streaming Replication）：基于 WAL 日志，按块复制，从库是主库的字节级副本。整实例、全表、全库一起复制，从库默认只读，不能单独改结构。适合高可用和故障切换。
  • 逻辑复制（Logical Replication）：基于「逻辑解码」，复制的是 行级数据变更，按表、按库、可选择性复制。从库（订阅端）是普通可写库，能建额外索引、能加表、能版本不同。

一句话记法：物理复制复制「磁盘」，逻辑复制复制「数据行」。

逻辑复制的典型场景

对个人站长来说，它解决三类真实需求：

  1. 零停机迁移：老 VPS 上的 PG 迁到新 VPS，用逻辑复制同步，追平后一个 switchover 切过去，停机时间以秒计。
  2. 读写分离 / 报表：主库跑业务，订阅端跑数据分析和导出，重查询不锁主库。
  3. 跨版本升级：从 PG 12 升到 PG 16，逻辑复制可以跨大版本，物理复制不行。

注意：逻辑复制不是备份方案。它不做 PITR（时间点恢复），不能回滚误删的 DELETE——因为 DELETE 会被原样复制过去。备份请用 pg_dump / pg_basebackup / WAL 归档，别混淆。

前置条件：wal_level 必须是 logical

这是第一道坎。默认 wal_level=replica，逻辑复制不工作。改 postgresql.conf：

wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

max_replication_slots 决定你能建多少个复制槽（也就是多少个订阅），max_wal_senders 决定并发的 WAL 发送进程上限。个人站给 10 足够。改完必须重启 PostgreSQL，这三个参数不是 reload 能生效的：

systemctl restart postgresql
# 验证
psql -c "SHOW wal_level;"   # 应输出 logical

踩坑提醒：如果你是从物理复制环境改过来的，从库上改 wal_level 需要先停复制。另外 wal_level=logical 会比 replica 多写一些 WAL 信息，磁盘 IO 略增，但个人站量级可以忽略。

发布端（主库）配置

逻辑复制的模型是 Publication（发布方）→ Subscription（订阅方）。先在主库建一个用于复制的角色，给它 REPLICATION 权限：

CREATE ROLE repl_user WITH LOGIN REPLICATION PASSWORD 'StrongPass123';

然后对要复制的表授权。假设你只想同步 articles 和 comments 两张表：

GRANT SELECT ON articles, comments TO repl_user;

建发布（Publication）：

-- 指定表发布
CREATE PUBLICATION my_pub FOR TABLE articles, comments;

-- 或者发布整个库的所有表（PG 15+ 支持）
-- CREATE PUBLICATION my_pub FOR ALL TABLES;

-- 只发布 INSERT/UPDATE（不复制 DELETE，某些归档场景有用）
-- CREATE PUBLICATION my_pub FOR TABLE articles WITH (publish = 'insert, update');

查看发布：

SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

订阅端（从库）配置

关键前提：订阅端必须已经存在同名空表，且表结构兼容。逻辑复制不会帮你建表、不会同步 DDL（表结构变更）。这是新手最大的认知陷阱。

所以第一步是在从库上把表结构建好。最省事的做法是用 pg_dump --schema-only 只导结构：

# 从主库导出结构（不导数据）
pg_dump -h  -U postgres -s -t articles -t comments mydb > schema.sql

# 在从库导入结构
psql -d mydb -f schema.sql

然后在从库建订阅：

CREATE SUBSCRIPTION my_sub
CONNECTION 'host= port=5432 dbname=mydb user=repl_user password=StrongPass123'
PUBLICATION my_pub;

执行这条命令时，PostgreSQL 会：建立复制槽、拉取表的初始快照（全量数据）、然后开始流式增量同步。CREATE SUBSCRIPTION 是同步阻塞的——它会等待初始数据拷贝完成才返回。表大的话会卡很久，别以为死机了。

pg_hba.conf：别忽略的认证配置

订阅端要连主库，主库的 pg_hba.conf 必须放行：

# 允许 repl_user 从订阅端 IP 做复制连接
host    mydb    repl_user    /32    scram-sha-256

改完 SELECT pg_reload_conf(); 或 systemctl reload postgresql。如果没配，订阅会一直报 connection failed: FATAL: no pg_hba.conf entry，而错误只出现在主库日志里，订阅端只显示「同步中」，排查时容易找错方向。

监控复制延迟（重点）

逻辑复制的延迟不像物理复制那样有现成的 pg_stat_replication 列。要看订阅端状态，主要查 pg_stat_subscription：

SELECT subname, pid, received_lsn, latest_end_lsn,
       latest_end_time
FROM pg_stat_subscription;

received_lsn（已收到）和 latest_end_lsn（主库最新）之间的差距就是延迟。但 LSN 是十六进制的日志位置，肉眼比不出「落后几秒」。更实用的是在主库查复制槽的确认位点：

SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots;

这个 lag 是「订阅端落后主库多少 WAL 字节」。它一直增长不下降，说明订阅端消费不过来或已经断了。这是最该盯的指标。

还有一个隐藏杀手：复制槽泄漏。如果一个订阅被 DROP 了但主库的 slot 没删，或者订阅端长时间离线，主库会为了保留 WAL 而疯狂占用磁盘，直到把盘写满、主库挂掉。所以：

-- 列出所有复制槽，检查 inactive 的
SELECT slot_name, active, active_pid, restart_lsn FROM pg_replication_slots;

-- 确认订阅已经不存在了，手动删掉残留槽
SELECT pg_drop_replication_slot('my_sub');

个人站务必把这个检查写进你的日常巡检脚本。磁盘被 WAL 撑爆导致数据库宕机，是逻辑复制最常见的生产事故。

增量 DDL 怎么办

逻辑复制只同步 DML（增删改），不同步 DDL。也就是说，你在主库 ALTER TABLE articles ADD COLUMN x，从库不会跟着变。下次这条表的 INSERT 复制过去时，会因为字段数不匹配而报错，复制直接中断。

正确流程是「先改从库，再改主库」：

  1. 在订阅端执行 ALTER TABLE（订阅端表可写，且复制是行级的，改结构不会冲突）。
  2. 确认从库结构就绪。
  3. 再在主库执行同样的 ALTER TABLE。

这样增量数据复制到从库时，结构已经能容纳新字段。顺序反了就会踩坑，且恢复复制需要手动 ALTER SUBSCRIPTION ... REFRESH 甚至重建订阅。

常见故障速查

  • 订阅卡在 initial sync：检查主库 max_worker_processes 是否够大（每个订阅至少 2 个 worker），以及大表初始快照是否真的在跑。
  • 报 duplicate key：初始快照期间目标表已有数据，或者复制中断后重启导致重复插入。用 ALTER SUBSCRIPTION ... DISABLE 停掉，清理冲突行，再 ENABLE。
  • 报 relation does not exist：发布端加了新表，但订阅端没建对应表。ALTER SUBSCRIPTION ... REFRESH PUBLICATION 后仍需手动补表结构。
  • 主库磁盘暴涨：复制槽泄漏，见上文。

小结

PostgreSQL 逻辑复制是把「迁移、读写分离、跨版本升级」三件事一次性解决的工具，代价是几个必须记住的前提：wal_level=logical 要重启、从库要预先建表、DDL 要手工同步、复制槽会吃磁盘。把这四点刻在脑子里，它就是一个可靠、零成本、官方的数据同步方案。

对于个人站长，我的建议是：日常备份照样用 pg_dump + 定时任务，逻辑复制留给「真需要实时同步」的场景。工具用对地方，才不会被工具反噬。

Last modification：October 5th, 2026 at 07:25 pm
© 允许规范转载

相关文章

Leave a Comment