SLES 15.01 Postgresql 12

Would it be possible to obtain the ouput of zabbix_get on the discovery key? 
E.g.:

zabbix_get -s127.0.0.1 -k'pgsql.replication.process.discovery["URI","USER","PASSWORD"]'

And populate the fields within the key accordingly.

Please also do the same for the DB discovery key.

For discovery rule

zabbix2020:/etc/pgbouncer # zabbix_get -s127.0.0.1 -k'pgsql.db.discovery["/var/run/postgresql/.s.PGSQL.6432","postgres","postgres"]'
{"data" : [{"

" : "zabbix"}]}

Hello. I send a screenshot of the execution of commands at your request. I noticed that when running a test of an unsupported data element, any, error (SQLSTATE 26000), then appears, then disappears. But in general, in the interface, the data element remains in the status not supported.

1435:20210201:103326.196 item "Zabbix server:pgsql.replication.lag.sec["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]" became not supported: Cannot fetch data: ERROR: prepared statement "lrupsc_97_0" does not exist (SQLSTATE 26000).
1435:20210201:103326.196 item "Zabbix server:pgsql.replication.status["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]" became not supported: Cannot fetch data: ERROR: prepared statement "lrupsc_96_0" does not exist (SQLSTATE 26000).
1435:20210201:103326.196 item "Zabbix server:pgsql.replication.lag.b["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]" became not supported: Cannot fetch data: ERROR: prepared statement "lrupsc_98_0" does not exist (SQLSTATE 26000).
1435:20210201:103326.196 item "Zabbix server:pgsql.oldest.xid["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]" became not supported: Cannot fetch data: ERROR: prepared statement "lrupsc_101_0" does not exist (SQLSTATE 26000).
1435:20210201:103326.196 item "Zabbix server:pgsql.replication.count["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]" became not supported: Cannot fetch data: ERROR: prepared statement "lrupsc_102_0" does not exist (SQLSTATE 26000).
1435:20210201:103326.196 item "Zabbix server:pgsql.autovacuum.count["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}"]" became not supported: Cannot fetch data: ERROR: prepared statement "lrupsc_103_0" does not exist (SQLSTATE 26000).
1435:20210201:103327.210 item "Zabbix server:pgsql.db.bloating_tables["{$PG.URI}","{$PG.USER}","{$PG.PASSWORD}","zabbix"]" became not supported: Cannot fetch data: ERROR: prepared statement "lrupsc_104_1" does not exist (SQLSTATE 26000).

zabbix2020:/etc/pgbouncer # zabbix_get -s127.0.0.1 -k'pgsql.autovacuum.count["/var/run/postgresql/.s.PGSQL.6432","postgres","postgres"]'
0
zabbix2020:/etc/pgbouncer # zabbix_get -s127.0.0.1 -k'pgsql.replication.lag.sec["/var/run/postgresql/.s.PGSQL.6432","postgres","postgres"]'
ZBX_NOTSUPPORTED: Cannot fetch data: ERROR: prepared statement "lrupsc_97_0" does not exist (SQLSTATE 26000).
zabbix2020:/etc/pgbouncer # zabbix_get -s127.0.0.1 -k'pgsql.replication.status["/var/run/postgresql/.s.PGSQL.6432","postgres","postgres"]'
ZBX_NOTSUPPORTED: Cannot fetch data: ERROR: prepared statement "lrupsc_96_0" does not exist (SQLSTATE 26000).
zabbix2020:/etc/pgbouncer # zabbix_get -s127.0.0.1 -k'pgsql.oldest.xid[["/var/run/postgresql/.s.PGSQL.6432","postgres","postgres"]'
ZBX_NOTSUPPORTED: Invalid item key format.
zabbix2020:/etc/pgbouncer # zabbix_get -s127.0.0.1 -k'pgsql.oldest.xid["/var/run/postgresql/.s.PGSQL.6432","postgres","postgres"]'
0
zabbix2020:/etc/pgbouncer # zabbix_get -s127.0.0.1 -k'pgsql.autovacuum.count["/var/run/postgresql/.s.PGSQL.6432","postgres","postgres"]'
1
zabbix2020:/etc/pgbouncer # zabbix_get -s127.0.0.1 -k'pgsql.replication.lag.sec["/var/run/postgresql/.s.PGSQL.5432","postgres","postgres"]'
0
zabbix2020:/etc/pgbouncer # zabbix_get -s127.0.0.1 -k'pgsql.replication.lag.sec["/var/run/postgresql/.s.PGSQL.6432","postgres","postgres"]'
ZBX_NOTSUPPORTED: Cannot fetch data: ERROR: prepared statement "lrupsc_97_0" does not exist (SQLSTATE 26000).

