PostgreSQL 15 逻辑复制操作流程详解


 TL;DR
本文通过实际操作演示 PostgreSQL 15 逻辑复制的完整流程,重点展示: - WAL(Write-Ahead Logging)日志的生成与解析 - LSN(Log Sequence Number)的推进机制 - 事务(Transaction)与 WAL 的关系 - 快照(Snapshot)…
难度 中等

概述

本文通过实际操作演示 PostgreSQL 15 逻辑复制的完整流程,重点展示:

  • WAL(Write-Ahead Logging)日志的生成与解析
  • LSN(Log Sequence Number)的推进机制
  • 事务(Transaction)与 WAL 的关系
  • 快照(Snapshot)创建流程
  • DDL(数据定义语言)、DML(数据操作语言)、DQL(数据查询语言)在逻辑复制中的处理过程

通过开启 WAL 调试模式,我们可以直观地看到:

  1. 执行 DDL/DML 时写入的 WAL 日志内容
  2. 复制传输过程中 LSN 的推进过程
  3. 快照创建的具体步骤
  4. 逻辑复制过程中操作的具体文件和文件内容

环境准备

1. PostgreSQL 15 安装与配置

首先确保已安装 PostgreSQL 15。以下配置参数需在 postgresql.conf 中设置:

# 设置 WAL 级别为 logical,这是逻辑复制的前提
wal_level = logical

# 设置最大复制槽数量
max_replication_slots = 10

# 设置最大 WAL 发送进程数量
max_wal_senders = 10

# 设置热备模式,允许在备份时进行查询
hot_standby = on

# 设置最大连接数
max_connections = 100

# 设置共享内存大小
shared_buffers = 128MB

# 设置 WAL 日志大小
wal_buffers = 16MB

# 设置检查点间隔
checkpoint_timeout = 15min

重启 PostgreSQL 服务使配置生效:

# 根据系统不同,重启命令可能不同
sudo systemctl restart postgresql-15
# 或
pg_ctl restart -D /var/lib/pgsql/15/data

2. 创建测试数据库和用户

-- 创建测试数据库
CREATE DATABASE logical_test;

-- 切换到 logical_test 数据库
\c logical_test

-- 创建用于复制的用户
CREATE USER repl_user WITH REPLICATION LOGIN PASSWORD 'repl_password';

-- 授予必要权限
GRANT ALL PRIVILEGES ON DATABASE logical_test TO repl_user;

3. 准备测试环境

我们将使用两个数据库来模拟发布者(Publisher)和订阅者(Subscriber):

  • 发布者:logical_test 数据库
  • 订阅者:logical_sub 数据库(在同一实例或不同实例中)

创建订阅者数据库:

CREATE DATABASE logical_sub;

第一部分:WAL 日志基础

1.1 查看当前 WAL 状态

-- 查看当前 WAL LSN 位置
SELECT pg_current_wal_lsn();

-- 查看 WAL 文件名和 LSN 的对应关系
SELECT pg_walfile_name(pg_current_wal_lsn());

-- 查看 WAL 目录位置
SHOW data_directory;

-- 查看 WAL 相关配置
SHOW wal_level;
SHOW max_wal_size;
SHOW min_wal_size;

1.2 开启 WAL 调试

PostgreSQL 没有直接的 “WAL 调试模式”,但我们可以通过以下方式查看 WAL 内容:

  1. 使用 pg_waldump 工具:解析二进制 WAL 文件
  2. 设置 log_statement = ‘all’:记录所有 SQL 语句
  3. 使用 pg_logical 扩展:更详细的逻辑解码信息

在 postgresql.conf 中添加:

# 记录所有 SQL 语句
log_statement = 'all'

# 记录连接信息
log_connections = on

# 记录断开连接信息
log_disconnections = on

# 记录复制相关活动
log_replication_commands = on

重启服务后,可以在日志文件中看到详细的 SQL 执行信息。

第二部分:逻辑复制搭建

2.1 创建发布者(Publisher)

