Search code examples
postgresqllogical-replication

Trying to create a filtered logical replication on postgresql but the whole table gets replicated

I am trying to create a logical replication with filter between two postgresql 16 servers

On publisher:

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)

(I've also tried with replica identity full)

On subscriber:

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

And after that, all of the rows are replicated on the subscription table regardless of filter, which is not what I wanted, I wanted only rows where id < 7

Edit: Answers to Adrian's questions.

  1. Yes, I am sure the subscription is pointing to the correct publication database because I've created these two sample databases with only one table to try out the logical replication.

  2. I have just inserted 20 sample rows on the publication side

  3. This is the log on the publication side:

    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

Solution

  • That is to be expected during the initial coping of the data. Perhaps you missed this warning from the documentation:

    Because initial data synchronization does not take into account the publish parameter when copying existing table data, some rows may be copied that would not be replicated using DML. Refer to Section 31.7.1, and see Section 31.2.2 for examples.

    (It is an omission that the documentation does not mention the WHERE clause.)

    You can either skip the initial data copy when you create the subscription and transfer the existing data in some other way, or you can delete the unwanted rows on the subscriber.