PostgreSQL 16带过滤逻辑复制失效：全表被复制问题求助

操作步骤 #

发布端操作 #

create table repl_sample
(   id int not null, 
    name varchar (64) not null, 
    age int not null, 
 constraint pk_repl_sample primary key (id)
)

alter table repl_sample replica identity default 

create publication repl_sample_publication
for table repl_sample 
where (id < 7)

（也曾尝试设置replica identity full）

订阅端操作 #

create table repl_sample
(   id int not null, 
name varchar (64) not null, 
age int not null, 
 constraint pk_repl_sample primary key (id)
)

alter table repl_sample replica identity default

create subscription repl_sample_subscription
connection 'host=192.168.4.1 port=5432 user=postgres dbname=sampledb'
publication repl_sample_publication

问题现象 #

配置完成后，订阅端同步了所有20条测试数据，完全无视发布端设置的id < 7过滤条件，不符合预期。

补充信息 #

1. 确认订阅指向正确的发布数据库，仅创建了两个测试库及单表用于验证。
2. 发布端日志如下：

2024-03-05 12:44:36.114 CET [1602854] STATEMENT: CREATE_REPLICATION_SLOT "pg_80016_sync_80009_7184072373314447002" LOGICAL pgoutput USE_SNAPSHOT
2024-03-05 12:44:36.248 CET [1602854] LOG:  starting logical decoding for slot "pg_80016_sync_80009_7184072373314447002"
2024-03-05 12:44:36.248 CET [1602854] DETAIL:  Streaming transactions committing after 9A/F98C8370, reading WAL from 9A/F98C8338.
2024-03-05 12:44:36.248 CET [1602854] STATEMENT:  START_REPLICATION SLOT "pg_80016_sync_80009_7184072373314447002" LOGICAL 9A/F98C8370 (proto_version '2', publication_names '"repl_sample_publication"')
2024-03-05 12:44:36.248 CET [1602854] LOG:  logical decoding found consistent point at 9A/F98C8338
2024-03-05 12:44:36.248 CET [1602854] DETAIL:  There are no running transactions.
2024-03-05 12:44:36.248 CET [1602854] STATEMENT:  START_REPLICATION SLOT "pg_80016_sync_80009_7184072373314447002" LOGICAL 9A/F98C8370 (proto_version '2', publication_names '"repl_sample_publication"')
2024-03-05 12:48:25.289 CET [698] LOG:  checkpoint starting: time
2024-03-05 12:48:29.842 CET [698] LOG:  checkpoint complete: wrote 46 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.537 s, sync=0.007 s, total=
4.553 s; sync files=31, longest=0.002 s, average=0.001 s; distance=277 kB, estimate=3808 kB; lsn=9A/F98EC808, redo lsn=9A/F98EAC30

问题原因与解决方案 #

核心原因 #

PostgreSQL逻辑复制的初始快照同步会忽略发布的WHERE过滤条件，只有创建订阅之后的增量变更（INSERT/UPDATE/DELETE）才会应用过滤规则。你看到的全量同步，是初始快照拉取了发布端已存在的所有历史数据。

解决步骤 #

1. 清理现有订阅与复制槽
   先在订阅端执行：

   DROP SUBSCRIPTION repl_sample_subscription;
   

   再在发布端清理自动生成的复制槽（名称从日志中获取）：

   SELECT pg_drop_replication_slot('pg_80016_sync_80009_7184072373314447002');
   

2. 创建订阅时跳过初始快照
   在订阅端创建订阅时添加copy_data = false参数，避免全量同步历史数据：

   CREATE SUBSCRIPTION repl_sample_subscription
   CONNECTION 'host=192.168.4.1 port=5432 user=postgres dbname=sampledb'
   PUBLICATION repl_sample_publication
   WITH (copy_data = false);
   

3. 手动同步符合过滤条件的初始数据
   在发布端导出指定条件的数据，再导入到订阅端：

   # 发布端导出过滤后的数据
   pg_dump -h localhost -U postgres -d sampledb -t repl_sample --data-only --where="id < 7" > filtered_data.sql
   
   # 订阅端导入数据（替换主机地址为订阅端实际地址）
   psql -h 192.168.4.2 -U postgres -d sampledb -f filtered_data.sql
   

4. 验证增量同步规则
   在发布端插入两条测试数据：

   -- 会被同步的记录
   INSERT INTO repl_sample VALUES (6, 'test_sync', 25);
   -- 不会被同步的记录
   INSERT INTO repl_sample VALUES (7, 'test_ignore', 30);
   

   检查订阅端，仅id=6的记录会被同步，说明过滤规则生效。

额外说明 #

• 若需保留现有订阅的增量规则，也可先清空订阅端表数据，手动导入过滤后的数据，再重启订阅，但删除重建的方式更清晰可控。
• 发布的WHERE过滤仅对增量变更生效是PostgreSQL逻辑复制的设计特性，官方文档明确标注初始快照不应用过滤规则。

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