在 logical_test 数据库中:

-- 创建测试表
CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入初始数据
INSERT INTO test_table (name) VALUES ('初始数据1'), ('初始数据2'), ('初始数据3');

-- 创建发布
CREATE PUBLICATION test_pub FOR TABLE test_table;

-- 查看发布信息
SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

2.2 创建订阅者(Subscriber)

在 logical_sub 数据库中:

-- 创建相同的表结构(注意:不需要创建序列,逻辑复制会复制数据)
CREATE TABLE test_table (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP
);

-- 创建订阅
CREATE SUBSCRIPTION test_sub
CONNECTION 'dbname=logical_test host=localhost user=repl_user password=repl_password'
PUBLICATION test_pub
WITH (copy_data = true);

-- 查看订阅信息
SELECT * FROM pg_subscription;
SELECT * FROM pg_stat_subscription;

2.3 验证复制状态

-- 在发布者端查看复制槽
SELECT * FROM pg_replication_slots;

-- 查看复制统计信息
SELECT * FROM pg_stat_replication;

-- 查看当前复制进度
SELECT
    application_name,
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;

-- 在订阅者端查看数据是否已复制
SELECT * FROM test_table;

第三部分:DDL 操作与 WAL 日志分析

3.1 执行 DDL 操作

-- 在发布者端执行 DDL
ALTER TABLE test_table ADD COLUMN email VARCHAR(255);

-- 添加索引
CREATE INDEX idx_test_table_name ON test_table(name);

-- 修改列类型
ALTER TABLE test_table ALTER COLUMN name TYPE VARCHAR(200);

3.2 查看 DDL 对应的 WAL 日志

首先找到当前的 WAL 文件:

SELECT pg_walfile_name(pg_current_wal_lsn());

假设得到的 WAL 文件名为 000000010000000000000001,使用 pg_waldump 解析:

# 切换到 WAL 目录
cd /var/lib/pgsql/15/data/pg_wal

# 使用 pg_waldump 解析 WAL 文件
pg_waldump 000000010000000000000001 -p

# 如果要查看特定 LSN 范围的 WAL
pg_waldump 000000010000000000000001 -s 0/1000000 -e 0/2000000

3.3 分析 DDL 的 WAL 记录

DDL 操作在 WAL 中通常记录为:

  1. XLOG_SMGR_CREATE:创建文件
  2. XLOG_SMGR_TRUNCATE:截断文件
  3. XLOG_DBASE_CREATE:创建数据库
  4. XLOG_DBASE_DROP:删除数据库

对于表结构变更,WAL 会记录系统表(pg_class、pg_attribute等)的变更。

3.4 查看系统表变更

-- 查看最近的事务
SELECT * FROM pg_stat_activity WHERE backend_type = 'client backend';

-- 查看当前事务ID
SELECT txid_current();

-- 查看事务快照
SELECT pg_current_snapshot();

第四部分:DML 操作与 WAL 日志分析

4.1 执行 DML 操作

-- 记录当前 LSN
SELECT pg_current_wal_lsn() AS before_lsn;

-- 执行 INSERT
INSERT INTO test_table (name, email) VALUES ('新数据1', 'test1@example.com');

-- 执行 UPDATE
UPDATE test_table SET name = '更新后的数据' WHERE id = 1;

-- 执行 DELETE
DELETE FROM test_table WHERE id = 2;

-- 记录操作后的 LSN
SELECT pg_current_wal_lsn() AS after_lsn;

4.2 查看 DML 对应的 WAL 日志

使用 pg_waldump 查看 INSERT/UPDATE/DELETE 对应的 WAL 记录:

# 解析特定 LSN 范围的 WAL
pg_waldump 000000010000000000000001 -s 0/3000000 -e 0/4000000 -r heap

-r heap 参数只显示堆表相关的 WAL 记录。

4.3 WAL 记录结构分析

一个典型的 INSERT WAL 记录包含:

  • 事务ID(xid)
  • 关系OID(表的对象ID)
  • 插入的数据(tuple)
  • LSN(该记录在WAL中的位置)

