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

光蛋
30 阅读7分钟

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

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

一、前置条件配置

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

参数名配置值说明
wal_levellogical开启逻辑解码所需的 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 阶段。
avatar
文章
阅读
粉丝