1、逻辑复制概述
- 逻辑复制可指定单个或多个表进行复制
- 备库可读写：逻辑复制的备库（订阅端）可读写
- 支持不同大版本间复制（如 PG14 → PG16）
- 可通过 WHERE 子句过滤复制的数据（如仅复制某类状态的记录）
- 主库的 DDL 操作不会自动同步到备库，需手动维护
2、核心组件与原理
1. 组件构成
- 发布端：主库，负责将指定表的 DML 操作（INSERT/UPDATE/DELETE）转换为逻辑日志并发布。
- 订阅端：备库，订阅发布端的日志并应用到本地表。
- 复制槽：发布端用于保留未同步的逻辑日志，防止因订阅端延迟导致日志被清理（避免数据丢失）。
- 逻辑解码：将 WAL 日志（物理格式）解析为可读的逻辑变更（如 SQL 语句或结构化数据），是逻辑复制的核心。
2. 工作原理
- 发布端开启 wal_level = logical，确保 WAL 日志包含足够的逻辑信息。
- 发布端对指定表创建 “发布”，记录表的 DML 操作到 WAL 日志。
- 订阅端连接发布端，通过复制槽获取逻辑日志。
- 订阅端将逻辑日志解析为本地可执行的 SQL 并应用到目标表，保持数据同步。
3、PostgreSQL 16 逻辑复制（单表同步及扩展操作）笔记
3.1、发布节点配置（主库）
1. 修改postgresql.conf（需重启生效）
# 启用逻辑复制（必需，默认是 replica，需改为 logical）
wal_level = logical
# 复制槽最大数量（至少为订阅端数量，建议 ≥ 8）
max_replication_slots = 8
# WAL 发送进程最大数量（需 ≥ 订阅端数量，建议 ≥ 10）
max_wal_senders = 10
# 可选：逻辑解码工作进程数（PG16 支持并行解码，建议 ≥ 4）
max_logical_replication_workers = 4
pg_ctl -D /var/lib/pgsql/16/data restart
2、修改pg_hba.conf（配置访问权限，无需重启）
- 允许订阅端通过指定用户连接发布端进行复制
- 说明：PG16 默认认证方式为 scram-sha-256（比 md5 更安全，建议优先使用）
# 格式：host 数据库名 用户名 订阅端IP/掩码 认证方式
host postgres logical_user 172.21.16.10/32 scram-sha-256
SELECT pg_reload_conf();
3. 创建逻辑复制相关对象
-- 创建具备复制权限的用户logical_user，限制连接数
CREATE USER logical_user
WITH REPLICATION
LOGIN
CONNECTION LIMIT 8
ENCRYPTED PASSWORD '123456';
-- 逻辑复制依赖唯一标识定位记录，表必须有主键或唯一索引，否则 UPDATE/DELETE 无法同步：
CREATE TABLE t1 (
id int4 PRIMARY KEY, -- 主键（必需）
name text
);
-- 复制用户logical_user需对发布的表t1有 SELECT 权限（用于读取数据生成逻辑日志）
GRANT SELECT ON t1 TO logical_user;
4、创建发布
- 说明：
- 发布仅包含 DML 操作，不包含表创建 / 删除（DDL 需手动同步）。
- 表必须有主键或唯一索引（否则 UPDATE/DELETE 无法正确同步，因逻辑复制依赖唯一标识）。
-- 创建名为 pub1 的发布，包含表 t1
CREATE PUBLICATION pub1 FOR TABLE t1;
-- 发布单个表（如 public.table1）
CREATE PUBLICATION pub_user FOR TABLE public.table1;
-- 发布多个表
CREATE PUBLICATION pub_multi FOR TABLE public.table1, public.table2;
-- 发布所有表（谨慎使用）
CREATE PUBLICATION pub_all FOR ALL TABLES;
-- 带条件的发布（仅复制 status = 'active' 的记录，PG15+ 支持）
CREATE PUBLICATION pub_filtered FOR TABLE public.table1 WHERE (status = 'active');
5、 验证发布端配置
-- 查看复制槽（初始为空，订阅端创建订阅后会自动生成）
SELECT * FROM pg_replication_slots;
-- 查看所有发布
SELECT * FROM pg_publication;
-- 查看发布包含的表（PG16 可通过此视图）
SELECT * FROM pg_publication_tables WHERE pubname = 'pub1';
3.2、订阅节点配置（备库）
1、修改配置文件（postgresql.conf，需重启）
# 复制槽最大数量（与发布端保持一致或更大）
max_replication_slots = 8
# 逻辑复制工作进程数（用于应用同步数据，建议 ≥ 8）
max_logical_replication_workers = 8
# 可选：并行应用worker数（PG16 支持并行同步，提升性能）
max_parallel_apply_workers_per_subscription = 4
pg_ctl -D /var/lib/pgsql/16/data restart
2、创建目标表（结构需与发布端一致）
- 订阅端需手动创建与发布端结构相同的表（逻辑复制不同步表结构）：
CREATE TABLE t1 (
id int4 PRIMARY KEY, -- 必须与发布端一致（字段名、类型、主键）
name text
);
3、创建订阅
-- 在 PostgreSQL 中，使用 CREATE SUBSCRIPTION 创建订阅时，默认行为是自动在主库（发布端）创建一个对应的复制槽，除非显式指定 WITH (create_slot = false) 来禁用这一行为
CREATE SUBSCRIPTION sub1
CONNECTION 'host=172.21.16.6 port=5432 dbname=postgres user=logical_user password=123456'
PUBLICATION pub1
CREATE SUBSCRIPTION sub1
CONNECTION 'host=172.21.16.6 port=5432 dbname=postgres user=logical_user password=123456'
PUBLICATION pub1
WITH (
create_slot = true, -- 自动在发布端创建复制槽（推荐，避免手动操作）
slot_name = 'slot_sub1', -- 复制槽名称（自定义，便于识别）
copy_data = true -- 订阅时同步发布端表中已有的数据（首次订阅必需，否则仅同步后续变更）。
);
4、验证订阅端配置
-- 查看所有订阅
SELECT * FROM pg_subscription;
-- 查看订阅状态（是否活跃）
SELECT subname, status FROM pg_subscription_status;
3.3、逻辑复制扩展操作（添加 / 删除表）
3.3.1、向现有发布添加表（发布端）
1、创建新表并插入测试数据
CREATE TABLE t_big (
id int4 PRIMARY KEY, -- 必需：主键或唯一索引
create_time timestamp(0) without time zone DEFAULT clock_timestamp(),
name varchar(32)
);
-- 插入10万条测试数据
INSERT INTO t_big(id, name)
SELECT n, (n * random() * 1000)::varchar(32)
FROM generate_series(1, 100000) n;
2、授权并添加到发布
-- 授予复制用户查询权限
GRANT SELECT ON t_big TO logical_user;
-- 将表添加到现有发布 pub1
ALTER PUBLICATION pub1 ADD TABLE t_big;
3、验证发布包含的表
-- 查看发布 pub1 中的所有表。使用元命令
\dRp+ pub1
-- 查看发布 pub1 中的所有表。使用 SQL 语句
SELECT * FROM pg_publication_tables WHERE pubname = 'pub1';
3.3.2、订阅端同步新增表（订阅端）
1、创建与发布端一致的新表
CREATE TABLE t_big (
id int4 PRIMARY KEY,
create_time timestamp(0) without time zone DEFAULT clock_timestamp(),
name varchar(32)
);
2、刷新订阅（获取新增表的同步配置）
-- 刷新订阅，使订阅端感知发布端新增的表
ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
此时，t_big 表的数据会自动从发布端同步到订阅端（包括历史数据和后续变更）。
3.3.3、从发布中删除表（发布端）
-- 从 pub1 中移除 t_big 表
ALTER PUBLICATION pub1 DROP TABLE t_big;
-- 验证（t_big 应从列表中消失）
SELECT * FROM pg_publication_tables WHERE pubname = 'pub1';
注意：发布端移除表后，订阅端不会自动删除表，仅停止同步该表的后续变更，需手动删除订阅端表（若需）。
3.4、逻辑复制的启动 / 停止（订阅端）
- 暂停期间，发布端的变更会被复制槽保留，恢复后自动同步积压的变更。
-- 暂停订阅（停止同步，保留订阅关系）
ALTER SUBSCRIPTION sub1 DISABLE;
-- 恢复订阅（继续同步）
ALTER SUBSCRIPTION sub1 ENABLE;
4、常用操作与管理
4.1. 查看发布与订阅
-- 发布端：查看所有发布
SELECT * FROM pg_publication;
-- 发布端：查看发布包含的表
SELECT * FROM pg_publication_tables;
-- 订阅端：查看所有订阅
SELECT * FROM pg_subscription;
4.2. 新增 / 移除复制表
-- 发布端：向现有发布添加表
ALTER PUBLICATION <pub_name> ADD TABLE public.orders;
-- 发布端：从发布中移除表
ALTER PUBLICATION <pub_name> DROP TABLE public.orders;
4.3. 暂停 / 恢复订阅
-- 订阅端：暂停订阅（停止同步）
ALTER SUBSCRIPTION <sub_name> DISABLE;
-- 订阅端：恢复订阅
ALTER SUBSCRIPTION <sub_name> ENABLE;
4.4. 删除发布 / 订阅
-- 删除订阅（需先禁用）
ALTER SUBSCRIPTION <sub_name> DISABLE;
DROP SUBSCRIPTION <sub_name> ;
-- 删除发布（需先确保无订阅依赖）
DROP PUBLICATION <pub_name>;
4.5. 同步初始数据
- 若订阅前发布端已有数据，需手动同步初始数据（逻辑复制仅同步订阅后的变更）：
# 从发布端导出数据并导入订阅端
pg_dump -U postgres -d pub_db -t public.user_table -a | psql -U postgres -d sub_db -h 192.168.1.3
-a：仅导出数据（不包含表结构）。
5、注意事项
- PG16 默认使用 scram-sha-256 认证，pg_hba.conf 中建议避免使用 md5（兼容性差且不安全）。
- PG16 支持订阅端并行应用日志，大表同步建议开启，提升效率
- 通过 max_parallel_apply_workers_per_subscription 控制
- 逻辑复制不同步 DDL 操作，若发布端修改表结构，需手动在订阅端执行相同 DDL，否则可能导致复制中断。
- 订阅端若手动修改复制表数据，可能与发布端同步的变更冲突，可通过 ON CONFLICT 策略（如 ALTER SUBSCRIPTION ... SET (conflict_action = overwrite)）处理。
- 长期不用的订阅需及时删除，避免复制槽占用发布端磁盘空间（可通过 DROP REPLICATION SLOT slot_sub1; 手动清理）。
浙公网安备 33010602011771号