UPDATE 和 DELETE 记录类似,但包含旧元组和新元组的信息。

4.4 查看逻辑解码输出

PostgreSQL 提供了逻辑解码的测试接口:

-- 创建逻辑复制槽(用于测试)
SELECT * FROM pg_create_logical_replication_slot('test_slot', 'test_decoding');

-- 执行一些 DML 操作
INSERT INTO test_table (name, email) VALUES ('解码测试', 'decode@example.com');

-- 查看逻辑解码输出
SELECT * FROM pg_logical_slot_get_changes('test_slot', NULL, NULL);

-- 删除测试槽
SELECT pg_drop_replication_slot('test_slot');

第五部分:DQL 操作与快照分析

5.1 执行 DQL 操作

-- 创建快照
SELECT pg_export_snapshot();

-- 在快照中查询
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT '刚才导出的快照ID';
SELECT * FROM test_table;
COMMIT;

-- 查看当前活跃快照
SELECT * FROM pg_stat_activity WHERE state = 'active';

5.2 快照与 LSN 的关系

快照实际上是一个事务ID的集合,表示在该快照创建时哪些事务已经提交、哪些正在运行、哪些还未开始。

-- 查看快照内容
SELECT pg_current_snapshot();

-- 快照格式:xmin:xmax:xip_list
-- xmin: 最早仍活跃的事务ID
-- xmax: 下一个将要分配的事务ID
-- xip_list: 当前活跃的事务ID列表

5.3 快照在逻辑复制中的应用

逻辑复制中的初始数据拷贝(copy_data = true)使用一致性快照来确保数据的一致性:

  1. 创建快照:在复制开始时创建一个一致性快照
  2. 基于快照拷贝数据:使用该快照读取数据,确保看到一致的数据视图
  3. 从快照LSN开始复制:复制快照之后的所有WAL变化

第六部分:LSN 推进流程追踪

6.1 LSN 基本概念

LSN(Log Sequence Number)是WAL日志中的位置标识,是一个64位整数,通常表示为两个32位十六进制数(如 0/152B8A0)。

-- 查看各种LSN
SELECT
    pg_current_wal_lsn() AS current_lsn,
    pg_last_wal_receive_lsn() AS receive_lsn,
    pg_last_wal_replay_lsn() AS replay_lsn,
    pg_current_wal_insert_lsn() AS insert_lsn,
    pg_current_wal_flush_lsn() AS flush_lsn;

6.2 复制过程中的 LSN 推进

在逻辑复制中,有多个LSN概念:

  1. sent_lsn:发送者已发送的LSN
  2. write_lsn:接收者已写入的LSN
  3. flush_lsn:接收者已刷盘的LSN
  4. replay_lsn:接收者已应用的LSN
  5. confirmed_flush_lsn:确认已刷盘的LSN(复制槽中记录)
-- 查看复制进度
SELECT
    slot_name,
    plugin,
    slot_type,
    datoid,
    database,
    active,
    xmin,
    catalog_xmin,
    restart_lsn,
    confirmed_flush_lsn
FROM pg_replication_slots;

-- 查看复制延迟
SELECT
    application_name,
    pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS send_lag_bytes,
    pg_wal_lsn_diff(sent_lsn, write_lsn) AS write_lag_bytes,
    pg_wal_lsn_diff(write_lsn, flush_lsn) AS flush_lag_bytes,
    pg_wal_lsn_diff(flush_lsn, replay_lsn) AS replay_lag_bytes,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS total_lag_bytes
FROM pg_stat_replication;

6.3 LSN 与事务的关系

每个事务提交时都会记录一个LSN,表示该事务的WAL记录结束位置。

-- 查看事务与LSN的关系
BEGIN;
INSERT INTO test_table (name, email) VALUES ('LSN测试', 'lsn@example.com');
-- 记录事务提交前的LSN
SELECT pg_current_wal_lsn() AS before_commit_lsn;
COMMIT;
-- 记录事务提交后的LSN
SELECT pg_current_wal_lsn() AS after_commit_lsn;