I found a FAQ on the pgbouncer website http://www.pgbouncer.org/faq.html to fix this PHP/PDO error, but it seems this is not our case.

Hello. Will this problem be solved?

Hello. Thanks. And it turns out that Zabbix-server and zabbix-web work through pgbouncer, but the official template for database monitoring does not. This is probably wrong.

Thanks for the information Alexander!

Just to confirm - the discovery rule works as expected without pgbouncer and the issue happens only when pgbouncer is used?

Hello Arturs!
Yes - the discovery rule works as expected without pgbouncer and the issue happens only when pgbouncer is used.

Hello.
In version 6.0 it also does not work

Hi a.yagupov, I tried on Friday to reproduce the issue with some basic pg-bouncer set-up and did not succeed. Maybe you have some steps to reproduce, that would make the attempt easier. Also is it possible for you to share your pg-bouncer configuration, without any sensitive data of course.

Hi Eriks Sneiders, zabbix works great through pgbouncer. The configuration files of pgbouncer and zabbix server are attached. The PostgreSQL by Zabbix agent 2 template does not work via pgbouncer,
when trying to specify the {$PG macro.URI} - tcp://localhost:6432, or /var/run/postgresql/.s.PGSQL.6432 pgbouncer.ini zabbix.conf.php zabbix_server.conf

Hi a.yagupov. I've managed to reproduce the problem: for that you can restart only the postgresql service during the monitoring and yeah, some items will become unsupported. The problem goes away if after that you also restart either pgbouncer or zabbix-agent2 service.

Please note that connecting Zabbix server or frontend to PostgreSQL via PgBouncer is not declared to be officially supported as well as monitoring of PostgreSQL inscances through it and you use this method at your own risk. Also consider using the latest version (link is for Zabbix 6.0) of the template as PostgreSQL templates were updated recently.

So the reason behind this behavior is that the plugin uses prepared statements by default. This is not the case with the templates by the classic agent or ODBC, so these work just fine.

The PgBouncer FAQ mentions that when you use transaction pooling mode you can't use prepared statements as in that case PgBouncer will have to store them, and it doesn't, so you have to disable them on the client's level. esneiders - we probably could think of implementing the ability to disable these in the plugin config, but maybe the easiest and fastest solution for Alexander would be to use the session pooling mode for the user that is used for monitoring purpose, which will allow to clean old statements in the reset query (with the "server_reset_query = DISCARD ALL" parameter).

So you could add a record to the [users] section to the pgbouncer.ini like so (replace zbx_monitor with your user if needed):

[users]
zbx_monitor = pool_mode=session

In that case the template should work correctly. Also, you have this record:

[databases]
zabbix =

Note, that because that's the only connection option specified in your config file, it will only allow connections to the zabbix DB. In this case some metrics for discovered databases will be broken as well, as these require connecting directly to the discovered database. So you must provide connectivity with all DBs for the user that is used for monitoring. Check the template README for instructions about creating a user with proper permissions.

Hello Denis Rasikhov. Your recommendations do not work on Zabbix version 5.0.13. As soon as I set the statement mode in pgbouncer, the web interface becomes unavailable with the errors I wrote about. On Zabbix version 6.0.4, I didn't even try it, because this version works for me to monitor the system, with more than 3000 nodes and I just can't train on it. I will work at my own risk with writing/reading to the database via pgbouncer, via port 6432, which works steadily, gives a big increase in performance, and monitoring the database directly, via port 5432. It is strange that Zabbix does not support the standard products of the software used, in particular Postgresql, which are recommended and supported by developers. By the way, this also applies to the inability to use memcached for PHP-FPM in Zabbix 6.0. I hope in the Zabbix 7.0 version, this will be used for pgbouncer and memcached. (sorry for my English)

That most likely happens because you're changing the pooling mode for all connections, not just for the user that you use for monitoring. In the example above I've only changed the mode for the user zbx_monitor, that template's README suggests you to create. Yeah, if you set mode to session for everyone, Zabbix server connection works fine, but not the frontend.

So the setup that I've tested and which works for me on versions since 6.0.21:

• user zabbix is used for connections with DB "zabbix" for Zabbix server and frontend in the transaction mode;
• user zbx_monitor is used for connections to all DBs in the session mode.

There's my configuration files:
pgbouncer-1.ini
zabbix_server-1.conf
zabbix.conf-1.php

Wonderful! it worked in version 5.0.13. I think it will work in version 6.0, just wait for the technological window for maintenance. Thank you. The topic can be closed.