How to Remove a Table from FOR ALL TABLES Logical Replication PUBLICATION in PostgreSQL

postgresqlreplication

I created a logical replication PUBLICATION using FOR ALL TABLES. Now I've realized I don't want to replicate all of the tables after all. I tried dropping one of the tables from the publication, but:

mydb=# alter publication replicapublication drop table only migrations;
ERROR:  publication "replicapublication" is defined as FOR ALL TABLES
DETAIL:  Tables cannot be added to or dropped from FOR ALL TABLES publications.

I've done a huge sync of hundreds of GB, and I don't want to start over with a new publication.

Questions:
1. Is there any hope of redeeming this publication?
2. If not, is there any way to create a new publication that's fully synced without starting over with a full sync?

Thanks.

Best Answer

For 1, no, I don't think for. For 2, possibly but it seems fraught.

I naively thought that creating a new publication listing your tables, and then on the subscribing end disabling the old subscription and creating new one using the same slot, all in one transaction, would work. But doing an experiment, this does not work if the publisher is under load. It ends up losing some changes. However, it does appear to work if the publisher is quiescent and the subscriber is caught up at the time of the swap.

-- warning: does not work transactionally!
begin; 
alter subscription sub disable;
alter subscription sub set (SLOT_NAME = NONE); -- used to be 'sub'
create subscription sub2 CONNECTION '' PUBLICATION pub2 with (
    copy_data = false, 
    slot_name='sub', 
    create_slot=false) ;
commit;

I can't decide if this is a bug or not. I don't see anything in the documentation which excuses this non-transactional behavior, but I did not read the entire thing carefully.

Related Question