订阅（CREATE SUBSCRIPTION）

订阅（subscription）是逻辑复制的「消费端入口」：它定义了「连到哪个发布端、消费哪些发布、怎么应用变更」。一条订阅在发布端对应一个逻辑复制槽，在订阅端对应一个常驻的 apply worker 进程，它持续接收变更、按事务顺序重新执行 INSERT / UPDATE / DELETE / TRUNCATE。如果说发布只是一份静态声明，订阅就是一个「活」的运行体——它会建连接、起进程、做初始同步、维护复制进度，也因此承载了逻辑复制运维中大部分的日常。

订阅端是行为最复杂的角色。CREATE SUBSCRIPTION 的十几个 WITH 参数决定了初始同步要不要做、复制槽要不要建、大事务怎么流式传输、错误发生时自动停还是继续重试、应用变更时以谁的权限执行……这些选项彼此还有耦合（例如 slot_name = NONE 会强制 enabled = false）。本页按「语法 → 参数 → 生命周期 → 监控 → 完整演练 → 排错」的顺序，把订阅端一次讲透。

一个重要提醒：订阅定义在订阅端数据库中，创建者即所有者；从 PG16 起，创建订阅不再要求超级用户，但必须是预置角色 pg_create_subscription 的成员并拥有当前数据库的 CREATE 权限。订阅端与发布端之间走的是标准 libpq 复制协议，因此 pg_hba.conf、防火墙、密码认证这些「普通连接」的前置条件一个都不能少。

前置知识

建议先阅读：发布（CREATE PUBLICATION）

基础概念

概念	英文 / 记法	说明
订阅	subscription	定义在订阅端数据库内的「连接 + 消费」声明，创建者即 owner
apply worker	logical replication apply worker	每个启用的订阅一个，接收变更并按事务顺序应用到本地表
table sync worker	table synchronization worker	初始同步阶段每张表一个，负责全量拷贝
parallel apply worker	parallel apply worker	`streaming = parallel` 时并行应用大事务（PG16+）
订阅关系	`pg_subscription_rel`	订阅与每张目标表的同步状态（初始化/拷贝中/已同步/就绪）
复制原点	replication origin	订阅端为「来自上游的变更」打的标记，名字形如 `pg_<oid>`
跳过事务	`ALTER SUBSCRIPTION ... SKIP`	指定 LSN，让 apply worker 跳过卡住的那个远端事务（PG15+）

订阅的几个基本事实：

一个订阅只连一个发布端数据库，但可以同时订阅该库中的多个发布（PUBLICATION pub1, pub2）。
订阅与复制槽一对一（除非 slot_name = NONE）。默认情况下槽名等于订阅名。
允许订阅尚不存在的发布：建订阅时指定的发布名可以先不存在，等发布端建好后再 REFRESH PUBLICATION 补齐表清单。这为「先定拓扑、后建发布」的流程留了口子，但也意味着拼错发布名不会在建订阅时报错。
同一集簇内订阅自己需要特殊处理：默认的「连接自己建槽」会永久挂起，必须先手工建槽再 create_slot = false（见下文）。

语法与用法

CREATE SUBSCRIPTION 的完整语法（PostgreSQL 18）：

sql

CREATE SUBSCRIPTION subscription_name
    CONNECTION 'conninfo'
    PUBLICATION publication_name [, ...]
    [ WITH ( subscription_parameter [= value] [, ... ] ) ]

conninfo 是标准 libpq 连接串，常用键包括 host、port、dbname、user、password，还可以加 application_name（会显示在发布端的 pg_stat_replication.application_name，便于识别连接来源）：

sql

CREATE SUBSCRIPTION sub_orders
    CONNECTION 'host=192.168.1.50 port=5432 dbname=app user=repl password=secret application_name=sub_orders'
    PUBLICATION pub_orders_dw, pub_audit_archive
    WITH (copy_data = true, streaming = on);

WITH 参数全表（按用途分组，版本列为「首次引入」）：

参数	类型	默认值	说明	版本
`connect`	boolean	`true`	建订阅时是否连接发布端；`false` 会强制 `create_slot` / `enabled` / `copy_data` 也为 `false`	PG10
`create_slot`	boolean	`true`	是否由本命令在发布端创建复制槽	PG10
`enabled`	boolean	`true`	建好即启动复制，还是先置为禁用	PG10
`slot_name`	string	订阅名	使用发布端哪个复制槽；`NONE` 表示不关联槽	PG10
`copy_data`	boolean	`true`	启动时是否拷贝发布中的已有数据	PG10
`streaming`	enum	PG14~17 为 `off`，PG18 起为 `parallel`	在途事务流式发送：`off` / `on` / `parallel`	PG14（`parallel` 为 PG16+）
`synchronous_commit`	enum	`off`	覆盖 apply worker 内的 `synchronous_commit` 设置	PG10
`two_phase`	boolean	`false`	是否以两阶段方式复制 `PREPARE TRANSACTION`	PG15
`disable_on_error`	boolean	`false`	apply 出错时自动禁用订阅	PG10
`origin`	string	`any`	`none` 表示只接收无 origin 的变更（防回环）	PG15
`binary`	boolean	`false`	以二进制格式传输数据	PG14
`password_required`	boolean	`true`	连接必须使用密码认证且密码写在连接串里；仅超级用户可设 `false`	PG16
`run_as_owner`	boolean	`false`	`true` 时所有复制动作以订阅 owner 身份执行	PG16
`failover`	boolean	`false`	关联槽同步到物理备库，切主后可恢复复制	PG17