-- 计算事务占用的WAL大小
SELECT pg_wal_lsn_diff('0/152B8B0', '0/152B8A0') AS wal_size_bytes;

第七部分:文件级操作追踪

7.1 WAL 文件管理

WAL 文件位于 pg_wal 目录中,每个文件默认16MB。

# 查看WAL文件
ls -la /var/lib/pgsql/15/data/pg_wal/

# 查看WAL文件大小
du -sh /var/lib/pgsql/15/data/pg_wal/

# 查看当前正在使用的WAL文件
psql -c "SELECT pg_walfile_name(pg_current_wal_lsn());"

7.2 数据文件追踪

每个表对应一个或多个数据文件(在 base 目录中)。

-- 查看表的文件节点信息
SELECT
    pg_relation_filepath('test_table'),
    pg_relation_size('test_table'),
    pg_total_relation_size('test_table');

-- 查看表的物理位置
SELECT
    relname,
    relfilenode,
    reltablespace,
    relpages,
    reltuples
FROM pg_class
WHERE relname = 'test_table';

7.3 逻辑复制相关的文件

  1. 复制槽状态文件:pg_replslot/<slot_name>/state
  2. 逻辑解码插件:lib/pgoutput.so 或 lib/decoder.so
  3. 订阅状态文件:pg_subscription/<sub_oid>/state
# 查看复制槽文件
ls -la /var/lib/pgsql/15/data/pg_replslot/

# 查看逻辑解码插件
ls -la /usr/pgsql-15/lib/ | grep -E '(pgoutput|decoder)'

第八部分:完整实验演示

8.1 实验目标

通过一个完整的实验演示:

  1. DDL、DML、DQL 操作的执行
  2. 对应 WAL 日志的生成
  3. LSN 的推进过程
  4. 快照的创建和使用
  5. 逻辑复制的完整流程

8.2 实验步骤

步骤1:环境准备

# 停止 PostgreSQL 服务
sudo systemctl stop postgresql-15

# 备份配置文件
cp /var/lib/pgsql/15/data/postgresql.conf /var/lib/pgsql/15/data/postgresql.conf.backup

# 修改配置文件(添加调试参数)
echo "
log_statement = 'all'
log_connections = on
log_disconnections = on
log_replication_commands = on
log_line_prefix = '%m [%p] %q%u@%d '
log_timezone = 'UTC'
" >> /var/lib/pgsql/15/data/postgresql.conf

# 启动 PostgreSQL 服务
sudo systemctl start postgresql-15

步骤2:初始化测试环境

-- 创建测试数据库
DROP DATABASE IF EXISTS logical_test;
DROP DATABASE IF EXISTS logical_sub;
CREATE DATABASE logical_test;
CREATE DATABASE logical_sub;

-- 创建测试表
\c logical_test
CREATE TABLE experiment_table (
    id SERIAL PRIMARY KEY,
    operation_type VARCHAR(50),
    operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data JSONB
);

-- 创建发布
CREATE PUBLICATION experiment_pub FOR TABLE experiment_table;

步骤3:监控脚本准备

创建一个监控脚本 monitor_replication.sh:

#!/bin/bash
# monitor_replication.sh

while true; do
    clear
    echo "=== PostgreSQL 逻辑复制监控 ==="
    echo "时间: $(date)"
    echo ""

    echo "1. 当前 WAL LSN:"
    psql -d logical_test -c "SELECT pg_current_wal_lsn();"

    echo ""
    echo "2. 复制槽状态:"
    psql -d logical_test -c "SELECT slot_name, active, restart_lsn, confirmed_flush_lsn FROM pg_replication_slots;"

    echo ""
    echo "3. 复制连接状态:"
    psql -d logical_test -c "SELECT application_name, state, sent_lsn, write_lsn, flush_lsn, replay_lsn FROM pg_stat_replication;"

    echo ""
    echo "4. 表数据量:"
    psql -d logical_test -c "SELECT COUNT(*) FROM experiment_table;"
    psql -d logical_sub -c "SELECT COUNT(*) FROM experiment_table;" 2>/dev/null || echo "订阅者表不存在"

    echo ""
    echo "5. 最近WAL文件:"
    ls -la /var/lib/pgsql/15/data/pg_wal/ | tail -5

    sleep 2
