Monitoring statements in pg10

Recently we migrated our first client to postgres 10.1, the database used to run on 9.2.
Everything went smooth with a total downtime of 12 seconds but obviously monitoring broke so i had to fix that and i thought to share the new statements to save people some trouble.
The statements that broke had to do with the change of xlog -> wal (and location -> lsn) which mostly affected replication monitoring statements. Bellow is what i used to have on PG 9 and what i got for PG 10.

Slot monitoring :
pg 9
SELECT slot_name, database, active,pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn) AS retained_bytes
FROM pg_replication_slots;

pg 10
SELECT slot_name, database, active, pg_wal_lsn_diff (pg_current_wal_insert_lsn(), restart_lsn)
AS retained_bytes
FROM pg_replication_slots;

Replication Lag monitoring :
pg 9
SELECT pg_xlog_location_diff(pg_current_xlog_insert_location(), flush_location) AS lag_bytes, application_name
FROM pg_stat_replication;

pg 10
SELECT pg_wal_lsn_diff (pg_current_wal_flush_lsn(), flush_lsn ) AS lag_bytes, application_name
FROM pg_stat_replication;

Thanks for reading
– Vasilis Ventirozos

Source: eVOL Monkey

Leave a Reply

Your email address will not be published. Required fields are marked *