与pgAdmin的远程PostgreSQL连接 [英] Remote PostgreSQL connection with pgAdmin

问题描述

我正在尝试通过基于Ubuntu 16.04的服务器上运行的PostgreSQL建立远程连接。到目前为止，当我单击pgAdmin上的Save按钮时，它停滞了，什么也没做。键入... / manage.py runserver My_droplet_IP：5432后，我尝试了该网页，并且该网页可以访问。

I'm trying to set up a remote connection through PostgreSQL running on my server , based on Ubuntu 16.04. So far, when I click on the Save button on pgAdmin, it sort of freezes, does nothing. After typing .../manage.py runserver My_droplet_IP:5432, I try the webpage, and it is accessible.

我在创建我的Droplet之后遵循了本教程。
https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-16-04

I followed this tutorial after creating my droplet. https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-16-04

然后我编辑了settings.py; pg_hba.conf; postgresql.conf文件

Then I edited the settings.py; pg_hba.conf; postgresql.conf files

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

然后，当然也用我的Droplet IP更改了ALLOWED HOSTS = ['....']。

And, ofcourse changed the ALLOWED HOSTS = ['....'] with my droplet ip aswell.

postgresql.conf listen_address is set to '*'

pg_hba.conf文件：

pg_hba.conf file:

# 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例外。

Also allowed firewall, and made an exception to 5432 to be allowed.

有什么想法吗？

推荐答案

首先测试是否可以通过 psql ：

First of all test if you can connect to the database via psql:

psql -h ip_address -d name_of_the_database -U username

如果出现连接拒绝错误，则必须设置错误并检查我应该检查远程连接到PostgreSQL是否不起作用？

If you get connection refused error you had to set up something wrong and check the What should I check if remote connect to PostgreSQL not working?

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

---

我应该如何检查远程连接到PostgreSQL无法正常工作？

---

What should I check if remote connect to PostgreSQL not working?

1. 检查 pg_hba.conf

1. Check the authentication configuration in pg_hba.conf

通常位于linux上- /etc/postgresql/version/main/pg_hba.conf 。
您应该允许所有IP地址中的特定IP的客户端身份验证：

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

更多信息如何设置 pg_hba.conf 您可以在文档。

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

然后您应该设置监听

您必须找到 postgresql.conf 。通常位于 /etc/postgresql/9.1/main/postgresql.conf ）文件中，然后将带有listen_address的行更改为：

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 = '*'

每一步后，您都应重新启动Postgresql服务：

sudo service postgresql restart

2您应该在netstat命令后的侦听地址中看到端口5432（或5433）：

netstat -ntlp

之后，您必须在防火墙中打开PostgreSQL的端口：

sudo ufw allow 5432

您可以使用以下命令检查防火墙设置（您应该在列表中看到5432）：

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

sudo ufw status

如果上一步中的任何一个不起作用，则应检查PostgreSQL是否不在其他端口上运行（通常是5433），然后重复上一步。

当您有更多运行的PostgreSQL版本或升级数据库而忘记停止先前的PostgreSQL版本时，这种情况经常发生。

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文件在哪里？。

If you have problems to find configuration files you can check this thread Where are my postgres *.conf files?.

这篇关于与pgAdmin的远程PostgreSQL连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！