创建订阅需要的权限：当前数据库的 CREATE 权限 + pg_create_subscription 预置角色的成员身份（PG16+；PG15 及更早版本要求超级用户）。

创建成功时通常能看到一条 NOTICE：

text

NOTICE:  created replication slot "sub_orders" on publisher
CREATE SUBSCRIPTION

详细说明

订阅端进程模型与 worker 池

一条启用的订阅在订阅端至少占一个 apply worker；初始同步期间，每张同步中的表再加一个 table sync worker；streaming = parallel 时还会按需启动 parallel apply worker。三类 worker 都从订阅端的后台进程池分配，配额关系如下：

conf

# 订阅端 postgresql.conf
max_worker_processes = 16                          # 所有后台 worker 的总池
max_logical_replication_workers = 8                # 逻辑复制专用配额（apply + sync + parallel apply 总数）
max_sync_workers_per_subscription = 2              # 每个订阅初始同步的并行度
max_parallel_apply_workers_per_subscription = 2    # 每个订阅并行应用大事务的 worker 数（PG16+）

容量规划的两条经验：

max_logical_replication_workers ≥ 「订阅数 × 1」+「初始同步高峰期同步中的表数」。多个订阅同时初始化时，sync worker 会瞬间吃满配额，表现为「有些表的状态一直停在 i / d」；
worker 起不来不会报错，只会排队等池子。看到「pg_stat_subscription 里没有预期的行」时，先数进程池余量，再查网络与认证。

连接准备：角色、pg_hba.conf 与网络

订阅连接走的是 libpq 普通连接（附带复制协议参数），因此按普通数据库连接来准备即可：

发布端创建专用角色，具备 LOGIN 与 REPLICATION 属性（或超级用户）：

sql

CREATE ROLE repl LOGIN REPLICATION PASSWORD 'strong-secret';

发布端 pg_hba.conf 放行订阅端来源（逻辑复制连接匹配普通数据库条目，不需要 replication 特殊库名）：

conf

# TYPE  DATABASE  USER  ADDRESS           METHOD
host    app       repl  192.168.1.0/24    scram-sha-256

初始数据拷贝要求该角色对发布表有 SELECT 权限（增量应用走逻辑解码，不检查 SELECT，但初始同步检查）：

sql

GRANT SELECT ON public.orders, public.order_items TO repl;

发布端 wal_level = logical 并重启（max_replication_slots、max_wal_senders 留足余量）。

发布端权限只查一次

发布端对连接角色的权限检查只发生在连接建立时，之后逐条变更不再复查。这意味着连接建立后再回收 repl 的 SELECT 权限，已建立的复制会话仍会继续工作。

创建期参数：connect / create_slot / enabled / slot_name

这四个参数决定「建订阅那一刻发生什么」：

create_slot = true（默认）：命令直接在发布端建逻辑槽。此时 CREATE SUBSCRIPTION 不能在事务块内执行——建槽与本地目录更新需要独立的事务边界，在 BEGIN ... COMMIT 里跑会直接报错。
create_slot = false：槽由你自己事先建好（或复用已有槽），配合 slot_name 指定。
enabled = false：只登记定义、不起 apply worker，适合「先铺拓扑、择日启用」。
connect = false：完全不连接发布端（用于发布端暂时不可达的预案），此时 create_slot、enabled、copy_data 都被强制为 false，且没有任何表被订阅；之后需要手工建槽、启用订阅并 REFRESH PUBLICATION。

同集簇自订阅必须 create_slot = false

在同一个 PostgreSQL 集簇内（甚至同一数据库内）做订阅，默认的 CREATE SUBSCRIPTION 会「连自己建槽」而永久挂起。正确做法是先手工建槽再引用：

sql

SELECT * FROM pg_create_logical_replication_slot('sub_self_slot', 'pgoutput');

CREATE SUBSCRIPTION sub_self
    CONNECTION 'host=127.0.0.1 port=5432 dbname=app user=postgres'
    PUBLICATION pub_self
    WITH (create_slot = false, slot_name = 'sub_self_slot', copy_data = false);

注意初始数据同步在同库场景同样无法进行，只能 copy_data = false。

slot_name = NONE 与延迟建槽

