PostgreSQL unix sock connections issues

[heron]

Hi,

I encountered a problem during upgrade from Zabbix server 2.0.9 to 2.2.1, via openSUSE monitoring repo packages. Previously Zabbix was connecting using a unix-style socket file (instead of a listening network socket). After upgrading, this type of connection seems to be failing - from the server log:

13632:20140204:174820.079 Database is down. Reconnecting in 10 seconds.
13632:20140204:174830.080 [Z3001] connection to database 'zabbix' failed: [0] 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"?

Note this /tmp/.s.PGSQL.5432 file does exist (& database server is running), and I can verify it works for connection using the command line psql tool.

Previously I'd followed instructions for the zabbix-server.conf file for using unix sockets with Zabbix, by setting DBHost to blank. Relevant snippet below:

DBHost=
DBName=zabbix
DBUser=zabbix
DBSocket=/tmp/.s.PGSQL.5432

While troubleshooting this, I've verified that SELinux is not active, that I can connect using psql via unix-style socket, and that PostgreSQL & Zabbix server ARE actually running.

At this point I am wondering if the syntax in zabbix-server.conf required to force Unix domain socket connections has changed, or is broken, between 2.0.9 and 2.2.1. I've tried fiddling with this also, but so far no luck.

For now I've allowed Postgres to listen on localhost via network socket, and am (successfully) allowing Zabbix to connect that way instead. The database was upgraded properly & new setup is (mostly) working, but I'd like to go back to using Unix sockets if possible.

Before opening a bug, I thought I'd check to see if any others have run into/fixed this type of problem recently. Didn't see any relevant postings here, so here is mine...

Thanks in advance!

[eugene.istomin]

Yes,

we have the same issue.
Seems like a bug.

[heron]

ZBX-7795 for tracking, in case anyone else runs into the same thing.

[heron]

Incidentally, this issue has been resolved. See bug report for fix related to systemd service file.

[heron]

Haven't had a chance to reach out to openSUSE Zabbix maintainer on this, but in the meantime I got tired of patching the systemd service file for every new version of Zabbix.

Prevented this issue from occurring in the future by moving the PostgreSQL default sock file to /var/pgsql/.s.PGSQL.5432 instead of /tmp.

I created the folder /var/pgsql (owned by postgres user, with 0755 permissions), and updated PG & Zabbix Server configurations accordingly:

1. /var/lib/postgresql.conf : Uncomment & change line to unix_socket_directory = '/var/pgsql'
2. /etc/zabbix/zabbix-server.conf : Change line to DBHost=/var/pgsql
3. /usr/share/zabbix/conf/zabbix.conf.php : Change line to $DB['SERVER'] = '/var/pgsql';
4. In any external scripts : Add -h /var/pgsql to psql/pg_dump/etc command lines

Also added /etc/profile.d/postgres.sh file to set PGHOST=/var/pgsql as a global environment variable. This allows use of 'psql' & APIs to connect to the new location by default, without having to specify a host parameter. After the above is set up, need to restart zabbix-server and postgresql services.

For some PostgreSQL tools it may still be necessary to specify "-h /var/pgsql" on the command line; I'm trying to figure out why this happens, it might be only for cases where the default Unix profile is not getting loaded.

EDIT: Changed all /var/run/pgsql to /var/pgsql. /var/run is tmpfs on newer systems, and the /var/run/pgsql folder would need to be created by root on each boot. This would be trivial with init scripts, less so with a .service file.