os: ubuntu 16.04
zabbix: 3.4
pg_monz: 2.1
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 节点上使用 pg_monz 监控 postgresql 的具体过程。
查看 zabbix_agentd.conf
# egrep ^[A-Z] /etc/zabbix/zabbix_agentd.conf
PidFile=/var/run/zabbix/zabbix_agentd.pid
LogFile=/var/log/zabbix/zabbix_agentd.log
LogFileSize=0
Server=192.168.56.103
ServerActive=192.168.56.103
Hostname=node2
AllowRoot=1
Include=/etc/zabbix/zabbix_agentd.d/*.conf
LoadModulePath=/usr/lib/zabbix/modules
pg_monz 的简介
Directory/File name Function
Template Monitoring template
usr-local-bin/* Backend scripts
usr-local-etc/* Configuration files for backend scripts
zabbix-agentd.d/userparameter_pgsql.conf UserParameter configuration file for Zabbix AgentBackend scripts
Usr-local-bin directory includes some backend scripts. These scripts are called by UserParameters which are defined at userparameter_pgsql.conf.
Configuration files
Usr-local-etc directory includes two configuration files. These scripts are used to executing backend scripts.
pgsql_funcs.conf : Configuration file of connection information to PostgreSQL server
pgpool_funcs.conf : Configuration file of connection information to pgpool-II
[Note] At version 1.0, this information is set to Zabbix MACRO. But, at version 2.0, this information is set to above files.
pg_monz 的模版说明
Template directory includes the following 5 monitoring template xml.
Template name Use
Template_App_PostgreSQL.xml Monitoring for single PostgreSQL server
Template_App_PostgreSQL_SR.xml Monitoring for Streaming Replication
Template_App_PostgreSQL_SR_Cluster.xml Monitoring for the whole Streaming Replication cluster
Template_App_pgpool-II.xml Monitoring for pgpool-II (pgpool-II 3.5 or earlier)
Template_App_pgpool-II-36.xml Monitoring for pgpool-II (pgpool-II 3.6 or later)
Template_App_pgpool-II_watchdog.xml Monitoring for the whole pgpool-II clusterpg_monz下载、安装
Requirements
pg_monz requires the following software products: Also note that Zabbix Agent and Zabbix Sender must be installed on the monitoring target server since it utilizes the functions of Zabbix Agent and Zabbix Sender for acquiring PostgreSQL information.
Software name Version
Zabbix Server,Zabbix Agent,Zabbix Sender 2.0 or later
PostgreSQL 9.2 or later
pgpool-II 3.4.0 or laterSince bc command is executed in pg_monz backend scripts, bc command must be installed on the monitoring target server.
安装 zabbix-sender
# apt install zabbix-sender bc
# dpkg -l |grep -i zabbix
ii zabbix-agent 1:3.4.13-1+xenial amd64 Zabbix network monitoring solution - agent
ii zabbix-release 3.4-1+xenial all Zabbix official repository configuration
ii zabbix-sender 1:3.4.13-1+xenial amd64 Zabbix network monitoring solution - sender
从git下载,node1、node2上都需要操作
# cd /tmp
# wget https://github.com/pg-monz/pg_monz/archive/2.1.tar.gz
# tar -zxvf ./2.1.tar.gz
# cd pg_monz-2.1/
# more quick-install.txt
# cd pg_monz/
# cp usr-local-etc/* /usr/local/etc
# cp usr-local-bin/* /usr/local/bin
# cp zabbix_agentd.d/* /etc/zabbix/zabbix_agentd.dzabbix 导入监控模版
zabbix web 页面操作
Configuration -> Templates -> Import
导入 pg_monz 模版 Template App PostgreSQL.xml、Template_App_PostgreSQL_SR.xml、Template_App_PostgreSQL_SR_Cluster.xml
zabbix 主机配置模板
zabbix web 页面操作
Configuration -> Host groups -> Create host group
输入 PostgreSQL,输入的这个名字对应 Template_App_PostgreSQL_SR_Cluste 模版的 {$PG_HOST_GROUP} 值
Configuration -> Hosts
点击某台机器，进去选择 Templates，
点击Select，选中 Template App PostgreSQL、Template_App_PostgreSQL_SR 或者Template_App_PostgreSQL_SR_Cluster ,点击页下面的 Select
点击 Add
点击 Update
参考：
http://pg-monz.github.io/pg_monz/index-en.html
https://github.com/pg-monz/pg_monz/releases
https://github.com/pg-monz/pg_monz/releases/tag/2.1
https://github.com/pg-monz/pg_monz
附录：
# ls -l /tmp/pg_monz-2.1/pg_monz/template
total 344
-rw-rw-r-- 1 root root 56856 Mar 30 17:32 Template_App_pgpool-II-36.xml
-rw-rw-r-- 1 root root 6216 Mar 30 17:32 Template_App_pgpool-II_watchdog.xml
-rw-rw-r-- 1 root root 53618 Mar 30 17:32 Template_App_pgpool-II.xml
-rw-rw-r-- 1 root root 7974 Mar 30 17:32 Template_App_PostgreSQL_SR_Cluster.xml
-rw-rw-r-- 1 root root 52620 Mar 30 17:32 Template_App_PostgreSQL_SR.xml
-rw-rw-r-- 1 root root 164024 Mar 30 17:32 Template_App_PostgreSQL.xml# ls -l /tmp/pg_monz-2.1/pg_monz/usr-local-bin
total 88
-rwxrwxr-x 1 root root 747 Mar 30 17:32 find_dbname.sh
-rwxrwxr-x 1 root root 2064 Mar 30 17:32 find_dbname_table.sh
-rwxrwxr-x 1 root root 1363 Mar 30 17:32 find_pgpool_backend_ip.sh
-rwxrwxr-x 1 root root 841 Mar 30 17:32 find_pgpool_backend.sh
-rwxrwxr-x 1 root root 490 Mar 30 17:32 find_sr_client_ip.sh
-rwxrwxr-x 1 root root 406 Mar 30 17:32 find_sr.sh
-rwxrwxr-x 1 root root 1921 Mar 30 17:32 pgpool_backend_status.sh
-rwxrwxr-x 1 root root 2287 Mar 30 17:32 pgpool_cache.sh
-rwxrwxr-x 1 root root 1547 Mar 30 17:32 pgpool_connections.sh
-rwxrwxr-x 1 root root 433 Mar 30 17:32 pgpool_delegate_ip.sh
-rwxrwxr-x 1 root root 255 Mar 30 17:32 pgpool_simple.sh
-rwxrwxr-x 1 root root 2781 Mar 30 17:32 pgsql_db_funcs.sh
-rwxrwxr-x 1 root root 256 Mar 30 17:32 pgsql_primary.sh
-rwxrwxr-x 1 root root 4903 Mar 30 17:32 pgsql_server_funcs.sh
-rwxrwxr-x 1 root root 241 Mar 30 17:32 pgsql_simple.sh
-rwxrwxr-x 1 root root 4680 Mar 30 17:32 pgsql_sr_server_funcs.sh
-rwxrwxr-x 1 root root 249 Mar 30 17:32 pgsql_standby.sh
-rwxrwxr-x 1 root root 5368 Mar 30 17:32 pgsql_tbl_funcs.sh
-rwxrwxr-x 1 root root 1319 Mar 30 17:32 pgsql_userdb_funcs.sh# ls -l tmp/pg_monz-2.1/pg_monz/usr-local-etc
total 8
-rw-rw-r-- 1 root root 119 Mar 30 17:32 pgpool_funcs.conf
-rw-rw-r-- 1 root root 65 Mar 30 17:32 pgsql_funcs.conf# ls -l /tmp/pg_monz-2.1/pg_monz/zabbix_agentd.d
total 4
-rw-rw-r-- 1 root root 3108 Mar 30 17:32 userparameter_pgsql.conf需要copy的东西
# cp /tmp/pg_monz-2.1/pg_monz/zabbix_agentd.d/* /etc/zabbix/zabbix_agentd.d
# cp /tmp/pg_monz-2.1/pg_monz/usr-local-bin/* /usr/local/bin
# cp /tmp/pg_monz-2.1/pg_monz/usr-local-etc/* /usr/local/etc1、userparameter_pgsql.conf
# PostgreSQL user parameter
# Discovery Rule
#
# Database Discovery
UserParameter=db.list.discovery[*],"$1"/find_dbname.sh "$2"
UserParameter=db_table.list.discovery[*],"$1"/find_dbname_table.sh "$2"
UserParameter=sr.discovery[*],"$1"/find_sr.sh "$2"
UserParameter=sr.status.discovery[*],"$1"/find_sr_client_ip.sh "$2"
UserParameter=sr.db.list.discovery[*],"$1"/find_dbname.sh "$2"
# For pg_monz 2.0 psql monitoring
# $1: pgsql_*_funcs.sh directory path
# $2: pgsql_funcs.conf directory path
UserParameter=psql.running[*],"$1"/pgsql_simple.sh "$2"
UserParameter=psql.primary_server[*],"$1"/pgsql_primary.sh "$2"
UserParameter=psql.standby_server[*],"$1"/pgsql_standby.sh "$2"
# For pg_monz 2.0
#--- PostgreSQL user parameter -----------------------------------------------
# $1: pgsql_*_funcs.sh directory path
# $2: pgsql_funcs.conf directory path
# $3: hostname (which means the designated name specified on Zabbix Web UI)
# $4: zabbix_agentd.conf file path
# $5: DB name (only for LLD) or {$PGSLOWQUERY_TIME_THRESHOLD} ( only for pgsql.get.pg.slow_query )
# $6: DB schema name (only for LLD)
# $7: DB table name (only for LLD)
#-------------------------------------------------------------------------------
UserParameter=pgsql.get.pg.transactions[*],"$1"/pgsql_server_funcs.sh pg.transactions "$2" "$3" "$4"
UserParameter=pgsql.get.pg.bgwriter[*],"$1"/pgsql_server_funcs.sh pg.bgwriter $2 "$3" "$4"
UserParameter=pgsql.get.pg.slow_query[*],"$1"/pgsql_server_funcs.sh pg.slow_query "$2" "$3" "$4" "$5"
UserParameter=pgsql.get.pg.stat_database[*],"$1"/pgsql_db_funcs.sh pg.stat_database "$2" "$3" "$4" "$5"
UserParameter=pgsql.get.pg.size[*],"$1"/pgsql_userdb_funcs.sh pg.size "$2" "$3" "$4" "$5"
UserParameter=pgsql.get.pg.stat_table[*],"$1"/pgsql_tbl_funcs.sh pg.stat_table "$2" "$3" "$4" "$5" "$6" "$7"
#--- PostgreSQL SR user parameter --------------------------------------------
# $1: pgsql_*_funcs.sh directory path
# $2: pgsql_funcs.conf directory path
# $3: hostname (which means the designated name specified on Zabbix Web UI)
# $4: zabbix_agentd.conf file path
#-------------------------------------------------------------------------------
UserParameter=pgsql.get.pg.stat_replication[*],"$1"/pgsql_sr_server_funcs.sh pg.stat_replication "$2" "$3" "$4"
UserParameter=pgsql.get.pg.sr.status[*],"$1"/pgsql_sr_server_funcs.sh pg.sr.status "$2" "$3" "$4"
#--- pgpool-II user parameter --------------------------------------------
# Discovery Rule
UserParameter=backend.discovery[*],"$1"/find_pgpool_backend.sh "$2"
UserParameter=backend.status.discovery[*],"$1"/find_pgpool_backend_ip.sh "$2"
#--- pgpool-II user parameter --------------------------------------------
UserParameter=pgpool.running[*],"$1"/pgpool_simple.sh "$2"
UserParameter=pgpool.have_delegate_ip[*],"$1"/pgpool_delegate_ip.sh "$2"
UserParameter=pgpool.get.nodes[*],"$1"/pgpool_backend_status.sh pgpool.nodes "$2" "$3" "$4"
UserParameter=pgpool.get.connections[*],"$1"/pgpool_connections.sh pgpool.connections "$2" "$3" "$4"
UserParameter=pgpool.get.cache[*],"$1"/pgpool_cache.sh pgpool.cache $2 "$3" "$4"2、pgsql_funcs.conf
PGHOST=127.0.0.1
PGPORT=5432
PGROLE=postgres
PGDATABASE=postgres
3、find_sr.sh
#!/bin/bash
PGSHELL_CONFDIR="$1"
GETROW="select count(*) from pg_stat_replication"
# Load the psql connection option parameters.
source $PGSHELL_CONFDIR/pgsql_funcs.conf
result=$(psql -h $PGHOST -p $PGPORT -U $PGROLE -d $PGDATABASE -t -c "$GETROW" 2>&1)
if [ $? -ne 0 ]; then
echo "$result"
exit
fi
if [ $result -ge 1 ]; then
echo '{"data":[{"{#MODE}":"streaming"} ]}'
else
echo '{"data":[ ]}'
fi4、find_sr_client_ip.sh
#!/bin/bash
PGSHELL_CONFDIR="$1"
GETTABLE="select row_to_json(t) from (select client_addr as "{#SRCLIENT}" from pg_stat_replication) as t"
# Load the psql connection option parameters.
source $PGSHELL_CONFDIR/pgsql_funcs.conf
result=$(psql -h $PGHOST -p $PGPORT -U $PGROLE -d $PGDATABASE -t -c "${GETTABLE}" 2>&1)
if [ $? -ne 0 ]; then
echo "$result"
exit
fi
IFS=$'
'
for row in $result; do
sr_client_list="$sr_client_list,"${row# }
done
echo '{"data":['${sr_client_list#,}' ]}'