- Создать пользователя zbx_monitor
CREATE USER zbx_monitor WITH PASSWORD '<PASSWORD>' INHERIT;
GRANT pg_monitor TO zbx_monitor;
- Разрешить мониторинг. Добавляем в файл pg_hba.conf следующие строки
host all zbx_monitor 127.0.0.1/32 trust
host all zbx_monitor 0.0.0.0/0 md5
host all zbx_monitor ::0/0 md5
- Копируем SQL запросы из официального репозитария (используется оболочка IPython)
url = 'https://git.zabbix.com/projects/ZBX/repos/zabbix/raw/templates/db/postgresql/postgresql/{}?at=refs%2Fheads%2Fmaster'
files = [
'pgsql.bgwriter.sql',
'pgsql.cache.hit.sql',
'pgsql.config.hash.sql',
'pgsql.connections.prepared.sql',
'pgsql.connections.sql',
'pgsql.connections.sum.sql',
'pgsql.dbstat.sql',
'pgsql.dbstat.sum.sql',
'pgsql.discovery.db.sql',
'pgsql.frozenxid.sql',
'pgsql.locks.sql',
'pgsql.ping.time.sql',
'pgsql.query.time.sql',
'pgsql.replication.lag.sql',
'pgsql.replication.recovery_role.sql',
'pgsql.replication.status.sql',
'pgsql.scans.sql',
'pgsql.transactions.sql',
'pgsql.uptime.sql',
'pgsql.wal.stat.sql'
]
for file in files:
full = url.format(file)
print(full)
!wget -O /var/lib/zabbix/postgresql/$file "$full"
- Создать файл template_db_postgresql.conf
UserParameter=pgsql.bgwriter[*], psql -qtAX -h $1 -p $2 -U $3 -d $4 -f "/var/lib/zabbix/postgresql/pgsql.bgwriter.sql"
UserParameter=pgsql.connections.sum[*], psql -qtAX -h $1 -p $2 -U $3 -d $4 -f "/var/lib/zabbix/postgresql/pgsql.connections.sum.sql"
UserParameter=pgsql.connections[*], psql -qtAX -h $1 -p $2 -U $3 -d $4 -f "/var/lib/zabbix/postgresql/pgsql.connections.sql"
UserParameter=pgsql.connections.prepared[*], psql -qtAX -h $1 -p $2 -U $3 -d $4 -f "/var/lib/zabbix/postgresql/pgsql.connections.prepared.sql"
UserParameter=pgsql.dbstat.sum[*], psql -qtAX -h $1 -p $2 -U $3 -d $4 -f "/var/lib/zabbix/postgresql/pgsql.dbstat.sum.sql"
UserParameter=pgsql.dbstat[*], psql -qtAX -h $1 -p $2 -U $3 -d $4 -f "/var/lib/zabbix/postgresql/pgsql.dbstat.sql"
UserParameter=pgsql.transactions[*], psql -qtAX -h $1 -p $2 -U $3 -d $4 -f "/var/lib/zabbix/postgresql/pgsql.transactions.sql"
UserParameter=pgsql.config.hash[*], psql -qtAX -h $1 -p $2 -U $3 -d $4 -f "/var/lib/zabbix/postgresql/pgsql.config.hash.sql"
UserParameter=pgsql.wal.stat[*], psql -qtAX -h $1 -p $2 -U $3 -d $4 -f "/var/lib/zabbix/postgresql/pgsql.wal.stat.sql"
UserParameter=pgsql.locks[*], psql -qtAX -h $1 -p $2 -U $3 -d $4 -f "/var/lib/zabbix/postgresql/pgsql.locks.sql"
UserParameter=pgsql.queries[*], psql -qtAX -h $1 -p $2 -U $3 -d $4 -v tmax=$5 -f "/var/lib/zabbix/postgresql/pgsql.query.time.sql"
UserParameter=pgsql.uptime[*], psql -qtAX -h $1 -p $2 -U $3 -d $4 -f "/var/lib/zabbix/postgresql/pgsql.uptime.sql"
UserParameter=pgsql.cache.hit[*], psql -qtAX -h $1 -p $2 -U $3 -d $4 -f "/var/lib/zabbix/postgresql/pgsql.cache.hit.sql"
UserParameter=pgsql.scans[*], psql -qtAX -h $1 -p $2 -U $3 -d $4 -f "/var/lib/zabbix/postgresql/pgsql.scans.sql"
UserParameter=pgsql.frozenxid[*], psql -qtAX -h $1 -p $2 -U $3 -d $4 -f "/var/lib/zabbix/postgresql/pgsql.frozenxid.sql"
UserParameter=pgsql.discovery.db[*], psql -qtAX -h $1 -p $2 -U $3 -d $4 -f "/var/lib/zabbix/postgresql/pgsql.discovery.db.sql"
UserParameter=pgsql.db.size[*], psql -qtAX -h $1 -p $2 -U $3 -d $4 -c "SELECT pg_database_size('$5')"
UserParameter=pgsql.ping[*], pg_isready -h $1 -p $2 -U $3 -d $4
UserParameter=pgsql.ping.time[*], psql -qtAX -h $1 -p $2 -U $3 -d $4 -f "/var/lib/zabbix/postgresql/pgsql.ping.time.sql"
UserParameter=pgsql.version[*], psql -qtAX -h $1 -p $2 -U $3 -d $4 -c "SELECT version();"
UserParameter=pgsql.replication.count[*], psql -qtAX -h $1 -p $2 -U $3 -d $4 -c "SELECT count(*) FROM pg_stat_replication"
UserParameter=pgsql.replication.recovery_role[*], psql -qtAX -h $1 -p $2 -U $3 -d $4 -f "/var/lib/zabbix/postgresql/pgsql.replication.recovery_role.sql"
UserParameter=pgsql.replication.lag.sec[*], psql -qtAX -h $1 -p $2 -U $3 -d $4 -f "/var/lib/zabbix/postgresql/pgsql.replication.lag.sql"
UserParameter=pgsql.replication.status[*], psql -qtAX -h $1 -p $2 -U $3 -d $4 -f "/var/lib/zabbix/postgresql/pgsql.replication.status.sql"
- Макросы
| Имя |
Описание |
Значение по умолчанию |
| {$PG.HOST} |
Database server host or socket directory |
127.0.0.1 |
| {$PG.PORT} |
Database server port |
5432 |
| {$PG.USER} |
Database user name |
zbx_monitor |
| {$PG.DB} |
Database name to connect to the server |
postgres |
| {$PG.LLD.FILTER.DBNAME} |
Regular expression for filtering names of discovered databases |
(.*) |
| {$PG.CHECKPOINTS_REQ.MAX.WARN} |
Requested checkpoints threshold for trigger expression |
5 |
| {$PG.PING_TIME.MAX.WARN} |
Maximum ping time for trigger expression |
1s |
| {$PG.CACHE_HITRATIO.MIN.WARN} |
Minimum cache hit ratio for trigger expression |
90 |
| {$PG.CONN_TOTAL_PCT.MAX.WARN} |
Maximum number of open connections for trigger expression |
90 |
| {$PG.CONN_WAIT.MAX.WARN} |
Maximum number of waiting connections for trigger expression |
0 |
| {$PG.CONN_IDLE_IN_TRANS.MAX.WARN} |
Maximum number of 'idle in transaction' connections for trigger expression |
5 |
| {$PG.DEADLOCKS.MAX.WARN} |
Maximum number of deadlocks for trigger expression |
0 |
| {$PG.CONFLICTS.MAX.WARN} |
Maximum number of recovery conflicts for trigger expression |
0 |
| {$PG.REPL_LAG.MAX.WARN} |
Maximum replication lag for trigger expression |
10m |
| {$PG.TRANS_ACTIVE.MAX.WARN} |
Maximum active transaction time for trigger expression |
30s |
| {$PG.TRANS_IDLE.MAX.WARN} |
Maximum 'idle in transaction' connection time for trigger expression |
30s |
| {$PG.TRANS_WAIT.MAX.WARN} |
Maximum waiting transaction time for trigger expression |
30s |
| {$PG.LOCKS.MAX.WARN} |
Maximum number of locks for trigger expression |
100 |
| {$PG.QUERY_ETIME.MAX.WARN} |
Maximum query execution time in seconds |
30 |
| {$PG.SLOW_QUERIES.MAX.WARN} |
Maximum number of slow queries for trigger expression |
5 |
| {$PG.FROZENXID_PCT_STOP.MIN.HIGH} |
Minimum percentage of frozen XID |
75 |
Ссылки
|