Postgres Logical Replication - Monitor Subscriber Without Accessing the Publisher Server

Question

I would like each subscriber server to monitor its health without accessing the publisher server

1. I use the following code from the publisher to get the lag. Is it possible to compute the lag also from the subscriber server

  SELECT 
        slot_name, active, confirmed_flush_lsn, pg_current_wal_lsn(), 
        (pg_current_wal_lsn() - confirmed_flush_lsn) AS bytes_lag
    FROM pg_replication_slots;

If I use from the subscriber the following

select received_lsn, latest_end_lsn from pg_stat_subscription

I will still need the following from the publisher select pg_current_wal_lsn();

Is there a way to know the lag without accessing the publisher?

2. I have a duplicate value at one of the tables that caused the replication to stop, but

select srsubstate from pg_subscription_rel

is showing as 'r' for all tables.

How can I know which table is problematic
How can I know what is the reason that the replication stopped

3. How can a subscriber know that its logical slot or even publisher was dropped

`received_lsn - latest_end_lsn` is the size in bytes that have been received from the publisher but have not been replayed yet. Which qualifies as some kind of "lag". — , Apr 19 '21 at 09:34

score 0 · Accepted Answer · answered Apr 19 '21 at 09:40

No, you cannot get that information from the subscriber. The subscriber doesn't know what there is to receive that it has not yet received.

To figure out the cause when replication breaks, you have to look at the subscriber's log file. Yes, that is manual activity, but so is conflict resolution.

You will quickly figure out if the replication slot has been dropped, because there will be nasty error messages in the log. This is quite similar to dropped tables.

Postgres Logical Replication - Monitor Subscriber Without Accessing the Publisher Server

1 Answers1