postgresql 无法从远程主机连接到 postgres
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6150738/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Cannot connect to postgres from remote host
提问by Luc
I have a database server (192.168.1.50) running postgres. I have created a database named "testdb" and a user "testuser" with password "testuserpw".
Locally, I can connect to the db using:
我有一个运行 postgres 的数据库服务器 (192.168.1.50)。我创建了一个名为“testdb”的数据库和一个密码为“testuserpw”的用户“testuser”。
在本地，我可以使用以下方法连接到数据库：
psql -d testdb -U testuser
When I issue the command from another host (192.168.1.60):
当我从另一台主机 (192.168.1.60) 发出命令时：
psql -h 192.168.1.50 -d testdb -U testuser
I have the error:
我有错误：
psql: could not connect to server: Connection refused
Is the server running on host "192.168.1.50" and accepting
TCP/IP connections on port 5432?
Any idea ?
任何的想法 ？
回答by araqnid
Check the setting of listen_addressesin your postgresql.conffile. Many distributions make it default to 127.0.0.1, i.e. listen only to connections coming in from localhost. It should be set to '*'to listen for connections on all interfaces.
检查的设置listen_addresses在你的postgresql.conf文件中。许多发行版将其默认为 127.0.0.1，即仅侦听来自 localhost 的连接。它应该设置为'*'侦听所有接口上的连接。
If you are still having trouble, use lsofto see what network sockets the postgres process is listening on.
如果您仍然遇到问题，请使用lsof查看 postgres 进程正在侦听哪些网络套接字。
回答by raven_arkadon
On Ubuntu, I noticed that remote access at some point stopped working (currently using 9.1.9). The reason is, that postgres is no longer started with the -i switch [1] so no matter what you configure for listen_addresses, it will be ignored.
在 Ubuntu 上，我注意到远程访问在某个时候停止工作（当前使用 9.1.9）。原因是，postgres 不再以 -i 开关 [1] 启动，因此无论您为 listen_addresses 配置什么，它都会被忽略。
Fortunately, adding the following line to /etc/environmentsolves the problem after logging out and in again (or reboot):
幸运的是，/etc/environment在注销并重新登录（或重新启动）后添加以下行来解决问题：
PGOPTIONS="-i"
See [2] for more options. Note, that adding this to /etc/postgresql/9.1/main/environmentdid NOT work for me.
有关更多选项，请参阅 [2]。请注意，将其添加到/etc/postgresql/9.1/main/environment对我不起作用。
Now, when doing nmap ip-of-my-remote-serverI finally get this again:
现在，当nmap ip-of-my-remote-server我终于再次得到这个时：
5432/tcp open postgresql
Yay!
好极了！
[1] http://www.postgresql.org/docs/9.1/static/runtime-config-short.html
[1] http://www.postgresql.org/docs/9.1/static/runtime-config-short.html
[2] http://www.postgresql.org/docs/9.1/static/libpq-envars.html
[2] http://www.postgresql.org/docs/9.1/static/libpq-envars.html
回答by Sorrow
Is the firewall letting the connections through? Or, check if pg_hba.confallows connecting from addresses other than localhost.
防火墙是否允许连接通过？或者，检查是否pg_hba.conf允许从本地主机以外的地址连接。
回答by Donald J. Axel
The listen_address configvar in postgresql.conf is not the only way to get postgres to listen on the non-local IP-address (or addresses).
postgresql.conf 中的 listen_address configvar 并不是让 postgres 监听非本地 IP 地址（或地址）的唯一方法。
Use option "-o -h *" if you start postgres from pg_ctl, otherwise do add "-h" "*" to the postgres command line, like e.g.
如果您从 pg_ctl 启动 postgres，请使用选项“-o -h *”，否则请在 postgres 命令行中添加“-h”“*”，例如
/usr/local/pgsql/bin/postgres -D /pg/data "-h" "*"
/usr/local/pgsql/bin/postgres -D /pg/data "-h" "*"
Of course /pg/data must be changed to your current datapath.
当然 /pg/data 必须更改为您当前的数据路径。
This is especially useful when experimenting.
这在试验时特别有用。
回答by ikraduya edian
I came across the same problem as yours, and my source of problem is the firewall settings.
If you're using Ubuntu, print your firewall status:
sudo ufw status verbose
我遇到了和你一样的问题，我的问题根源是防火墙设置。如果您使用的是 Ubuntu，请打印您的防火墙状态：
sudo ufw status verbose
It may looks like this:
它可能看起来像这样：
Logging: on (low)
Default: deny (incoming), allow (outgoing), disabled (routed)
New profiles: skip
...
The default rule of incoming connection is "deny", so you need to specify the "allow"ed port.
传入连接的默认规则是“拒绝”，因此您需要指定“允许”的端口。
type:
sudo ufw allow 5432/tcp
类型：
sudo ufw allow 5432/tcp
reference: https://www.vultr.com/docs/how-to-configure-ufw-firewall-on-ubuntu-14-04
参考：https: //www.vultr.com/docs/how-to-configure-ufw-firewall-on-ubuntu-14-04
回答by Erick Amoedo
Connection refused (0x0000274D/10061) i fixed here with:
连接被拒绝 (0x0000274D/10061) 我在这里修复了：
Open the terminal and type:
打开终端并输入：
VIM /var/lib/pgsql/data/postgresql.conf
Edit the "listen_adresses", it should be set to '*'
编辑“listen_adresses”，它应该设置为“*”
After this, rest it on terminal:
在此之后，将其放在终端上：
/scripts/restartsrv_postgres
