pg_monz

PostgreSQL monitoring template for Zabbix

PostgreSQL monitoring template for Zabbix (pg_monz)

About pg_monz

PostgreSQL monitoring template for Zabbix (pg_monz) is a Zabbix template for monitoring PostgreSQL. It enables various types of monitoring of PostgreSQL such as alive, resource, performance, etc. Pg_monz also supports automatic discovery of databases and tables using the discovery feature of Zabbix and can automatically start monitoring.

Pg_monz consists of the following contents:

File nameFunction
pg_monz_template.xmlMonitoring template
userparameter_pgsql.confUser parameter configuration file for agent
find_dbname.shDatabase discovery script
find_dbname_table.shTable discovery script

Release notes

  • 2013/11/05 ver.1.0

Download

Download from GitHub releases page

Requirements

pg_monz requires the following software products:
Also note that Zabbix agent must be installed on the monitoring target server since it utilizes the functions of Zabbix agent for acquiring PostgreSQL information.

Software nameVersion
Zabbix2.0 or later
PostgreSQL9.2 or later

Installation

The following instruction assumes that the installation and configuration of the above software is finished.

1. Installation of configuration file and scripts

Copy the User parameter configuration file for agent (userparameter_pgsql.conf) to the specified location of the machine that has agent installed. For example, if Zabbix agent is installed under /usr/local/zabbix/, copy the file to the following location:

/usr/local/zabbix/etc/zabbix_agentd.conf.d/userparameter_pgsql.conf

Also add Include setting to zabbix_agentd.conf so that the above file is loaded. (requires restart to apply the setting)

Include=/usr/local/zabbix/etc/zabbix_agentd.conf.d/

Next, copy the scripts for discovery and add them executable permission. By default, it is assumed that they are installed under /usr/local/bin.

cp find_dbname.sh find_dbname_table.sh /usr/local/bin
chmod +x /usr/local/bin/find_dbname.sh
chmod +x /usr/local/bin/find_dbname_table.sh

2. Import of template

Log into the Zabbix Web interface and import the template with the following procedure:

Select ‘Configuration’ - ‘Templates’ tab and display templates list. template_list Click ‘Import’ at the upper right, select pg_monz_template.xml on ‘Import file’ and click ‘Import’. template_import If successful, ‘Template App PostgreSQL’ will be added on the templates list. template_imported

3. Configuration of template macros

Modify the configuration of template macros according to the system environments by the following procedure:

Select ‘Configuration’ - ‘Templates’ tab and display templates list. Click ‘Template App PostgreSQL’ and select ‘Macros’ tab. template_macro Modify the values of each macro according to the system environments and click ‘Save’. Normally following macros will require modifications.

Macro nameDescription
{$PGDATABASE}Database name to connect
{$PGHOST}PostgreSQL host (if same host as Zabbix agent: 127.0.0.1)
{$PGLOGDIR}Directory that contains PostgreSQL log files
{$PGPORT}Port number
{$PGROLE}PostgreSQL user name
{$PGSCRIPTDIR}Directory that has scripts installed

Usage

The following instruction describes how to start monitoring using the imported templates.

1. Creating PostgreSQL host

Creates PostgreSQL host.

Select ‘Configuration’ - ‘Hosts’ tab and display hosts list. host_list Click ‘Create host’ at the upper right and configure host name, groups etc. of target. host_config Select ‘Templates’ tab and click ‘Add’. Select ‘Template App PostgreSQL’ and click ‘Select’ and ‘Save’. host_template_select

2. Check the result of monitoring

If configured correctly, monitoring will be started automatically after a while. To check the result of monitoring, select ‘Monitoring’ - ‘Latest data’ tab.

If monitoring data are succesfully obtained, the registered host is displayed on the list. Click ‘+’ at the left of the host name to display the obtained latest value of each item. latest_items Also note that it takes a while for per-database monitoring items to be displayed because the discovery of database name is executed every hour by default.

Monitoring items

Alive monitoring of PostgreSQL server