slot_name = NONE 表示订阅不关联任何复制槽，此时必须同时 enabled = false、create_slot = false。它的典型用途是「延迟建槽」：先在订阅端把订阅定义登记好（纳入配置管理），等发布端就绪后再手工建槽、把 slot_name 指过去、刷新并启用。反过来它也是「解绑槽」的标准手段——想在删除订阅时保留发布端的槽（例如下游要做停机迁移），先执行：

sql

ALTER SUBSCRIPTION sub_orders SET (slot_name = NONE);
DROP SUBSCRIPTION sub_orders;
-- 发布端的 sub_orders 槽被保留，可交给新的订阅或手工消费

copy_data 与初始数据同步

copy_data = true（默认）时，订阅建立后会为每张目标表启动 table sync worker 做全量拷贝，再追平增量进入稳态。要点：

并行度由订阅端 GUC max_sync_workers_per_subscription（默认 2）控制；
初始拷贝不应用 publish 过滤：即使发布只开 insert，历史数据照样整表搬过去；
行过滤在初始拷贝时生效（订阅端 ≥ PG15），只拷贝满足 WHERE 的行；
列清单在初始拷贝时生效（订阅端 ≥ PG15）；
每张表的同步状态记录在 pg_subscription_rel.srsubstate：i（初始化）、d（拷贝中）、f（单表拷贝完成）、s（已同步）、r（就绪）；
REFRESH PUBLICATION 刷新进来的新表也会走一遍初始同步（copy_data 默认 true）；已订阅过的表不会因为行过滤被改过而重新拷贝。

streaming：流式与并行应用（PG14/PG16）

streaming 决定「发布端还在跑的大事务」如何到达订阅端：

取值	发布端行为	订阅端行为	版本
`off`	事务完整解码、缓存在内存（`logical_decoding_work_mem`，不足则落盘），提交后才整体发送	串行应用	PG14 起可用
`on`	事务进行中就开始分批发送	变更先写临时文件，等发布端提交后再一次性应用	PG14 起可用
`parallel`	同 `on`	若有空闲的 parallel apply worker 则并行应用，否则退化为临时文件方案	PG16+，PG18 起为默认值

并行应用由订阅端 GUC max_parallel_apply_workers_per_subscription（默认 2）控制。parallel 带来一个排障层面的副作用：parallel apply worker 中出错的远端事务，其 finish LSN 可能不会写进服务器日志，而这正是 ALTER SUBSCRIPTION ... SKIP 需要的值。遇到这种情况，把 streaming 临时改成 on 或 off 重现冲突拿到 LSN，再改回来。

binary：二进制传输格式（PG14+）

binary = true 让发布端以各类型的二进制表示发送数据，省去文本序列化与反序列化，对大数值、时间戳密集的负载能降低两端 CPU 开销。代价是两端类型必须严格匹配：文本格式下订阅端列类型只需「可转换」（例如发布端 integer、订阅端 bigint 可以工作），二进制格式则要求一致，否则应用报错。

两条边界要记住：

初始数据同步不一定走二进制：订阅端低于 PG16 时，即使订阅开了 binary，初始拷贝仍按文本格式进行；
平台差异由 PostgreSQL 处理，跨平台订阅（如 Linux → Windows）是官方明确支持的用法。

synchronous_commit 与 apply 落盘

订阅参数 synchronous_commit 只覆盖 apply worker 会话内的同名 GUC，默认 off。官方明确：对普通逻辑复制用 off 是安全的——订阅端即使因崩溃丢掉最后一点已应用数据，发布端也会重发。只有当订阅被用作同步逻辑复制的一环（发布端等待订阅端 flush）时，才建议调到 local 或更高，否则发布端 COMMIT 延迟会变大。

two_phase 两阶段提交（PG15+）

two_phase = true 时，发布端的 PREPARE TRANSACTION 会在 PREPARE 时就同步给订阅端，并在订阅端同样以两阶段方式处理；默认（false）则是等到 COMMIT PREPARED 才把整个事务发过去、一次性应用。

subtwophasestate 的三个状态：

值	含义
`d`	disabled：订阅禁用（或 `two_phase` 关闭）
`p`	pending：两阶段已请求，等待初始同步完成
`e`	enabled：两阶段复制生效中

关键约束：

两阶段状态存储在 pg_subscription.subtwophasestate：初始同步完成前是 pending（p），完成后才真正 enabled（e）；
two_phase 与 failover 参数只能在订阅禁用时修改；SET (two_phase = false) 与 REFRESH PUBLICATION 不能在事务块内执行；
two_phase = true 的订阅做 REFRESH PUBLICATION / SET/ADD/DROP PUBLICATION（隐含刷新）时，必须带 copy_data = false；
把 two_phase 从 true 改回 false 时，如果订阅端还残留着逻辑复制 worker PREPARE 的事务会报错；这些事务的 GID 形如 pg_gid_<订阅oid>_<远端xid>，可用 pg_prepared_xacts 找到并手工 ROLLBACK PREPARED 后重试；
ALTER SUBSCRIPTION ... SKIP 对已被 PREPARE 的两阶段事务无效。

