Skip to content

Instantly share code, notes, and snippets.

@shrkw

shrkw/userparameter_postgresql.conf

Last active December 20, 2015 02:49
Show Gist options
  • Save shrkw/6059326 to your computer and use it in GitHub Desktop.
Save shrkw/6059326 to your computer and use it in GitHub Desktop.
Download ZIP
Zabbix template xml and user parameter config file for PostgreSQL 8.2 You need to make public.pg_stat_activity_as_admin view before using postgres.pg_stat_activity.waiting since seeing other owner's processes without superuser permission. http://postgres.sios.com/modules/newbb/viewtopic.php?viewmode=thread&topic_id=117&forum=1
Raw
userparameter_postgresql.conf
UserParameter=postgres.pg_stat_database[*],$PGSQL_HOME/bin/psql -h localhost -p 5432 -U zabbix-mon YOUR_DB -At -c "SELECT SUM($1) FROM pg_stat_database;"
UserParameter=postgres.pg_locks[*],$PGSQL_HOME/bin/psql -h localhost -p 5432 -U zabbix-mon YOUR_DB -At -c "SELECT COUNT(*) FROM pg_locks WHERE mode='$1';"
UserParameter=postgres.pg_stat_activity.waiting,$PGSQL_HOME/bin/psql -h localhost -p 5432 -U zabbix-mon YOUR_DB -At -c "SELECT COUNT(*) FROM public.pg_stat_activity_as_admin WHERE waiting = false;"
UserParameter=postgres.version,$PGSQL_HOME/bin/psql --version | head -1
Raw
zbx_export_templates_Postgresql8_2.xml
<?xml version="1.0" encoding="UTF-8"?>
<zabbix_export>
<version>2.0</version>
<date>2013-07-23T02:36:52Z</date>
<groups>
<group>
<name>Templates</name>
</group>
</groups>
<templates>
<template>
<template>Template App PostgreSQL 8.2</template>
<name>Template App PostgreSQL 8.2</name>
<groups>
<group>
<name>Templates</name>
</group>
</groups>
<applications>
<application>
<name>Postgres Buffers</name>
</application>
<application>
<name>Postgres CheckPoint</name>
</application>
<application>
<name>Postgres Connections</name>
</application>
<application>
<name>Postgres Locks</name>
</application>
<application>
<name>Postgres Misc</name>
</application>
<application>
<name>Postgres Tuples</name>
</application>
<application>
<name>Postgres Xact</name>
</application>
</applications>
<items>
<item>
<name>Committed transactions per second (xactcommit)</name>
<type>0</type>
<snmp_community/>
<multiplier>0</multiplier>
<snmp_oid/>
<key>postgres.pg_stat_database[xact_commit]</key>
<delay>60</delay>
<history>90</history>
<trends>365</trends>
<status>0</status>
<value_type>3</value_type>
<allowed_hosts/>
<units/>
<delta>1</delta>
<snmpv3_securityname/>
<snmpv3_securitylevel>0</snmpv3_securitylevel>
<snmpv3_authpassphrase/>
<snmpv3_privpassphrase/>
<formula>1</formula>
<delay_flex/>
<params/>
<ipmi_sensor/>
<data_type>0</data_type>
<authtype>0</authtype>
<username/>
<password/>
<publickey/>
<privatekey/>
<port/>
<description/>
<inventory_link>0</inventory_link>
<applications>
<application>
<name>Postgres Xact</name>
</application>
</applications>
<valuemap/>
</item>
<item>
<name>Exclusive lock mode (exclusivelock)</name>
<type>0</type>
<snmp_community/>
<multiplier>0</multiplier>
<snmp_oid/>
<key>postgres.pg_locks[ExclusiveLock]</key>
<delay>30</delay>
<history>90</history>
<trends>365</trends>
<status>0</status>
<value_type>3</value_type>
<allowed_hosts/>
<units/>
<delta>1</delta>
<snmpv3_securityname/>
<snmpv3_securitylevel>0</snmpv3_securitylevel>
<snmpv3_authpassphrase/>
<snmpv3_privpassphrase/>
<formula>1</formula>
<delay_flex/>
<params/>
<ipmi_sensor/>
<data_type>0</data_type>
<authtype>0</authtype>
<username/>
<password/>
<publickey/>
<privatekey/>
<port/>
<description/>
<inventory_link>0</inventory_link>
<applications>
<application>
<name>Postgres Locks</name>
</application>
</applications>
<valuemap/>
</item>
<item>
<name>Least restrictive lock mode (accesssharelock)</name>
<type>0</type>
<snmp_community/>
<multiplier>0</multiplier>
<snmp_oid/>
<key>postgres.pg_locks[AccessShareLock]</key>
<delay>30</delay>
<history>90</history>
<trends>365</trends>
<status>0</status>
<value_type>3</value_type>
<allowed_hosts/>
<units/>
<delta>1</delta>
<snmpv3_securityname/>
<snmpv3_securitylevel>0</snmpv3_securitylevel>
<snmpv3_authpassphrase/>
<snmpv3_privpassphrase/>
<formula>1</formula>
<delay_flex/>
<params/>
<ipmi_sensor/>
<data_type>0</data_type>
<authtype>0</authtype>
<username/>
<password/>
<publickey/>
<privatekey/>
<port/>
<description/>
<inventory_link>0</inventory_link>
<applications>
<application>
<name>Postgres Locks</name>
</application>
</applications>
<valuemap/>
</item>
<item>
<name>Most restrictive lock mode (accessexclusivelock)</name>
<type>0</type>
<snmp_community/>
<multiplier>0</multiplier>
<snmp_oid/>
<key>postgres.pg_locks[AccessExclusiveLock]</key>
<delay>30</delay>
<history>90</history>
<trends>365</trends>
<status>0</status>
<value_type>3</value_type>
<allowed_hosts/>
<units/>
<delta>1</delta>
<snmpv3_securityname/>
<snmpv3_securitylevel>0</snmpv3_securitylevel>
<snmpv3_authpassphrase/>
<snmpv3_privpassphrase/>
<formula>1</formula>
<delay_flex/>
<params/>
<ipmi_sensor/>
<data_type>0</data_type>
<authtype>0</authtype>
<username/>
<password/>
<publickey/>
<privatekey/>
<port/>
<description/>
<inventory_link>0</inventory_link>
<applications>
<application>
<name>Postgres Locks</name>
</application>
</applications>
<valuemap/>
</item>
<item>
<name>Number of connections pool (activeconn)</name>
<type>0</type>
<snmp_community/>
<multiplier>0</multiplier>
<snmp_oid/>
<key>postgres.pg_stat_database[numbackends]</key>
<delay>30</delay>
<history>90</history>
<trends>365</trends>
<status>0</status>
<value_type>3</value_type>
<allowed_hosts/>
<units/>
<delta>1</delta>
<snmpv3_securityname/>
<snmpv3_securitylevel>0</snmpv3_securitylevel>
<snmpv3_authpassphrase/>
<snmpv3_privpassphrase/>
<formula>1</formula>
<delay_flex/>
<params/>
<ipmi_sensor/>
<data_type>0</data_type>
<authtype>0</authtype>
<username/>
<password/>
<publickey/>
<privatekey/>
<port/>
<description/>
<inventory_link>0</inventory_link>
<applications>
<application>
<name>Postgres Connections</name>
</application>
</applications>
<valuemap/>
</item>
<item>
<name>Number of postgres owner processes</name>
<type>0</type>
<snmp_community/>
<multiplier>0</multiplier>
<snmp_oid/>
<key>proc.num[,postgres]</key>
<delay>30</delay>
<history>90</history>
<trends>365</trends>
<status>0</status>
<value_type>3</value_type>
<allowed_hosts/>
<units/>
<delta>0</delta>
<snmpv3_securityname/>
<snmpv3_securitylevel>0</snmpv3_securitylevel>
<snmpv3_authpassphrase/>
<snmpv3_privpassphrase/>
<formula>1</formula>
<delay_flex/>
<params/>
<ipmi_sensor/>
<data_type>0</data_type>
<authtype>0</authtype>
<username/>
<password/>
<publickey/>
<privatekey/>
<port/>
<description/>
<inventory_link>0</inventory_link>
<applications>
<application>
<name>Postgres Connections</name>
</application>
</applications>
<valuemap/>
</item>
<item>
<name>Number of waiting processes</name>
<type>0</type>
<snmp_community/>
<multiplier>0</multiplier>
<snmp_oid/>
<key>postgres.pg_stat_activity.waiting</key>
<delay>30</delay>
<history>90</history>
<trends>365</trends>
<status>0</status>
<value_type>3</value_type>
<allowed_hosts/>
<units/>
<delta>0</delta>
<snmpv3_securityname/>
<snmpv3_securitylevel>0</snmpv3_securitylevel>
<snmpv3_authpassphrase/>
<snmpv3_privpassphrase/>
<formula>1</formula>
<delay_flex/>
<params/>
<ipmi_sensor/>
<data_type>0</data_type>
<authtype>0</authtype>
<username/>
<password/>
<publickey/>
<privatekey/>
<port/>
<description/>
<inventory_link>0</inventory_link>
<applications>
<application>
<name>Postgres Connections</name>
</application>
</applications>
<valuemap/>
</item>
<item>
<name>PostgreSQL version</name>
<type>0</type>
<snmp_community/>
<multiplier>0</multiplier>
<snmp_oid/>
<key>postgres.version</key>
<delay>86400</delay>
<history>90</history>
<trends>365</trends>
<status>0</status>
<value_type>1</value_type>
<allowed_hosts/>
<units/>
<delta>0</delta>
<snmpv3_securityname/>
<snmpv3_securitylevel>0</snmpv3_securitylevel>
<snmpv3_authpassphrase/>
<snmpv3_privpassphrase/>
<formula>1</formula>
<delay_flex/>
<params/>
<ipmi_sensor/>
<data_type>0</data_type>
<authtype>0</authtype>
<username/>
<password/>
<publickey/>
<privatekey/>
<port/>
<description/>
<inventory_link>0</inventory_link>
<applications>
<application>
<name>Postgres Misc</name>
</application>
</applications>
<valuemap/>
</item>
<item>
<name>Rollback transactions per second (xactrollback)</name>
<type>0</type>
<snmp_community/>
<multiplier>0</multiplier>
<snmp_oid/>
<key>postgres.pg_stat_database[xact_rollback]</key>
<delay>30</delay>
<history>90</history>
<trends>365</trends>
<status>0</status>
<value_type>3</value_type>
<allowed_hosts/>
<units/>
<delta>1</delta>
<snmpv3_securityname/>
<snmpv3_securitylevel>0</snmpv3_securitylevel>
<snmpv3_authpassphrase/>
<snmpv3_privpassphrase/>
<formula>1</formula>
<delay_flex/>
<params/>
<ipmi_sensor/>
<data_type>0</data_type>
<authtype>0</authtype>
<username/>
<password/>
<publickey/>
<privatekey/>
<port/>
<description/>
<inventory_link>0</inventory_link>
<applications>
<application>
<name>Postgres Xact</name>
</application>
</applications>
<valuemap/>
</item>
<item>
<name>Row exclusive lock mode (rowexclusivelock)</name>
<type>0</type>
<snmp_community/>
<multiplier>0</multiplier>
<snmp_oid/>
<key>postgres.pg_locks[RowExclusiveLock]</key>
<delay>30</delay>
<history>90</history>
<trends>365</trends>
<status>0</status>
<value_type>3</value_type>
<allowed_hosts/>
<units/>
<delta>1</delta>
<snmpv3_securityname/>
<snmpv3_securitylevel>0</snmpv3_securitylevel>
<snmpv3_authpassphrase/>
<snmpv3_privpassphrase/>
<formula>1</formula>
<delay_flex/>
<params/>
<ipmi_sensor/>
<data_type>0</data_type>
<authtype>0</authtype>
<username/>
<password/>
<publickey/>
<privatekey/>
<port/>
<description/>
<inventory_link>0</inventory_link>
<applications>
<application>
<name>Postgres Locks</name>
</application>
</applications>
<valuemap/>
</item>
<item>
<name>Row share lock mode (rowsharelock)</name>
<type>0</type>
<snmp_community/>
<multiplier>0</multiplier>
<snmp_oid/>
<key>postgres.pg_locks[RowShareLock]</key>
<delay>30</delay>
<history>90</history>
<trends>365</trends>
<status>0</status>
<value_type>3</value_type>
<allowed_hosts/>
<units/>
<delta>1</delta>
<snmpv3_securityname/>
<snmpv3_securitylevel>0</snmpv3_securitylevel>
<snmpv3_authpassphrase/>
<snmpv3_privpassphrase/>
<formula>1</formula>
<delay_flex/>
<params/>
<ipmi_sensor/>
<data_type>0</data_type>
<authtype>0</authtype>
<username/>
<password/>
<publickey/>
<privatekey/>
<port/>
<description/>
<inventory_link>0</inventory_link>
<applications>
<application>
<name>Postgres Locks</name>
</application>
</applications>
<valuemap/>
</item>
<item>
<name>Share row exclusive lock mode (sharerowexclusivelock)</name>
<type>0</type>
<snmp_community/>
<multiplier>0</multiplier>
<snmp_oid/>
<key>postgres.pg_locks[ShareRowExclusiveLock]</key>
<delay>30</delay>
<history>90</history>
<trends>365</trends>
<status>0</status>
<value_type>3</value_type>
<allowed_hosts/>
<units/>
<delta>1</delta>
<snmpv3_securityname/>
<snmpv3_securitylevel>0</snmpv3_securitylevel>
<snmpv3_authpassphrase/>
<snmpv3_privpassphrase/>
<formula>1</formula>
<delay_flex/>
<params/>
<ipmi_sensor/>
<data_type>0</data_type>
<authtype>0</authtype>
<username/>
<password/>
<publickey/>
<privatekey/>
<port/>
<description/>
<inventory_link>0</inventory_link>
<applications>
<application>
<name>Postgres Locks</name>
</application>
</applications>
<valuemap/>
</item>
<item>
<name>Share update exclusive lock mode (shareupdateexclusivelock)</name>
<type>0</type>
<snmp_community/>
<multiplier>0</multiplier>
<snmp_oid/>
<key>postgres.pg_locks[ShareUpdateExclusiveLock]</key>
<delay>30</delay>
<history>90</history>
<trends>365</trends>
<status>0</status>
<value_type>3</value_type>
<allowed_hosts/>
<units/>
<delta>1</delta>
<snmpv3_securityname/>
<snmpv3_securitylevel>0</snmpv3_securitylevel>
<snmpv3_authpassphrase/>
<snmpv3_privpassphrase/>
<formula>1</formula>
<delay_flex/>
<params/>
<ipmi_sensor/>
<data_type>0</data_type>
<authtype>0</authtype>
<username/>
<password/>
<publickey/>
<privatekey/>
<port/>
<description/>
<inventory_link>0</inventory_link>
<applications>
<application>
<name>Postgres Locks</name>
</application>
</applications>
<valuemap/>
</item>
<item>
<name>Total memory used</name>
<type>0</type>
<snmp_community/>
<multiplier>0</multiplier>
<snmp_oid/>
<key>proc.mem[,postgres]</key>
<delay>30</delay>
<history>90</history>
<trends>365</trends>
<status>0</status>
<value_type>3</value_type>
<allowed_hosts/>
<units/>
<delta>0</delta>
<snmpv3_securityname/>
<snmpv3_securitylevel>0</snmpv3_securitylevel>
<snmpv3_authpassphrase/>
<snmpv3_privpassphrase/>
<formula>1</formula>
<delay_flex/>
<params/>
<ipmi_sensor/>
<data_type>0</data_type>
<authtype>0</authtype>
<username/>
<password/>
<publickey/>
<privatekey/>
<port/>
<description/>
<inventory_link>0</inventory_link>
<applications>
<application>
<name>Postgres Connections</name>
</application>
</applications>
<valuemap/>
</item>
</items>
<discovery_rules/>
<macros/>
<templates/>
<screens/>
</template>
</templates>
<triggers>
<trigger>
<expression>{Template App PostgreSQL 8.2:proc.num[,postgres].last(0)}&lt;1</expression>
<name>PostgreSQL is down on {HOSTNAME}</name>
<url/>
<status>0</status>
<priority>5</priority>
<description>PostgreSQL server process is not present.</description>
<type>0</type>
<dependencies/>
</trigger>
</triggers>
<graphs>
<graph>
<name>Postgres Connections</name>
<width>900</width>
<height>200</height>
<yaxismin>0.0000</yaxismin>
<yaxismax>100.0000</yaxismax>
<show_work_period>1</show_work_period>
<show_triggers>1</show_triggers>
<type>0</type>
<show_legend>1</show_legend>
<show_3d>0</show_3d>
<percent_left>0.0000</percent_left>
<percent_right>0.0000</percent_right>
<ymin_type_1>0</ymin_type_1>
<ymax_type_1>0</ymax_type_1>
<ymin_item_1>0</ymin_item_1>
<ymax_item_1>0</ymax_item_1>
<graph_items>
<graph_item>
<sortorder>0</sortorder>
<drawtype>0</drawtype>
<color>009900</color>
<yaxisside>0</yaxisside>
<calc_fnc>2</calc_fnc>
<type>0</type>
<item>
<host>Template App PostgreSQL 8.2</host>
<key>postgres.pg_stat_database[numbackends]</key>
</item>
</graph_item>
<graph_item>
<sortorder>1</sortorder>
<drawtype>0</drawtype>
<color>0000CC</color>
<yaxisside>0</yaxisside>
<calc_fnc>2</calc_fnc>
<type>0</type>
<item>
<host>Template App PostgreSQL 8.2</host>
<key>proc.num[,postgres]</key>
</item>
</graph_item>
<graph_item>
<sortorder>2</sortorder>
<drawtype>0</drawtype>
<color>EE0000</color>
<yaxisside>0</yaxisside>
<calc_fnc>2</calc_fnc>
<type>0</type>
<item>
<host>Template App PostgreSQL 8.2</host>
<key>postgres.pg_stat_activity.waiting</key>
</item>
</graph_item>
</graph_items>
</graph>
<graph>
<name>Postgres Locks</name>
<width>900</width>
<height>200</height>
<yaxismin>0.0000</yaxismin>
<yaxismax>100.0000</yaxismax>
<show_work_period>1</show_work_period>
<show_triggers>1</show_triggers>
<type>0</type>
<show_legend>1</show_legend>
<show_3d>0</show_3d>
<percent_left>0.0000</percent_left>
<percent_right>0.0000</percent_right>
<ymin_type_1>0</ymin_type_1>
<ymax_type_1>0</ymax_type_1>
<ymin_item_1>0</ymin_item_1>
<ymax_item_1>0</ymax_item_1>
<graph_items>
<graph_item>
<sortorder>4</sortorder>
<drawtype>0</drawtype>
<color>CC00CC</color>
<yaxisside>0</yaxisside>
<calc_fnc>2</calc_fnc>
<type>0</type>
<item>
<host>Template App PostgreSQL 8.2</host>
<key>postgres.pg_locks[RowExclusiveLock]</key>
</item>
</graph_item>
<graph_item>
<sortorder>3</sortorder>
<drawtype>0</drawtype>
<color>888888</color>
<yaxisside>0</yaxisside>
<calc_fnc>2</calc_fnc>
<type>0</type>
<item>
<host>Template App PostgreSQL 8.2</host>
<key>postgres.pg_locks[ShareUpdateExclusiveLock]</key>
</item>
</graph_item>
<graph_item>
<sortorder>6</sortorder>
<drawtype>0</drawtype>
<color>DD0000</color>
<yaxisside>0</yaxisside>
<calc_fnc>2</calc_fnc>
<type>0</type>
<item>
<host>Template App PostgreSQL 8.2</host>
<key>postgres.pg_locks[AccessExclusiveLock]</key>
</item>
</graph_item>
<graph_item>
<sortorder>5</sortorder>
<drawtype>0</drawtype>
<color>00CC00</color>
<yaxisside>0</yaxisside>
<calc_fnc>2</calc_fnc>
<type>0</type>
<item>
<host>Template App PostgreSQL 8.2</host>
<key>postgres.pg_locks[AccessShareLock]</key>
</item>
</graph_item>
<graph_item>
<sortorder>1</sortorder>
<drawtype>0</drawtype>
<color>00CCCC</color>
<yaxisside>0</yaxisside>
<calc_fnc>2</calc_fnc>
<type>0</type>
<item>
<host>Template App PostgreSQL 8.2</host>
<key>postgres.pg_locks[RowShareLock]</key>
</item>
</graph_item>
<graph_item>
<sortorder>2</sortorder>
<drawtype>0</drawtype>
<color>0000CC</color>
<yaxisside>0</yaxisside>
<calc_fnc>2</calc_fnc>
<type>0</type>
<item>
<host>Template App PostgreSQL 8.2</host>
<key>postgres.pg_locks[ShareRowExclusiveLock]</key>
</item>
</graph_item>
<graph_item>
<sortorder>0</sortorder>
<drawtype>0</drawtype>
<color>CCCC00</color>
<yaxisside>0</yaxisside>
<calc_fnc>2</calc_fnc>
<type>0</type>
<item>
<host>Template App PostgreSQL 8.2</host>
<key>postgres.pg_locks[ExclusiveLock]</key>
</item>
</graph_item>
</graph_items>
</graph>
<graph>
<name>Postgres Memory process</name>
<width>900</width>
<height>200</height>
<yaxismin>0.0000</yaxismin>
<yaxismax>100.0000</yaxismax>
<show_work_period>0</show_work_period>
<show_triggers>0</show_triggers>
<type>0</type>
<show_legend>1</show_legend>
<show_3d>0</show_3d>
<percent_left>0.0000</percent_left>
<percent_right>0.0000</percent_right>
<ymin_type_1>0</ymin_type_1>
<ymax_type_1>0</ymax_type_1>
<ymin_item_1>0</ymin_item_1>
<ymax_item_1>0</ymax_item_1>
<graph_items>
<graph_item>
<sortorder>0</sortorder>
<drawtype>1</drawtype>
<color>009900</color>
<yaxisside>0</yaxisside>
<calc_fnc>2</calc_fnc>
<type>0</type>
<item>
<host>Template App PostgreSQL 8.2</host>
<key>proc.mem[,postgres]</key>
</item>
</graph_item>
</graph_items>
</graph>
<graph>
<name>Postgres Transacctions Per Second</name>
<width>900</width>
<height>200</height>
<yaxismin>0.0000</yaxismin>
<yaxismax>100.0000</yaxismax>
<show_work_period>0</show_work_period>
<show_triggers>0</show_triggers>
<type>0</type>
<show_legend>1</show_legend>
<show_3d>0</show_3d>
<percent_left>0.0000</percent_left>
<percent_right>0.0000</percent_right>
<ymin_type_1>0</ymin_type_1>
<ymax_type_1>0</ymax_type_1>
<ymin_item_1>0</ymin_item_1>
<ymax_item_1>0</ymax_item_1>
<graph_items>
<graph_item>
<sortorder>1</sortorder>
<drawtype>0</drawtype>
<color>009900</color>
<yaxisside>0</yaxisside>
<calc_fnc>2</calc_fnc>
<type>0</type>
<item>
<host>Template App PostgreSQL 8.2</host>
<key>postgres.pg_stat_database[xact_commit]</key>
</item>
</graph_item>
<graph_item>
<sortorder>0</sortorder>
<drawtype>0</drawtype>
<color>BB0000</color>
<yaxisside>0</yaxisside>
<calc_fnc>2</calc_fnc>
<type>0</type>
<item>
<host>Template App PostgreSQL 8.2</host>
<key>postgres.pg_stat_database[xact_rollback]</key>
</item>
</graph_item>
</graph_items>
</graph>
</graphs>
</zabbix_export>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment