Skip to content

Postgresql monitoring Zabbix

add to /etc/zabbix/zabbix_agentd.conf this :

UserParameter=psql_collect[*],tmpfile=/tmp/zbx_psql; case "$1" in "psql_collect" ) psql -U zabbix -d postgres -c "select * from eyes.get_activity ();" > $tmpfile; echo $?;; "psql_metric" ) grep -w "$2" /tmp/zbx_psql | sed 's/.*|//g; s/ //g';; "backup_status" ) A=$(psql -U zabbix -d postgres -c "SELECT * FROM eyes.get_activity('interval_1_hour');" | grep backup | cut -d \| -f 2); [ "$A" -eq 0 ] 2>/dev/null && echo 0 || echo 1;; "database_size" ) A=$(psql -U zabbix -d postgres -c "SELECT * FROM eyes.get_activity('interval_1_hour');" | grep size | awk '{ print $3 }'); [ "$A" ] && echo "$A" || echo 0;; esac

this monitoring works with this extension ( u need to install and enable it to database postgres)

add custom metrics with different polling frequency

INSERT INTO eyes.get_activity VALUES('database_size', 'interval_1_hour',
    'SELECT sum(pg_database_size(datname)) as database_size FROM pg_database;',
    'Общий размер баз данных на сервере');

at our servers we use custom script to backup databases and we check backup metadata for backup status in this way:

INSERT INTO eyes.get_activity VALUES('backup_status', 'interval_1_hour',
    WHEN task_status = ''failed'' THEN 1
    WHEN task_status = ''running''
        AND now()-task_start_time > INTERVAL ''12h'' THEN 1
    WHEN now()-task_start_time > INTERVAL ''36h'' THEN 1
    ELSE 0
    END backup_status
FROM backup_history
WHERE task_type = ''full backup''
ORDER BY task_id DESC LIMIT 1;',
    'Статус выполнения последнего бэкапа');

and import this template to zabbix:

Primary Sidebar