origin 与防回环

每个订阅在订阅端对应一个复制原点（名字形如 pg_<订阅oid>）。订阅参数 origin 控制订阅端要求发布端只发什么来源的变更：

origin = any（默认）：不管变更来自哪里都发送；
origin = none：只发送没有 origin 标记的变更，即发布端「本地产生」的变更；从更上游同步来的变更不再转发。

origin = none 是构建级联、环形、双向拓扑防回环的基础开关。配合 copy_data = true 使用时官方有一个明确提醒：初始数据是从发布端直接拷贝的，其真实来源无从追溯——如果发布端自己也是别人的订阅端，拷来的数据可能带着更上游的「血统」，此时 PostgreSQL 只会记一条 WARNING，甄别责任在使用者。

一个三级级联链路的典型配置：

sql

-- 节点 A（源）：本地写入，无需特殊配置
CREATE PUBLICATION pub_a FOR TABLE public.t;

-- 节点 B：订阅 A，并向 C 分发
CREATE SUBSCRIPTION sub_b_from_a
    CONNECTION 'host=a ...' PUBLICATION pub_a
    WITH (copy_data = true, origin = none);   -- 只收 A 的本地变更

CREATE PUBLICATION pub_b FOR TABLE public.t;

-- 节点 C：同样只收「本地于 B」的变更（即源自 A 的数据）
CREATE SUBSCRIPTION sub_c_from_b
    CONNECTION 'host=b ...' PUBLICATION pub_b
    WITH (copy_data = true, origin = none);

每一跳都带 origin = none，变更就沿 A → B → C 单向流动；即使拓扑里再出现反向链路，已转发过的变更也不会回流成环。

disable_on_error 与冲突应对

apply worker 应用变更时若违反订阅端约束（典型如唯一键冲突），复制会停在该错误上并反复重试，订阅端日志持续报错。两种应对：

disable_on_error = true：出错后自动把订阅置为 disabled，避免日志风暴，等人工处理后再 ALTER SUBSCRIPTION ... ENABLE；
默认（false）：订阅保持启用、apply worker 循环重试同一个事务。

错误日志会给出冲突事务的 finish LSN 与复制原点名，可用 ALTER SUBSCRIPTION ... SKIP (lsn = '...') 跳过整个事务，或用 pg_replication_origin_advance() 推进原点。详见本页实战示例二与逻辑复制限制中的冲突清单。

run_as_owner 与订阅端权限模型（PG16+）

默认（run_as_owner = false）的权限模型是「双层切换」：

apply worker 在会话级始终以订阅 owner 的权限运行；
但对某张表执行 DML 时，会临时切换到该表的 owner 身份执行；
因此订阅 owner 必须能 SET ROLE 到每一张目标表的 owner。这个模型让各表的权限检查各归其位，更安全。

run_as_owner = true（PG16 引入）则完全以订阅 owner 执行所有操作：订阅 owner 只需要对目标表有 SELECT / INSERT / UPDATE / DELETE 权限，不再需要 SET ROLE。但官方安全文档给出的警告很直接：任何拥有目标表的角色都可以通过挂触发器等方式，以订阅 owner 的权限执行任意代码。除非数据库内的用户安全无足轻重，否则不要开启。

订阅生命周期管理（ALTER SUBSCRIPTION）

ALTER SUBSCRIPTION 是订阅端运维的主战场，全部子动作如下：

sql

-- 换连接串（例如发布端迁移后改地址）
ALTER SUBSCRIPTION sub_orders CONNECTION 'host=10.0.0.9 port=5432 dbname=app user=repl password=new-secret';

-- 调整订阅的发布清单
ALTER SUBSCRIPTION sub_orders SET PUBLICATION pub_orders_dw;          -- 整体替换
ALTER SUBSCRIPTION sub_orders ADD PUBLICATION pub_audit_archive;      -- 追加
ALTER SUBSCRIPTION sub_orders DROP PUBLICATION pub_audit_archive;     -- 移除
-- 以上默认隐含一次 REFRESH；WITH (refresh = false) 可关掉，稍后手工刷新

-- 刷新表清单：把发布端新增的表纳入订阅（新表会做初始同步）
ALTER SUBSCRIPTION sub_orders REFRESH PUBLICATION;
ALTER SUBSCRIPTION sub_orders REFRESH PUBLICATION WITH (copy_data = false);

-- 启用 / 禁用
ALTER SUBSCRIPTION sub_orders ENABLE;
ALTER SUBSCRIPTION sub_orders DISABLE;