done

步骤4:执行 DDL 操作并观察

-- 记录开始LSN
SELECT pg_current_wal_lsn() AS start_lsn \gset

-- 执行DDL
ALTER TABLE experiment_table ADD COLUMN ddl_marker BOOLEAN DEFAULT FALSE;

-- 记录结束LSN
SELECT pg_current_wal_lsn() AS end_lsn \gset

-- 计算DDL产生的WAL大小
SELECT pg_wal_lsn_diff(:'end_lsn', :'start_lsn') AS ddl_wal_size;

步骤5:执行 DML 操作并观察

-- 批量插入数据
INSERT INTO experiment_table (operation_type, data, ddl_marker)
SELECT
    'INSERT',
    jsonb_build_object('value', i, 'timestamp', now()),
    true
FROM generate_series(1, 100) AS i;

-- 更新数据
UPDATE experiment_table SET operation_type = 'UPDATE' WHERE id % 10 = 0;

-- 删除数据
DELETE FROM experiment_table WHERE id % 20 = 0;

-- 查看WAL生成量
SELECT pg_current_wal_lsn() AS current_lsn \gset
SELECT pg_wal_lsn_diff(:'current_lsn', :'end_lsn') AS dml_wal_size;

步骤6:创建订阅并观察复制过程

-- 在订阅者数据库创建表结构
\c logical_sub
CREATE TABLE experiment_table (
    id INTEGER PRIMARY KEY,
    operation_type VARCHAR(50),
    operation_time TIMESTAMP,
    data JSONB,
    ddl_marker BOOLEAN
);

-- 创建订阅
CREATE SUBSCRIPTION experiment_sub
CONNECTION 'dbname=logical_test host=localhost user=repl_user password=repl_password'
PUBLICATION experiment_pub
WITH (copy_data = true, create_slot = true);

-- 监控复制进度
\c logical_test
SELECT
    slot_name,
    confirmed_flush_lsn,
    pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes
FROM pg_replication_slots;

步骤7:分析 WAL 日志

# 找到包含实验操作的WAL文件
WAL_FILE=$(psql -d logical_test -t -c "SELECT pg_walfile_name(:'start_lsn');")

# 解析WAL文件
pg_waldump /var/lib/pgsql/15/data/pg_wal/$WAL_FILE -s :'start_lsn' -e :'current_lsn' -p > experiment_wal_dump.txt

# 查看解析结果
head -100 experiment_wal_dump.txt

步骤8:验证数据一致性

-- 在发布者和订阅者比较数据
\c logical_test
SELECT 'Publisher' as source, COUNT(*) as row_count FROM experiment_table
UNION ALL
\c logical_sub
SELECT 'Subscriber' as source, COUNT(*) as row_count FROM experiment_table;

-- 比较具体数据
\c logical_test
SELECT id, operation_type, ddl_marker FROM experiment_table ORDER BY id LIMIT 10;
\c logical_sub
SELECT id, operation_type, ddl_marker FROM experiment_table ORDER BY id LIMIT 10;

8.3 实验结果分析

通过上述实验,我们可以观察到:

  1. DDL 操作:会产生系统表变更的WAL记录,但不会通过逻辑复制自动传播到订阅者(除非使用pglogical等扩展)。
  2. DML 操作:每个INSERT/UPDATE/DELETE都会产生对应的WAL记录,并通过逻辑复制传播到订阅者。
  3. LSN 推进:每次事务提交都会推进LSN,复制槽的confirmed_flush_lsn会逐渐向前移动。
  4. 快照使用:初始数据拷贝使用一致性快照,确保数据一致性。
  5. 文件变化:WAL文件会不断生成,复制槽状态文件会记录复制进度。

