0

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='???')

to this:

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

and that gives me this error:

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

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())

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.

If I edit my config to this:

# 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.

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

Error: Invalid data directory for cluster 12 main

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.

Break
  • 332
  • 3
  • 19
  • Is `local` a valid host name in your environment? Maybe you mean `localhost`? – sticky bit Aug 08 '20 at 01:35
  • I have tried that too. I edited the post with the config file. This time I tried to include all of the config changes but none seem to work. It's the same error regardless of whether its `md5` or `trust` too. This time I used a separate config file instead though, so that it won't break on my windows machine too. – Break Aug 08 '20 at 01:53
  • What version of ubuntu are you using, and how did you install PostgreSQL on it? which repo? – jjanes Aug 09 '20 at 17:52
  • I'm pretty sure I used this command to install `sudo apt install postgresql postgresql-contrib` but I also uninstalled a couple times and also restarted the server completely once too. I've also tried to just directly copy the postgres folder from my normal machine into the Ubuntu server, hoping that it would just get me past as these errors as it's working on my Windows. My Ubuntu version is `Ubuntu 20.04 x64`. I'm thinking about just changing my bot database to a json instead at this point :l – Break Aug 09 '20 at 18:37

1 Answers1

0

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
jjanes
  • 37,812
  • 5
  • 27
  • 34
  • Well that was one of the examples that I've tried. I've tried basically everything but it seems like its not even reading the config file at all. Here is what it is right now, which continues to work on my Windows machine:```host all all 0.0.0.0/0 md5``` I can't seem to format it well in a comment, but all the lines are similar to this. Yet the error I'm receiving is: ```OSError: Multiple exceptions: [Errno 111] Connect call failed ('::1', 5432, 0, 0), [Errno 111] Connect call failed ('127.0.0.1', 5432)``` but none of the connections in conf include that IP – Break Aug 09 '20 at 15:21
  • That error is coming from the client code, not from PostgreSQL. I would guess PostgreSQL is not running at all. – jjanes Aug 09 '20 at 17:49
  • I think you're right but I can't figure out why. I've searched google for like every error and none of the solutions seem to work. The server appears to have remote connections enabled and from any address as long as it has the password, but it's like it doesn't even read the config file. During one of the installations, it didn't even come with a config file and I had to drag+drop my own into the the unbuntu server with FileZilla. Idk, all of this is weird and it's been like 3 days and I still can't get past this lol – Break Aug 09 '20 at 18:41