Zabbix 通过shell脚本监控PostgreSQL

        PostgreSQL是一个功能强大的开源数据库系统。经过长达15年以上的积极开发和不断改进，PostgreSQL已在可靠性、稳定性、数据一致性等获得了业内极高的声誉。目前PostgreSQL可以运行在所有主流操作系统上，包括Linux、Unix（AIX、BSD、HP-UX、SGI IRIX、Mac OS X、Solaris和Tru64）和Windows。PostgreSQL是完全的事务安全性数据库，完整地支持外键、联合、视图、触发器和存储过程（并支持多种语言开发存储过程）。它支持了大多数的SQL:2008标准的数据类型，包括整型、数值值、布尔型、字节型、字符型、日期型、时间间隔型和时间型，它也支持存储二进制的大对像，包括图片、声音和视频。PostgreSQL对很多高级开发语言有原生的编程接口，如C/C++、Java、.Net、Perl、Python、Ruby、Tcl 和ODBC以及其他语言等，也包含各种文档。

安装（略）

参考：

         https://www.cnblogs.com/qiyebao/p/4562557.html

授权：

CREATE ROLE zabbix WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;

GRANT CONNECT ON DATABASE test TO zabbix;

alter user zabbix with password 'zabbix';

编辑zabbix客户端配置文件，添加监控项
[root@localhost zabbix]# vim /etc/zabbix/zabbix_agentd.conf
[root@localhost zabbix]# systemctl restart zabbix-agent

UserParameter=pg_chk[*],/etc/zabbix/chk_pg.sh $1 $2

[root@localhost zabbix]# more chk_pg.sh

#shell 参数说明：

• $1--->'totalsize')
• $2--->数据库名

#!/bin/bash
# by dwh 2018.6.21
case $1 in
'totalsize')
psql -h localhost -U zabbix -p 5432 test -t -c "select sum(pg_database_size(datid)) as total_size from pg_stat_database"
;;

'db_cache')
db_ca=`psql -h localhost -U zabbix -p 5432 test -t -c "select cast(blks_hit/(blks_read+blks_hit+0.000001)*100.0 as numeric(5,2)) as cache from pg_stat_database where datname
= '$2'"`
if [[ "$db_ca" == 0 ]];then
echo 0
else 
echo $db_ca
fi
;;

'db_success')
db_su=`psql -h localhost -U zabbix -p 5432 test -t -c "select cast(xact_commit/(xact_rollback+xact_commit+0.000001)*100.0 as numeric(5,2)) as success from pg_stat_database w
here datname = '$2'"`
if [[ "$db_su" == 0 ]];then
echo 0
else 
echo $db_su
fi
;;

'server_processes')
server_p=`psql -h localhost -U zabbix -p 5432 test -t -c "select sum(numbackends) from pg_stat_database"`
if [[ $server_p -eq 0 ]];then
echo 0
else 
echo $server_p
fi
;;

'tx_commited')
tx_c=`psql -h localhost -U zabbix -p 5432 test -t -c "select sum(xact_commit) from pg_stat_database"`
if [[ $tx_c -eq 0 ]];then
echo 0
else 
echo $tx_c
fi
;;

'tx_rolledback')
tx_r=`psql -h localhost -U zabbix -p 5432 test -t -c "select sum(xact_rollback) from pg_stat_database"`
if [[ $tx_r -eq 0 ]];then
echo 0
else 
echo $tx_r
fi
;;

'db_size')
db_s=`psql -h localhost -U zabbix -p 5432 test -t -c "select pg_database_size('$2')"` #as size"
if [[ $db_s -eq 0 ]];then
echo 0
else 
echo $db_s
fi
;;

'db_connections')
db_c=`psql -h localhost -U zabbix -p 5432 test -t -c "select numbackends from pg_stat_database where datname = '$2'"`
if [[ $db_c -eq 0 ]];then
echo 0
else 
echo $db_c
fi
;;

'db_returned')
db_r=`psql -h localhost -U zabbix -p 5432 test -t -c "select tup_returned from pg_stat_database where datname = '$2'"`
if [[ $db_r -eq 0 ]];then
echo 0
else 
echo $db_r
fi
;;

'db_fetched')
db_f=`psql -h localhost -U zabbix -p 5432 test -t -c "select tup_fetched from pg_stat_database where datname = '$2'"`
if [[ $db_f -eq 0 ]];then
echo 0
else 
echo $db_f
fi
;;

'db_inserted')
db_i=`psql -h localhost -U zabbix -p 5432 test -t -c "select tup_inserted from pg_stat_database where datname = '$2'"`
if [[ $db_i -eq 0 ]];then
echo 0
else 
echo $db_i
fi
;;

'db_updated')
db_u=`psql -h localhost -U zabbix -p 5432 test -t -c "select tup_updated from pg_stat_database where datname = '$2'"`
if [[ $db_u -eq 0 ]];then
echo 0
else 
echo $db_u
fi
;;

'db_deleted')
db_d=`psql -h localhost -U zabbix -p 5432 test -t -c "select tup_deleted from pg_stat_database where datname = '$2'"`
if [[ $db_d -eq 0 ]];then
echo 0
else 
echo $db_d
fi
;;

'db_commited')
db_co=`psql -h localhost -U zabbix -p 5432 test -t -c "select xact_commit from pg_stat_database where datname = '$2'"`
if [[ $db_co -eq 0 ]];then
echo 0
else 
echo $db_co
fi
;;

'db_rolled')
db_ro=`psql -h localhost -U zabbix -p 5432 test -t -c "select xact_rollback from pg_stat_database where datname = '$2'"`
if [[ $db_ro -eq 0 ]];then
echo 0
else 
echo $db_ro
fi
;;

'version')
# psql -h localhost -U zabbix -p 5432 test -t -c "select version()"
psql --version|head -n1 |awk -F " " '{print $3}'
;;
esac

进行监控：

模版下载：https://download.youkuaiyun.com/download/abel_dwh/10509978

监控结果展示：