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

Comments

  1. David J.27 November 2017 at 14:48

    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. Evol Monkey27 November 2017 at 15:16

      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. Michael Vitale30 November 2017 at 09:53

      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

Post a Comment

Popular posts from this blog

Tuning checkpoints

-
Image
We recently had the chance to help a customer with some IO related issues that ended up being unconfigured checkpoints. Something that may not always be obvious but can actually be somewhat common. Let's start with how things roughly work. Postgres smallest IO unit is a disk block that is 8kb (by default). Each time postgres needs a new block it will fetch it from the disks and load it to an area in RAM called shared_buffers. When postgres needs to write, it does it in the same manner: Fetches the block(s) from the disks if the block is not in shared_buffers Changes the page in shared buffers. Marks the page as changed (dirty) in shared buffers. It writes the change in  a "sequential ledger of changes" called WAL to ensure durability. This basically means that the writes are not yet "on disk". This operation is taken care of by a postgres process called checkpointer. Checkpoints are how postgres guarantees that data files and index files will be upda
Read more

AWS Aurora Postgres, not a great first impression

-
Image
Recently we had a customer facing some memory issues with an Aurora PostgreSQL. What was happening is that while loading data or creating GiST indexes they got the following error : 2018 - 01 - 04 14 : 03 : 06 UTC: 171 . 32 . 45 . 250 ( 35508 ):root @ test:[ 20457 ]:ERROR: out of memory 2018 - 01 - 04 14 : 03 : 06 UTC: 171 . 32 . 45 . 250 ( 35508 ):root @ test:[ 20457 ]:DETAIL: Failed on request of size 23 . 2018 - 01 - 04 14 : 03 : 06 UTC: 171 . 32 . 45 . 250 ( 35508 ):root @ test:[ 20457 ]: STATEMENT : INSERT INTO test_table1 VALUES ( 'domain1.com' , 'Manual' , '[198.168.1.148,198.168.1.250]' , 't' , 'f' , '2016-02-16 15:58:58.016626' , '' , '' ), ( 'domain2.com' , 'Manual' , '[192.168.1.1,192.168.1.174]' , 't' , 'f' , '2016-02-16 15:58:58.023136' , '' , '' ), ... At the same time they had a major drop in freeable memory
Read more

Setting up Postgres Automated Failover

-
Nowadays, failover is one of the simplest procedures in postgres. Still, there are not many ways to automate this procedure and perform it without any human interference. This blog post will guide you through a way to implement automated failover by using two open source projects, repmgr2 and pgbouncer . PGbouncer will act like a connection pool and as an entry point to the database cluster. Repmgr now, (since version 2) supports automatic failover, the slave will try to connect to the master for a predefined time and number of attempts, if it fails, it will run a script that can be anything. We are going to use this functionality to perform auto-failover, redirect our application connections to the new master database, and notify the dba that a failover has been performed. Each server will have its own copy of pgbouncer configuration file(s) and when a server is being promoted, the new master also has the responsibility to overwrite the configuration file on the server that
Read more