Posts

Showing posts from 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:

SELECTslot_name,database,active,pg_xlog_location_diff(pg_current_xlog_insert_location(),restart_lsnASretained_bytesFROMpg_replication_slots;

Replication Slot monitoring Postgres 10:

SELECTslot_name,database,active,pg_wal_lsn_diff(pg_current_wal_insert_lsn(),restart_lsn)ASretained_bytesFROMpg_replication_slots;
Replication Lag monitoring Postgres 9 :
SELECTpg_xlog_location_diff(pg_current_xlog_insert_location(),flush_location)ASlag_bytes,application_nameFROMpg_stat_replication…