Active checks not working with non default PostgreSQL port

[haiscd]

We are having an issue with zabbix monitoring for PostgreSQL instances runnning on non-default ports and could not identify the issue.

Zabbix Version - 2.4.7 PostgreSQL DB version - 9.5.1

Zabbix monitoring is setup as per the documentation. PostgreSQL server has to run with non-default port (5411) due to security issues.

I can see libzbxpgsql module is loaded from zabbix agent log on DB server.

23405:20160518:121117.214 Starting Zabbix Agent [Zabbix server]. Zabbix 2.4.7 (revision 56694). 23405:20160518:121117.214 using configuration file: /etc/zabbix/zabbix_agentd.conf 23405:20160518:121117.215 Starting agent module libzbxpgsql 0.2.1 23405:20160518:121117.215 loaded modules: libzbxpgsql.so 23405:20160518:121117.216 agent #0 started [main process] 23407:20160518:121117.216 agent #1 started [collector] 23408:20160518:121117.216 agent #2 started [listener #1] 23409:20160518:121117.217 agent #3 started [listener #2] 23410:20160518:121117.217 agent #4 started [listener #3] 23411:20160518:121117.217 agent #5 started [active checks #1] 23411:20160518:121117.217 active check configuration update from [127.0.0.1:10051] started to fail (cannot connect to [[127.0.0.1]:10051]: [111] Connection refused)

Monitoring template is imported and macros are edited to use the non-default port.

I have added below entries in both pg_hba.conf and pg_identi.conf

pg_hba.conf

local all all md5 host all all 127.0.0.1/32 md5 host all all ::1/128 md5 host all all 0.0.0.0/0 md5 host all all ::0/0 md5 host replication replication 0.0.0.0/0 md5 local postgres monitoring ident map=monitoring

pg_ident.conf

MAPNAME SYSTEM-USERNAME PG-USERNAME

monitoring root monitoring monitoring zabbix monitoring

libzbxpgsql checks are pointing only to default port 5432.

zabbix_agentd -p | grep '^pg.'

zabbix_agentd [15363]: Starting agent module libzbxpgsql 0.2.1 pg.modver [s|libzbxpgsql 0.2.1] zabbix_agentd [15363]: Error: Failed to connect to PostgreSQL in pg_connect(): could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

pg.connect [u|0] zabbix_agentd [15363]: Error: Failed to connect to PostgreSQL in pg_connect(): could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

pg.version [m|ZBX_NOTSUPPORTED] [Unknown error.] zabbix_agentd [15363]: Error: Failed to connect to PostgreSQL in pg_connect(): could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"? pg.starttime [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.uptime [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.setting[,,data_directory] [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.setting.discovery [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.query.string[,,SELECT 'Lorem ipsum dolor';] [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.query.integer[,,SELECT pg_backend_pid();] [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.query.double[,,SELECT CAST(1234 AS double precision);] [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.query.discovery[,,SELECT * FROM pg_database;] [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.backends.count [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.queries.longest [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.checkpoints_timed [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.checkpoints_req [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.checkpoint_write_time [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.checkpoint_sync_time [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.buffers_checkpoint [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.buffers_clean [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.maxwritten_clean [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.buffers_backend [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.buffers_backend_fsync [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.buffers_alloc [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.stats_reset [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.db.discovery [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.namespace.discovery [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.schema.discovery [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.tablespace.discovery [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.table.discovery [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.table.children.discovery[,,pg_proc] [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.index.discovery [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.db.size [m|ZBX_NOTSUPPORTED] [Unknown error.]

zabbix_agentd [15363]: Error: Failed to connect to PostgreSQL in pg_connect(): could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

