Connection refused - Is the server running locally and accepting

Question

So I've got an error when trying to connect to a Postgres instance on OSX.

I get this error:

 Connection refused
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

I run this:

ps -ax | grep postgres

And get this:

 7782 ??         0:00.06 /usr/local/opt/postgresql@9.6/bin/postgres -D /usr/local/var/postgresql@9.6
 7786 ??         0:00.00 postgres: checkpointer process     
 7787 ??         0:00.01 postgres: writer process     
 7788 ??         0:00.00 postgres: wal writer process     
 7789 ??         0:00.00 postgres: autovacuum launcher process     
 7790 ??         0:00.00 postgres: stats collector process     
 7794 ttys000    0:00.00 grep postgres

/tmp/.s.PGSQL.5432 exists, though it has the group "daemon" as opposed to everything else which has "wheel".

I setup Postgres 9.6 through brew.

What could be causing this?

My pg_hba.conf looks like this:

host  all  all  0.0.0.0/0  trust
hostnossl    all          all            0.0.0.0/0  trust

What could be causing this?

This is what the postgres log says:

2017-12-15 19:40:37 UTC LOG:  database system was shut down at 2017-12-15 19:40:32 UTC
2017-12-15 19:40:37 UTC LOG:  MultiXact member wraparound protections are now enabled
2017-12-15 19:40:37 UTC LOG:  database system is ready to accept connections
2017-12-15 19:40:37 UTC LOG:  autovacuum launcher started

I'm just not seeing ANYTHING that could cause this.

Full pg_hba.conf:

#
# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access.  Records take one of these forms:
#
# local      DATABASE  USER  METHOD  [OPTIONS]
# host       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostssl    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnossl  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Default:
# Added by ansible
# Added by ansible
host  all  all  0.0.0.0/0  trust
hostnossl    all          all            0.0.0.0/0  trust
local  all  all    trust
# Password hosts

# Trusted hosts

# User custom

you try connecting local, and `hba.conf` you show is only for host - please show rules for socket connections — Vao Tsun, Dec 16 '17 at 19:51
Please show how you are connecting to your server (connection string or parameters if using an API or `psql` options if using the command line) — jcaron, Dec 17 '17 at 21:52

Laurenz Albe · Answer 1 · 2017-12-21T14:34:58.207

The problem has nothing to do with pg_hba.conf, because your connection attempt is rejected by the operating system – the PostgreSQL server is not yet involved.

Some things to check:

You are aware that for a UNIX domain socket connection client and server must be on the same machine, right?
Socket permissions:
```
ls -l /tmp/.s.PGSQL.5432
```
I don't know if OSX ignores permissions on UNIX domain sockets, but if not, a user needs both read and write permissions on a socket to connect to it.

If that is the problem, adjust the PostgreSQL parameter unix_socket_permissions and restart.

(Of course, it could also be missing permissions on /tmp, but if that is wrong, a lot of things should stop working on this machine.)
Are you using the correct socket?

List the sockets where the postmaster is listening with
```
lsof -p 7782 | grep PGSQL
```
Here, 7782 is the process ID of the postmaster.

The PostgreSQL parameter unix_socket_directories determines where the sockets are created.

Connection refused - Is the server running locally and accepting

1 Answers1