没有主机的 pg_hba.conf 条目/连接调用失败/集群 12 主的无效数据目录 - Postgresql Ubuntu [英] no pg_hba.conf entry for host / Connect call failed / Invalid data directory for cluster 12 main - Postgresql Ubuntu

问题描述

我正在尝试将我的机器人从 Vultr 移动到 Ubuntu 虚拟服务器，但连接到 postgres 数据库时出现问题.我试过将配置从 md5 编辑为 true，将主机编辑为本地等.但这些只会给我不同的错误，并且也会使其在我的原始机器上停止工作.它在我的 Windows 机器上运行良好.这是我面临的错误:

I'm trying to move my bot to an Ubuntu virtual server from Vultr but it's having a problem connecting to the postgres database. I've tried editing the config from md5 to true, and host to local, etc. But those only give me different errors and also make it stop working on my original machine too. It's working perfectly fine on my Windows machine. Here is the error I'm facing:

asyncpg.exceptions.InvalidAuthorizationSpecificationError: no pg_hba.conf entry for host "[local]", user "postgres", database "xxx", SSL off

所以我试图改变这一行:

So I've tried to change this line:

async def create_db_pool():
    bot.pg_con = await asyncpg.create_pool(database='xxx', user='postgres', password='???')

为此:

async def create_db_pool():
    bot.pg_con = await asyncpg.create_pool(database='xxx', user='postgres', password='???', ssl=True)

这给了我这个错误:

asyncpg.exceptions._base.InterfaceError: `ssl` parameter can only be enabled for TCP addresses, got a UNIX socket path: '/run/postgresql/.s.PGSQL.5432'

所以我不知道还能尝试什么.我已经坚持了一段时间.如果相关，它会连接到 bot.py 文件的底部，如下所示:

So I don't know what else to try. I've been stuck on this for a while. If it's relevant, it connects at the bottom of the bot.py file like this:

bot.loop.run_until_complete(create_db_pool())

无论 ssl 是否为 True，数据库似乎仍在我的 Windows 机器上运行.但我无法让它在我的 Ubuntu 虚拟服务器上运行.

Whether ssl is True or not, the database seems to still function on my Windows machine. But I can't get it to work on my Ubuntu virtual server.

如果我将配置编辑为:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             0.0.0.0/0            md5
# IPv6 local connections:
host    all             all             ::/0                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
host    replication     all             0.0.0.0/0            md5
host    replication     all             ::/0                 md5

然后我收到这样的调用错误:

Then I get a call error like this:

OSError: Multiple exceptions: [Errno 111] Connect call failed ('::1', 5432, 0, 0), [Errno 111] Connect call failed ('127.0.0.1', 5432)

这真的让我发疯.我不知道该怎么做.我购买了这个虚拟服务器来托管我的机器人，但我什至无法让它连接到数据库.

This is really driving me crazy. I have no idea what to do. I bought this virtual server to host my bot on but I can't even get it to connect to the database.

当我在终端中简单地输入 psql 时，我得到这个错误:

When I simply type psql in the terminal, I get this error:

Error: Invalid data directory for cluster 12 main

Postgres 基本上没有按预期工作.如果重要的话，我正在使用 Vultr.com 来托管 Ubuntu 服务器.并与 PuTTy 连接.

Postgres is not working as intended in basically any way. I'm using Vultr.com to host the Ubuntu server, if that matters. And connecting with PuTTy.

推荐答案

您的 pg_hba.conf 有多个语法错误.本地主机"根本不允许连接类型，并且本地"连接类型是不允许的.连接类型不接受 IP 地址字段.服务器将拒绝使用您显示的文件启动/重新启动，如果您尝试重新加载正在运行的服务器，它将继续使用以前的设置.

Your pg_hba.conf has multiple syntax errors. The "localhost" connection type is not allowed at all, and the "local" connection type does not accept an IP address field. The server would refuse to start/restart with the file you show, and if you try to reload a running server it will just keep using the previous settings.

LOG:  invalid connection type "localhost"
CONTEXT:  line 4 of configuration file "/home/jjanes/pgsql/data/pg_hba.conf"
LOG:  invalid authentication method "127.0.0.1/32"
CONTEXT:  line 5 of configuration file "/home/jjanes/pgsql/data/pg_hba.conf"
LOG:  invalid authentication method "::1/128"
CONTEXT:  line 9 of configuration file "/home/jjanes/pgsql/data/pg_hba.conf"
LOG:  invalid connection type "localhost"
CONTEXT:  line 10 of configuration file "/home/jjanes/pgsql/data/pg_hba.conf"
LOG:  invalid authentication method "127.0.0.1/32"
CONTEXT:  line 102 of configuration file "/home/jjanes/pgsql/data/pg_hba.conf"
FATAL:  could not load pg_hba.conf
LOG:  database system is shut down

这篇关于没有主机的 pg_hba.conf 条目/连接调用失败/集群 12 主的无效数据目录 - Postgresql Ubuntu的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！