-- 修改运行参数（可改：slot_name、synchronous_commit、binary、streaming、
-- disable_on_error、password_required、run_as_owner、origin、failover、two_phase）
ALTER SUBSCRIPTION sub_orders SET (streaming = parallel);
ALTER SUBSCRIPTION sub_orders SET (disable_on_error = true);

-- 跳过卡住的事务（PG15+）
ALTER SUBSCRIPTION sub_orders SKIP (lsn = '0/14C0378');
ALTER SUBSCRIPTION sub_orders SKIP (lsn = NONE);   -- 清除待跳过标记

-- 改名 / 转移所有权
ALTER SUBSCRIPTION sub_orders RENAME TO sub_orders_v2;
ALTER SUBSCRIPTION sub_orders OWNER TO repl_admin;

几条硬规则要背下来：

REFRESH PUBLICATION、带隐含刷新的 SET/ADD/DROP PUBLICATION、SET (two_phase = false)、SET (failover = ...) 不能在事务块内执行；
two_phase、failover 只能在订阅禁用时修改；
REFRESH PUBLICATION 会顺带清理不再属于发布的表及其遗留的同步槽；若因故障删不掉远端槽会报错，重试或先 SET (slot_name = NONE) 再删订阅。

SET/ADD/DROP PUBLICATION 默认隐含一次刷新（refresh = true），也可以拆开做：

sql

-- 先改清单但不刷新（适合先演练、后生效）
ALTER SUBSCRIPTION sub_orders SET PUBLICATION pub_orders_dw, pub_audit_archive
    WITH (refresh = false);

-- 确认无误后再统一刷新（新纳入的表会开始初始同步）
ALTER SUBSCRIPTION sub_orders REFRESH PUBLICATION;

拆开做的好处是「发布端变更」与「订阅端生效」可以拆成两个变更动作，各自评审、各自回滚。

删除订阅与复制槽的善后

DROP SUBSCRIPTION 的默认行为是「先到发布端删槽、再删本地订阅」，因此有关联槽时不能在事务块内执行，且发布端必须可达。两种特殊情形的处理：

sql

-- 情形一：发布端已下线（实例迁移、报废），直接 DROP 会因删槽失败而报错
-- 先解除槽关联，再删订阅：
ALTER SUBSCRIPTION sub_orders SET (slot_name = NONE);
DROP SUBSCRIPTION sub_orders;

-- 情形二：想保留发布端的槽（把进度锚点交给接替的新订阅）
ALTER SUBSCRIPTION sub_orders SET (slot_name = NONE);
DROP SUBSCRIPTION sub_orders;   -- 槽保留，可由新订阅以 create_slot = false 复用

-- 发布端遗留槽的手工清理
SELECT pg_drop_replication_slot('sub_orders');

反过来，槽被误删而订阅仍在时，订阅失去了进度锚点，恢复无望只能重建（槽机制详见复制槽）。

订阅端的表结构要求

订阅端目标表不是「随便建建就行」，官方给出的兼容性边界：

订阅端表至少要包含所有被发布的列（列清单场景则是被列出的列），列的顺序与数量可以不同；
列类型需兼容：不要求完全相同，但必须可按文本（或二进制）方式转换，例如发布端 integer、订阅端 bigint 可以工作；
订阅端可以有发布端没有的额外列，但带 NOT NULL 的额外列必须有默认值——发布端不会为它提供数据；
发布端复制标识不是 FULL 时，订阅端必须定义「相同或更少列」的复制标识；发布端用 FULL 时，订阅端的行定位索引要求是 B-tree 或 Hash、非部分、最左字段为普通列；
最省心的初始化方式仍是 pg_dump --schema-only 生成两端一致的结构，再按需调整。

监控视图

视图	位置	用途
`pg_subscription`	订阅端目录	所有订阅定义：`subname`、`subowner`、`subenabled`、`subslotname`、`subpublications`、`substream`、`subtwophasestate`、`subdisableonerror`、`subrunasowner`、`subfailover`、`subskiplsn` 等；连接串 `subconninfo` 仅超级用户可见
`pg_subscription_rel`	订阅端目录	每张目标表的同步状态（`srsubstate`）
`pg_stat_subscription`	订阅端统计	每个订阅 worker 一行：`subid`、`subname`、`worker_type`（apply / sync / parallel apply）、`pid`、`leader_pid`、`relid`、`received_lsn`、`last_msg_send_time`、`last_msg_receipt_time`、`latest_end_lsn`、`latest_end_time`。订阅被禁用或崩溃时没有行
`pg_stat_subscription_stats`	订阅端统计（PG15+）	错误计数：`subid`、`subname`、`apply_error_count`、`sync_error_count`，以及 PG17 起的各 `confl_*` 冲突计数器
`pg_stat_replication`	发布端	每条订阅连接对应一个 walsender，`application_name` 即订阅侧标识
`pg_replication_slots`	发布端	逻辑槽的 `active`、`restart_lsn`、`confirmed_flush_lsn`、滞留 WAL 量

sql

