ubuntu 16.04 + zabbix 3.4 + postgresql UserParameter

os: ubuntu 16.04
zabbix: 3.4

ip 规划
192.168.56.101 node1 pgsql 9.6 master
192.168.56.102 node2 pgsql 9.6 slave
192.168.56.103 node3 zabbix proxy
192.168.56.104 node4 zabbix server

本篇blog介绍在 node1、node2 节点上使用 UserParameter 监控 postgresql的具体过程。

创建 postgresql.conf

# egrep ^[A-Z] /etc/zabbix/zabbix_agentd.conf |grep -i include
Include=/etc/zabbix/zabbix_agentd.d/*.conf

# vi /etc/zabbix/zabbix_agentd.d/manual_postgresql96.conf

#postgresql version
UserParameter=manual_postgresql96.version,su - postgres -c "psql --version|head -n1"

#postgres isalive 
UserParameter=manual_postgresql96.isalive,su - postgres -c "psql -U zabbix postgres  -Atqc 'select 1'"

#postgresql connection count
UserParameter=manual_postgresql96.server_processes,su - postgres -c "psql -U zabbix postgres -Atqc 'select sum(numbackends) from pg_stat_database'"

#postgresql commited transactions
UserParameter=manual_postgresql96.tx_commited,su - postgres -c "psql -U zabbix postgres -Atqc 'select sum(xact_commit) from pg_stat_database'"

#postgresql rolled back transactions
UserParameter=manual_postgresql96.tx_rolledback,su - postgres -c "psql -U zabbix postgres -Atqc 'select sum(xact_rollback) from pg_stat_database'"

测试一下

# zabbix_agentd -t manual_postgresql96.version
# zabbix_agentd -t manual_postgresql96.isalive
# zabbix_agentd -t manual_postgresql96.server_processes
# zabbix_agentd -t manual_postgresql96.tx_commited
# zabbix_agentd -t manual_postgresql96.tx_rolledback

创建模版文件

就是手动创建一个符合规范的模版文件 Template_Manual_PostgreSQL96.xml
内容较长，附后。

导入监控模版

zabbix web 页面操作

Configuration -> Templates -> Import

可以看到模版 Template_Manual_PostgreSQL96.xml

主机配置模板

zabbix web 页面操作

Configuration -> Hosts

点击某台机器，进去选择 Templates，
点击Select，选中 Template DB Manual PostgreSQL96 ,点击页下面的 Select
点击 Add
点击 Update

稍等一会，就能看到采集到postgresql的数据了。

一些注意事项

需要注意的是模版文件的 value_type 需要和 manual_postgresql96.conf 对应之类型一致，否则会报错。
value_type integer
0 - 浮点数
1 - 字符
2 - 日志
3 - 数字（无正负）
4 - 文本

下面这个错误描述的是模版定义的是 “Numeric (unsigned)” 类型，但是实际执行返回的值是 “string”

  1640:20180906:090855.099 item "node1:manual_postgresql96.isalive" became not supported: Value "su: must be run from a terminal" of type "string" is not suitable for value type "Numeric (unsigned)"
  1640:20180906:090855.099 item "node1:manual_postgresql96.server_processes" became not supported: Value "su: must be run from a terminal" of type "string" is not suitable for value type "Numeric (unsigned)"
  1640:20180906:090855.099 item "node1:manual_postgresql96.tx_commited" became not supported: Value "su: must be run from a terminal" of type "string" is not suitable for value type "Numeric (unsigned)"
  1640:20180906:090855.100 item "node1:manual_postgresql96.tx_rolledback" became not supported: Value "su: must be run from a terminal" of type "string" is not suitable for value type "Numeric (unsigned)"

这是是需要修改 AllowRoot=1

# vi /etc/zabbix/zabbix_agentd.conf
AllowRoot=1

# /etc/init.d/zabbix-agent restart

然后在 zabbix server 的日志可以看到如下信息。

# tail -f /var/log/zabbix/zabbix_server.log 

  1639:20180906:095615.954 item "node1:manual_postgresql96.isalive" became supported
  1637:20180906:095625.966 item "node1:manual_postgresql96.server_processes" became supported
  1637:20180906:095625.967 item "node1:manual_postgresql96.tx_commited" became supported
  1637:20180906:095625.967 item "node1:manual_postgresql96.tx_rolledback" became supported

本次是通过 UserParameter 实现了监控 postgresql 的几个指标,应该是作为一个实现的样例。

实际中都是通过shell脚本集成到zabbix里来实现监控补充的。

参考：
https://www.zabbix.com/documentation/3.4/zh/manual/config/items/userparameters
https://www.zabbix.com/documentation/3.4/zh/manual/config/items/userparameters/extending_agent

https://www.zabbix.com/documentation/3.4/zh/manual/xml_export_import/templates?s[]=value&s[]=type

Template_Manual_PostgreSQL96.xml 的具体内容

<?xml version="1.0" encoding="UTF-8"?>
<zabbix_export>
    <version>3.4</version>
    <date>2018-09-05T15:51:31Z</date>
    <groups>
        <group>
            <name>Templates/Databases</name>
        </group>
    </groups>
    <templates>
        <template>
            <template>Template DB Manual PostgreSQL96</template>
            <name>Template DB Manual PostgreSQL96</name>
            <description/>
            <groups>
                <group>
                    <name>Templates/Databases</name>
                </group>
            </groups>
            <applications/>
            <items>
                <item>
                    <name>Manual PostgreSQL Status</name>
                    <type>0</type>
                    <snmp_community/>
                    <snmp_oid/>
                    <key>net.tcp.listen[5432]</key>
                    <delay>30s</delay>
                    <history>90d</history>
                    <trends>365d</trends>
                    <status>0</status>
                    <value_type>3</value_type>
                    <allowed_hosts/>
                    <units/>
                    <snmpv3_contextname/>
                    <snmpv3_securityname/>
                    <snmpv3_securitylevel>0</snmpv3_securitylevel>
                    <snmpv3_authprotocol>0</snmpv3_authprotocol>
                    <snmpv3_authpassphrase/>
                    <snmpv3_privprotocol>0</snmpv3_privprotocol>
                    <snmpv3_privpassphrase/>
                    <params/>
                    <ipmi_sensor/>
                    <authtype>0</authtype>
                    <username/>
                    <password/>
                    <publickey/>
                    <privatekey/>
                    <port/>
                    <description/>
                    <inventory_link>0</inventory_link>
                    <applications/>
                    <valuemap/>
                    <logtimefmt/>
                    <preprocessing/>
                    <jmx_endpoint/>
                    <master_item/>
                </item>
                <item>
                    <name>Manual PostgreSQL Version</name>
                    <type>0</type>
                    <snmp_community/>
                    <snmp_oid/>
                    <key>manual_postgresql96.version</key>
                    <delay>30s</delay>
                    <history>90d</history>
                    <trends>0</trends>
                    <status>0</status>
                    <value_type>0</value_type>
                    <allowed_hosts/>
                    <units/>
                    <snmpv3_contextname/>
                    <snmpv3_securityname/>
                    <snmpv3_securitylevel>0</snmpv3_securitylevel>
                    <snmpv3_authprotocol>0</snmpv3_authprotocol>
                    <snmpv3_authpassphrase/>
                    <snmpv3_privprotocol>0</snmpv3_privprotocol>
                    <snmpv3_privpassphrase/>
                    <params/>
                    <ipmi_sensor/>
                    <authtype>0</authtype>
                    <username/>
                    <password/>
                    <publickey/>
                    <privatekey/>
                    <port/>
                    <description/>
                    <inventory_link>0</inventory_link>
                    <applications/>
                    <valuemap/>
                    <logtimefmt/>
                    <preprocessing/>
                    <jmx_endpoint/>
                    <master_item/>
                </item>
                <item>
                    <name>Manual PostgreSQL IsAlive</name>
                    <type>0</type>
                    <snmp_community/>
                    <snmp_oid/>
                    <key>manual_postgresql96.isalive</key>
                    <delay>30s</delay>
                    <history>90d</history>
                    <trends>365d</trends>
                    <status>0</status>
                    <value_type>3</value_type>
                    <allowed_hosts/>
                    <units/>
                    <snmpv3_contextname/>
                    <snmpv3_securityname/>
                    <snmpv3_securitylevel>0</snmpv3_securitylevel>
                    <snmpv3_authprotocol>0</snmpv3_authprotocol>
                    <snmpv3_authpassphrase/>
                    <snmpv3_privprotocol>0</snmpv3_privprotocol>
                    <snmpv3_privpassphrase/>
                    <params/>
                    <ipmi_sensor/>
                    <authtype>0</authtype>
                    <username/>
                    <password/>
                    <publickey/>
                    <privatekey/>
                    <port/>
                    <description/>
                    <inventory_link>0</inventory_link>
                    <applications/>
                    <valuemap/>
                    <logtimefmt/>
                    <preprocessing/>
                    <jmx_endpoint/>
                    <master_item/>
                </item>
                <item>
                    <name>Manual PostgreSQL Server Processes</name>
                    <type>0</type>
                    <snmp_community/>
                    <snmp_oid/>
                    <key>manual_postgresql96.server_processes</key>
                    <delay>30s</delay>
                    <history>90d</history>
                    <trends>365d</trends>
                    <status>0</status>
                    <value_type>3</value_type>
                    <allowed_hosts/>
                    <units/>
                    <snmpv3_contextname/>
                    <snmpv3_securityname/>
                    <snmpv3_securitylevel>0</snmpv3_securitylevel>
                    <snmpv3_authprotocol>0</snmpv3_authprotocol>
                    <snmpv3_authpassphrase/>
                    <snmpv3_privprotocol>0</snmpv3_privprotocol>
                    <snmpv3_privpassphrase/>
                    <params/>
                    <ipmi_sensor/>
                    <authtype>0</authtype>
                    <username/>
                    <password/>
                    <publickey/>
                    <privatekey/>
                    <port/>
                    <description/>
                    <inventory_link>0</inventory_link>
                    <applications/>
                    <valuemap/>
                    <logtimefmt/>
                    <preprocessing/>
                    <jmx_endpoint/>
                    <master_item/>
                </item>
                <item>
                    <name>Manual PostgreSQL Tx Commited</name>
                    <type>0</type>
                    <snmp_community/>
                    <snmp_oid/>
                    <key>manual_postgresql96.tx_commited</key>
                    <delay>30s</delay>
                    <history>90d</history>
                    <trends>365d</trends>
                    <status>0</status>
                    <value_type>3</value_type>
                    <allowed_hosts/>
                    <units/>
                    <snmpv3_contextname/>
                    <snmpv3_securityname/>
                    <snmpv3_securitylevel>0</snmpv3_securitylevel>
                    <snmpv3_authprotocol>0</snmpv3_authprotocol>
                    <snmpv3_authpassphrase/>
                    <snmpv3_privprotocol>0</snmpv3_privprotocol>
                    <snmpv3_privpassphrase/>
                    <params/>
                    <ipmi_sensor/>
                    <authtype>0</authtype>
                    <username/>
                    <password/>
                    <publickey/>
                    <privatekey/>
                    <port/>
                    <description/>
                    <inventory_link>0</inventory_link>
                    <applications/>
                    <valuemap/>
                    <logtimefmt/>
                    <preprocessing/>
                    <jmx_endpoint/>
                    <master_item/>
                </item>
                <item>
                    <name>Manual PostgreSQL Tx Rollback</name>
                    <type>0</type>
                    <snmp_community/>
                    <snmp_oid/>
                    <key>manual_postgresql96.tx_rolledback</key>
                    <delay>30s</delay>
                    <history>90d</history>
                    <trends>365d</trends>
                    <status>0</status>
                    <value_type>3</value_type>
                    <allowed_hosts/>
                    <units/>
                    <snmpv3_contextname/>
                    <snmpv3_securityname/>
                    <snmpv3_securitylevel>0</snmpv3_securitylevel>
                    <snmpv3_authprotocol>0</snmpv3_authprotocol>
                    <snmpv3_authpassphrase/>
                    <snmpv3_privprotocol>0</snmpv3_privprotocol>
                    <snmpv3_privpassphrase/>
                    <params/>
                    <ipmi_sensor/>
                    <authtype>0</authtype>
                    <username/>
                    <password/>
                    <publickey/>
                    <privatekey/>
                    <port/>
                    <description/>
                    <inventory_link>0</inventory_link>
                    <applications/>
                    <valuemap/>
                    <logtimefmt/>
                    <preprocessing/>
                    <jmx_endpoint/>
                    <master_item/>
                </item>
            </items>
            <discovery_rules/>
            <httptests/>
            <macros/>
            <templates/>
            <screens/>
        </template>
    </templates>
    <triggers>
        <trigger>
            <expression>{Template DB Manual PostgreSQL96:net.tcp.listen[5432].last()}=0</expression>
            <recovery_mode>0</recovery_mode>
            <recovery_expression/>
            <name>PostgreSQL is down</name>
            <correlation_mode>0</correlation_mode>
            <correlation_tag/>
            <url/>
            <status>0</status>
            <priority>3</priority>
            <description/>
            <type>0</type>
            <manual_close>0</manual_close>
            <dependencies/>
            <tags/>
        </trigger>
    </triggers>
    <graphs>
        <graph>
            <name>Manual PostgreSQL Status</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>1A7C11</color>
                    <yaxisside>0</yaxisside>
                    <calc_fnc>2</calc_fnc>
                    <type>0</type>
                    <item>
                        <host>Template DB Manual PostgreSQL96</host>
                        <key>net.tcp.listen[5432]</key>
                    </item>
                </graph_item>
            </graph_items>
        </graph>
        <graph>
            <name>Manual PostgreSQL Server Processes</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>1A7C11</color>
                    <yaxisside>0</yaxisside>
                    <calc_fnc>2</calc_fnc>
                    <type>0</type>
                    <item>
                        <host>Template DB Manual PostgreSQL96</host>
                        <key>manual_postgresql96.server_processes</key>
                    </item>
                </graph_item>
            </graph_items>
        </graph>
        <graph>
            <name>Manual PostgreSQL Tx</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>1A7C11</color>
                    <yaxisside>0</yaxisside>
                    <calc_fnc>2</calc_fnc>
                    <type>0</type>
                    <item>
                        <host>Template DB Manual PostgreSQL96</host>
                        <key>manual_postgresql96.tx_commited</key>
                    </item>
                </graph_item>
                <graph_item>
                    <sortorder>1</sortorder>
                    <drawtype>0</drawtype>
                    <color>F63100</color>
                    <yaxisside>0</yaxisside>
                    <calc_fnc>2</calc_fnc>
                    <type>0</type>
                    <item>
                        <host>Template DB Manual PostgreSQL96</host>
                        <key>manual_postgresql96.tx_rolledback</key>
                    </item>
                </graph_item>
            </graph_items>
        </graph>
    </graphs>
</zabbix_export>