PostgreSQL 逻辑解码（Logical Decoding）实操指南

逻辑解码是 PostgreSQL 提供的核心功能，可将数据库的 WAL（Write-Ahead Log）日志转换为人类可读/机器可解析的格式，常用于数据同步、审计、变更捕获等场景。本文通过实操示例，详细讲解逻辑解码的两种核心控制方式（SQL 接口、流复制协议），以及两阶段事务的解码方法。

一、前置条件配置

使用逻辑解码前，必须完成以下参数配置（修改 postgresql.conf 后重启数据库生效）：

参数名	配置值	说明
wal_level	logical	开启逻辑解码所需的 WAL 级别（默认是 replica，不支持逻辑解码）
max_replication_slots	≥ 1	逻辑解码依赖复制槽，需至少分配 1 个槽位
max_wal_senders	≥ 1	使用流复制协议（pg_recvlogical）时，需预留 WAL 发送进程
max_prepared_transactions	≥ 1	解码两阶段事务时必填

权限要求：操作逻辑解码需以超级用户（superuser）身份连接目标数据库。

二、通过 SQL 接口控制逻辑解码

SQL 接口是最直观的逻辑解码方式，核心依赖 pg_create_logical_replication_slot、pg_logical_slot_get_changes 等系统函数。

2.1 创建逻辑复制槽

使用 test_decoding 插件（PostgreSQL 内置）创建名为 regression_slot 的复制槽：

-- 创建复制槽：参数依次为槽名、输出插件、临时槽（false）、支持两阶段（true） SELECT * FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding', false, true);

执行结果：

slot_name | lsn -----------------+----------- regression_slot | 0/16B1970 (1 row)

2.2 查看复制槽信息

通过系统视图 pg_replication_slots 验证复制槽创建结果：

SELECT slot_name, plugin, slot_type, database, active, restart_lsn, confirmed_flush_lsn FROM pg_replication_slots;

执行结果：

slot_name | plugin | slot_type | database | active | restart_lsn | confirmed_flush_lsn -----------------+---------------+-----------+----------+--------+-------------+----------------- regression_slot | test_decoding | logical | postgres | f | 0/16A4408 | 0/16A4440 (1 row)

2.3 解码数据库变更

（1）解码 DDL 操作（无实际数据变更）

逻辑解码不复制 DDL 本身，仅记录 DDL 对应的事务起止：

-- 无变更时，解码结果为空 SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL); -- 创建测试表（DDL 操作） CREATE TABLE data(id serial primary key, data text); -- 解码 DDL 对应的事务 SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);

执行结果：

lsn | xid | data -----------+-------+-------------- 0/BA2DA58 | 10297 | BEGIN 10297 0/BA5A5A0 | 10297 | COMMIT 10297 (2 rows)

（2）解码 DML 操作（INSERT）

DML 操作会被完整解码，包含具体的变更内容：

-- 执行 INSERT 事务 BEGIN; INSERT INTO data(data) VALUES('1'); INSERT INTO data(data) VALUES('2'); COMMIT; -- 解码 DML 变更 SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);

执行结果：

lsn | xid | data -----------+-------+--------------------------------------------------------- 0/BA5A688 | 10298 | BEGIN 10298 0/BA5A6F0 | 10298 | table public.data: INSERT: id[integer]:1 data[text]:'1' 0/BA5A7F8 | 10298 | table public.data: INSERT: id[integer]:2 data[text]:'2' 0/BA5A8A8 | 10298 | COMMIT 10298 (4 rows)

2.4 消费型解码 vs 预览型解码

函数	特性	适用场景
pg_logical_slot_get_changes	读取后消费变更（不可重复读取）	正式消费变更数据
pg_logical_slot_peek_changes	读取后不消费变更（可重复读取）	调试、预览变更内容

预览型解码示例：

-- 插入测试数据 INSERT INTO data(data) VALUES('3'); -- 第一次预览（返回变更） SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL); -- 第二次预览（仍返回相同变更） SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL); -- 传递插件参数（如包含时间戳） SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL, 'include-timestamp', 'on');

带时间戳的执行结果：

lsn | xid | data -----------+-------+--------------------------------------------------------- 0/BA5A8E0 | 10299 | BEGIN 10299 0/BA5A8E0 | 10299 | table public.data: INSERT: id[integer]:3 data[text]:'3' 0/BA5A990 | 10299 | COMMIT 10299 (at 2017-05-10 12:07:21.272494-04) (3 rows)

2.5 删除复制槽

不再使用的复制槽需手动删除，避免占用数据库资源：

SELECT pg_drop_replication_slot('regression_slot');

三、通过流复制协议（pg_recvlogical）控制逻辑解码

