psql 无法通过 IP 和端口 5432 连接到 PostgreSQL 服务器（postmaster）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19916720/
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
psql cant connect to PostgreSQL server (postmaster) on IP and port 5432?
提问by NullException
Please read before replying it as duplicate (as it perhaps can happen). I am running my postmaster (postgres) server. See below for 'sudo netstat -anp|grep 5432' output?
请在回复之前阅读重复（因为它可能会发生）。我正在运行我的 postmaster (postgres) 服务器。请参阅下面的“sudo netstat -anp|grep 5432”输出？
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 29606/postmaster
unix 2 [ ACC ] STREAM LISTENING 1650581 29606/postmaster /var/run/postgresql/.s.PGSQL.5432
unix 2 [ ACC ] STREAM LISTENING 1650582 29606/postmaster /tmp/.s.PGSQL.5432
I am able to connect from localhost using
我可以使用从本地主机连接
psql -h localhost (OR 127.0.0.1) -d <DB> -U user -W
But when I try to connect from other hosts using tcp, by specifying
但是当我尝试使用 tcp 从其他主机连接时，通过指定
psql -h ip_add_postmaster -d <DB> -U user -W
It throws:
它抛出：
psql: could not connect to server: Connection refused Is the server running on host XXXXXX and accepting TCP/IP connections on port 5432?
psql：无法连接到服务器：连接被拒绝 服务器是否在主机 XXXXXX 上运行并接受端口 5432 上的 TCP/IP 连接？
What's wrong here?
这里有什么问题？
pg_hba.conf
# 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
In postgresql.conf,
在 postgresql.conf 中，
listen_addresses = 'localhost, 127.0.0.1, ip_add_postmaster'
Note: ip_add_postmaster is same as my Elastic IP and not public DNS. If this information
matters.
What am I doing wrong here? Machine is hosted on Amazon EC2 and have open the port 5432.
我在这里做错了什么？机器托管在 Amazon EC2 上并打开了端口 5432。
采纳答案by NullException
listen_addresses='localhost, private_ip' fixed the issue. I was not able to start postmaster server on elastic IPs. Once postgres server started o localhost and private IPs, I was able to connect.
listen_addresses='localhost, private_ip' 解决了这个问题。我无法在弹性 IP 上启动 postmaster 服务器。一旦 postgres 服务器启动 o 本地主机和私有 IP，我就能够连接。
回答by Wolph
As your netstat output indicates, it's listening at 127.0.0.1:5432which is localhost. That is only connectable from localhost ;)
正如您的 netstat 输出所示，它正在侦听127.0.0.1:5432哪个是 localhost。那只能从本地主机连接;)
Set listen_addresses='*'in your config and it will work.
设置listen_addresses='*'在您的配置中，它将起作用。
[edit] Other things to check:
[编辑] 其他检查事项：
- is the amazon firewall blocking anything?
- is iptables blocking anything?
- 亚马逊防火墙阻止了什么吗？
- iptables 是否阻塞了任何东西？
But first make sure the listening address is correct, your netstat output shows that it won't work like this.
但是首先要确保监听地址是正确的，你的 netstat 输出显示它不会像这样工作。
回答by Ashalynd
One other issue I have found was if you end up with two Postgres installations, the second one can choose non-default port (in my case it was 5433 i/o 5432). So checking the port in postgresql.conf might be a good idea.
我发现的另一个问题是，如果您最终安装了两个 Postgres，第二个可以选择非默认端口（在我的情况下是 5433 i/o 5432）。所以检查 postgresql.conf 中的端口可能是一个好主意。
回答by Chris Ivan
I ran into this issue and tried all sorts of fixes I found across SO, and want to add a simple solution that worked for me after realizing it had to do with permissions in my case.
我遇到了这个问题并尝试了我在 SO 中找到的各种修复程序，并希望添加一个简单的解决方案，在意识到它与我的情况有关的权限后，我想添加一个对我有用的简单解决方案。
Simply, if you're running a psql server on Windows, you are initially restricted to the default postgres superuser for logging in, launching the server, and so on.
简单地说，如果您在 Windows 上运行 psql 服务器，您最初只能使用默认的 postgres 超级用户登录、启动服务器等。
So, first try running from the command line:
psql -U postgres -h localhost -p 5432and enter your password at the prompt. If you've managed to login and the server is up, then it was a permissions issues. From here, you can create a role for yourself that has login privileges to whatever database you are trying to run.
因此，首先尝试从命令行运行：
psql -U postgres -h localhost -p 5432并在提示符下输入您的密码。如果您已经成功登录并且服务器已启动，那么这是权限问题。从这里，您可以为自己创建一个角色，该角色对您尝试运行的任何数据库都具有登录权限。
If the error persists, then consider checking postgresql.conf as mentioned above, to make sure default IP is set to * or localhost, and the port set to 5432 or whatever port you want as default.
如果错误仍然存在，请考虑检查上述 postgresql.conf，以确保默认 IP 设置为 * 或 localhost，并将端口设置为 5432 或您想要的任何默认端口。
回答by Vinu Joseph
I also ran into the same issue. On debugging, it was nothing related to the port, but due to some missing directories in the Postgres folder.
我也遇到了同样的问题。在调试时，它与端口无关，但由于 Postgres 文件夹中缺少一些目录。
While updating Mac OS (from 10.13.1 -> 10.13.13), some folders in the directory /usr/local/var/postgres/gets deleted. The fix was the adding the missing directories:
在更新 Mac OS 时（从 10.13.1 -> 10.13.13），目录中的一些文件夹/usr/local/var/postgres/被删除。修复是添加缺少的目录：
mkdir /usr/local/var/postgres/pg_tblspc
mkdir /usr/local/var/postgres/pg_twophase
mkdir /usr/local/var/postgres/pg_stat
mkdir /usr/local/var/postgres/pg_stat_tmp
mkdir /usr/local/var/postgres/pg_replslot
mkdir /usr/local/var/postgres/pg_snapshots
mkdir /usr/local/var/postgres/pg_logical/{snapshots,mappings}
