postgresql monitoring error

[eugenesable26]

Have some errors during execution with pg_buffercache, pg_stat_statements. Both extension have been created properly.

template 3.4 zabbix 3.4.15 postgresql 9.5

EXECUTE_STR() command:'psql -qAtX -h 127.0.0.1 -p 5432 -U postgres -d rgu_pre -c "SELECT row_to_json(j) FROM (SELECT current_setting('block_size')::intcount() AS total, current_setting('block_size')::intsum(CASE WHEN isdirty THEN 1 ELSE 0 END) AS dirty, current_setting('block_size')::intsum(CASE WHEN isdirty THEN 0 ELSE 1 END) AS clear, current_setting('block_size')::intsum(CASE WHEN reldatabase IS NOT NULL THEN 1 ELSE 0 END) AS used, current_setting('block_size')::intsum(CASE WHEN usagecount>=3 THEN 1 ELSE 0 END) AS popular FROM pg_buffercache) AS j"' len:186 cmd_result:'ERROR: relation "pg' 48984:20190329:131129.261 Sending back [ERROR: relation "pg_buffercache" does not exist LINE 1: ... usagecount>=3 THEN 1 ELSE 0 END) AS popular FROM pg_bufferc...

EXECUTE_STR() command:'psql -qAtX -h 127.0.0.1 -p 5432 -U postgres -d rgu_pre -c "select round((sum(total_time) / sum(calls))::numeric,2) from pg_stat_statements"' len:190 cmd_result:'ERROR: relation "pg' 48984:20190329:131154.517 Sending back [ERROR: relation "pg_stat_statements" does not exist LINE 1: ...d((sum(total_time) / sum(calls))::numeric,2) from pg_stat_st... ^]

EXECUTE_STR() command:'psql -qAtX -h 127.0.0.1 -p 5432 -U postgres -d rgu_pre -c "select coalesce(extract(epoch from max(age(now(), query_start))), 0) from pg_stat_activity where wait_event is not null"' len:180 cmd_result:'ERROR: column "wait' 48984:20190329:131102.065 Sending back [ERROR: column "wait_event" does not exist LINE 1: ...), query_start))), 0) from pg_stat_activity where wait_event...

[eugenesable26]

2 of 3 errors I resolved with recreating extensions)

But "ERROR: column "wait_event" does not exist" is left, because of this column is absent in pg_stat_activity in pg9.5

[eugenesable26]

The last error resolved by changing user scripts :

waiting_event is not null -> waiting = 'true'

UserParameter=pgsql.connections[],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 = 'true' THEN 1 ELSE 0 END) AS waiting from pg_stat_activity) AS j"

UserParameter=pgsql.transactions.waiting[*],psql -qAtX $1 -c "select coalesce(extract(epoch from max(age(now(), query_start))), 0) from pg_stat_activity where waiting ='true'"

[stephankn]

Hello @eugenesable , thanks for reporting this issue. Looks this change broke compatibility with version 9.5: e6faef5dc2ecf414d3f3ae0f51dfd193a124cd19

Can you please confirm that with the following line it works as expected?

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

[stephankn]

your change will no longer work with version 9.6 and newer. Can you confirm the above line with version branching works with 9.5?

[eugenesable26]

Hello! Now it looks multipurpose. Works fine, thank u so much!