Database gets corrupted after running for a while

[kyesil]

I have basic docker compose file. It work 1-2 day after i see my app can't connect to db.

pgdb:
    image: postgres:15
    environment:
      - POSTGRES_DB=app
      - POSTGRES_USER=app
      - POSTGRES_PASSWORD=app
    ports:
      - "5432:5432"
    volumes:
      - ./_pgdata:/var/lib/postgresql/data

I try remove all images and containers and start. I try alpine and debian image. It does not run. I remove _pgdata folder and start again I work. But i lost all data. I don't understand what is change after time?

Can i use postgresql in production with docker ?

folder permision:
drwx------ 19 999 root 4096 Oct 2 10:43 _pgdata

docker inspect pgdb (borken):
https://hastebin.com/share/xeyadefuve.json

DB container logs here:

LOG:  database system is ready to accept connections
pgdb | 2023-10-02 10:14:50.097 +03 [35377] FATAL:  password authentication failed for user "app"
pgdb | 2023-10-02 10:14:50.097 +03 [35377] DETAIL:  Connection matched file "/var/lib/postgresql/data/pg_hba.conf" line 128: "host all pgdb all scram-sha-256"
pgdb | 2023-10-02 10:14:52.511 +03 [35378] FATAL:  password authentication failed for user "app"
pgdb | 2023-10-02 10:14:52.511 +03 [35378] DETAIL:  Connection matched file "/var/lib/postgresql/data/pg_hba.conf" line 128: "host all all all scram-sha-256"
pgdb | 2023-10-02 10:14:53.927 +03 [35379] FATAL:  password authentication failed for user "app"
pgdb | 2023-10-02 10:14:53.927 +03 [35379] DETAIL:  Connection matched file "/var/lib/postgresql/data/pg_hba.conf" line 128: "host all all all scram-sha-256"

[rranjangupta]

Hi @kyesil Have you fix this issue?

[manujsdev]

I have the same problem, please help me

[alexkorsun]

I faced the same issue with my local postgres.

This issue is caused by the incorrect initialization of the Postgres container. By default, the user password is generated using md5, so the password should be updated to support scram-sha-256. To resolve this issue, you can use POSTGRES_INITDB_ARGS="--auth-host=scram-sha-256" in the docker-compose.

But on my end, it didn't work to set a variable to that value for some reason.

I've added this to the config, so it should be ok for the local env

      POSTGRES_HOST_AUTH_METHOD: trust
      POSTGRES_INITDB_ARGS: --auth-host=trust