-
Notifications
You must be signed in to change notification settings - Fork 0
/
postgresql.conf
104 lines (88 loc) · 14.2 KB
/
postgresql.conf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
# Background writer statistics. https://www.postgresql.org/docs/9.6/static/monitoring-stats.html
UserParameter=pgsql.bgwriter[*],psql -qAtX $1 -c "SELECT row_to_json(j) FROM (SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time, current_setting('block_size')::int*buffers_checkpoint AS buffers_checkpoint, current_setting('block_size')::int*buffers_clean AS buffers_clean, maxwritten_clean, current_setting('block_size')::int*buffers_backend AS buffers_backend, buffers_backend_fsync, current_setting('block_size')::int*buffers_alloc AS buffers_alloc FROM pg_stat_bgwriter) AS j"
# pg_buffercache - extension which allow examining what's happening in the shared buffer cache in real time. https://www.postgresql.org/docs/9.6/static/pgbuffercache.html
UserParameter=pgsql.buffercache[*],psql -qAtX $1 -c "SELECT row_to_json(j) FROM (SELECT current_setting('block_size')::int*count(*) AS total, current_setting('block_size')::int*sum(CASE WHEN isdirty THEN 1 ELSE 0 END) AS dirty, current_setting('block_size')::int*sum(CASE WHEN isdirty THEN 0 ELSE 1 END) AS clear, current_setting('block_size')::int*sum(CASE WHEN reldatabase IS NOT NULL THEN 1 ELSE 0 END) AS used, current_setting('block_size')::int*sum(CASE WHEN usagecount>=3 THEN 1 ELSE 0 END) AS popular FROM pg_buffercache) AS j"
# General info
UserParameter=pgsql.ping[*],/bin/echo -e "\\\timing \n select 1" | psql -qAtX $1 | tail -n 1 |cut -d' ' -f2|sed 's/,/./'
UserParameter=pgsql.uptime[*],psql -qAtX $1 -c "select date_part('epoch', now() - pg_postmaster_start_time())::int"
UserParameter=pgsql.cache.hit[*],psql -qAtX $1 -c "select round(sum(blks_hit)*100/sum(blks_hit+blks_read), 2) from pg_stat_database"
# Connections
UserParameter=pgsql.connections[*],if [ "$(psql -qAtX $1 -c 'show server_version_num')" -ge "090600" ]; then psql -qAtX $1 -c "SELECT row_to_json(j) FROM (select sum(CASE WHEN state = 'active' THEN 1 ELSE 0 END) AS active, sum(CASE WHEN state = 'idle' THEN 1 ELSE 0 END) AS idle, sum(CASE WHEN state = 'idle in transaction' THEN 1 ELSE 0 END) AS idle_in_transaction, count(*) AS total, count(*)*100/(select current_setting('max_connections')::int) AS total_pct, sum(CASE WHEN wait_event is not null THEN 1 ELSE 0 END) AS waiting from pg_stat_activity) AS j"; else psql -qAtX $1 -c "SELECT row_to_json(j) FROM (select sum(CASE WHEN state = 'active' THEN 1 ELSE 0 END) AS active, sum(CASE WHEN state = 'idle' THEN 1 ELSE 0 END) AS idle, sum(CASE WHEN state = 'idle in transaction' THEN 1 ELSE 0 END) AS idle_in_transaction, count(*) AS total, count(*)*100/(select current_setting('max_connections')::int) AS total_pct, sum(CASE WHEN waiting IS TRUE THEN 1 ELSE 0 END) AS waiting from pg_stat_activity) AS j"; fi
UserParameter=pgsql.connections.prepared[*],psql -qAtX $1 -c "select count(*) from pg_prepared_xacts"
# Size of database, table or indexes of specified table
UserParameter=pgsql.db.size[*],psql -qAtX $1 -c "select pg_database_size('$2')"
UserParameter=pgsql.table.size[*],psql -qAtX $1 -c "select pg_relation_size('$2')"
UserParameter=pgsql.index.size[*],psql -qAtX $1 -c "select pg_total_relation_size('$2') - pg_relation_size('$2')"
# Summary database statistics
UserParameter=pgsql.dbstat.sum[*],psql -qAtX $1 -c "SELECT row_to_json(j) FROM (SELECT sum(numbackends) AS numbackends, sum(xact_commit) AS xact_commit, sum(xact_rollback) AS xact_rollback, sum(blks_read) AS blks_read, sum(blks_hit) AS blks_hit, sum(tup_returned) AS tup_returned, sum(tup_fetched) AS tup_fetched, sum(tup_inserted) AS tup_inserted, sum(tup_updated) AS tup_updated, sum(tup_deleted) AS tup_deleted, sum(conflicts) AS conflicts, sum(temp_files) AS temp_files, sum(temp_bytes) AS temp_bytes, sum(deadlocks) AS deadlocks FROM pg_stat_database) AS j"
# Specified database statistics
UserParameter=pgsql.dbstat.numbackends[*],psql -qAtX $1 -c "select numbackends from pg_stat_database where datname = '$2'"
UserParameter=pgsql.dbstat.xact_commit[*],psql -qAtX $1 -c "select xact_commit from pg_stat_database where datname = '$2'"
UserParameter=pgsql.dbstat.xact_rollback[*],psql -qAtX $1 -c "select xact_rollback from pg_stat_database where datname = '$2'"
UserParameter=pgsql.dbstat.blks_read[*],psql -qAtX $1 -c "select blks_read from pg_stat_database where datname = '$2'"
UserParameter=pgsql.dbstat.blks_hit[*],psql -qAtX $1 -c "select blks_hit from pg_stat_database where datname = '$2'"
UserParameter=pgsql.dbstat.tup_returned[*],psql -qAtX $1 -c "select tup_returned from pg_stat_database where datname = '$2'"
UserParameter=pgsql.dbstat.tup_fetched[*],psql -qAtX $1 -c "select tup_fetched from pg_stat_database where datname = '$2'"
UserParameter=pgsql.dbstat.tup_inserted[*],psql -qAtX $1 -c "select tup_inserted from pg_stat_database where datname = '$2'"
UserParameter=pgsql.dbstat.tup_updated[*],psql -qAtX $1 -c "select tup_updated from pg_stat_database where datname = '$2'"
UserParameter=pgsql.dbstat.tup_deleted[*],psql -qAtX $1 -c "select tup_deleted from pg_stat_database where datname = '$2'"
UserParameter=pgsql.dbstat.conflicts[*],psql -qAtX $1 -c "select conflicts from pg_stat_database where datname = '$2'"
UserParameter=pgsql.dbstat.temp_files[*],psql -qAtX $1 -c "select temp_files from pg_stat_database where datname = '$2'"
UserParameter=pgsql.dbstat.temp_bytes[*],psql -qAtX $1 -c "select temp_bytes from pg_stat_database where datname = '$2'"
UserParameter=pgsql.dbstat.deadlocks[*],psql -qAtX $1 -c "select deadlocks from pg_stat_database where datname = '$2'"
# Table statistics (pg_stat_user_tables and pg_statio_user_tables)
UserParameter=pgsql.table.stat.heap_blks_read[*],psql -qAtX $1 -c "select coalesce(heap_blks_read,0) from pg_statio_user_tables where (schemaname || '.' || relname) = '$2'"
UserParameter=pgsql.table.stat.heap_blks_hit[*],psql -qAtX $1 -c "select coalesce(heap_blks_hit,0) from pg_statio_user_tables where (schemaname || '.' || relname) = '$2'"
UserParameter=pgsql.table.stat.idx_blks_read[*],psql -qAtX $1 -c "select coalesce(idx_blks_read,0) from pg_statio_user_tables where (schemaname || '.' || relname) = '$2'"
UserParameter=pgsql.table.stat.idx_blks_hit[*],psql -qAtX $1 -c "select coalesce(idx_blks_hit,0) from pg_statio_user_tables where (schemaname || '.' || relname) = '$2'"
UserParameter=pgsql.table.stat.toast_blks_read[*],psql -qAtX $1 -c "select coalesce(toast_blks_read,0) from pg_statio_user_tables where (schemaname || '.' || relname) = '$2'"
UserParameter=pgsql.table.stat.toast_blks_hit[*],psql -qAtX $1 -c "select coalesce(toast_blks_hit,0) from pg_statio_user_tables where (schemaname || '.' || relname) = '$2'"
UserParameter=pgsql.table.stat.tidx_blks_read[*],psql -qAtX $1 -c "select coalesce(tidx_blks_read,0) from pg_statio_user_tables where (schemaname || '.' || relname) = '$2'"
UserParameter=pgsql.table.stat.tidx_blks_hit[*],psql -qAtX $1 -c "select coalesce(tidx_blks_hit,0) from pg_statio_user_tables where (schemaname || '.' || relname) = '$2'"
UserParameter=pgsql.table.stat.seq_scan[*],psql -qAtX $1 -c "select coalesce(seq_scan,0) from pg_stat_user_tables where (schemaname || '.' || relname) = '$2'"
UserParameter=pgsql.table.stat.seq_tup_read[*],psql -qAtX $1 -c "select coalesce(seq_tup_read,0) from pg_stat_user_tables where (schemaname || '.' || relname) = '$2'"
UserParameter=pgsql.table.stat.idx_scan[*],psql -qAtX $1 -c "select coalesce(idx_scan,0) from pg_stat_user_tables where (schemaname || '.' || relname) = '$2'"
UserParameter=pgsql.table.stat.idx_tup_fetch[*],psql -qAtX $1 -c "select coalesce(idx_tup_fetch,0) from pg_stat_user_tables where (schemaname || '.' || relname) = '$2'"
UserParameter=pgsql.table.stat.n_tup_ins[*],psql -qAtX $1 -c "select coalesce(n_tup_ins,0) from pg_stat_user_tables where (schemaname || '.' || relname) = '$2'"
UserParameter=pgsql.table.stat.n_tup_del[*],psql -qAtX $1 -c "select coalesce(n_tup_del,0) from pg_stat_user_tables where (schemaname || '.' || relname) = '$2'"
UserParameter=pgsql.table.stat.n_tup_upd[*],psql -qAtX $1 -c "select coalesce(n_tup_upd,0) from pg_stat_user_tables where (schemaname || '.' || relname) = '$2'"
UserParameter=pgsql.table.stat.n_tup_hot_upd[*],psql -qAtX $1 -c "select coalesce(n_tup_hot_upd,0) from pg_stat_user_tables where (schemaname || '.' || relname) = '$2'"
UserParameter=pgsql.table.stat.n_live_tup[*],psql -qAtX $1 -c "select coalesce(n_live_tup,0) from pg_stat_user_tables where (schemaname || '.' || relname) = '$2'"
UserParameter=pgsql.table.stat.n_dead_tup[*],psql -qAtX $1 -c "select coalesce(n_dead_tup,0) from pg_stat_user_tables where (schemaname || '.' || relname) = '$2'"
UserParameter=pgsql.table.stat.vacuum_count[*],psql -qAtX $1 -c "select coalesce(vacuum_count,0) from pg_stat_user_tables where (schemaname || '.' || relname) = '$2'"
UserParameter=pgsql.table.stat.autovacuum_count[*],psql -qAtX $1 -c "select coalesce(autovacuum_count,0) from pg_stat_user_tables where (schemaname || '.' || relname) = '$2'"
UserParameter=pgsql.table.stat.analyze_count[*],psql -qAtX $1 -c "select coalesce(analyze_count,0) from pg_stat_user_tables where (schemaname || '.' || relname) = '$2'"
UserParameter=pgsql.table.stat.autoanalyze_count[*],psql -qAtX $1 -c "select coalesce(autoanalyze_count,0) from pg_stat_user_tables where (schemaname || '.' || relname) = '$2'"
# Streaming replication
UserParameter=pgsql.streaming.count[*],psql -qAtX $1 -c "select count(*) from pg_stat_replication"
UserParameter=pgsql.streaming.state[*],psql -qAtX $1 -c "select pg_is_in_recovery()"
UserParameter=pgsql.streaming.lag.bytes[*],if [ "$(psql -qAtX $1 -c 'show server_version_num')" -ge "100000" ]; then psql -qAtX $1 -c "select greatest(0,pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) from pg_stat_replication where client_addr = '$2'"; else psql -qAtX $1 -c "select greatest(0,pg_xlog_location_diff(pg_current_xlog_location(), replay_location)) from pg_stat_replication where client_addr = '$2'"; fi
UserParameter=pgsql.streaming.lag.seconds[*],if [ "$(psql -qAtX $1 -c 'show server_version_num')" -ge "100000" ]; then psql -qAtX -h $2 $1 -c "SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) END"; else psql -qAtX -h $2 $1 -c "SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) END"; fi
# Transactions
UserParameter=pgsql.transactions.idle[*],psql -qAtX $1 -c "select coalesce(extract(epoch from max(age(now(), xact_start))), 0) from pg_stat_activity where state='idle in transaction'"
UserParameter=pgsql.transactions.active[*],psql -qAtX $1 -c "select coalesce(extract(epoch from max(age(now(), xact_start))), 0) from pg_stat_activity where state='active' and query NOT LIKE 'autovacuum: %'"
UserParameter=pgsql.transactions.waiting[*],if [ "$(psql -qAtX $1 -c 'show server_version_num')" -ge "090600" ]; then psql -qAtX $1 -c "select coalesce(extract(epoch from max(age(now(), xact_start))), 0) from pg_stat_activity where wait_event is not null"; else psql -qAtX $1 -c "select coalesce(extract(epoch from max(age(now(), xact_start))), 0) from pg_stat_activity where waiting IS TRUE"; fi
UserParameter=pgsql.transactions.prepared[*],psql -qAtX $1 -c "select coalesce(extract(epoch from max(age(now(), prepared))), 0) from pg_prepared_xacts"
# pg_stat_statements
UserParameter=pgsql.pgstatstatements.avg_query_time[*],psql -qAtX $1 -c "select round((sum(total_time) / sum(calls))::numeric,2) from pg_stat_statements"
# Others
UserParameter=pgsql.table.tuples[*],psql -qAtX $1 -c "select count(*) from $2"
UserParameter=pgsql.config[*],psql -qAtX $1 -c "select json_build_object('extensions',(select array_agg(extname) from (select extname from pg_extension order by extname) as e),'settings', (select json_object(array_agg(name),array_agg(setting)) from (select name,setting from pg_settings where name != 'application_name' AND name != 'server_version' AND name != 'server_version_num' order by name) as s));"
UserParameter=pgsql.trigger[*],psql -qAtX $1 -c "select count(*) from pg_trigger where tgenabled='O' and tgname='$2'"
UserParameter=pgsql.wal.write[*],if [ "$(psql -qAtX $1 -c 'show server_version_num')" -ge "100000" ]; then psql -qAtX $1 -c "select pg_wal_lsn_diff(pg_current_wal_lsn(),'0/00000000')"; else psql -qAtX $1 -c "select pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000')"; fi
UserParameter=pgsql.wal.count[*],if [ "$(psql -qAtX $1 -c 'show server_version_num')" -ge "100000" ]; then psql -qAtX $1 -c "select count(*) from pg_ls_waldir()"; else psql -qAtX $1 -c "select count(*) from pg_ls_dir('pg_xlog')"; fi
# Discovery
UserParameter=pgsql.db.discovery[*],/bin/echo -n '{"data":['; for db in $(psql -qAtX $1 -c "select datname from pg_database where not datistemplate and datallowconn and datname!='postgres'"); do /bin/echo -n "{\"{#DBNAME}\": \"$db\"},"; done |sed -e 's:,$::'; /bin/echo -n ']}'
UserParameter=pgsql.table.discovery[*],/bin/echo -n '{"data":['; for table in $(psql -qAtX -F. $1 -c "select n.nspname,c.relname from pg_catalog.pg_class c left join pg_catalog.pg_namespace n on n.oid = c.relnamespace where c.relkind in ('r','s','') and n.nspname not in ('^pg_toast','information_schema','pg_catalog')"); do /bin/echo -n "{\"{#TABLENAME}\": \"$table\"},"; done |sed -e 's:\},$:\}:'; /bin/echo -n ']}'
UserParameter=pgsql.streaming.discovery[*],/bin/echo -n '{"data":['; for replica in $(psql -qAtX $1 -c "select client_addr from pg_stat_replication"); do /bin/echo -n "{\"{#HOTSTANDBY}\": \"$replica\"},"; done |sed -e 's:,$::'; /bin/echo -n ']}'
UserParameter=psql.db.discovery,/etc/zabbix/zabbix_agentd.conf.d/detect_database_postgresql.sh
UserParameter=psql.db.size[*],/etc/zabbix/zabbix_agentd.conf.d/size.sh db_size $1
UserParameter=psql.db.cache[*],/etc/zabbix/zabbix_agentd.conf.d/size.sh db_cache $1
UserParameter=psql.db.connections[*],/etc/zabbix/zabbix_agentd.conf.d/size.sh db_connections $1
UserParameter=psql.db.fetched[*],/etc/zabbix/zabbix_agentd.conf.d/size.sh db_fetched $1
UserParameter=psql.db.inserted[*],/etc/zabbix/zabbix_agentd.conf.d/size.sh db_inserted $1
UserParameter=psql.db.updated[*],/etc/zabbix/zabbix_agentd.conf.d/size.sh db_updated $1
UserParameter=psql.db.deleted[*],/etc/zabbix/zabbix_agentd.conf.d/size.sh db_deleted $1
UserParameter=psql.db.commited[*],/etc/zabbix/zabbix_agentd.conf.d/size.sh db_commited $1
UserParameter=psql.db.rolled[*],/etc/zabbix/zabbix_agentd.conf.d/size.sh db_rolled $1
UserParameter=psql.db.success[*],/etc/zabbix/zabbix_agentd.conf.d/size.sh db_success $1