postgresql 服务器不听

postgresql server doesn't listen

提问by Leandro Garcia

I just recently install PostgreSQL on our server via SSH. The installation went successful, until the time I tried to connect to it using pgAdmin on my Windows machine.I received this kind of error:

我最近刚刚通过 SSH 在我们的服务器上安装了 PostgreSQL。安装成功，直到我尝试在我的 Windows 机器上使用 pgAdmin 连接到它。我收到了这种错误：

could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "xxx.xxx.xxx.xxx" and accepting TCP/IP connections on port 5432?

无法连接到服务器：连接被拒绝 (0x0000274D/10061) 服务器是否在主机“xxx.xxx.xxx.xxx”上运行并接受端口 5432 上的 TCP/IP 连接？

xxx.xxx.xxx.xxx= my server's public IP.

xxx.xxx.xxx.xxx= 我服务器的公共 IP。

The docs suggest this can be fixed by setting the value of listen_addresses = '*'in the /etc/postgresql/9.1/main/postgresql.conf. I did that but still it won't let me.

该文档建议这可以通过设置的值是固定listen_addresses = '*'的/etc/postgresql/9.1/main/postgresql.conf。我这样做了，但它仍然不让我。

additional error came up

出现了额外的错误

FATAL: no pg_hba.conf entry for host "xxx.xx.xxx.xxx", user "postgres", database "postgres", SSL on FATAL: no pg_hba.conf entry for host "xxx.xx.xxx.xxx", user "postgres", database "postgres", SSL off

致命：没有主机“xxx.xx.xxx.xxx”的 pg_hba.conf 条目，用户“postgres”，数据库“postgres”，致命的 SSL：没有主机“xxx.xx.xxx.xxx”的 pg_hba.conf 条目，用户“postgres”，数据库“postgres”，关闭 SSL

xxx.xx.xxx.xxx= my IP address.

xxx.xx.xxx.xxx= 我的 IP 地址。

What seems to be I'm missing?

我似乎缺少什么？

回答by Frank Farmer

Things that could block a postgres connection:

可能阻止 postgres 连接的事情：

1. misconfigured listen_addressin postgresql.conf
2. selinux (?)
3. iptables
4. pg_hba.conf(although this should cause a different error, not server doesn't listen)

1. 错误地配置listen_address在postgresql.conf
2. selinux (?)
3. iptables
4. pg_hba.conf（虽然这应该会导致不同的错误，而不是服务器不听）

Can you connect to the server locally, if you ssh in and run psql?

如果您 ssh 并运行，您可以在本地连接到服务器psql吗？

On our internal dev servers, I just turn off selinux and iptables. This is a bad idea from a security standpoint, but it might serve as a temporary step to help you narrow down where the problem is.

在我们的内部开发服务器上，我只是关闭了 selinux 和 iptables。从安全的角度来看，这是一个坏主意，但它可以作为帮助您缩小问题范围的临时步骤。

回答by Mike Sherrill 'Cat Recall'

You might need to change more than one configuration file. In your case, you probably need to edit pg_hba.conf, too. Search that file for "non-local connections".

您可能需要更改多个配置文件。在您的情况下，您可能也需要编辑pg_hba.conf。在该文件中搜索“非本地连接”。

I like to keep configuration files under version control. It's easier to recover from mistakes that way.

我喜欢将配置文件置于版本控制之下。这样更容易从错误中恢复。

You probably need to restart the PostgreSQL server after making those changes.

进行这些更改后，您可能需要重新启动 PostgreSQL 服务器。

回答by Joshua Berry

• After changing listen_addresses settings on the server, make sure to restart the PostgreSQL server (send SIGHUP to the postmaster process, with kill -HUP, etc).
• Make sure that postgresql.conf port is set to 5432
• Make sure that if a firewall is running on the server, that port 5432 is open for connections coming from the window's (client) machine you are using
• Check pg_hba.conf to make sure that the subnet of your client machine is given access
• Try using psql locally

• 在服务器上更改 listen_addresses 设置后，确保重新启动 PostgreSQL 服务器（将 SIGHUP 发送到 postmaster 进程，使用 kill -HUP 等）。
• 确保 postgresql.conf 端口设置为 5432
• 确保如果服务器上正在运行防火墙，则端口 5432 对来自您正在使用的窗口（客户端）机器的连接开放
• 检查 pg_hba.conf 以确保您的客户端机器的子网被授予访问权限
• 尝试在本地使用 psql