Получение параметров PostgreSQL с Zabbix агента


Ответы
  1. Создать пользователя zbx_monitor
    CREATE USER zbx_monitor WITH PASSWORD '<PASSWORD>' INHERIT;
    GRANT pg_monitor TO zbx_monitor;
  2. Разрешить мониторинг. Добавляем в файл 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
  3. Копируем 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"
  4. Создать файл 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"
  5. Макросы
    Имя Описание Значение по умолчанию
    {$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

Ссылки