Implicit table removal from logical replication publication

From: Avi Weinberg <AviW(at)gilat(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Implicit table removal from logical replication publication
Date: 2021-06-10 16:33:37
Message-ID: DB9PR07MB718011006221BF54F8B2DE2FCB359@DB9PR07MB7180.eurprd07.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi Experts

I had a case where a team member deleted and recreated an empty table which participated in logical replication. After that action the table no longer was part of the replication and new inserts were not copied to the subscribers.

How can I check existing publication for the list of participating tables so I will know (from the publication side) that a table was removed?

It is possible for table to be removed from publication using the following steps:

create a publication with 2 tables
create a subscription to that publication
verify you have two lines for the following query on subscriber side "select * from pg_subscription_rel"
delete one of the tables from the publisher side
run alter subscription the_name refresh publication
run select * from pg_subscription_rel and see that only one row remain
even if you now add the deleted table on the publisher side, it will no longer participate in the publication until you add it explicitly again using "alter publication add table"

How can I know that the table was removed from publication so I will know to add it?

Thanks!

IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter J. Holzer 2021-06-10 17:00:59 Re: How to pass a parameter in a query to postgreSQL 12
Previous Message David G. Johnston 2021-06-10 16:30:16 Re: Logical Replication: SELECT pg_catalog.set_config Statement appears to be hanging