postgresql “psql：无法连接到服务器：连接被拒绝”连接到远程数据库时出错

"psql: could not connect to server: Connection refused" Error when connecting to remote database

提问by Poonam Anthony

I am trying to connect to a postgres database installed in a remote server using the following command:

我正在尝试使用以下命令连接到安装在远程服务器中的 postgres 数据库：

psql -h host_ip-U db_username-d db_name

psql -h host_ip-U db_username-ddb_name

This the error that occurs:

这是发生的错误：

psql: could not connect to server: Connection refused Is the server running on host "" and accepting TCP/IP connections on port 5432?

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

1. Postgres installed version is 9.4.
2. Host operating system : Ubuntu 15.04
3. Client operating system : Centos 7

1. Postgres 安装的版本是 9.4。
2. 主机操作系统：Ubuntu 15.04
3. 客户端操作系统：Centos 7

I already tried the following but the issue remains unresolved:

我已经尝试了以下但问题仍未解决：

1. Edited pg_hba.conffile to include

1. 编辑pg_hba.conf文件以包含

host all all 0.0.0.0/0 md5

托管所有所有 0.0.0.0/0 md5

1. Edited 'postgresql.conf' and changed the listen parameter to

2. 编辑“postgresql.conf”并将listen参数更改为

listen_addresses='*'

listen_addresses='*'

1. Restarted postgres service.
2. Disabled firewall and iptables on host and client.
3. I checked by running the psql command locally and it worked.
4. I tried the second solution given in this question. Running nmapgave me the following output:

3. 重新启动 postgres 服务。
4. 在主机和客户端上禁用防火墙和 iptables。
5. 我通过在本地运行 psql 命令进行了检查，它工作正常。
6. 我尝试了这个问题中给出的第二种解决方案。运行nmap给了我以下输出：

