5

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.

1

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.

2
  • Wow, I would have never thought of doing this in a transaction. It feels like a bug to me that this doesn't work, but I'm biased. I guess this also points to a documentation bug. Again, I'm biased, but it seems like the docs should say that there's a lot of commitment in using FOR ALL TABLES.
    – mlissner
    Nov 20 '18 at 21:49
  • For a different purpose I've been doing very similar things over in this ticket.
    – mlissner
    Jan 30 '20 at 22:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.