pg.table.size [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.table.rows [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.table.children[,,pg_database] [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.table.children.size[,,pg_database] [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.table.children.rows[,,pg_database] [m|ZBX_NOTSUPPORTED] [Unknown error.] zabbix_agentd [15363]: pg.index.size [m|ZBX_NOTSUPPORTED] [Unknown error.] Error: Failed to connect to PostgreSQL in pg_connect(): could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

pg.index.rows [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.tablespace.size[,,pg_default] [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.namespace.size[,,pg_catalog] [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.schema.size[,,pg_catalog] [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.db.numbackends [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.db.xact_commit [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.db.xact_rollback [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.db.blks_read [m|ZBX_NOTSUPPORTED] [Unknown error.] pg.db.blks_hit [m|ZBX_NOTSUPPORTED] [Unknown error.]

zabbix_agentd [15363]: Error: Failed to connect to PostgreSQL in pg_connect(): could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

pg.db.tup_returned [m|ZBX_NOTSUPPORTED] [Unknown error.] zabbix_agentd [15363]: Error: Failed to connect to PostgreSQL in pg_connect(): could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

pg.db.tup_fetched [m|ZBX_NOTSUPPORTED] [Unknown error.] zabbix_agentd [15363]: Error: Failed to connect to PostgreSQL in pg_connect(): could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

pg.db.tup_inserted [m|ZBX_NOTSUPPORTED] [Unknown error.] zabbix_agentd [15363]: Error: Failed to connect to PostgreSQL in pg_connect(): could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

And Zabbix monitoring data is completely empty

is there anything incorrect in our configuration?

[cavaliercoder]

Thanks for raising this with the thorough info.

I can't see any issue with your configuration so we might need to just confirm a few components.

Can you please confirm if there are any PostgreSQL connection errors in your Zabbix agent log? I don't see any there which indicates that the agent is actually not even trying to connect. Perhaps the agent configuration is not propagating from the Zabbix server to the agent for active items?

Can you please try connecting from the local host using psql, ensuring you are connecting via TCP/IP rather than Unix sockets. Please use the same connection settings (user, password, etc.) as your Zabbix connection string and then post the command and output here (omitting sensitive details).

If that works, please try querying the Zabbix agent with zabbix_get -s 127.0.0.1 -k "pg.connect[<conn string>]" and do the same.

These will confirm that PostgreSQL is correctly configured and that it is reachable using your connection string.

Some things that could be in the way:

• selinux
• iptables/firewalld

[haiscd]

Thanks Ryan for responding to my query.

I was able to connect to make local connection. However zabbix_get is not giving any output.

[root@****_~]# zabbixget -s 127.0.0.1 -k "pg.connect[host=127.0.0.1 user=monitoring port=5411 password=******,postgres]" [root@sj1glm1014 ~]# psql -h127.0.0.1 -p5411 -Umonitoring postgres Password for user monitoring: psql (8.4.20, server 9.5.1) WARNING: psql version 8.4, server version 9.5. Some psql features might not work. Type "help" for help.

postgres=> postgres=> postgres=> \q [root@*** ~]# zabbixget -s 127.0.0.1 -k "pg.connect[host=127.0.0.1 user=monitoring port=5411 password=****,postgres]" [root@**_*****~]#

[cavaliercoder]

Did anything show up in the Zabbix agent logs for the failed zabbix_get?

[haiscd]

Strangely nothing is getting appended to zabbix log. It has the same entries that are shared earlier.

23405:20160518:121117.214 Starting Zabbix Agent [Zabbix server]. Zabbix 2.4.7 (revision 56694). 23405:20160518:121117.214 using configuration file: /etc/zabbix/zabbix_agentd.conf 23405:20160518:121117.215 Starting agent module libzbxpgsql 0.2.1 23405:20160518:121117.215 loaded modules: libzbxpgsql.so 23405:20160518:121117.216 agent #0 started [main process] 23407:20160518:121117.216 agent #1 started [collector] 23408:20160518:121117.216 agent #2 started [listener #1] 23409:20160518:121117.217 agent #3 started [listener #2] 23410:20160518:121117.217 agent #4 started [listener #3] 23411:20160518:121117.217 agent #5 started [active checks #1] 23411:20160518:121117.217 active check configuration update from [127.0.0.1:10051] started to fail (cannot connect to [[127.0.0.1]:10051]: [111] Connection refused)

We don't set firewall rules for any local connections. Does zabbix server needs direct access to postgresql instance? Our assumption is zabbix agent has to collect all the relevant information and push to zabbix server through 10051 port. Correct me if I am wrong.

[cavaliercoder]

That's really odd. It feels like the Zabbix agent is not even attempting to connect to PG. If there was a connection error, it would definitely be in the logs.

Can you please try another zabbix_get with debug logging enabled in the agent?

[ruiztulio]

Hi,

I have a similar issue, but I don't know if it is related, I used zabbix_get to test the connection and it seems to be ok:

Nothing is shown in the agent log, then I tested with wrong credentials (just to double check):

And the message is propely logged in the agent log file:

Then my issue is that nothing is shown in the Zabbix web interface: ![http://screenshots.vauxoo.com/tulio/16563521416-BFb8OyJOos.jpg]

But also no error is shown in the log (nor server neither agent). Just in case I "copy and pate" the credentials from the macro to the console to be sure I'm using the same.

Running Zabbix 3.0.4 in Ubuntu 16.04 (both machines) and Postgresql 9.5

If there's any other information I can provide you with let me know.

Regards.

[cavaliercoder]

@ruiztulio please see #57. If switching to standard checks does not solve your issue, please raise a separate issue.

[cavaliercoder]

@haiscd have you solved this issue? Have your tried Zabbix v3 with libzbxpgsql v1.0.0?

If this is still a problem, please post the previously requested log file.

[piseydba]

I met the same error: but after i use "Template_PostgreSQL_Server_3.0.xml" template and Zabbix v3 with libzbxpgsql v1.0.0. the problem was solve.