前面介紹的PostgreSQL監控工具都偏向于性能分析，沒有告警功能。而且它們隻是針對PostgreSQL的監視，有時需要監控整個業務相關的系統，這時候就要考慮通用的監控工具了。Linux下比較适合監控資料庫的常用的工具有Nagios和Zabbix。Zabbix更容易使用，現在看上去也更被多數人看好，是以本文隻介紹Zabbix監控PostgreSQL的方法。
Zabbix是一個all in one高度內建的企業級監控解決方案。由一個中心的Zabbix Server和若幹可能安裝有Zabbix Agent被監控裝置構成，主要特性可概括為以下幾點
資料采集
支援agent和agent less(SNMP, IPMI, HTTP，FTP...)
支援基于JMX對java應用的監視
可靈活定制agent
資料存儲
資料庫為PostgreSQL,Mysql,Oracle，SQLite或DB2
可配置曆史和趨勢資料的儲存時間
内建舊清理程式防止資料膨脹
報警
可定制報警門檻值
靈活設定報警方式，郵件，SMS，腳本
支援報警更新
報警消息可使用宏變量定制
可視化
可定制的資料圖形
儀表盤
地圖
所有配置都通過GUI編輯
大規模部署
支援模闆
自動發現主機和監控項目
通過Zabbix Proxy實作分布式部署
其他
Zabbix API
認證和通路控制
IT資産收集
zabbix要想監視PostgreSQL這種應用型的對象，一般使用zabbix agent。zabbix agent有2種工作方式。
方式1：被動代理
由Zabbix Server(或Proxy)主動查詢資料（如CPU負載），作為響應Zabbix Agent傳回查詢結果。這也是最簡單最常用的方式。
方式2：主動代理
Zabbix Agent先從Zabbix Server擷取需要主動報告的監控項目一覽，然後定期發送新值到Zabbix Server。主動代理可以用于處理時間比較長的監控項，比如log 。
也可以使用Zabbix Trapper
方式3：Trapper
由Zabbix Agent主動報告資料。被監控端可調用zabbix_send指令或直接利用Socket發送資料到Zabbix Server。通過Trapper可以隻在狀态變更時進行報告。
另外還有把PostgreSQL狀态通過SNMP代理釋出的方案（http://pgsnmpd.projects.pgfoundry.org/），估計用的不多，本文不涉及。
詳細參考:
https://www.zabbix.com/documentation/2.4/manual/concepts/agent
https://www.zabbix.com/documentation/2.4/manual/appendix/items/activepassive
https://www.zabbix.com/documentation/2.4/manual/config/items/itemtypes/trapper
Zabbix沒有内置對PostgreSQL的監控項，是以如果要監控PostgreSQL需要做一些監控項的配置或定制，下面會介紹幾種方法。
自己修改zabbix_agentd.conf的配置檔案，在Zabbix agent上增加PostgreSQL相關的監控項，使用psql發SQL的方式擷取PostgreSQL的性能資料。
例
zabbix_agentd.conf
#Get the PostgreSQL version
UserParameter=psql.version,psql --version|head -n1
#Get the total number of Server Processes that are active
UserParameter=psql.server_processes,psql -t -c "select sum(numbackends) from pg_stat_database"
#Get the total number of commited transactions
UserParameter=psql.tx_commited,psql -t -c "select sum(xact_commit) from pg_stat_database"
#Get the total number of rolled back transactions
UserParameter=psql.tx_rolledback,psql -t -c "select sum(xact_rollback) from pg_stat_database"
參照
https://www.zabbix.com/wiki/howto/monitor/db/postgresql
比如參照下面的例子
https://www.zabbix.com/forum/showthread.php?t=8009
zabbix_agentd.conf：
UserParameter=postgresql[*],/opt/zabbix/bin/zapost $1 $2
zapost：
#
# Name: zapost
# Checks PostgreSQL activity.
# Author: bashman
# Version: 1.0
zapostver="1.0"
rval=0
sql=""
case $1 in
#'summary')
# sql="select a.datname, pg_size_pretty(pg_database_size(a.datid)) as size, cast(blks_hit/(blks_read+blks_hit+0.000001)*100.0 as numeric(5,2)) as cache, cast(xact_commit/(xact_rollback+xact_commit+0.000001)*100.0 as numeric(5,2)) as success from pg_stat_database a order by a.datname"
# ;;
#'size')
#comprobar aqui los parametros
# shift
# sql="select pg_database_size('$1') as size"
#'version')
# sql='select version()'
'totalsize')
sql="select sum(pg_database_size(datid)) as total_size from pg_stat_database"
;;
'db_cache')
# comprueba los parametros
if [ ! -z $2 ]; then
shift
sql="select cast(blks_hit/(blks_read+blks_hit+0.000001)*100.0 as numeric(5,2)) as cache from pg_stat_database where datname = '$1'"
fi
'db_success')
if [ ! -z $2 ]; then
sql="select cast(xact_commit/(xact_rollback+xact_commit+0.000001)*100.0 as numeric(5,2)) as success from pg_stat_database where datname = '$1'"
;;
'server_processes')
sql="select sum(numbackends) from pg_stat_database"
'tx_commited')
sql="select sum(xact_commit) from pg_stat_database"
'tx_rolledback')
sql="select sum(xact_rollback) from pg_stat_database"
'db_size')
# comprueba los parametros
sql="select pg_database_size('$1')" #as size"
'db_connections')
sql="select numbackends from pg_stat_database where datname = '$1'"
'db_returned')
sql="select tup_returned from pg_stat_database where datname = '$1'"
'db_fetched')
sql="select tup_fetched from pg_stat_database where datname = '$1'"
'db_inserted')
shift
sql="select tup_inserted from pg_stat_database where datname = '$1'"
'db_updated')
sql="select tup_updated from pg_stat_database where datname = '$1'"
'db_deleted')
sql="select tup_deleted from pg_stat_database where datname = '$1'"
'db_commited')
sql="select xact_commit from pg_stat_database where datname = '$1'"
'db_rolled')
sql="select xact_rollback from pg_stat_database where datname = '$1'"
'version')
sql="version"
'zapostver')
echo "$zapostver"
exit $rval
*)
echo "zapost version: $zapostver"
echo "usage:"
echo " $0 totalsize -- Check the total databases size."
echo " $0 db_cache dbname> -- Check the database cache hit ratio (percentage)."
echo " $0 db_success dbname> -- Check the database success rate (percentage)."
echo " $0 server_processes -- Check the total number of Server Processes that are active."
echo " $0 tx_commited -- Check the total number of commited transactions."
echo " $0 tx_rolledback -- Check the total number of rolled back transactions."
echo " $0 db_size dbname> -- Check the size of a Database (in bytes)."
echo " $0 db_connections dbname> -- Check the number of active connections for a specified database."
echo " $0 db_returned dbname> -- Check the number of tuples returned for a specified database."
echo " $0 db_fetched dbname> -- Check the number of tuples fetched for a specified database."
echo " $0 db_inserted dbname> -- Check the number of tuples inserted for a specified database."
echo " $0 db_updated dbname> -- Check the number of tuples updated for a specified database."
echo " $0 db_deleted dbname> -- Check the number of tuples deleted for a specified database."
echo " $0 db_commited dbname> -- Check the number of commited back transactions for a specified database."
echo " $0 db_rolled dbname> -- Check the number of rolled back transactions for a specified database."
echo " $0 version -- The PostgreSQL version."
echo " $0 zapostver -- Version of this script."
exit $rval
esac
if [ "$sql" != "" ]; then
if [ "$sql" == "version" ]; then
psql --version|head -n1
rval=$?
else
psql -t -c "$sql"
fi
if [ "$rval" -ne 0 ]; then
echo "ZBX_NOTSUPPORTED
和前面提到的2種方法相比，Postbix插件的功能更全面，它包含了PostgreSQL相關的監控項和圖形的Zabbix模闆。Postbix以一個的背景java deamon運作，這個deamon通過jdbc查詢遠端被監控資料庫的狀态然後以trap agent的方式發送到Zabbix Server。
Postbix來自http://www.smartmarmot.com/，除了Postbix該公司還有Orabbix,MySQLBix，這些工具的内部架構和使用方法基本相同，唯一的差別就是支援的被監控資料庫不同。是以smartmarmot又推出了整合這幾種資料庫監控能力的DBforBIX。DBforBIX的内部結構和使用方法和Postbix基本相同，下面介紹一下DBforBIX的簡單的使用例子。
1）下載下傳dbforbix
http://www.smartmarmot.com/product/dbforbix/dbforbix-download/
2）在Zabbix Server上安裝dbforbix
[root@zabbix ~]# mkdir /opt/dbforbix
[root@zabbix ~]# cd /opt/dbforbix
[root@zabbix dbforbix]# unzip /root/dbforbix-0.6.1.zip
[root@zabbix dbforbix]# cp /opt/dbforbix/init.d/dbforbix /etc/init.d/dbforbix
[root@zabbix dbforbix]# chmod +x /etc/init.d/dbforbix
[root@zabbix dbforbix]# chmod +x /opt/dbforbix/run.sh
[root@zabbix dbforbix]# chkconfig dbforbix on
3)導入dbforix的模闆到Zabbix伺服器
點選Zabbix GUI畫面的"Configuration->Templates->Import"把下面的模闆檔案導入Zabbix伺服器。
/opt/dbforbix/template/template_postgresql.xml
4)在被監控PostgreSQL執行個體上建立DBforBIX使用的賬号并賦予權限
CREATE USER zabbix WITH PASSWORD 'passw0rd';
GRANT SELECT ON pg_stat_activity to zabbix;
GRANT SELECT ON pg_database to zabbix;
GRANT SELECT ON pg_authid to zabbix;
GRANT SELECT ON pg_stat_bgwriter to zabbix;
GRANT SELECT ON pg_locks to zabbix;
GRANT SELECT ON pg_stat_database to zabbix
5)修改config.props
通過拷貝config.props.sample生成config.props, 然後修改config.props設定Zabbix Server的IP和端口号，設定被監控PostgreSQL資料庫的通路賬号
[root@zabbix dbforbix]# cp /opt/dbforbix/conf/config.props.sample /opt/dbforbix/conf/config.props
[root@zabbix dbforbix]# vi /opt/dbforbix/conf/config.props
ZabbixServerList=ZabbixServer
ZabbixServer.Address=IP_ADDRESS_OF_ZABBIX_SERVER
ZabbixServer.Port=PORT_OF_ZABBIX_SERVER
...
DBforBIX.PidFile=./logs/dbforbix.pid
DatabaseList=PGSQLDB2
PGSQLDB2.Url=jdbc:postgresql://host:port/database
PGSQLDB2.User=zabbix
PGSQLDB2.Password=passw0rd
注意：/opt/dbforbix/init.d/dbforbix有個Bug，本來應該從config.props中讀取pid檔案名的，結果寫死了是dbforbix.pid，config.props.sample中的預設值又是orabix.pid。
6)修改pgsqlquery.props
通過拷貝pgsqlquery.props.sample生成pgsqlquery.props。pgsqlquery.props中定義了監控項目及對應的查詢SQL，可以編輯QueryList對監控項做篩選。
[root@zabbix dbforbix]# cp /opt/dbforbix/conf/pgsqlquery.props.sample /opt/dbforbix/conf/pgsqlquery.props
看一下pgsqlquery.props.sample包含的内容
pgsqlquery.props.sample:
QueryList=activeconn,tupfetched,tupinserted,tupupdated,tupdeleted,xactcommit,xactrollback,exclusivelock,accessexclusivelock,accesssharelock,rowsharelock,rowexclusivelock,shareupdateexclusivelock,sharerowexclusivelock,checkpoints_timed,checkpoints_req,buffers_checkpoint,buffers_clean,maxwritten_clean,buffers_backend,buffers_alloc
#statistic of database
activeconn.Query=select sum(numbackends) from pg_stat_database
tupreturned.Query=select sum(tup_returned) from pg_stat_database
tupfetched.Query=select sum(tup_fetched) from pg_stat_database
tupinserted.Query=select sum(tup_inserted) from pg_stat_database
tupupdated.Query=select sum(tup_updated) from pg_stat_database
tupdeleted.Query=select sum(tup_deleted) from pg_stat_database
xactcommit.Query=SELECT sum(xact_commit) FROM pg_stat_database
xactrollback.Query=SELECT sum(xact_rollback) FROM pg_stat_database
#locks
exclusivelock.Query=SELECT count(*) FROM pg_locks where mode='ExclusiveLock'
accessexclusivelock.Query=SELECT count(*) FROM pg_locks where mode='AccessExclusiveLock'
accesssharelock.Query=SELECT count(*) FROM pg_locks where mode='AccessShareLock'
rowsharelock.Query=SELECT count(*) FROM pg_locks where mode='RowShareLock'
rowexclusivelock.Query=SELECT count(*) FROM pg_locks where mode='RowExclusiveLock'
shareupdateexclusivelock.Query=SELECT count(*) FROM pg_locks where mode='ShareUpdateExclusiveLock'
sharerowexclusivelock.Query=SELECT count(*) FROM pg_locks where mode='ShareRowExclusiveLock'
checkpoints_timed.Query=select checkpoints_timed from pg_stat_bgwriter
checkpoints_req.Query=select checkpoints_req from pg_stat_bgwriter
buffers_checkpoint.Query=select buffers_checkpoint from pg_stat_bgwriter
buffers_clean.Query=select buffers_clean from pg_stat_bgwriter
maxwritten_clean.Query=select maxwritten_clean from pg_stat_bgwriter
buffers_backend.Query=select buffers_backend from pg_stat_bgwriter
buffers_alloc.Query=select buffers_alloc from pg_stat_bgwriter
7）啟動dbforbix deamon
[root@zabbix dbforbix]# /etc/init.d/dbforbix start
8) 在Zabbix Server上建立Host
點選Zabbix GUI畫面的"Configuration->Hosts->Create Host"為被監控資料庫建立一個專門的Host。"Host name"設定為“PGSQLDB2”( 必須和config.props的DatabaseList中的名稱一緻，這裡是“PGSQLDB2”)。并且把Host“PGSQLDB2”連結到前面導入的模闆"Template_PostgeSQL"。
9) 檢查資料是否已被收集
點選Zabbix GUI畫面的"Monitoring->Last data"檢查資料是否已被收集。
參考
http://www.smartmarmot.com/wiki/index.php/DBforBIX
pg_monz是一套可以監控PostgreSQL的zabbix模闆，通過定制的agent UserParameter監控PostgreSQL資料庫，并且利用Zabbix的發現機制可以自動發現和監視資料庫和表。
pg_monz由下面幾個檔案組成
檔案
說明
pg_monz_template.xml
模版定義檔案
userparameter_pgsql.conf
提供PostgreSQL監控項目的使用者參數定義
find_dbname.sh
PG資料庫的自動發現腳本
find_dbname_table.sh
PG資料表的自動發現腳本
要了解pg_monz支援哪些監控項目，看一下userparameter_pgsql.conf就可以了
userparameter_pgsql.conf:
點選(此處)折疊或打開
# PostgreSQL user parameter
# Server specific examples
# Get the total number of commited transactions
UserParameter=psql.tx_commited[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select sum(xact_commit) from pg_stat_database"
# Get the total number of rolled back transactions
UserParameter=psql.tx_rolledback[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select sum(xact_rollback) from pg_stat_database"
# Max Connections
UserParameter=psql.server_maxcon[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "show max_connections"
# PostgreSQL is running
UserParameter=psql.running[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select 1" > /dev/null 2>&1 ; echo $?
# Added by SRA OSS
# Get number of checkpoint count (by checkpoint_timeout)
UserParameter=psql.checkpoints_timed[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select checkpoints_timed from pg_stat_bgwriter"
# Get number of checkpoint count (by checkpoint_segments)
UserParameter=psql.checkpoints_req[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select checkpoints_req from pg_stat_bgwriter"
# Get the total number of connections
UserParameter=psql.server_connections[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select count(*) from pg_stat_activity;"
# Get the total number of active (on processing SQL) connections
UserParameter=psql.active_connections[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select count(state) from pg_stat_activity where state = 'active'"
# Get the total number of idle connections
UserParameter=psql.idle_connections[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select count(state) from pg_stat_activity where state = 'idle'"
# Get the total number of idle in transaction connections
UserParameter=psql.idle_tx_connections[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select count(state) from pg_stat_activity where state = 'idle in transaction'"
# Get the total number of lock-waiting connections
UserParameter=psql.locks_waiting[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select count(*) from pg_stat_activity where waiting = 't'"
# Get buffer information
UserParameter=psql.buffers_checkpoint[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select buffers_checkpoint from pg_stat_bgwriter"
UserParameter=psql.buffers_clean[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select buffers_clean from pg_stat_bgwriter"
UserParameter=psql.maxwritten_clean[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select maxwritten_clean from pg_stat_bgwriter"
UserParameter=psql.buffers_backend[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select buffers_backend from pg_stat_bgwriter"
UserParameter=psql.buffers_backend_fsync[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select buffers_backend_fsync from pg_stat_bgwriter"
UserParameter=psql.buffers_alloc[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select buffers_alloc from pg_stat_bgwriter"
# Get number of slow queries
UserParameter=psql.slow_queries[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select count(*) from pg_stat_activity where state = 'active' and now() - query_start > '$5 sec'::interval"
UserParameter=psql.slow_select_queries[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select count(*) from pg_stat_activity where state = 'active' and now() - query_start > '$5 sec'::interval and query ilike 'select%'"
UserParameter=psql.slow_dml_queries[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select count(*) from pg_stat_activity where state = 'active' and now() - query_start > '$5 sec'::interval and query ~* '^(insert|update|delete)'"
# Database specific examples
# Get the size of a Database (in bytes)
UserParameter=psql.db_size[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select pg_database_size('$5')"
# Get number of active connections for a specified database
UserParameter=psql.db_connections[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select numbackends from pg_stat_database where datname = '$5'"
# Get number of tuples returned for a specified database
UserParameter=psql.db_returned[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select tup_returned from pg_stat_database where datname = '$5'"
# Get number of tuples fetched for a specified database
UserParameter=psql.db_fetched[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select tup_fetched from pg_stat_database where datname = '$5'"
# Get number of tuples inserted for a specified database
UserParameter=psql.db_inserted[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select tup_inserted from pg_stat_database where datname = '$5'"
# Get number of tuples updated for a specified database
UserParameter=psql.db_updated[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select tup_updated from pg_stat_database where datname = '$5'"
# Get number of tuples deleted for a specified database
UserParameter=psql.db_deleted[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select tup_deleted from pg_stat_database where datname = '$5'"
# Get number of commited/rolled back transactions for a specified database
UserParameter=psql.db_tx_commited[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select xact_commit from pg_stat_database where datname = '$5'"
UserParameter=psql.db_tx_rolledback[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select xact_rollback from pg_stat_database where datname = '$5'"
# Cache Hit Ratio
UserParameter=psql.cachehit_ratio[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "SELECT round(blks_hit*100/(blks_hit+blks_read), 2) AS cache_hit_ratio FROM pg_stat_database WHERE datname = '$5' and blks_read > 0 union all select 0.00 AS cache_hit_ratio order by cache_hit_ratio desc limit 1"
# Get number of temp files
UserParameter=psql.db_temp_files[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select temp_files from pg_stat_database where datname = '$5'"
# Get temp file size (in bytes)
UserParameter=psql.db_temp_bytes[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select temp_bytes from pg_stat_database where datname = '$5'"
# Get percentage of dead tuples of all tables for a specified database
UserParameter=psql.db_dead_tup_ratio[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select round(sum(n_dead_tup)*100/sum(n_live_tup+n_dead_tup), 2) as dead_tup_ratio from pg_stat_all_tables where n_live_tup > 0"
# Get number of deadlocks for a specified database (9.2 or later)
UserParameter=psql.db_deadlocks[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select deadlocks from pg_stat_database where datname = '$5'"
# Table specific examples
# Get table cache hit ratio of a specific table
UserParameter=psql.table_cachehit_ratio[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select round(heap_blks_hit*100/(heap_blks_hit+heap_blks_read), 2) as cache_hit_ratio from pg_statio_user_tables where schemaname = '$5' and relname = '$6' and heap_blks_read > 0 union all select 0.00 as cache_hit_ratio order by cache_hit_ratio desc limit 1"
# Get number of sequencial scan of a specific table
UserParameter=psql.table_seq_scan[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select seq_scan from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
# Get number of index scan of a specific table
UserParameter=psql.table_idx_scan[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select coalesce(idx_scan,0) from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
# Get number of vacuum count of a specific table
UserParameter=psql.table_vacuum_count[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select vacuum_count from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
# Get number of analyze count of a specific table
UserParameter=psql.table_analyze_count[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select analyze_count from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
# Get number of autovacuum count of a specific table
UserParameter=psql.table_autovacuum_count[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select autovacuum_count from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
# Get number of autoanalyze count of a specific table
UserParameter=psql.table_autoanalyze_count[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select autoanalyze_count from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
# Get number of tuples of a specific table
UserParameter=psql.table_n_tup_ins[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select n_tup_ins from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
UserParameter=psql.table_n_tup_upd[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select n_tup_upd from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
UserParameter=psql.table_n_tup_del[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select n_tup_del from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
UserParameter=psql.table_seq_tup_read[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select seq_tup_read from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
UserParameter=psql.table_idx_tup_fetch[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select coalesce(idx_tup_fetch,0) from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
UserParameter=psql.table_n_tup_hot_upd[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select n_tup_hot_upd from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
UserParameter=psql.table_n_live_tup[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select n_live_tup from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
UserParameter=psql.table_n_dead_tup[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select n_dead_tup from pg_stat_user_tables where schemaname = '$5' and relname = '$6'"
# Discovery Rule
# Database Discovery
UserParameter=db.list.discovery[*],$5/find_dbname.sh $1 $2 $3 $4
UserParameter=db_table.list.discovery[*],$5/find_dbname_table.sh $1 $2 $3 $4
使用例：
1）下載下傳pg_monz
https://github.com/pg-monz/pg_monz/releases
2）安裝pg_monz
[root@zabbix ~]# tar xfz pg_monz-1.0.tar.gz
[root@zabbix ~]# cd pg_monz-1.0/pg_monz
[root@zabbix pg_monz]# cp find_dbname.sh find_dbname_table.sh /usr/local/bin/
[root@zabbix pg_monz]# cp userparameter_pgsql.conf /etc/zabbix/zabbix_agentd.d/
[root@zabbix pg_monz]# chmod +x /usr/local/bin/find_dbname.sh
[root@zabbix pg_monz]# chmod +x /usr/local/bin/find_dbname_table.sh
[root@zabbix pg_monz]# /etc/init.d/zabbix-agent restart
3）導入模闆到Zabbix伺服器
點選Zabbix GUI畫面的"Configuration->Templates->Import"把模闆檔案pg_monz_template.xml導入Zabbix伺服器。
4）設定模闆中的宏
點選Zabbix GUI畫面的"Configuration->Templates"，再點選其中的"PostgreSQL Check"模闆，然後點選"Marcos" Tab設定必要宏參數（尤其是連接配接相關的參數）。
5)在Zabbix Server上建立Host
點選Zabbix GUI畫面的"Configuration->Hosts->Create Host"為被監控資料庫所在主機建立一個Host，如果該主機的Host已存在也可使用已有Host。這個Host要設定Zabbix Agent，并且把該Host連結到前面導入的模闆"PostgeSQL Check"。
6) 檢查資料是否已被收集
http://pg-monz.github.io/pg_monz/index-en.html
以上的方法1和方法2都需要自己再進行定制，而DBforBIX和pg_monz已經比較成熟了。pg_monz和DBforBIX相比更簡單，可監控的PostgreSQL項目也更多，還可以自動發現庫和表；DBforBIX的優勢則在于支援監控多種常用的資料庫以及可以使用jdbc連接配接池。綜合而言如果不需要監控多種資料庫個人傾向于pg_monz。