Starting Nmap 6.47 ( http://nmap.org ) at 2015-09-07 18:08 IST Nmap scan report for 10.17.250.250 Host is up (0.0000040s latency). Not shown: 997 closed ports PORT STATE SERVICE 22/tcp open ssh 25/tcp open smtp 80/tcp open http

Starting Nmap 6.47 ( http://nmap.org ) at 2015-09-07 18:08 IST Nmap scan report for 10.17.250.250 Host is up (0.0000040s latency). Not shown: 997 closed ports PORT STATE SERVICE 22/tcp open ssh 25/tcp open smtp 80/tcp open http

Am I missing something. Hope someone can help.

我是不是错过了什么。希望有人能帮忙。

回答by Thameem

cd /etc/postgresql/9.x/main/

open file named postgresql.conf

打开文件名 postgresql.conf

sudo vi postgresql.conf

add this line to that file

将此行添加到该文件

listen_addresses = '*'

then open file named pg_hba.conf

然后打开名为的文件 pg_hba.conf

sudo vi pg_hba.conf

and add this line to that file

并将这一行添加到该文件中

host  all  all 0.0.0.0/0 md5

It allows access to all databases for all users with an encrypted password

它允许所有用户使用加密密码访问所有数据库

restart your server

重启你的服务器

sudo /etc/init.d/postgresql restart

回答by jorge Godoy

Check the port defined in postgresql.conf. My installation of postgres 9.4 uses port 5433instead of 5432

检查中定义的端口postgresql.conf。我安装的 postgres 9.4 使用端口5433而不是5432

回答by aherocalledFrog

I have struggled with this when trying to remotely connect to a new PostgreSQL installation on my Raspberry Pi. Here's the full breakdown of what I did to resolve this issue:

在尝试远程连接到我的 Raspberry Pi 上的新 PostgreSQL 安装时，我一直在努力解决这个问题。以下是我为解决此问题所做的工作的完整细分：

First, open the PostgreSQL configuration file and make sure that the service is going to listen outside of localhost.

首先，打开 PostgreSQL 配置文件并确保服务将在 localhost 之外侦听。

sudo [editor] /etc/postgresql/[version]/main/postgresql.conf

I used nano, but you can use the editor of your choice, and while I have version 9.1installed, that directory will be for whichever version you have installed.

我使用了nano，但您可以使用您选择的编辑器，虽然我安装了版本9.1，但该目录将适用于您安装的任何版本。

Search down to the section titled 'Connections and Authentication'. The first setting should be 'listen_addresses', and might look like this:

向下搜索到标题为“连接和身份验证”的部分。第一个设置应该是'listen_addresses'，并且可能如下所示：

#listen_addresses = 'localhost'     # what IP address(es) to listen on;

The comments to the right give good instructions on how to change this field, and using the suggested '*'for all will work well.

右边的注释对如何更改此字段给出了很好的说明，使用'*'为所有人建议的方法会很好地工作。

Please note that this field is commented out with #. Per the comments, it will default to 'localhost', so just changing the value to '*'isn't enough, you also need to uncomment the setting by removing the leading #.

请注意，此字段用# 注释掉。根据评论，它将默认为“localhost”，因此仅将值更改为'*'是不够的，您还需要通过删除前导#.

It should now look like this:

它现在应该是这样的：

listen_addresses = '*'         # what IP address(es) to listen on;

You can also check the next setting, 'port', to make sure that you're connecting correctly. 5432 is the default, and is the port that psql will try to connect to if you don't specify one.

您还可以检查下一个设置“端口”，以确保您正确连接。5432 是默认的，如果你不指定，它是 psql 将尝试连接的端口。

Save and close the file, then open the Client Authentication config file, which is in the same directory:

保存并关闭文件，然后打开客户端身份验证配置文件，该文件位于同一目录中：

sudo [editor] /etc/postgresql/[version]/main/pg_hba.conf

I recommend reading the file if you want to restrict access, but for basic open connections you'll jump to the bottom of the file and add a line like this:

如果您想限制访问，我建议您阅读该文件，但对于基本的打开连接，您将跳转到文件底部并添加如下一行：

host all all all md5

You can press tab instead of space to line the fields up with the existing columns if you like.

如果您愿意，您可以按 Tab 而不是空格键将字段与现有列对齐。

Personally, I instead added a row that looked like this:

就个人而言，我改为添加了如下所示的行：

host [database_name] pi 192.168.1.0/24 md5

This restricts the connection to just the one user and just the one database on the local area network subnet.

这将连接限制为只有一个用户和局域网子网上的一个数据库。

Once you've saved changes to the file you will need to restart the service to implement the changes.

保存对文件的更改后，您将需要重新启动服务以实施更改。

sudo service postgresql restart

Now you can check to make sure that the service is openly listening on the correct port by using the following command:

现在，您可以使用以下命令检查以确保该服务正在公开侦听正确的端口：

sudo netstat -ltpn

If you don't run it as elevated (using sudo) it doesn't tell you the names of the processes listening on those ports.

如果您不以提升的方式运行它（使用sudo），它不会告诉您侦听这些端口的进程的名称。

One of the processes should be Postgres, and the Local Address should be open (0.0.0.0) and not restricted to local traffic only (127.0.0.1). If it isn't open, then you'll need to double check your config files and restart the service. You can again confirm that the service is listening on the correct port (default is 5432, but your configuration could be different).

其中一个进程应该是 Postgres，并且本地地址应该是开放的 (0.0.0.0) 并且不只限于本地流量 (127.0.0.1)。如果它没有打开，那么你需要仔细检查你的配置文件并重新启动服务。您可以再次确认该服务正在侦听正确的端口（默认为5432，但您的配置可能有所不同）。

Finally you'll be able to successfully connect from a remote computer using the command:

最后，您将能够使用以下命令从远程计算机成功连接：

psql -h [server ip address] -p [port number, optional if 5432] -U [postgres user name] [database name]

回答by Amirio

Make sure the settings are applied correctly in the config file.

确保在配置文件中正确应用了设置。

vim /etc/postgresql/x.x/main/postgresql.conf

Try the following to see the logs and find your problem.

尝试以下操作以查看日志并找到您的问题。

tail /var/log/postgresql/postgresql-x.x-main.log

回答by Jon

Mine was quite straightforward if you are on a Mac try:

如果你在 Mac 上尝试，我的很简单：

brew install postgres

This will tell you if you have it already install and what version or install the latest version for you if not then run

这将告诉您是否已经安装了它以及什么版本或为您安装最新版本如果没有然后运行

brew upgrade postgresql

This will make sure you have the latest version installed then finally

这将确保您安装了最新版本，然后最后

brew services start postgresql

This will start the service again. I hope this helps someone.

这将再次启动服务。我希望这可以帮助别人。

回答by Kabir Ahmadi

See the port and make a port change in postgresql.conf. My installation of postgres 9.4 uses port 5431 or 5434 instead of 5432. If it say the port is in use so change the port. And check if you give password in psql installation so give the password in file and save it.

查看端口并在 postgresql.conf 中更改端口。我安装的 postgres 9.4 使用端口 5431 或 5434 而不是 5432。如​​果它说端口正在使用中，请更改端口。并检查您是否在 psql 安装中提供密码，以便在文件中提供密码并保存。

回答by Neue Haas Grotesk

In my case I had removed a locale and generated another locale. Database failed to open because of fatal errors in the postgresql.conf file, on 'lc_messages', 'lc_monetary', 'lc_numberic', and 'lc_time'.

就我而言，我删除了一个语言环境并生成了另一个语言环境。由于 postgresql.conf 文件中“lc_messages”、“lc_monetary”、“lc_numberic”和“lc_time”上的致命错误，数据库无法打开。

Restoring the locale sorted it out for me.

恢复语言环境为我解决了这个问题。

回答by makwana gopikumar

Try to migrate your database. For instance, if you are using Heroku to host your project and with Django, then try heroku run python manage.py migratecommand; the error should go away.

尝试迁移您的数据库。例如，如果您使用 Heroku 来托管您的项目并使用 Django，那么请尝试heroku run python manage.py migrate命令；错误应该消失。

回答by Mohanavel T

I think you are using the machine-name instead of the ip of the host.

我认为您使用的是机器名而不是主机的 ip。

I got the same error when i tried with machine's name. Because, It is allowed only when both the client and host are under same network and they have the same Operating system installed.

当我尝试使用机器名称时，我遇到了同样的错误。因为，只有当客户端和主机在同一网络下并且安装了相同的操作系统时才允许。

回答by new2cpp

In my case, I did not change azure default security policy in management portal. The original is port 22 allowed and the rest are all denied. As long as I add 5432 port, everything becomes good.

就我而言，我没有更改管理门户中的 azure 默认安全策略。原来的22端口是允许的，其余的都被拒绝了。只要我添加5432端口，一切都会好起来的。