连接到亚马逊 ec2 上的远程 postgresql 服务器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14545298/
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
Connect to remote postgresql server on amazon ec2
提问by whatf
I started an amazon ec2 instance, and installed postgresql 9.1 over it. I then went to the
Security Group: quicklaunch-1(there was one moredefault` which i did not change) and opened the 5432 TCP Port, the table looks like this:
我启动了一个亚马逊 ec2 实例，并在其上安装了 postgresql 9.1。然后我转到安全组：quicklaunch-1 (there was one moredefault`，我没有更改）并打开 5432 TCP 端口，该表如下所示：
(Service) Source Action
22 0.0.0.0/0 Delete
5432 0.0.0.0/32 Delete
5433 0.0.0.0/32 Delete
6432 0.0.0.0/32 Delete
I have created a database and user .
My /etc/postgresql/9.1/main/pg_hba.conflooks like this:
我已经创建了一个数据库和用户。我的/etc/postgresql/9.1/main/pg_hba.conf看起来像这样：
# Database administrative login by Unix domain socket
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
host all all 0.0.0.0/0 md5
host db_name user_name 0.0.0.0/0 md5
# "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 postgres peer
host replication postgres 127.0.0.1/32 md5
host replication postgres ::1/128 md5
and /etc/postgresql/9.1/main/postgresql.conflooks like this:
和/etc/postgresql/9.1/main/postgresql.conf看起来像这样：
# - Connection Settings -
listen_addresses = '*'
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
port = 5432 # (change requires restart)
I then try to connect on to the remote machine as follows:
然后我尝试连接到远程机器，如下所示：
psql -h ec2-xxx-xx-xxx-xxx.compute-1.amazonaws.com -d <database_name> -U <username>
where ec2-xxx-xx-xxx-xxx.compute-1.amazonaws.comis my Public DNS.
其中ec2-xxx-xx-xxx-xxx.compute-1.amazonaws.com是我的Public DNS。
The above command does not result in any connection, how can i connect?
上面的命令没有导致任何连接，我如何连接？
回答by Daniel Vérité
In this table:
在这个表中：
5432 0.0.0.0/32 Delete
5433 0.0.0.0/32 Delete
6432 0.0.0.0/32 Delete
the CIDRs look like you're not allowing any IP in. Shouldn't they be 0.0.0.0/0instead, like what you have for port 22 (ssh)?
CIDR 看起来您不允许任何 IP 进入。难道它们不应该是0.0.0.0/0，就像您对端口 22 (ssh) 所拥有的那样？
回答by javacreed
I Found the resolution to this problem. Two things are required.
我找到了这个问题的解决方案。需要做两件事。
Use a text editor to modify pg_hba.conf. Locate the line host all all 127.0.0.1/0 md5. Immediately below it, add this new line: host all all 0.0.0.0/0 md5
Editing the PostgreSQL postgresql.conf file:
Use a text editor to modify postgresql.conf. Locate the line that starts with #listen_addresses = 'localhost'. Uncomment the line by deleting the #, and change localhost to
*. The line should now look like this:listen_addresses = '*'# what IP address(es) to listen on;.
使用文本编辑器修改 pg_hba.conf。定位到线路主机全部为 127.0.0.1/0 md5。在它的正下方，添加这一行：host all all 0.0.0.0/0 md5
编辑 PostgreSQL postgresql.conf 文件：
使用文本编辑器修改 postgresql.conf。找到以#listen_addresses = 'localhost' 开头的行。通过删除 # 取消注释该行，并将 localhost 更改为
*. 该行现在应如下所示：listen_addresses = '*'# 要侦听的 IP 地址；。
Now Just restart your postgres service and it will connect
现在只需重新启动您的 postgres 服务，它就会连接
回答by sal
your psql command needs a -W option added, which allows you to enter a password for the db user and a -p option followed by the postgres port number (5432 per default)
您的 psql 命令需要添加一个 -W 选项，它允许您输入 db 用户的密码和一个 -p 选项，后跟 postgres 端口号（默认为 5432）
cheers!
干杯！
