libzbxpgsql on Zabbix 4.0 : no data

[jrdonath]

Hi,

I have compiled/installed libzbxpgsql-1.1.0 on a client running CentOS 7 with Zabbix-agent 4.0. I also have imported the Template_PostgreSQL_Server_3.0.xml on the Zabbix server.

Now everything looks fine:

• module is loaded succesfully on the agent
• I can query Postgresql server data with zabbix-get both from the server and the client

But unfortunately no data shows up when using the template.

The Zabbix server has (as shown above) tcp/10050 access to the Postgres server and vice versa on port tcp/10051.

What am I doing wrong?

Thanks, John

[jedd]

Hi John,

Some troubleshooting questions:

Are you running v4 on the zabbix server as well?

Did you set up the macros (template or host) for PG_DB and PG_CONN?

If you can share those and your zabbix_get CLI that'd be great.

What's your pg_hba.conf line for connection (and are you using pg_ident.conf option) - can you share those fragments?

Can you confirm agent is installed on the server running PostgreSQL? (What version of PostgreSQL?)

Are you getting non-PostgreSQL zabbix-agent metrics coming back to the server?

cheers, Jedd.

[jrdonath]

Hi Jedd,

I am running the Zabbix 4.0 on both server and clients.

I have setup the macro's for the host: {$PG_CONN} => user=zabbix {$PG_DB} => postgres

I have not touched these macro's on template level!

Example of zabbix_get: [root@zabbix01 ~]# zabbix_get -s course-pg01 -k 'pg.tablespace.size[user=zabbix,postgres,pg_default]' 9849435579 [root@zabbix01 ~]# zabbix_get -s course-pg01 -k 'pg.tablespace.size[user=zabbix,postgres,pg_global]' ZBX_NOTSUPPORTED: PostgreSQL error for query "SELECT pg_tablespace_size($1)": ERROR: permission denied for tablespace pg_global [root@zabbix01 ~]# zabbix_get -s course-pg01 -k 'pg.setting[user=zabbix,postgres,autovacuum_analyze_scale_factor]' 0.100000 [root@zabbix01 ~]# zabbix_get -s course-pg01 -k 'pg.setting[user=zabbix,postgres,wal_block_size]' 8192

pg_hba.conf on the client server: ` [postgres@course-pg01 ~]$ cat $PGDATA/pg_hba.conf

TYPE DATABASE USER ADDRESS METHOD

"local" is for Unix domain socket connections only

local all all peer

IPv4 local connections:

host all zabbix 127.0.0.1/32 trust host all all 127.0.0.1/32 md5 host all all 127.0.0.1/32 ident

vpn

hostssl all all 10.211.10.22/32 md5

Allow replication connections from localhost, by a user with the replication privilege.

local replication all peer host replication all 127.0.0.1/32 ident host replication replica 10.227.45.50/32 md5 `

[root@course-pg01 ~]# ps -ef | grep zabb zabbix 24935 1 0 Nov25 ? 00:00:00 /usr/sbin/zabbix_agentd -c /etc/zabbix/zabbix_agentd.conf zabbix 24940 24935 0 Nov25 ? 00:00:47 /usr/sbin/zabbix_agentd: collector [idle 1 sec] zabbix 24941 24935 0 Nov25 ? 00:01:53 /usr/sbin/zabbix_agentd: listener #1 [waiting for connection] zabbix 24942 24935 0 Nov25 ? 00:01:52 /usr/sbin/zabbix_agentd: listener #2 [waiting for connection] zabbix 24943 24935 0 Nov25 ? 00:01:53 /usr/sbin/zabbix_agentd: listener #3 [waiting for connection] zabbix 24944 24935 0 Nov25 ? 00:00:14 /usr/sbin/zabbix_agentd: active checks #1 [idle 1 sec] zabbix 24945 24935 0 Nov25 ? 00:00:15 /usr/sbin/zabbix_agentd: active checks #2 [idle 1 sec

PostgreSQL server version: [root@zabbix01 ~]# zabbix_get -s course-pg01 -k 'pg.version[user=zabbix,postgres]' PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit

Finally, yes, I am getting lot of non-PostgreSQL zabbix-agent metrics coming back to the server.

Thanks, John

[jrdonath]

@jedd , Sorry for the corrupted formatting!

[jedd]

Hi John,

Very weird behaviour.

I saw some oddness, that I didn't fully explore, with dissimilar zabbix_get and zabbix GUI configs, but I put that down to the changes I was making at the time with pg_ident & pg_hba. Can you try chucking in a ' host=localhost port=5432' on your PG_CONN macro, just in case? (I realise this suggestion borders on superstition.)

I'm assuming you're tailing the logs on the agent - is there anything interesting?

Slight aside - I'd be wary about using 'trust' with your pg_hba (though it doesn't look like permissions are the problem here).

Actually you could flip to local rather than host access (ie. socket rather than tcp) - but again it doesn't look like a permissions or postgres configuration issue, given zabbix_get is working.

On Zabbix server, can you check the 5 discovery rules for the template? I think the Settings rule is disabled by default.

[purpendicular]

@jedd Hi Jedd, I will get back to your last comment next week ... Thanks so far.

[purpendicular]

Hi Jedd,

I 'chucked in' localhost port=5432 on the PG_CONN macro; now it looks like this: host=localhost port=5432 user=zabbix connect_timeout=10

But still no luck.

I don't see any sign of activity on the clients port 5432 when using tcpdump.

As I told before bot local and remote zabbix-get's using the PG_CONN settings are positive:

Server: [root@zabbix01 ~]# zabbix_get -s course-pg01 -k 'pg.backends.free[host=localhost port=5432 user=zabbix,postgres]' 1999.000000

Client: [zabbix@course-pg01 ~]$ zabbix_get -s 127.0.0.1 -k 'pg.backends.free[host=localhost user=zabbix,postgres] ' 1999.000000

Nothing relevant discovered in both client and server log files.

I created a discovery rule which "discovers" some valid pg key/value on the client server with success.

Might something be wrong while using the Template_PostgreSQLServer3.0.xml version with Zabbix server version 4.0?!

Tnx again, John

[jedd]

Hi John,

Very curious then - and I'm reaching the end of my list of things to try.

My gut feel was a db permissions thing - relating to localhost (or 127.0.0.1) versus socket based connections, but your test suggests that's not a/the problem. I ended up using account mapping via pg_ident.conf but again, if you're using the zabbix user to connect, and using host rather than local .. (well, your first catch-all is local, but zabbix_get works, so it's connecting).

Lack of errors on the agent means you're not seeing similar problems to what I've seen before. You could try bumping verbosity up, but I'm doubtful that'd help.

I can confirm .so & template works fine with Zabbix v4, as well as PostgreSQL 9, 10, and 11.

When you say some new discovery rules are able to pick up pg data ... does that mean items outside the vanilla template are working okay?

[purpendicular]

Hi Jedd,

Finally I found my blind spot .. I just did not completely understand the Active versus Passive check method. So I forgot to set the "Hostname" variable in the client's zabbix-agentd.conf equal to the hostname on the Zabbix server.

Now everything works wonderful as it should.

Thanks very very much for your time and support!

[purpendicular]

Hi Jedd,

By the way, do you now if there are also module based (non-user parameter defined) solutions for Oracle and MySQL?

KInd regards, John