Обсуждение: Implicit table removal from logical replication publication
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!
Вложения
Wow, the drop table silently removes entry from publication without any logs.I could not find any monitoring view to help me figure out if the publication is broken due to ddl change.pg_stat_replication on publisher, and pg_stat_subscription on subscriber only help with lsn based lag.unless this is not an issue but a feature ?i'll check more on the better part of monitoring logical replication stuff.but for your case,you can set up an event trigger that would avoid dropping the table.basically any drop of a table or any ddl that would break publication.you can have a custom query filter that would prevent dropping of objects part of publication accidentally.and then you want to exclusively drop the table, once not part of publication, you have to first remove the table from publication and then drop.I have not run this in production, so I believe others may chime in, but logical replication issues from logs are not the best.I am happy to be corrected.I'll update on more scenarios.