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.

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

3 comments:

  1. FYI 10.x is the major release series just like 9.2.x was. The x.1 is now a minor/patch release identifier. Next year 11.x will be released. There is no longer a third position in the version number. Also, the "9" series included 9.3.x - 9.6.x. Your comparison skips a few releases but the name changes you cite were indeed released in 10, the later 9 releases all used the same old names.

    ReplyDelete
    Replies
    1. I don't see any comparisons in my post, or how version naming changes are relevant , i just say how statements (more like functions) were named on pg9 and how they got renamed in pg10 , thanks for the comment though.

      Delete
    2. Here is the breakdown of the 9.x major versions with respect to the replication tables mentioned above:

      PG 9.0 has neither pg_replication_slots or pg_stat_replication

      PG 9.1 does not have pg_replication_slots, but does have pg_stat_replication without the function, pg_xlog_location_diff()

      PG 9.2 does not have pg_replication_slots, but does have pg_stat_replication with the function, pg_xlog_location_diff()

      PG 9.3 does not have pg_replication_slots, but does have pg_stat_replication with the function, pg_xlog_location_diff()

      PG 9.4 - PG 9.6 have both pg_replication_slots and pg_stat_replication.

      Delete