第九部分:原理与实操结合

9.1 LSN、事务、WAL 之间的关系

  1. LSN 是 WAL 的地址:每个WAL记录都有一个唯一的LSN。
  2. 事务包含多个 WAL 记录:一个事务可能包含多个DML操作,每个操作对应一个WAL记录。
  3. 事务提交记录最终的 LSN:事务提交时,会写入一个提交记录,该记录的LSN代表事务的结束位置。
  4. 逻辑复制按 LSN 顺序读取:逻辑解码器按LSN顺序读取WAL记录,重组事务。
-- 演示事务与LSN的关系
BEGIN;
SELECT pg_current_wal_lsn() AS lsn_before_insert \gset
INSERT INTO test_table (name) VALUES ('事务测试1');
SELECT pg_current_wal_lsn() AS lsn_after_insert \gset
INSERT INTO test_table (name) VALUES ('事务测试2');
SELECT pg_current_wal_lsn() AS lsn_before_commit \gset
COMMIT;
SELECT pg_current_wal_lsn() AS lsn_after_commit \gset

-- 计算各个阶段之间的WAL增量
SELECT
    pg_wal_lsn_diff(:'lsn_after_insert', :'lsn_before_insert') AS insert1_wal,
    pg_wal_lsn_diff(:'lsn_before_commit', :'lsn_after_insert') AS insert2_wal,
    pg_wal_lsn_diff(:'lsn_after_commit', :'lsn_before_commit') AS commit_wal;

9.2 快照与事务隔离

逻辑复制使用快照来确保数据一致性:

  1. 快照创建时机:在创建订阅或初始数据拷贝时创建。
  2. 快照内容:包含创建时所有已提交的事务。
  3. 快照与 LSN:每个快照都对应一个LSN,表示快照创建时的WAL位置。
-- 演示快照与LSN的关系
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT pg_export_snapshot() AS snapshot_id \gset
SELECT pg_current_wal_lsn() AS snapshot_lsn \gset

-- 在另一个会话中修改数据
-- 然后回到这个会话查询,应该看不到新修改的数据
SELECT * FROM test_table;

COMMIT;

-- 快照对应的LSN可以用于确定复制起点
SELECT :'snapshot_lsn' AS snapshot_lsn_position;

9.3 逻辑复制的工作流程

  1. 捕获变更:通过WAL日志捕获DDL/DML变更。
  2. 逻辑解码:使用输出插件(如pgoutput)将WAL记录解码为逻辑变更。
  3. 事务重组:使用ReorderBuffer将同一事务的变更重组。
  4. 传输变更:通过复制协议将变更发送到订阅者。
  5. 应用变更:订阅者应用变更,保持数据同步。

第十部分:故障排查与性能优化

10.1 常见问题排查

问题1:复制延迟大

-- 查看复制延迟
SELECT
    application_name,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS delay_bytes,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS delay_pretty
FROM pg_stat_replication;

-- 可能原因及解决方案:
-- 1. 网络带宽不足:检查网络连接
-- 2. 订阅者应用慢:检查订阅者负载
-- 3. 大事务:避免长时间运行的大事务
-- 4. WAL文件过多:定期清理旧复制槽

问题2:WAL磁盘空间不足

-- 查看WAL磁盘使用
SELECT
    slot_name,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_retained
FROM pg_replication_slots;

-- 解决方案:
-- 1. 删除不需要的复制槽:SELECT pg_drop_replication_slot('slot_name');
-- 2. 监控复制进度,确保订阅者及时确认
-- 3. 增加WAL磁盘空间

问题3:复制中断

-- 查看复制状态
SELECT * FROM pg_stat_replication WHERE state != 'streaming';

-- 查看错误日志
SELECT pg_read_file('log/postgresql-' || to_char(current_date, 'YYYY-MM-DD') || '.log')
FROM generate_series(1,1)
WHERE length(pg_read_file('log/postgresql-' || to_char(current_date, 'YYYY-MM-DD') || '.log')) > 0;

