Postgresql connectivity issue (sh.itjust.works)

submitted 10 months ago by idefix@sh.itjust.works to c/lemmy_admin@lemmy.ml

9 comments fedilink hide all child comments

Good afternoon! Newbie here, I've tried to install Lemmy using Ansible (Debian stable) and I ended up having an issue with the Postgresql connectivity (localhost via socket).

The error message I have is:

thread 'main' panicked at 'Error connecting to postgresql:///lemmy?user=lemmy&amp;host=/var/run/postgresql: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory

I have updated my /etc/postgresql/15/main/pg_hba.conf from peer to trust to md5 with no success (rebooting Postresql each time).

My config.hjson is:

database: {
 uri: "postgresql:///lemmy?user=lemmy&amp;host=/var/run/postgresql"
 password: "{{ postgres\_password }}"
}

Any idea / suggestion? Thanks!

PS: cross-posted on the Matrix.org install support chat

top 9 comments

sorted by: hot top controversial new old

[-] taaz@biglemmowski.win 1 points 10 months ago* (last edited 10 months ago)

Considering you are getting No such file or directory error then

Is the path correct?
Can lemmy access the file? For example if lemmy is running in docker the path/socket does not exist in the container unless specifically instructed...
Does lemmy process have the required permissions to actually read/access the file?

[-] idefix@sh.itjust.works 1 points 10 months ago

The path is correct, my investigations directed me towards a docker access issue as suggested by your #2. Looking into it now, thanks!

[-] taaz@biglemmowski.win 1 points 10 months ago* (last edited 10 months ago)

If lemmy is actually running in docker then you should rather use network instead of socket file - while it should work I would be afraid of docker shenanigans when it comes to mounting a socket file as volume into the container (it should work, but ...).

You should be able to access postgres over network on the host thru extra_hosts settings:

extra_hosts:
    - "host.docker.internal:host-gateway"

https://stackoverflow.com/a/43541732

postgres://host.docker.internal:5432

[-] idefix@sh.itjust.works 1 points 10 months ago

My investigations so far have led me to the conclusion that the Ansible install creates multiple docker containers, including one for Lemmy and one for Postgresql. I need now to figure out how inter-container communications work but the host itself is not used.

[-] taaz@biglemmowski.win 2 points 10 months ago* (last edited 10 months ago)

Ah right, I assumed you were trying to connect the lemmy container to postgres running outside of docker.

One important thing to remember with all docker compose files - the service name (the first keys in the services: configuration) is also the hostname of that container so to ping lemmy (from some other container in that docker compose) you would do ping lemmy, same for postgres ping postgres - but if the postgres service was named db0 then it would be ping db0.
You also do not have to expose ports - all containers in that compose share one network (exposing is for outside access).
All together your postgres config for lemmy should like this:

 database: { 
  # name of the postgres database for lemmy
  database: "lemmy" 
  # username to connect to postgres 
  user: "postgres"
  # password to connect to postgres
  password: "xxxxxxx"
  # host where postgres is running
  host: "postgres" 
  # port where postgres can be accessed
  port: 5432
  # maximum number of active sql connections
  pool_size: 10
}

[-] idefix@sh.itjust.works 2 points 10 months ago

Amazingly helpful, thanks!

[-] idefix@sh.itjust.works 1 points 10 months ago

Ok, the good news is that it works. The bad news is that I don't understand what changed. 🤨

[-] taaz@biglemmowski.win 1 points 10 months ago

I don't get what you mean here. Communication over (linux) socket file and TCP/IP is very different.

[-] idefix@sh.itjust.works 2 points 10 months ago

After reading your comment, I went back to investigate my install. I can't remember having changed anything relevant but Lemmy started to function properly, connecting as it should the database. It's been stable since, even after reboots.

As you suggested, I am using "postgres" as the host, as the service is described in the docker-compose.yml file. The communication is then via TCP/IP and not socket.

this post was submitted on 19 Dec 2023

2 points (100.0% liked)

Lemmy Administration

1 readers

13 users here now

Anything about running your own Lemmy instance. Including how to install it, maintain and customise it.

Be sure to check out the docs: https://join-lemmy.org/docs/en/administration/administration.html

If you have any problems, describe them here and we will try to help you fixing them.

founded 4 years ago

MODERATORS

nutomic@lemmy.ml