Error connecting to postgres

[LizardWizardGB]

Hi there,

I updated from postgres:15-alpine to postgres:16-alpine, but afterwards I started getting the following error:

lemmy-lemmy-1 | Lemmy v0.19.3
lemmy-lemmy-1 | thread 'main' panicked at crates/db_schema/src/utils.rs:281:56:
lemmy-lemmy-1 | Error connecting to postgres://lemmy:password@postgres:5432/lemmy: connection to server at "postgres" (172.21.0.2), port 5432 failed: Connection refused
lemmy-lemmy-1 | Is the server running on that host and accepting TCP/IP connections?

docker-compose.yml lemmy.hjson Log from postgres:

postgres_1  | PostgreSQL Database directory appears to contain a database; Skipping initialization
postgres_1  | 2024-06-08 21:01:51.159 GMT [1] LOG:  starting PostgreSQL 16.3 on x86_64-pc-linux-musl, compiled by gcc (Alpine 13.2.1_git20240309) 13.2.1 20240309, 64-bit
postgres_1  | 2024-06-08 21:01:51.159 GMT [1] LOG:  listening on IPv4 address "127.0.0.1", port 5432
postgres_1  | 2024-06-08 21:01:51.159 GMT [1] LOG:  could not bind IPv6 address "::1": Address not available
postgres_1  | 2024-06-08 21:01:51.164 GMT [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres_1  | 2024-06-08 21:01:51.173 GMT [23] LOG:  database system was shut down at 2024-06-08 21:01:50 GMT
postgres_1  | 2024-06-08 21:01:51.179 GMT [1] LOG:  database system is ready to accept connections

I've tried downgrading to postgres:15-alpine, but still the same error message.

I've tried deleting volumes/postgres/ and followed the Backup and restore from the docs and have tried multiple times. No luck.

After restoring the db_dumb, the documentation says that I might need to update permissions: chown -R $USER volumes, but when the postgres container is later restarted, the permissions change back to owner id: 70. However, doing chown -R $USER volumes and then restarting the lemmy container, doesn't make a difference.

I am using the same password across the board (docker-compose.yml, lemmy.hjson, DB password), but I've also tried the mentioned commands for dropping the database and changing the password for the DB user...

Docker version 24.0.5, build 24.0.5-0ubuntu1~22.04.1 docker-compose version 1.29.2, build unknown

[LizardWizardGB]

So I tried installing Lemmy from scratch and got that up and running. I was able to setup the admin user and so forth. I then shut Lemmy down, deleted the DB, created it again and then imported the DB backup.

Jun 09 00:46:45 lemmy lemmy_server[236]: Error: LemmyError { message: Unknown("Error connecting to database"), inner: Error connecting to database
Jun 09 00:46:45 lemmy lemmy_server[236]: Caused by:
Jun 09 00:46:45 lemmy lemmy_server[236]:     connection to server at "localhost" (::1), port 5432 failed: Connection refused
Jun 09 00:46:45 lemmy lemmy_server[236]:             Is the server running on that host and accepting TCP/IP connections?
Jun 09 00:46:45 lemmy lemmy_server[236]:     connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
Jun 09 00:46:45 lemmy lemmy_server[236]:             Is the server running on that host and accepting TCP/IP connections?

Could this mean that the DB backup is corrupted somehow?

Going to the website, it just says error and giving the error error "Lemmy is currently starting" in the console.

[citking]

This sounds like your pg_hba.conf file is not whitelisting your IP address. Make sure you have the proper host record(s) in there, otherwise it will not connect.

[LizardWizardGB]

This sounds like your pg_hba.conf file is not whitelisting your IP address. Make sure you have the proper host record(s) in there, otherwise it will not connect.

This was my pg_hba.conf:

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             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
local   lemmy           lemmy                                   md5

and then I added host all all all md5 at the bottom of the file. Now I get the following error:

Jun 09 17:30:27 lemmy lemmy_server[765]: Error: LemmyError { message: Unknown("Couldn't run DB Migrations: Failed to run 2024-02-24-034523_replaceable-schema with: constraint \"post_aggregates_post_id_fkey\" of relation \"post_aggregates\" does not exist"), inner: Couldn't run DB Migrations: Failed to run 2024-02-24-034523_replaceable-schema with: constraint "post_aggregates_post_id_fkey" of relation  "post_aggregates" does not exist, context: SpanTrace [] }

[LizardWizardGB]

It seems the issue was lemmy.hjson... To see if it actually read the file, I changed the password, to something that was wrong and instead of the original error message, it now said that the password was wrong. I then tried to change the host to host: localhost, knowing that this shouldn't work, and it didn't. However, when I changed back to host: postgres everything now works!

{
  # for more info about the config, check out the documentation
  # https://join-lemmy.org/docs/en/administration/configuration.html

  database: {
    host: postgres
    password: "secretpass"
  }
  hostname: "domain.tld"
}