-- 常见原因:
-- 1. 网络中断:检查网络连接
-- 2. 权限问题:检查复制用户权限
-- 3. 表结构不一致:确保发布者和订阅者表结构一致

10.2 性能优化建议

  1. 调整WAL参数:

    wal_buffers = 16MB
    wal_writer_delay = 200ms
    max_wal_size = 2GB
    min_wal_size = 1GB
  2. 优化复制参数:

    -- 创建订阅时调整参数
    CREATE SUBSCRIPTION ... WITH (
        copy_data = true,
        create_slot = true,
        enabled = true,
        slot_name = 'custom_slot',
        synchronous_commit = 'off'
    );
  3. 监控与告警:

    -- 创建监控视图
    CREATE VIEW replication_monitor AS
    SELECT
        application_name,
        state,
        pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replication_lag_bytes,
        pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) / 1024 / 1024 AS replication_lag_mb,
        CASE
            WHEN pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) > 100 * 1024 * 1024 THEN 'CRITICAL'
            WHEN pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) > 10 * 1024 * 1024 THEN 'WARNING'
            ELSE 'OK'
        END AS lag_status
    FROM pg_stat_replication;

第十一部分:总结

通过本文的实践操作,我们深入了解了:

  1. WAL 日志机制:DDL/DML操作如何记录到WAL中。
  2. LSN 推进原理:事务如何推动LSN前进,以及LSN在复制中的作用。
  3. 快照创建流程:逻辑复制如何使用快照确保数据一致性。
  4. 逻辑复制全流程:从变更捕获到数据同步的完整过程。
  5. 监控与调优:如何监控复制状态,优化复制性能。

关键点总结:

  • LSN是复制的核心:所有复制进度都以LSN为基准。
  • 事务是逻辑单位:逻辑复制以事务为单位保证一致性。
  • WAL是物理基础:所有变更都通过WAL记录和传输。
  • 快照确保一致性:初始数据拷贝使用快照保证数据一致性。

附录

A. 常用命令速查

-- WAL相关
SELECT pg_current_wal_lsn();
SELECT pg_walfile_name(pg_current_wal_lsn());
SELECT pg_wal_lsn_diff('0/152B8B0', '0/152B8A0');

-- 复制相关
SELECT * FROM pg_replication_slots;
SELECT * FROM pg_stat_replication;
SELECT * FROM pg_publication;
SELECT * FROM pg_subscription;

-- 快照相关
SELECT pg_current_snapshot();
SELECT pg_export_snapshot();

-- 表信息
SELECT pg_relation_filepath('table_name');
SELECT pg_relation_size('table_name');

B. 配置文件示例

postgresql.conf 关键配置:

# 复制相关
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10
hot_standby = on

# 性能相关
shared_buffers = 128MB
wal_buffers = 16MB
max_wal_size = 2GB
min_wal_size = 1GB

# 日志相关
log_statement = 'all'
log_connections = on
log_disconnections = on
log_replication_commands = on

C. WAL 日志实际示例分析

以下是使用 pg_waldump 工具解析的实际 WAL 日志示例,展示了不同操作对应的 WAL 记录。

示例1:INSERT 操作的 WAL 记录

rmgr: Heap        len (rec/tot):     70/   114, tx:       1234, lsn: 0/1512345, prev 0/1512000, desc: INSERT+INIT
  blkref #0: rel 1663/16384/24576 blk 0
  tuple data: 2 columns
    col 1: [1] 49  (integer 1)
    col 2: [7] 74 65 73 74 31  (text "test1")

字段解释:

  • rmgr: Heap:资源管理器为堆表(数据表)
  • tx: 1234:事务ID
  • lsn: 0/1512345:该记录的LSN位置
  • prev 0/1512000:前一个记录的LSN
  • desc: INSERT+INIT:操作类型为INSERT,INIT表示这是事务中的第一个操作
  • rel 1663/16384/24576:表OID(数据库OID/模式OID/表OID)
  • blk 0:块号
  • tuple data:插入的元组数据

