连接被拒绝 - 服务器是否在本地运行并接受连接？

Question

我在尝试连接OSX上的Postgres实例时遇到了错误。

我得到了这个错误：

 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 

还有这个：

 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 存在，但它的组是"daemon"，而其他所有内容都是"wheel"。

我通过brew安装了Postgres 9.6。

可能是什么原因导致这种情况发生？

我的pg_hba.conf文件如下：

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

可能是什么原因导致这种情况？

以下是Postgres日志中的信息：

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

我真的没有看到任何可能导致这种情况的问题。

完整的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

- Steven Matthews

---

你尝试连接本地，而你展示的 hba.conf 文件仅适用于主机 - 请展示套接字连接的规则。 - Vao Tsun

我也有本地的 all all trust，但它仍然无法工作。 - Steven Matthews

请展示您是如何连接到服务器的（如果使用API，则为连接字符串或参数，如果使用命令行，则为psql选项）。 - jcaron

Answer 1

问题与 pg_hba.conf 无关，因为你的连接尝试被操作系统拒绝 - PostgreSQL 服务器尚未涉及。

需要检查以下几点：

1. You are aware that for a UNIX domain socket connection client and server must be on the same machine, right?

2. 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.)

3. 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.

- Laurenz Albe