Info from pg_catalog. pg_subscription_rel table and logical replication lag.

Hi, PostgreSQL's friends.

I'm using logical replication in PostgreSQL 14 and looking for columns
usage of the pg_subscription_rel table.
Do you think I can use the srsublsn column to see a replication lag by
the subscription table?
Can I compare the values and use the function pg_wal_lsn_diff() to
determine the replication lag table by table?

I tried some options here using this query:

select h.nspname, r.relname, s.subname, sr.srsubstate,
coalesce(sr.srsublsn,'0/0'::pg_lsn) as srsublsn, st.latest_end_lsn,
st.received_lsn
from pg_catalog.pg_subscription_rel sr
inner join pg_catalog.pg_subscription s on sr.srsubid = s.oid
inner join pg_catalog.pg_stat_subscription st on st.subid = s.oid
inner join pg_catalog.pg_class r on sr.srrelid = r.oid
inner join pg_catalog.pg_namespace h on r.relnamespace = h.oid
where s.subname = ?
order by h.nspname,r.relname;

But all values in srsublsn returned nulls.
Thanks!

Lucio Chiessi

Senior Database Administrator

Trustly, Inc.

--
Please read our privacy policy here
<https://www.trustly.net/about-us/privacy-policy> on how we process your
personal data in accordance with the General Data Protection Regulation
(EU) 2016/679 (the “GDPR”) and other applicable data protection legislation