Agent2 Postgresql plugin not reporting any data

[Rillekille]

Hi!

I have scratched my head the last couple of days trying to get the PostgreSQL plugin for agent2 to work.

We have a Zabbix server --> Active proxy --> Active agent setup

The agent config file:
hostname=db1.emil-systest1.domtst.se
Server=proxy1.zabbix1.mgmt.dom.se
ServerActive=proxy1.zabbix1.mgmt.dom.se
LogFile=/var/log/zabbix/zabbix_agent2.log
Include=/etc/zabbix/zabbix_agent2.d/plugins.d/postgresql.conf
ControlSocket=/tmp/agent.sock


/etc/zabbix/zabbix_agent2.d/plugins.d/postgresql.conf:
Path to external plugin executable.
#
# Mandatory: yes
# Default:
Plugins.PostgreSQL.System.Path=/usr/sbin/zabbix-agent2-plugin/zabbix-agent2-plugin-postgresql

Everything else in this file default settings.

Template "PostgreSQL by Zabbix agent 2" is associated to the host together with template "Linux by Zabbix agent active".
The template "Linux by Zabbix agent active" is working and we are getting values from it.

We have the following macros for the Postgres template:
{$PG.DATABASE}=emil
{$PG.URI}=tcp://localhost:5432
{$PG.USER}=pgwatch2
{$PG.PASSWORD}=xxx

I can login with h=localhost via psql on the database server with user pgwatch2 without any problems.

pg_hba.conf for pgwatch2 looks like this:
# TYPE DATABASE USER ADDRESS METHOD
host all pgwatch2 localhost md5
ost all pgwatch2 127.0.0.1/32 md5

No Postgres data in Zabbix UI at all.
No visible errors in the UI.
No errors in the Zabbix Server, Zabbix Proxy or the Zabbix agent2 logfiles.

It's just like the postgresql monitoring does not take place at all.

Output from zabbix_agent2 -R metrics |grep pgsql.:

pgsql.archive: Returns info about size of archive files.
pgsql.autovacuum.count: Returns count of autovacuum workers.
pgsql.bgwriter: Returns JSON for sum of each type of bgwriter statistic.
pgsql.cache.hit: Returns cache hit percent.
pgsql.connections: Returns JSON for sum of each type of connection.
pgsql.custom.query: Returns result of a custom query.
pgsql.db.age: Returns age for specific database.
pgsql.db.bloating_tables: Returns percent of bloating tables for each database.
pgsql.db.discovery: Returns JSON discovery rule with names of databases.
pgsql.db.size: Returns size in bytes for specific database.
pgsql.dbstat: Returns JSON for sum of each type of statistic.
pgsql.dbstat.sum: Returns JSON for sum of each type of statistic for all database.
pgsql.locks: Returns collect all metrics from pg_locks.
pgsql.oldest.xid: Returns age of oldest xid.
pgsql.ping: Tests if connection is alive or not.
pgsql.queries: Returns queries statistic.
pgsql.replication.count: Returns number of standby servers.
pgsql.replication.lag.b: Returns replication lag with Master in byte.
pgsql.replication.lag.sec: Returns replication lag with Master in seconds.
pgsql.replication.process: Returns flush lag, write lag and replay lag per each sender process.
pgsql.replication.process.discovery: Returns JSON with application name from pg_stat_replication.
pgsql.replication.recovery_role: Returns postgreSQL recovery role.
pgsql.replication.status: Returns postgreSQL replication status.
pgsql.uptime: Returns uptime.
pgsql.wal.stat: Returns JSON wal by type.

At startup of agent2 I can see in the logfile that the PostgreSQL plugin is loaded:

023/05/10 10:46:57.530411 using plugin 'PostgreSQL' (/usr/sbin/zabbix-agent2-plugin/zabbix-agent2-plugin-postgresql) providing following interfaces: exporter, runner, configurator


Does anyone have a clue about why this is not working?

/Regards

/Rickard Hökros

[Rillekille]

We tested to open port 10050 from the proxy server to the agent and then it started to work.
Apparantly the PostgreSQL plugin uses/needs to do passive checks.

But my problem seems to be resolved now. :-)

