☰

无法使用 URI 连接到 postgres，但可以使用 psql -U 连接

tag-icon • tag-icon postgresql
无法使用 URI 连接到 postgres，但可以使用 psql -U 连接

我正在尝试在数字海洋上设置一个简单的 Web 服务器，但在使用 URI 通过 sqlalchemy 连接到数据库时遇到问题。

跑步

root@maudlin:/server/http/maudlin# psql postgresql://maudlin:<password>@localhost/maudlin
psql: error: could not connect to server: Connection refused
        Is the server running on host "localhost" (127.0.0.1) and accepting
        TCP/IP connections on port 5432?

失败但正在运行

root@maudlin:/server/http/maudlin# psql -U maudlin
Password for user maudlin: <password>
psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1))
Type "help" for help.

maudlin=>

通过。

据我所知，我的 pg_hba.conf 文件允许本地 IP 连接：

# This file is read on server startup and when the server receives a
# SIGHUP signal.  If you edit the file on a running system, you have to
# SIGHUP the server for the changes to take effect, run "pg_ctl reload",
# or execute "SELECT pg_reload_conf()".
#
# Put your actual configuration here
# ----------------------------------
#
# If you want to allow non-local connections, you need to add more
# "host" records.  In that case you will also need to make PostgreSQL
# listen on a non-local interface via the listen_addresses
# configuration parameter, or via the -i or -h command line switches.
host    maudlin         maudlin         <personal ip 1>/32         md5
host    maudlin         maudlin         <personal ip 2>/32        md5


# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# 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                                     md5
# 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     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

没有线

host    all             all             127.0.0.1/32            md5

意思是使用 md5 接受本地主机上带有密码的 ipv4 连接？

我想我可以将机器的 ip 添加到外部 ip 的顶部列表中，并通过它路由我的连接，但这看起来像是一个坏主意™

有人有任何调试技巧或建议吗？

答案1

事实证明我修改了我listen_addresses的postgresql.conf：

改变

listen_addresses = '<machine ip>'         # what IP address(es) to listen on;

到

listen_addresses = '<machine ip>, localhost'         # what IP address(es) to listen on;

并重新启动解决了问题。

相关内容