使用pgAdmin进行远程PostgreSQL连接

Question

我正在尝试通过运行在Ubuntu 16.04上的PostgreSQL设置远程连接。目前，当我在pgAdmin上单击保存按钮时，它似乎会冻结且没有任何反应。在键入.../manage.py runserver My_droplet_IP:5432后，我尝试了网页，并成功访问。

在创建我的虚拟机后，我遵循了这个教程： https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-16-04 然后我编辑了settings.py、pg_hba.conf和postgresql.conf文件。

settings.py:

DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresqlpsycopg2',
'NAME': '.....',
'USER': '....',
'PASSWORD': '....',
'HOST': '127.0.0.1',
'PORT': '5432',

STATICROOT = os.path.join(BASE_DIR, 'static/') - at the end of the page

当然，我也更改了ALLOWED HOSTS = ['....']，用我的droplet IP替换。

postgresql.conf listen_address is set to '*'

pg_hba.conf 文件：

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

也允许防火墙，并设置例外，允许5432端口通过。有什么想法吗？

- Rve

---

我得到了一个简单的答案：psql: 无法连接到服务器：连接被拒绝。服务器是否在主机“46.101.165.162”上运行，并接受端口5432上的TCP/IP连接？ - Rve

如果你运行"netstat -na"命令，会显示一个监听端口5432的行吗？这个端口真的是开放的吗（sudo ufw status显示端口5432）？这是PostgreSQL 10吗？ - Bulva

我正在使用相同的版本。你确定在postgresql.conf中设置了良好的listen_address='*'（开头没有#）吗？每次更改后，您都必须重新启动postgresql服务“sudo service postgresql restart”。在同一文件中，是否有端口5432？有时postgresql运行在5433上。 - Bulva

哇，我无语了。在处理问题的整个过程中，我简单地忽略了检查侦听地址前面是否有#的部分。非常感谢！问题解决了。 - Rve

很好。很高兴能帮忙。答案可以帮助其他人调试这个问题。 - Bulva

Answer 1

首先，通过psql测试是否可以连接到数据库：

psql -h ip_address -d name_of_the_database -U username

如果你遇到连接被拒绝的错误，说明你设置有误，请检查如果远程连接到PostgreSQL不起作用，我应该检查什么？

psql: could not connect to server: Connection refused Is the server running on host ip_address

如果远程连接到PostgreSQL无法工作，我应该检查什么？

1. Check the authentication configuration in pg_hba.conf

   Usually located on linux - /etc/postgresql/version/main/pg_hba.conf. You should allow authentication for client for specific IP all from all IP addresses:

   # Database administrative login by Unix domain socket
   local     all            postgres        peer
   
   # TYPE  DATABASE        USER            ADDRESS                 METHOD
   
   # "local" is for Unix domain socket connections only
   local     all            all             peer
   # IPv4 local connections:
   host     all             all             0.0.0.0/0               md5
   # IPv6 local connections:
   host     all             all             ::0/0                   md5
   #all ips
   host     all             all             all                     md5
   

   More information how to set up pg_hba.conf you can find in documentation.

2. Then you should set up listening on specific port.

   You have to find the postgresql.conf. Usually located /etc/postgresql/9.1/main/postgresql.conf) file and change the line with listen_address from:

   #listen_address = ''
   

   to (don't forget remove # which means comment):

   listen_address = '*'
   

3. After every step you should restart Postgresql service:

   sudo service postgresql restart
   

4. After step 2 you should see port 5432 (or 5433) in listening address after netstat command:

   netstat -ntlp
   

5. After that you have to open port for PostgreSQL in firewall:

   sudo ufw allow 5432
   

   You can check firewall settings with (you should see 5432 in the list):

   sudo ufw status
   

6. If any of the previous step doesn't work you should check if PostgreSQL is not running on different port (usually 5433) and repeat the previous steps.

   This happens very often when you have more running versions of PostgreSQL or you upgrade database and forgot stop the previous version of PostgreSQL.

如果您在查找配置文件时遇到问题，可以查看这个帖子：我的postgres *.conf文件在哪里？。

- Bulva

Answer 2

如果您正在使用GCP，请记得在GCP内设置防火墙规则以允许该端口，这可能会节省您一些调试时间。

- user3821178