zabbix-tools
Monitor Health State of Standby Database
Hi! I'm using the package since 6 months ago. It works so nice, but I cannot find the way to check the health of the stanby database. We got 2 PostgreSQL 9.5 servers configured as Master and Slave(replica) In our case we use postgresql native replication, and we basically got the "OK" state just checking if in the log there aren't errors related with WAL files. We do this check manually, it is not automated yet on zabbix due to permission issues with postgres directory using logr.count item of zabbix.
Is there some option to control the health of the replica in a better way? If not, definitely it will be a Feature Request very useful!!
Creating a general tool to monitor PostgreSQL logs is difficult because there is no standard log format or configuration that could be assumed. For this problem, I normally recommend using a dedicated log aggregation tool (like SumoLogic, Splunk, ELK, etc.) to complement the sample-based monitoring that Zabbix excels at.
However, I will take a look to see if there are any internal counters in PostgreSQL that could be sampled to determine standby health.
Hi! We check it manually, watching the logs and also checking some queries. For example, to get a resume of the state, you can execute this query and parse to get some results postgres=# select * from pg_stat_replication ; -[ RECORD 1 ]----+--------------------------------- pid | 1114 usesysid | 16384 usename | repuser application_name | walreceiver client_addr | 172.17.0.3 client_hostname | client_port | 52444 backend_start | 15-MAY-14 19:54:05.535695 -04:00 state | streaming sent_location | 0/290044C0 write_location | 0/290044C0 flush_location | 0/290044C0 replay_location | 0/290044C0 sync_priority | 0 sync_state | async
We get a lot of valuable information from this post: https://www.enterprisedb.com/blog/monitoring-approach-streaming-replication-hot-standby-postgresql-93
I can help you to develop that functions. Thanks in advance!