-- 订阅端：worker 存活与进度
SELECT subname, worker_type, pid, received_lsn, latest_end_lsn, latest_end_time
FROM pg_stat_subscription
ORDER BY subname, worker_type;

-- 订阅端：错误与冲突计数（非 0 即需关注）
SELECT subname, apply_error_count, sync_error_count
FROM pg_stat_subscription_stats;

-- 订阅端：每张表的同步状态
SELECT srsubid::regclass AS subscription, srrelid::regclass AS table, srsubstate
FROM pg_subscription_rel;

-- 发布端：槽滞留
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
WHERE slot_type = 'logical';

实战示例

示例一：完整搭建一条逻辑复制链路

目标：把发布端（192.168.1.50:5432，库 app）的 orders、order_items 两张表同步到订阅端（本机 5433，库 app），含历史数据。

第一步：发布端配置并重启。

conf

# postgresql.conf（发布端）
wal_level = logical
max_replication_slots = 10    # 订阅槽 + 初始同步临时槽的余量
max_wal_senders = 10          # 订阅连接 + 物理复制的余量

bash

# 重启生效（wal_level 不能 reload）
pg_ctl restart -D /data/pg18

第二步：发布端角色与发布。

sql

CREATE ROLE repl LOGIN REPLICATION PASSWORD 'repl-secret';
GRANT CONNECT ON DATABASE app TO repl;
GRANT USAGE ON SCHEMA public TO repl;
GRANT SELECT ON public.orders, public.order_items TO repl;

CREATE PUBLICATION pub_orders_dw FOR TABLE public.orders, public.order_items;

第三步：订阅端同步表结构。 逻辑复制不复制 DDL，先用 schema-only 导出初始化：

bash

pg_dump -h 192.168.1.50 -p 5432 -U postgres -d app \
    --schema-only --no-owner --no-privileges -t orders -t order_items \
    | psql -h 127.0.0.1 -p 5433 -U postgres -d app

第四步：订阅端配置（如未设置）。

conf

# postgresql.conf（订阅端）
max_worker_processes = 16                  # 所有后台 worker 的总池
max_logical_replication_workers = 8        # apply + sync worker 总数
max_sync_workers_per_subscription = 2      # 单订阅初始同步并行度

第五步：创建订阅。

sql

CREATE SUBSCRIPTION sub_orders_dw
    CONNECTION 'host=192.168.1.50 port=5432 dbname=app user=repl password=repl-secret application_name=sub_orders_dw'
    PUBLICATION pub_orders_dw
    WITH (copy_data = true, streaming = on);

text

NOTICE:  created replication slot "sub_orders_dw" on publisher
CREATE SUBSCRIPTION

第六步：验证初始同步与增量。

sql

-- 订阅端：等所有表状态变为 r（ready）
SELECT srrelid::regclass AS table, srsubstate FROM pg_subscription_rel;

text

        table        | srsubstate
---------------------+------------
 public.orders       | r
 public.order_items  | r
(2 rows)

bash

# 发布端写入增量数据
psql -h 192.168.1.50 -p 5432 -U postgres -d app \
    -c "INSERT INTO public.orders (user_id, amount) VALUES (42, 199.00) RETURNING order_id;"

sql

-- 订阅端确认到达
SELECT order_id, user_id, amount, status FROM public.orders ORDER BY order_id DESC LIMIT 1;

sql

-- 两端进度核对
-- 订阅端：
SELECT subname, worker_type, received_lsn, latest_end_lsn FROM pg_stat_subscription;
-- 发布端：
SELECT application_name, state, sent_lsn FROM pg_stat_replication WHERE application_name = 'sub_orders_dw';

第七步：发布端侧观察。 复制链路是「双向可见」的：订阅端看 worker，发布端看连接与槽。

sql

-- 发布端：订阅连接（walsender）与逻辑槽状态
SELECT application_name, state, sent_lsn, write_lsn, flush_lsn
FROM pg_stat_replication
WHERE application_name = 'sub_orders_dw';

SELECT slot_name, active, restart_lsn, confirmed_flush_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
WHERE slot_name = 'sub_orders_dw';

text

 application_name |   state   |  sent_lsn  |  write_lsn |  flush_lsn
------------------+-----------+------------+------------+------------
 sub_orders_dw    | streaming | 0/2A3B7C00 | 0/2A3B7C00 | 0/2A3B7C00

    slot_name   | active | restart_lsn | confirmed_flush_lsn | retained_wal
----------------+--------+-------------+---------------------+--------------
 sub_orders_dw  | t      | 0/2A3B6F00  | 0/2A3B7C00          | 0 bytes

retained_wal 长期增长说明订阅端消费停滞，是发布端磁盘风险的第一信号。

示例二：跳过冲突事务

订阅端日志出现：

text

