Monday, 27 November 2017

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.

Replication Slot monitoring Postgres 9:

SELECT slot_name, database, active,pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn
AS retained_bytes
FROM pg_replication_slots;


Replication Slot monitoring Postgres 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 Postgres 9 :
SELECT pg_xlog_location_diff(pg_current_xlog_insert_location(), flush_location) AS lag_bytes, application_name
FROM pg_stat_replication;

Replication Lag monitoring Postgres 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
OmniTI Computer Consulting

Zero downtime upgrade PostgreSQL 10 to 11.

PostgreSQL 11 has been officially released and it's packed with exciting changes.Native declarative partitioning is more robust, parall...