无法使用 pgAdmin 将 PostgreSQL 连接到远程数据库

Unable to connect PostgreSQL to remote database using pgAdmin

提问by vinayrks

I installed PostgreSQL on my local server (Ubuntu) with IP 192.168.1.10. Now, I'm trying to access the database from my client machine (Ubuntu) with IP 192.168.1.11 with pgAdmin.

我在我的本地服务器 (Ubuntu) 上安装了 PostgreSQL，IP 为 192.168.1.10。现在，我正在尝试使用 pgAdmin 从我的客户端机器 (Ubuntu) 访问数据库，IP 为 192.168.1.11。

I know I have to make changes in postgresql.conf and pg_hba.conf to allow the client to connect. Could you please guide me?

我知道我必须在 postgresql.conf 和 pg_hba.conf 中进行更改以允许客户端连接。你能指导我吗？

回答by Syed Aslam

It is actually a 3 step process to connect to a PostgreSQL server remotely through pgAdmin3.

通过 pgAdmin3 远程连接到 PostgreSQL 服务器实际上是一个 3 步过程。

Note: I use Ubuntu 11.04 and PostgreSQL 8.4.

注意：我使用 Ubuntu 11.04 和 PostgreSQL 8.4。

1. You have to make PostgreSQL listening for remote incoming TCP connections because the default settings allow to listen only for connections on the loopback interface. To be able to reach the server remotely you have to add the following line into the file /etc/postgresql/8.4/main/postgresql.conf:

   listen_addresses = '*'

2. PostgreSQL by default refuses all connections it receives from any remote address, you have to relax these rules by adding this line to /etc/postgresql/8.4/main/pg_hba.conf:

   host all all 0.0.0.0/0 md5

   This is an access control rule that let anybody login in from any address if he can provide a valid password (the md5 keyword). You can use needed network/mask instead of 0.0.0.0/0 .

3. When you have applied these modifications to your configuration files you need to restart PostgreSQL server. Now it is possible to login to your server remotely, using the username and password.

1. 您必须让 PostgreSQL 侦听远程传入 TCP 连接，因为默认设置只允许侦听环回接口上的连接。为了能够远程访问服务器，您必须将以下行添加到文件中/etc/postgresql/8.4/main/postgresql.conf:

   监听地址 = '*'

2. PostgreSQL 默认拒绝从任何远程地址接收的所有连接，您必须通过添加此行来放宽这些规则 /etc/postgresql/8.4/main/pg_hba.conf:

   托管所有所有 0.0.0.0/0 md5

   这是一个访问控制规则，只要他能提供有效的密码（md5 关键字），任何人都可以从任何地址登录。您可以使用需要的网络/掩码而不是 0.0.0.0/0 。

3. 将这些修改应用到配置文件后，您需要重新启动 PostgreSQL 服务器。现在可以使用用户名和密码远程登录到您的服务器。

回答by jwfearn

If you're using PostgreSQL 8 or above, you may need to modify the listen_addressessetting in /etc/postgresql/8.4/main/postgresql.conf.

如果你正在使用PostgreSQL 8或以上，你可能需要修改listen_addresses的设置/etc/postgresql/8.4/main/postgresql.conf。

Try adding the line:

尝试添加以下行：

listen_addresses = *

which will tell PostgreSQL to listen for connections on all network interfaces.

这将告诉 PostgreSQL 侦听所有网络接口上的连接。

If not explicitly set, this setting defaults to localhostwhich means it will only accept connections from the same machine.

如果没有明确设置，这个设置默认为localhost这意味着它只接受来自同一台机器的连接。

回答by samen tanay singh

In linux terminal try this:

在 linux 终端试试这个：

• sudo service postgresql start: to start the server
• sudo service postgresql stop: to stop thee server
• sudo service postgresql status: to check server status

• sudo service postgresql start: 启动服务器
• sudo service postgresql stop: 停止你的服务器
• sudo service postgresql status: 检查服务器状态

回答by max56

I didn't have to change my prostgresql.conf file but, i did have to do the following based on my psql via command line was connecting and pgAdmin not connecting on RDS with AWS.

