PostgreSQL Monitoring

[johnsmith]

Hi folks,

I have some general questions as I have some problems with porstgresql monitoring maybe I have incorrect approach and that`s a reason of it.

I just need to know how to monitor postgresql.


I have zabbix server node + 2 more nodes , different servers. So, I need to monitor remote hosts with zabbix.

How I need to set it up ? I just don't understand how zabbix agent works there . So, I need to clone postgresql templates from githab , that's ok but where I need to place it ? I know that I need to make /var/lib/zabbix dir but where ? on zabbix server or on host machines ? and where I need to place .pgpass ?

Thank you

[johnsmith]

anybody ?

I just do not understand.

[johnsmith]

Try to search for tutorials... here are first from google:

https://www.zabbix.com/integrations/postgresql

or this with screenshots for begginers https://techexpert.tips/zabbix/monit...-using-zabbix/

Thanks for your repply , in zabbix tutorial there`s not mentioned remote servers . There`s written if you`re monitoring remote zabbix server you need to use pgpass but where and how it`s not mentioned and second link returns error 404

[johnsmith]

Heym I have started postgresql monitoring but have a differenet problem

26933:20200514:141903.106 In zbx_popen() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "million_db" -f "/var/lib/zabbix/postgresql/pgsql.scans.sql"'
26933:20200514:141903.121 EXECUTE_STR() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "million_db" -f "/var/lib/zabbix/postgresql/pgsql.scans.sql"' len:63 cmd_result:'Password for user zb'
psql: fe_sendauth: no password supplied]
26933:20200514:141930.168 In zbx_popen() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -f "/var/lib/zabbix/postgresql/pgsql.bgwriter.sql"'
26933:20200514:141930.210 EXECUTE_STR() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -f "/var/lib/zabbix/postgresql/pgsql.bgwriter.sql"' len:255 cmd_result:'{"checkpoints_timed"' 26933:20200514:141930.644 In zbx_popen() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -f "/var/lib/zabbix/postgresql/pgsql.cache.hit.sql"'
26933:20200514:141930.665 EXECUTE_STR() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -f "/var/lib/zabbix/postgresql/pgsql.cache.hit.sql"' len:6 cmd_result:'100.00'
26932:20200514:141932.868 In zbx_popen() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -f "/var/lib/zabbix/postgresql/pgsql.connections.sum.sql"'
26932:20200514:141932.885 EXECUTE_STR() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -f "/var/lib/zabbix/postgresql/pgsql.connections.sum.sql"' len:94 cmd_result:'{"active":1,"idle":3'
26931:20200514:141934.321 In zbx_popen() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -f "/var/lib/zabbix/postgresql/pgsql.dbstat.sql"'
26931:20200514:141934.345 EXECUTE_STR() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -f "/var/lib/zabbix/postgresql/pgsql.dbstat.sql"' len:1129 cmd_result:'{ "postgres" : {"dat'
26932:20200514:141934.527 In zbx_popen() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -f "/var/lib/zabbix/postgresql/pgsql.locks.sql"'
26932:20200514:141934.539 EXECUTE_STR() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -f "/var/lib/zabbix/postgresql/pgsql.locks.sql"' len:372 cmd_result:'{ "postgres" : {"dbn'
26932:20200514:141937.351 In zbx_popen() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -f "/var/lib/zabbix/postgresql/pgsql.ping.time.sql"'
26932:20200514:141937.366 EXECUTE_STR() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -f "/var/lib/zabbix/postgresql/pgsql.ping.time.sql"' len:16 cmd_result:'1
26933:20200514:141937.786 In zbx_popen() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -v tmax=30 -f "/var/lib/zabbix/postgresql/pgsql.query.time.sql"'
26933:20200514:141937.804 EXECUTE_STR() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -v tmax=30 -f "/var/lib/zabbix/postgresql/pgsql.query.time.sql"' len:404 cmd_result:'{ "postgres" : {"dat'
26932:20200514:141938.653 In zbx_popen() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -c "SELECT count(*) FROM pg_stat_replication"'
26932:20200514:141938.684 EXECUTE_STR() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -c "SELECT count(*) FROM pg_stat_replication"' len:1 cmd_result:'0'
26931:20200514:141939.543 In zbx_popen() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -f "/var/lib/zabbix/postgresql/pgsql.replication.lag.sql"'
26931:20200514:141939.555 EXECUTE_STR() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -f "/var/lib/zabbix/postgresql/pgsql.replication.lag.sql"' len:1 cmd_result:'0'
26933:20200514:141941.586 In zbx_popen() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -f "/var/lib/zabbix/postgresql/pgsql.replication.recovery_role.sql"'
26933:20200514:141941.601 EXECUTE_STR() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -f "/var/lib/zabbix/postgresql/pgsql.replication.recovery_role.sql"' len:1 cmd_result:'0'
26931:20200514:141941.826 In zbx_popen() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -f "/var/lib/zabbix/postgresql/pgsql.replication.status.sql"'
26931:20200514:141941.842 EXECUTE_STR() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -f "/var/lib/zabbix/postgresql/pgsql.replication.status.sql"' len:1 cmd_result:'2'
26933:20200514:141943.015 In zbx_popen() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -f "/var/lib/zabbix/postgresql/pgsql.transactions.sql"'
26933:20200514:141943.033 EXECUTE_STR() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -f "/var/lib/zabbix/postgresql/pgsql.transactions.sql"' len:46 cmd_result:'{"idle":0,"active":0'
26933:20200514:141944.086 In zbx_popen() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -f "/var/lib/zabbix/postgresql/pgsql.uptime.sql"'
26933:20200514:141944.115 EXECUTE_STR() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "postgres" -f "/var/lib/zabbix/postgresql/pgsql.uptime.sql"' len:3 cmd_result:'520'
26932:20200514:141950.238 In zbx_popen() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "million_db" -f "/var/lib/zabbix/postgresql/pgsql.frozenxid.sql"'
26932:20200514:141950.253 EXECUTE_STR() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "million_db" -f "/var/lib/zabbix/postgresql/pgsql.frozenxid.sql"' len:63 cmd_result:'Password for user zb'
psql: fe_sendauth: no password supplied]
26932:20200514:142002.644 In zbx_popen() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "million_db" -f "/var/lib/zabbix/postgresql/pgsql.scans.sql"'
26932:20200514:142002.659 EXECUTE_STR() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "million_db" -f "/var/lib/zabbix/postgresql/pgsql.scans.sql"' len:63 cmd_result:'Password for user zb'
psql: fe_sendauth: no password supplied]

Let`s see, I`m monitoring with default postgresql template , everything works except 2 queries

26932:20200514:141950.253 EXECUTE_STR() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "million_db" -f "/var/lib/zabbix/postgresql/pgsql.frozenxid.sql"' len:63 cmd_result:'Password for user zb'
psql: fe_sendauth: no password supplied]

26932:20200514:142002.659 EXECUTE_STR() command:' psql -qtAX -h "127.0.0.1" -p "5432" -U "zbx" -d "million_db" -f "/var/lib/zabbix/postgresql/pgsql.scans.sql"' len:63 cmd_result:'Password for user zb'
psql: fe_sendauth: no password supplied][/HTML]

pgsql.scans.sql and pgsql.frozenxid.sql is problem , it returns no pasword supplied but other queirs are working great


How can I fix that problem ?