PostgreSQL version 10+

[meegoSVK]

Hi! Template for monitoring PostgreSQL is not working on PostgreSQL 10 since the naming convention was changed.

xlog was renamed to wal, location renamed to lsn etc. Monitoring role (pg_monitor) was added so now you do not need superuser (function pg_ls_dir for wal check should be changed to pg_ls_waldir).

I fixed it in my environment and I would like to share it.

postgres@$ diff postgresql_lt_10.conf postgresql_gt_10.conf
69,70c69,70
< UserParameter=pgsql.streaming.lag.bytes[*],psql -qAtX $1 -c "select greatest(0,pg_xlog_location_diff(pg_current_xlog_location(), replay_location)) from pg_stat_replication where client_addr = '$2'"
< UserParameter=pgsql.streaming.lag.seconds[*],psql -qAtX -h $2 $1 -c "SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) END"
---
> UserParameter=pgsql.streaming.lag.bytes[*],psql -qAtX $1 -c "select greatest(0,pg_wal_lsn_diff(pg_current_wal_lst(), replay_lsn)) from pg_stat_replication where client_addr = '$2'"
> UserParameter=pgsql.streaming.lag.seconds[*],psql -qAtX -h $2 $1 -c "SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) END"

85,86c85,86
< UserParameter=pgsql.wal.write[*],psql -qAtX $1 -c "select pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000')"
< UserParameter=pgsql.wal.count[*],psql -qAtX $1 -c "select count(*) from pg_ls_dir('pg_xlog')"
---
> UserParameter=pgsql.wal.write[*],psql -qAtX $1 -c "select pg_wal_lsn_diff(pg_current_wal_lsn(),'0/00000000')"
> UserParameter=pgsql.wal.count[*],psql -qAtX $1 -c "select count(*) from pg_ls_waldir()"
92d91
<
postgres@$

[stephankn]

Thank you for mentioning this incompatible change with PostgreSQL 10. I think we should implement a conditional query to still support older versions. As only the name of the function changed this should be easy.

Did you notice any further incompatibility with version 10?

[meegoSVK]

Not yet. We are currently deciding between a few monitoring systems. I am new to Zabbix so we are just discovering the spirit of this system. We are trying Zabbix 3.4 and we are using only PostgreSQL 10. There are not many targets to be monitored yet, so in a few months I will give better feedback.

Thank you for your work, this template looks pretty good for our use case!

Sincerely Meego

[stephankn]

@meegoSVK Would be great if you could confirm this works with version 10+, as my setup is still running 9.6.

[meegoSVK]

@stephankn It is working :)