我不必更改我的 prostgresql.conf 文件，但是，我确实必须根据我的 psql 通过命令行执行以下操作，而 pgAdmin 未在 RDS 上与 AWS 连接。

I did have my RDS set to Publicly Accessible. I made sure my ACL and security groups were wide open and still problem so, I did the following: sudo find . -name *.confthen sudo nano ./data/pg_hba.confthen added to top of directives in pg_hba.conf file host all all 0.0.0.0/0 md5and pgAdmin automatically logged me in.

我确实将 RDS 设置为公开访问。我确信我的ACL和安全组是敞开的，仍然问题，所以，我做了以下内容： sudo find . -name *.conf然后sudo nano ./data/pg_hba.conf再加入到pg_hba.conf文件指令的顶部host all all 0.0.0.0/0 md5和pgAdmin的自动登录我进去。

This also worked in pg_hba.conf file host all all md5without any IP address and this also worked with my IP address host all all <myip>/32 md5

这也适用于host all all md5没有任何 IP 地址的pg_hba.conf 文件 ，这也适用于我的 IP 地址host all all <myip>/32 md5

As a side note, my RDS was in my default VPC. I had an identical RDS instance in my non-default VPC with identical security group, ACL and security group settings to my default VPC and I could not get it to work. Not sure why but, that's for another day.

作为旁注，我的 RDS 在我的默认 VPC 中。我的非默认 VPC 中有一个相同的 RDS 实例，其安全组、ACL 和安全组设置与我的默认 VPC 相同，但我无法让它工作。不知道为什么，但那是另一天。

回答by Rujoota Shah

For redhat linux

对于红帽 linux

sudo vi /var/lib/pgsql9/data/postgresql.conf 

pgsql9 is the folder for the postgres version installed, might be different for others

pgsql9 是安装的 postgres 版本的文件夹，其他版本可能不同

changed listen_addresses = '*' from listen_addresses = ‘localhost' and then

从 listen_addresses = 'localhost' 改变了 listen_addresses = '*' 然后

sudo /etc/init.d/postgresql stop
sudo /etc/init.d/postgresql start

回答by Rujoota Shah

Check your firewall. When you disable it, then you can connect. If you want/can't disable the firewall, add a rule for your remote connection.

检查您的防火墙。当您禁用它时，您就可以连接了。如果您想要/不能禁用防火墙，请为您的远程连接添加规则。

回答by Saravanan Nandhan

Connecting to PostgreSQL via SSH Tunneling

通过 SSH 隧道连接到 PostgreSQL

In the event that you don't want to open port 5432 to any traffic, or you don't want to configure PostgreSQL to listen to any remote traffic, you can use SSH Tunneling to make a remote connection to the PostgreSQL instance. Here's how:

如果您不想向任何流量开放端口 5432，或者您不想配置 PostgreSQL 以侦听任何远程流量，则可以使用 SSH 隧道与 PostgreSQL 实例建立远程连接。就是这样：

1. Open PuTTY. If you already have a session set up to connect to the EC2 instance, load that, but don't connect to it just yet. If you don't have such a session, see this post.
2. Go to Connection > SSH > Tunnels
3. Enter 5433 in the Source Port field.
4. Enter 127.0.0.1:5432 in the Destination field.
5. Click the "Add" button.
6. Go back to Session, and save your session, then click "Open" to connect.
7. This opens a terminal window. Once you're connected, you can leave that alone.
8. Open pgAdmin and add a connection.
9. Enter localhost in the Host field and 8000 in the Port field. Specify a Name for the connection, and the username and password. Click OK when you're done.

1. 打开腻子。如果您已经设置了一个会话以连接到 EC2 实例，请加载该会话，但不要立即连接到它。如果您没有这样的会话，请参阅此帖子。
2. 转至连接 > SSH > 隧道
3. 在源端口字段中输入 5433。
4. 在目的地字段中输入 127.0.0.1:5432。
5. 单击“添加”按钮。
6. 返回会话，并保存您的会话，然后单击“打开”进行连接。
7. 这将打开一个终端窗口。连接后，您可以不理会它。
8. 打开 pgAdmin 并添加一个连接。
9. 在主机字段中输入 localhost，在端口字段中输入 8000。指定连接的名称以及用户名和密码。完成后单击“确定”。