PostgreSQL Monitor Logical replication Failure and Lag

Question

I have logical replication running among multiple PostgreSQL environments. I need to setup some alerting mechanism where I get an alert if one of my logical replication failed for whatever reason. How can I achieve that? I am aware of these Views, on which column can I use where class to catch the failure?

pg_stat_replication Pg_publication_tables Pg_replication_slots pg_stat_subscription pg_subscription_rel

Let me know, Thanks in advance !

Answer 1

Use

SELECT max(pg_lsn_diff(
          pg_current_wal_lsn(),
          replay_lsn
       ))
FROM pg_stat_replication;

That will tell you how many bytes the most delayed standby server is behind.