Re: Implicit table removal from logical replication publication

On Thu, Jun 10, 2021 at 12:39 PM Vijaykumar Jain <
vijaykumarjain(dot)github(at)gmail(dot)com> wrote:

> 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.
>
> functions-event-triggers
> <https://www.postgresql.org/docs/current/functions-event-triggers.html>
> event-trigger-definition
> <https://www.postgresql.org/docs/current/event-trigger-definition.html>
> how-use-event-triggers-postgresql
> <https://www.enterprisedb.com/postgres-tutorials/how-use-event-triggers-postgresql>
>
> 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.
>
> is pg_publication_tables what you are looking for?

>
>
> --
--cnemelka