ERROR:  conflict detected on relation "public.orders": conflict=insert_exists
DETAIL:  Key already exists in unique index "orders_pkey", which was modified locally in transaction 740 at 2026-09-30 10:47:04.727375+08.
Key (order_id)=(1001); existing local row (1001, 42, 199.00, local); remote row (1001, 42, 199.00, paid).
CONTEXT:  processing remote data for replication origin "pg_16395" during "INSERT" for replication target relation "public.orders" in transaction 725 finished at 0/14C0378

处理流程：

sql

-- 1. 先弄清业务上该保留哪边。若决定以发布端为准：
--    修正订阅端本地行，让远端事务可以重新应用（首选方案）
UPDATE public.orders SET status = 'paid' WHERE order_id = 1001;

-- 2. 若确认要丢弃整个远端事务，按日志里的 finish LSN 跳过：
ALTER SUBSCRIPTION sub_orders_dw SKIP (lsn = '0/14C0378');

-- 3. 确认复制恢复推进
SELECT subname, received_lsn, latest_end_lsn FROM pg_stat_subscription
WHERE subname = 'sub_orders_dw' AND worker_type = 'apply';

SKIP 会丢掉整个事务

SKIP 跳过的是整个远端事务，包括其中不违反约束的其余变更，可能让订阅端与发布端出现难以察觉的数据偏差。优先修复数据让事务自然重放；SKIP 是兜底，用一次记一次账。

示例三：排错清单

现象	常见原因	处理
`CREATE SUBSCRIPTION` 报 `logical decoding requires wal_level >= logical`	发布端 `wal_level` 未调整	发布端设 `wal_level = logical` 并重启
`no pg_hba.conf entry for replication connection` / 认证失败	pg_hba 缺条目或密码错	按普通连接条目放行订阅端网段；核对连接串密码
`publication "xxx" does not exist`	发布名拼错、或连接的库不对	发布是数据库级对象，核对 `dbname` 与发布名
`permission denied for table xxx`（初始同步阶段）	复制角色缺 SELECT	`GRANT SELECT` 于所有发布表
`replication slot "xxx" already exists`	同名槽已存在	复用：`create_slot = false` + `slot_name = 已有槽名`
`CREATE SUBSCRIPTION` 永久挂起	在同一集簇内订阅自己	先手工 `pg_create_logical_replication_slot(..., 'pgoutput')`，再 `create_slot = false`
建订阅报「cannot be executed inside a transaction block」	在 `BEGIN` 块里执行了带建槽的 CREATE/REFRESH	移出事务块单独执行
初始同步迟迟不结束	`max_sync_workers_per_subscription` 太小 / 发布端槽余量不足 / 大表拷贝中	查 `pg_subscription_rel` 状态与发布端 `pg_replication_slots`；低峰提高并行度
新加的表没有同步	发布端 `ADD TABLE` 后未刷新订阅	`ALTER SUBSCRIPTION ... REFRESH PUBLICATION`
日志反复报同一冲突，复制停滞	唯一键/外键冲突或权限问题	按日志 LSN 修数或 `SKIP`；考虑 `disable_on_error = true`
发布端 `pg_wal` 持续增长	订阅长期离线，逻辑槽滞留 WAL	监控槽滞留量；必要时设 `max_slot_wal_keep_size` 兜底
表状态停在 `i` / `d` 不动	worker 配额不足	提高 `max_logical_replication_workers` / `max_sync_workers_per_subscription`，或分批初始化
`pg_stat_subscription` 看不到订阅的行	订阅被 `disable_on_error` 自动禁用，或被手工 DISABLE	`ALTER SUBSCRIPTION ... ENABLE`；核对 `pg_subscription.subenabled`
发布端 `pg_stat_replication` 分不清连接属于哪个订阅	连接串未设置 `application_name`	连接串加 `application_name=订阅名`，与槽名保持一致
切换后主键/序列冲突	序列状态未被复制	切换前手工 `setval()` 同步序列（见限制页）

注意事项

连接串里的密码是敏感数据。 subconninfo 原样存进 pg_subscription，仅超级用户可读；且 PG16 起 password_required = true（默认）强制连接必须带密码认证。生产上应给复制角色强密码、最小权限，并通过网络隔离控制谁能连到发布端。

订阅名 = 默认槽名。 两者在创建时绑定，改名订阅不会改槽名。给订阅起名时就要按槽命名规范来，避免「订阅叫 A、槽叫 B」的管理混乱。

REFRESH PUBLICATION 会触发新表的初始同步。 在大表场景这等于一次隐性全量拷贝，请在低峰执行，并确认发布端 max_replication_slots 有余量（同步中的每张表占用一个临时槽）。

apply worker 的执行上下文特殊。 增量应用默认不触发普通用户触发器（session_replication_role = replica），初始拷贝阶段则会触发行级与语句级 INSERT 触发器。在订阅端做审计或级联逻辑时务必区分这两个阶段。