pg_recvlogical 是 PostgreSQL 内置的命令行工具，通过流复制协议实时消费逻辑解码数据，适合生产环境的持续同步场景。

3.1 基础示例（普通事务）

# 1. 创建复制槽 pg_recvlogical -d postgres --slot=test --create-slot # 2. 启动实时解码（-f - 表示输出到标准输出） pg_recvlogical -d postgres --slot=test --start -f - # 3. （另一个终端）插入测试数据 psql -d postgres -c "INSERT INTO data(data) VALUES('4');" # 4. 回到解码终端，可看到实时输出： # BEGIN 693 # table public.data: INSERT: id[integer]:4 data[text]:'4' # COMMIT 693 # 5. 停止解码（Ctrl+C），删除复制槽 pg_recvlogical -d postgres --slot=test --drop-slot

3.2 两阶段事务解码示例

两阶段事务（PREPARE/COMMIT PREPARED）需在创建复制槽时指定 --two-phase：

# 1. 创建支持两阶段的复制槽 pg_recvlogical -d postgres --slot=test --create-slot --two-phase # 2. 启动实时解码 pg_recvlogical -d postgres --slot=test --start -f - # 3. （另一个终端）执行两阶段事务 psql -d postgres -c "BEGIN;INSERT INTO data(data) VALUES('5');PREPARE TRANSACTION 'test';" # 4. 解码终端输出： # BEGIN 694 # table public.data: INSERT: id[integer]:5 data[text]:'5' # PREPARE TRANSACTION 'test', txid 694 # 5. （另一个终端）提交已准备的事务 psql -d postgres -c "COMMIT PREPARED 'test';" # 6. 解码终端输出： # COMMIT PREPARED 'test', txid 694 # 7. 停止解码，删除复制槽 pg_recvlogical -d postgres --slot=test --drop-slot

四、两阶段事务的 SQL 接口解码

除 pg_recvlogical 外，也可通过 SQL 接口解码两阶段事务，核心是创建复制槽时开启 two-phase 参数。

4.1 提交已准备的事务

-- 执行两阶段事务 BEGIN; INSERT INTO data(data) VALUES('5'); PREPARE TRANSACTION 'test_prepared1'; -- 解码 PREPARE 阶段 SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);

执行结果：

lsn | xid | data -----------+-----+--------------------------------------------------------- 0/1689DC0 | 529 | BEGIN 529 0/1689DC0 | 529 | table public.data: INSERT: id[integer]:3 data[text]:'5' 0/1689FC0 | 529 | PREPARE TRANSACTION 'test_prepared1', txid 529 (3 rows)

-- 提交已准备的事务 COMMIT PREPARED 'test_prepared1'; -- 解码 COMMIT 阶段 SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);

执行结果：

lsn | xid | data -----------+-----+-------------------------------------------- 0/168A060 | 529 | COMMIT PREPARED 'test_prepared1', txid 529 (1 row)

4.2 回滚已准备的事务

-- 执行两阶段事务 BEGIN; INSERT INTO data(data) VALUES('6'); PREPARE TRANSACTION 'test_prepared2'; -- 解码 PREPARE 阶段 SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);

执行结果：

lsn | xid | data -----------+-----+--------------------------------------------------------- 0/168A180 | 530 | BEGIN 530 0/168A1E8 | 530 | table public.data: INSERT: id[integer]:4 data[text]:'6' 0/168A430 | 530 | PREPARE TRANSACTION 'test_prepared2', txid 530 (3 rows)

-- 回滚已准备的事务 ROLLBACK PREPARED 'test_prepared2'; -- 解码 ROLLBACK 阶段 SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);

执行结果：

lsn | xid | data -----------+-----+---------------------------------------------- 0/168A4B8 | 530 | ROLLBACK PREPARED 'test_prepared2', txid 530 (1 row)

五、核心注意事项

1. 逻辑解码仅捕获事务性变更，DDL 操作仅记录事务起止，不包含 DDL 内容；
2. pg_logical_slot_get_changes 读取后会消费变更（不可重复读取），pg_logical_slot_peek_changes 仅预览（可重复读取）；
3. 复制槽需手动删除，否则会持续保留 WAL 日志，可能导致磁盘占满；
4. 两阶段事务解码需提前配置 max_prepared_transactions ≥ 1，且创建复制槽时开启 two-phase 参数。

总结

1. 逻辑解码的核心前提是配置 wal_level = logical 和足够的复制槽/ WAL 发送进程，且需超级用户权限；
2. 控制逻辑解码有两种方式：SQL 接口（适合调试/单次查询）、pg_recvlogical 流协议（适合实时消费）；
3. 两阶段事务解码需额外配置 max_prepared_transactions，并在创建复制槽时开启两阶段支持，可分别解码 PREPARE/COMMIT/ROLLBACK 阶段。