Thanks cyber for assisting in this matter!

Regards

/Rickard

[cyber]

And If trying with zabbix_get? no results or some error?
Or by "testing" in GUI?

[Rillekille]

Hi,

Isn't zabbix_get if you run Zabbix agent?
We run Zabbix agent2.

In the gui I haven't found the button "Test" to be available for any item, it's greyed out.
Even when I choose an item in the template "Linux by Zabbix agent active", which is working, the test button is greyed out.

I can choose an item in the template for Postgres or Linux and click Execeute and then it reports "Request sent succesfully."
But no feedback.

Attaching a screenshot from Latest data showing some Linux items and some PostgreSQL items.

Regards

/Rickard

[cyber]

Hi,

Isn't zabbix_get if you run Zabbix agent?
We run Zabbix agent2.

It does not matter... zabbix_get does not care of your agent type..

In the gui I haven't found the button "Test" to be available for any item, it's greyed out.
Even when I choose an item in the template "Linux by Zabbix agent active", which is working, the test button is greyed out.

I can choose an item in the template for Postgres or Linux and click Execeute and then it reports "Request sent succesfully."
But no feedback.

yeah.. it depends on item type... you cannot "test" active items... It should also say that, if you try "executing" of the active item...

Cannot send request: wrong item type.

And nothing in agent2 logfile about this? maybe increase of log level would give something?

[Rillekille]

I dont have zabbix_get as a command on the database server to be monitored.
The only zabbix command I have is zabbix_agent2.
These packages are installed:

zabbix-agent2/bullseye,now 1:6.4.1-1+debian11 amd64 [installed]
zabbix-agent2-plugin-postgresql/bullseye,now 0:6.4.0-1+debian11 amd64 [installed]

In the logfile from Zabbix server and Zabbix proxy I can see that sending configuration data is working.
May 11 06:38:28 app1 zabbix_server[2148]: sending configuration data to proxy "proxy1.zabbix1.mgmt.dom.se" at "10.20.20.51", datalen 164, bytes 126 with compression ratio 1.3

Proxy server receiving:
803575:20230511:063828.961 received configuration data from server at "app1.zabbix1.mgmt.dom.se", datalen 164

With DebugLevel=5 the following lines shows up when searching for 'postgres' when starting the agent:
2023/05/11 08:36:41.909054 Starting Zabbix Agent 2 (6.4.1)
2023/05/11 08:36:41.909168 Calling C function "tls_new_context()"
2023/05/11 08:36:41.909628 [PostgreSQL] plugin PostgreSQL executing Validate Request
2023/05/11 08:36:41.909643 [PostgreSQL] plugin PostgreSQL executed Validate Request
2023/05/11 08:36:41.909698 closed connection to loaded PostgreSQL plugin
---
2023/05/11 08:36:41.915941 using plugin 'PostgreSQL' (/usr/sbin/zabbix-agent2-plugin/zabbix-agent2-plugin-postgresql) providing following interfaces: exporter, runner, configurator
---

Searching for error, warning, fatal gives no hits at all.

I remember now that someone mentioned somewhere that the PostgreSQL plugin uses passive checks.
I'm quite sure that we only have opened the firewalls for active checks.
Could this be the problem?

Regards

/Rickard

[Rillekille]

We tested to open port 10050 from the proxy server to the agent and then it started to work.
Apparantly the PostgreSQL plugin uses/needs to do passive checks.

But my problem seems to be resolved now. :-)

Thanks cyber for assisting in this matter!

Regards

/Rickard

[cyber]

Oh.. of course, if item type is set as passive, then you should be able to connect from server/proxy to a agent...
But I think you can just change the type and use them also as active... I don't really see any obstacles..

Zabbix_get is usually on server/proxy. It is a good tool to test querying items from proxy/server. Locally on agent you can just use agent binary with -t option to test items.

[Rillekille]

You are right, the type is set to Zabbix agent in the template, not Zabbix agent (active).
I didn't know that it was possible to change type like that.

Seems I have to test some more, because I would like to have active checks since we will monitor quite many Postgres clusters.

I'm completely new with Zabbix so there is a lot to learn.

and thanks for the hint about zabbix_get.
I thought it should be on the agent.