database - PostgreSQL 的连接被拒绝，无法从事件状态(已退出)重新启动

Question

在此先感谢您的帮助。这是我第一次在这里提问，请原谅任何错误。

详细信息:

• 运行 Ubuntu 18.04 的服务器
• PostgreSQL 10

我的问题:

我在为 Django 2.1 运行迁移时遇到了这个错误，尽管现在已经运行相同的命令没有错误了一段时间。

psycopg2.OperationalError: could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5432?

我已经确定这是我的 PostgreSQL 数据库的问题，但尽管我尽了最大的努力，还是陷入了僵局。

因为这似乎是端口的问题，所以我检查了:

$ ss -nlt
State     Recv-Q      Send-Q            Local Address:Port            Peer Address:Port     
LISTEN    0           128               127.0.0.53%lo:53                   0.0.0.0:*        
LISTEN    0           128                     0.0.0.0:22                   0.0.0.0:*        
LISTEN    0           128                     0.0.0.0:443                  0.0.0.0:*        
LISTEN    0           128                   127.0.0.1:27017                0.0.0.0:*        
LISTEN    0           128                     0.0.0.0:80                   0.0.0.0:*        
LISTEN    0           128                        [::]:22                      [::]:*

明显突出的是这部分 -- 127.0.0.53%lo:53 -- 但我不知道如何修复它。看起来应该改为 5432...

我的 pg_hba.conf 文件如下所示:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
h!
ost    replication     all             ::1/128                 md5

我的 postgresql.conf 文件如下所示:

# - Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories

最后，我检查了我的防火墙并添加了端口 5432。

5432/tcp (v6)              ALLOW       Anywhere (v6)

---

一些其他可能有用的错误消息:

$ service postgresql status
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Thu 2018-10-11 08:34:35 UTC; 17min ago
  Process: 9046 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 9046 (code=exited, status=0/SUCCESS)

我已尝试按照说明进行操作 here ，输出如下:

$ sudo systemctl start postgresql@10-main
Job for postgresql@10-main.service failed because the service did not take the steps required by its unit configuration.
See "systemctl status postgresql@10-main.service" and "journalctl -xe" for details.

$ sudo systemctl status postgresql@10-main.service
● postgresql@10-main.service - PostgreSQL Cluster 10-main
   Loaded: loaded (/lib/systemd/system/postgresql@.service; indirect; vendor preset: enabled
   Active: failed (Result: protocol) since Thu 2018-10-11 08:42:52 UTC; 25s ago
  Process: 9093 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 10-main start (co

Oct 11 08:42:52 billow-droplet-1 postgresql@10-main[9093]: 2018-10-11 08:42:52.396 UTC [9098
Oct 11 08:42:52 billow-droplet-1 postgresql@10-main[9093]: 2018-10-11 08:42:52.396 UTC [9098
Oct 11 08:42:52 billow-droplet-1 postgresql@10-main[9093]: 2018-10-11 08:42:52.396 UTC [9098
Oct 11 08:42:52 billow-droplet-1 postgresql@10-main[9093]: 2018-10-11 08:42:52.396 UTC [9098
Oct 11 08:42:52 billow-droplet-1 postgresql@10-main[9093]: 2018-10-11 08:42:52.398 UTC [9098
Oct 11 08:42:52 billow-droplet-1 postgresql@10-main[9093]: pg_ctl: could not start server
Oct 11 08:42:52 billow-droplet-1 postgresql@10-main[9093]: Examine the log output.
Oct 11 08:42:52 billow-droplet-1 systemd[1]: postgresql@10-main.service: Can't open PID file
Oct 11 08:42:52 billow-droplet-1 systemd[1]: postgresql@10-main.service: Failed with result 
Oct 11 08:42:52 billow-droplet-1 systemd[1]: Failed to start PostgreSQL Cluster 10-main.

Answer 1

今天遇到了同样的问题。就我而言，我忘记设置 listen_addresses = '*'。

在 ss-nlt 中我监听了 5432 端口:

State       Recv-Q       Send-Q       Local Address:Port      Peer Address:Port
LISTEN      0        128               127.0.0.53%lo:53           0.0.0.0:*
LISTEN      0        128                 0.0.0.0:22               0.0.0.0:*
LISTEN      0        128                 0.0.0.0:5432             0.0.0.0:*
LISTEN      0        128                  [::]:22                   [::]:*
LISTEN      0        128                  [::]:5432                 [::]:*
LISTEN      0        128                   *:2377                   *:*
LISTEN      0        128                   *:7946                   *:*

所以，在你的情况下，postgresql 没有监听 5432。愚蠢的提议:你试过 sudo service postgresql restart 了吗？ :)

编辑:问题已经存在一个多月了，您可能已经解决了这个问题。如果是，请分享您的解决方案。