TypeName on ZabbixInformation of item and graph, trigger condition
ItemNumber of postgres processProcess check of PostgreSQL server
ItemPostgreSQL service is runningSQL response check of PostgreSQL server
TriggerPostgreSQL process is not running.Number of process of PostgreSQL server is 0
TriggerPostgreSQL service is not running.SQL execution on PostgreSQL server failed

Monitoring of PostgreSQL log

TypeName on ZabbixInformation of item and graph, trigger condition
ItemLog of $1Messages that include PANIC,FATAL,ERROR on server log

Monitoring of database size

TypeName on ZabbixInformation of item and graph, trigger condition
Item[DB name] DB SizeSize of target database
Trigger[DB name] DB Size is too largeDatabase size exceeds threshold
Graph[DB name] DB SizeSize transition of target database

Monitoring of backend process

TypeName on ZabbixInformation of item and graph, trigger condition
ItemConnectionsNumber of backend process (total)
ItemActive (SQL processing) connectionsNumber of backend process (SQL processing)
ItemIdle connectionsNumber of backend process (waiting for query from clients)
ItemIdle in transaction connectionsNumber of backend process (waiting for commands in transaction)
ItemLock waiting connectionsNumber of backend process (waiting for locks in transaction)
TriggerMany connections are forked.Number of backend process exceeds threshold
GraphConnection countTransition of number of backend process

Monitoring of execution of checkpoints

TypeName on ZabbixInformation of item and graph, trigger condition
ItemCheckpoint count (by checkpoint_segments)Checkpoint count by checkpoint_segments
ItemCheckpoint count (by checkpoint_timeout)Checkpoint count by checkpoint_timeout
TriggerCheckpoints are occurring too frequentlyCheckpoint count in a specific period exceeds threshold
GraphCheckpoint countTransition of Checkpoint count

Monitoring of cache hit ratio

TypeName on ZabbixInformation of item and graph, trigger condition
Item[DB name] Cache Hit RatioCache hit ratio of target database
Trigger[DB name] Cache hit ratio is too lowCache hit ratio of target database is less than its threshold
Graph[DB name] Cache Hit RatioTransition of cache hit ratio of target database

Monitoring of deadlocks

TypeName on ZabbixInformation of item and graph, trigger condition
Item[DB name] DeadlocksNumber of deadlocks on target database
Trigger[DB name] Deadlocks occurred too frequentlyDeadlocks occurred more than threshold on target database
Graph[DB name] DeadlocksTransition of number of deadlocks on target database

Monitoring of transaction processes

TypeName on ZabbixInformation of item and graph, trigger condition
Item[DB name] Commited transactionsNumber of COMMIT on target database
Item[DB name] Rolled back transactionsNumber of ROLLBACK on target database
Graph[DB name] Number of commited/rolled back transactionsTransition of number of COMMIT/ROLLBACK

Monitoring of temporary file generation

TypeName on ZabbixInformation of item and graph, trigger condition
Item[DB name] Temp bytesBytes of data written to temporary files on target database
Trigger[DB name] Too many temp bytesTemporary file output on target database exceeds threshold
Graph[DB name] Temp file sizeTransition of amount of temporary files on target database

Monitoring of retained backend processes

TypeName on ZabbixInformation of item and graph, trigger condition
ItemSlow queriesNumber of backend processes which take more than specified time (active)
ItemSlow DML queriesNumber of backend processes which take more than specified time (DML processing)
ItemSlow select queriesNumber of backend processes which take more than specified time (SELECT processing)
TriggerToo many slow queriesNumber of backend processes which take more than specified time exceeds threshold

Contact

pg_monz Users Group
pg_monz@googlegroups.com

License

pg_monz is distributed under the Apache License Version 2.0. The whole text of Apache License Version 2.0 can be referred to here.

Copyright (C) 2013-2014 SRA OSS, Inc. Japan All Rights Reserved.
Copyright (C) 2013-2014 TIS Inc. All Rights Reserved.