psql searching for socket at wrong location

Question

I am running a custom postgres 14.2 installation (via Ansible, but I do not think that matters here) which has a problem with running the client

postgres@server:> psql
psql: error: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

systemctl status postgresql as well as pg_ctl status show the server is running, so it seems it may be a problem with where the psql client is lookin for the socket.

the configuration should point to /tmp

> grep 'unix_socket_directories' $PGDATA/postgresql.conf
#unix_socket_directories = '/tmp'

psql -h /tmp works. Also I can work around this by export PGHOST=/tmp and then running plain psql.

EDIT: owerwriting the default with an explicit unix_socket_directories = '/tmp' did not do any good.

Can anybody advise what may be the root cause here and how to get around psql searching the wrong path for the socket?

Answer 1

The root cause is that the libpq that your psql is linked with was configured differently from your database server.

That usually happens if you have different PostgreSQL installations on your machine, and you happen to call the client from a different installation than the server.

There are several things you can do:

1. Connect with

   psql -h /tmp
   

   so that libpq looks for the domain sockets in that directory

2. Call the psql from the same installation as the server:

   /path/to/the/correct/psql
   

   If you prefer that solution, it might be a good idea to remove the unnecessary PostgreSQL installation from the machine.

3. Reconfigure and restart the server. postgresql.conf would have to contain

   unix_socket_directories = /var/run/postgresql,/tmp
   

   Make sure that there is no # at the start of the line, and make sure that /var/run/postgresql exists and user postgres can write to it.

For background: when you build PostgreSQL, you can specify the default location for domain sockets by editing src/include/pg_config_manual.h:

/*
 * This is the default directory in which AF_UNIX socket files are
 * placed.  Caution: changing this risks breaking your existing client
 * applications, which are likely to continue to look in the old
 * directory.  But if you just hate the idea of sockets in /tmp,
 * here's where to twiddle it.  You can also override this at runtime
 * with the postmaster's -k switch.
 *
 * If set to an empty string, then AF_UNIX sockets are not used by default: A
 * server will not create an AF_UNIX socket unless the run-time configuration
 * is changed, a client will connect via TCP/IP by default and will only use
 * an AF_UNIX socket if one is explicitly specified.
 *
 * This is done by default on Windows because there is no good standard
 * location for AF_UNIX sockets and many installations on Windows don't
 * support them yet.
 */
#ifndef WIN32
#define DEFAULT_PGSOCKET_DIR  "/tmp"
#else
#define DEFAULT_PGSOCKET_DIR ""
#endif