订阅端目标表不要被业务写入。 订阅表保持「事实只读」是避免冲突的根本手段；确需双写的场景先读逻辑复制限制中的冲突章节。

worker 数量都从 max_worker_processes 池子里出。 apply、sync、parallel apply worker 加上物理复制的进程共享一个池，池子太小会出现 worker 起不来的静默等待。

删除订阅与槽的归属要提前想好。 默认 DROP SUBSCRIPTION 会连带删除发布端槽；要保留槽先 SET (slot_name = NONE)。反过来，槽被手工删除后订阅就失去了进度锚点，只能重建。

pg_stat_subscription 没有行 ≠ 订阅不存在。 被禁用或崩溃的订阅在该视图中是零行，定义仍留在 pg_subscription。做存活巡检要两张视图对照：pg_subscription 看有哪些订阅，pg_stat_subscription 看哪些在运行。

连接串里显式设置 application_name。 它同时是发布端 pg_stat_replication 里识别订阅的身份，没有它，多订阅场景的排障效率会显著下降。

最佳实践

权限模型记三层：发布端连接角色（REPLICATION + LOGIN + 发布表 SELECT）、订阅端创建者（pg_create_subscription + 数据库 CREATE，PG16+）、订阅 owner（能 SET ROLE 到各表 owner 或显式 run_as_owner）。
用 enabled = false 先铺拓扑：把订阅定义纳入配置管理，发布端就绪后再启用，避免「边建边试」留下半成品。
初始同步安排低峰，按表规模评估 max_sync_workers_per_subscription，同步期间盯住 pg_subscription_rel 与发布端槽数量。
给订阅显式指定 slot_name（而不是依赖默认同名），让槽命名独立于订阅命名，便于跨环境对账。
生产订阅建议 disable_on_error = true：出问题自动熔断比无限重试刷日志更可控，配合告警人工介入。
冲突预案先行：为每个订阅记录复制原点名（pg_<oid>），演练一遍「修数重放」与「SKIP」两条路径，把 finish LSN 的位置写进 runbook。
大事务多的链路开 streaming = parallel（PG18 默认已开），但要理解它对 SKIP 排障的影响；发布端内存紧张时优先 on 而不是 off。
监控三件套缺一不可：pg_stat_subscription（worker 存活与 LSN 推进）、pg_stat_subscription_stats（apply_error_count / sync_error_count / confl_*）、pg_replication_slots（滞留 WAL）。
发布端变更走「改发布 → 刷新订阅 → 核对表清单」三步，任何一步都不要省，并核对 pg_publication_tables 与 pg_subscription_rel。
two_phase 只在确有 PREPARE TRANSACTION 的业务里开启，并记住它的状态机（pending → enabled）与「禁用态才能改」的限制。
同集簇自订阅、发布端不可达预案这类非常规场景，提前在测试环境演练 create_slot = false / connect = false 的完整流程，不要到生产才第一次试。
切换（switchover）前同步序列：逻辑复制不搬序列状态，接管前用发布端实时值对订阅端执行 setval()，写成脚本纳入切换手册。

继续学习：逻辑复制限制

订阅（CREATE SUBSCRIPTION） ​

基础概念 ​

语法与用法 ​

详细说明 ​

订阅端进程模型与 worker 池 ​

连接准备：角色、pg_hba.conf 与网络 ​

创建期参数：connect / create_slot / enabled / slot_name ​

slot_name = NONE 与延迟建槽 ​

copy_data 与初始数据同步 ​

streaming：流式与并行应用（PG14/PG16） ​

binary：二进制传输格式（PG14+） ​

synchronous_commit 与 apply 落盘 ​

two_phase 两阶段提交（PG15+） ​

origin 与防回环 ​

disable_on_error 与冲突应对 ​

run_as_owner 与订阅端权限模型（PG16+） ​

订阅生命周期管理（ALTER SUBSCRIPTION） ​

删除订阅与复制槽的善后 ​

订阅端的表结构要求 ​

监控视图 ​

实战示例 ​

示例一：完整搭建一条逻辑复制链路 ​

示例二：跳过冲突事务 ​

订阅（CREATE SUBSCRIPTION）

基础概念

语法与用法

详细说明

订阅端进程模型与 worker 池

连接准备：角色、pg_hba.conf 与网络

创建期参数：connect / create_slot / enabled / slot_name

slot_name = NONE 与延迟建槽

copy_data 与初始数据同步

streaming：流式与并行应用（PG14/PG16）

binary：二进制传输格式（PG14+）

synchronous_commit 与 apply 落盘

two_phase 两阶段提交（PG15+）

origin 与防回环

disable_on_error 与冲突应对

run_as_owner 与订阅端权限模型（PG16+）

订阅生命周期管理（ALTER SUBSCRIPTION）

删除订阅与复制槽的善后

订阅端的表结构要求

监控视图

实战示例

示例一：完整搭建一条逻辑复制链路

示例二：跳过冲突事务