示例2:UPDATE 操作的 WAL 记录

rmgr: Heap        len (rec/tot):    104/   148, tx:       1235, lsn: 0/1512500, prev 0/1512345, desc: UPDATE
  blkref #0: rel 1663/16384/24576 blk 0
  old tuple: 2 columns
    col 1: [1] 49  (integer 1)
    col 2: [7] 74 65 73 74 31  (text "test1")
  new tuple: 2 columns
    col 1: [1] 49  (integer 1)
    col 2: [8] 74 65 73 74 31 55  (text "test17")

字段解释:

  • desc: UPDATE:操作类型为UPDATE
  • old tuple:更新前的元组数据
  • new tuple:更新后的元组数据

示例3:DELETE 操作的 WAL 记录

rmgr: Heap        len (rec/tot):     66/   110, tx:       1236, lsn: 0/1512700, prev 0/1512500, desc: DELETE
  blkref #0: rel 1663/16384/24576 blk 0
  old tuple: 2 columns
    col 1: [1] 49  (integer 1)
    col 2: [8] 74 65 73 74 31 55  (text "test17")

示例4:事务提交记录

rmgr: Transaction len (rec/tot):     34/    34, tx:       1234, lsn: 0/1512800, prev 0/1512700, desc: COMMIT 2026-03-16 10:30:00.123456 UTC
  commit_ts: 2026-03-16 10:30:00.123456 UTC

字段解释:

  • rmgr: Transaction:资源管理器为事务
  • desc: COMMIT:事务提交
  • commit_ts:提交时间戳

示例5:检查点记录

rmgr: XLOG        len (rec/tot):    106/   106, tx:          0, lsn: 0/1513000, prev 0/1512800, desc: CHECKPOINT_ONLINE
  redo 0/1513000; undo 0/0; tli 1; prev tli 1; fpw true; xid 0:1236; oid 24576; multi 1; offset 0; oldest xid 562 in DB 1; oldest multi 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 0; shutdown false

示例6:DDL 操作(创建表)的 WAL 记录

DDL操作通常涉及多个系统表变更:

rmgr: Storage    len (rec/tot):     42/    42, tx:       1237, lsn: 0/1513200, prev 0/1513000, desc: SMGR_CREATE
  rel 1663/16384/24577 fork main size 0

rmgr: Heap        len (rec/tot):     92/    92, tx:       1237, lsn: 0/1513250, prev 0/1513200, desc: INSERT
  blkref #0: rel 1663/1262/1259 blk 0  # pg_class 系统表
  tuple data: ...  # 新表的元数据

rmgr: Heap        len (rec/tot):     85/    85, tx:       1237, lsn: 0/1513300, prev 0/1513250, desc: INSERT
  blkref #0: rel 1663/1262/1249 blk 0  # pg_attribute 系统表
  tuple data: ...  # 表列的元数据

说明:DDL操作不会直接记录SQL语句,而是记录系统表(pg_class、pg_attribute等)的变更。逻辑复制需要额外的机制(如pglogical)来捕获和复制DDL。

示例7:逻辑解码消息(Logical Message)

rmgr: LogicalMessage len (rec/tot):     58/    58, tx:          0, lsn: 0/1513400, prev 0/1513300, desc: MESSAGE
  prefix "pgoutput"; content "BEGIN 1234"

这是逻辑复制专用的消息,用于传输事务边界等信息。

D. 参考资源

  1. PostgreSQL官方文档:逻辑复制章节
  2. pg_waldump工具手册
  3. PostgreSQL源码:src/backend/replication/
  4. 逻辑复制扩展:pglogical、wal2json等

文档最后更新日期:2026年3月16日
适用于 PostgreSQL 15 及以上版本


文章作者: growdu
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 growdu !
这篇还发到了
今日头条 drafted
  目录