PostgreSQL 14.3同库不同Schema间逻辑复制实现问询

可行性结论 #

PostgreSQL 14.3完全支持同一数据库内的跨Schema逻辑复制，你的需求可以实现，只是操作中遗漏了几个关键配置步骤。

你的操作问题分析 #

1. 订阅参数缺失：创建订阅时未指定create_table=false和copy_data=false，默认情况下订阅会尝试创建同名表（但Y.customer已存在），导致报错；若需要初始同步数据可设copy_data=true，否则设为false。
2. 无行识别标识：X.customer表没有主键或唯一索引，pgoutput插件默认无法定位更新/删除操作的行，导致这类操作无法同步。
3. 槽名未加引号：slot_name=slot会被解析为标识符，而非你创建的槽名'slot'，需加单引号。
4. 数据库参数未配置：逻辑复制要求wal_level=logical，默认可能为replica，需修改后重启数据库。

修正后的完整步骤 #

1. 配置数据库核心参数

修改postgresql.conf文件：

wal_level = logical

重启PostgreSQL服务使配置生效。

2. 为发布表设置行识别标识

因为你的表无主键，设置REPLICA IDENTITY FULL（会复制整行数据用于定位，性能略低于主键，适合无主键场景）：

ALTER TABLE X.customer REPLICA IDENTITY FULL;

3. 创建发布（原操作正确）

CREATE PUBLICATION publicationname FOR TABLE X.customer;

4. 创建逻辑复制槽（原操作正确）

SELECT pg_create_logical_replication_slot('slot', 'pgoutput');

5. 创建订阅（修正参数）

CREATE SUBSCRIPTION mysub
CONNECTION 'host=localhost port=5432 user=user password=pass dbname=db'
PUBLICATION publicationname
WITH (
    slot_name='slot',
    create_slot=false,
    create_table=false,
    copy_data=false -- 若Y.customer需要初始同步X的数据，改为true
);

验证同步效果 #

执行插入操作：

INSERT INTO X.customer (customerstuff) VALUES ('测试同步数据');

查询Y库表：

SELECT * FROM Y.customer;

若能查询到插入的数据，说明同步生效；再测试更新和删除操作，确认是否正常同步。

额外注意事项 #

• 若订阅创建失败，需先执行DROP SUBSCRIPTION mysub;清理残留后重新创建。
• 确保操作用户拥有pg_create_subscription权限，以及对Y.customer的读写权限。
• 若后续给X.customer添加主键，可将REPLICA IDENTITY改回默认（主键），提升同步性能。

内容的提问来源于stack exchange，提问作者Skullruss