Problem connecting to a postgresql database in a docker stack

Docker Desktop
1

Hello all.

I have a problem connecting to a postgresql database running inside a docker stack. I am getting this error:

Datasource “db”: PostgreSQL database “postgres”, schema “public” at “localhost:5432”
Error: P1001: Can’t reach database server at `localhost:5432`
Please make sure your database server is running at `localhost:5432`.

My docker-compose is:

services:
  postgres:
    container_name: linkwarden-postgres
    image: postgres:16-alpine
    ports:
      - ${POSTGRES_PORT}:5432
    volumes:
      - ${DATA_DIR}/linkwarden/postgres:/var/lib/postgresql/data
    environment:
      - TZ=${TZ}
      - POSTGRES_USER=${POSTGRES_USER}
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
      - POSTGRES_HOST=${POSTGRES_HOST}
      - POSTGRES_DB=${POSTGRES_DB}
    restart: unless-stopped
  linkwarden:
    container_name: linkwarden
    image: ghcr.io/linkwarden/linkwarden:latest
    ports:
      - ${PORT}:3000
    volumes:
      - ${DATA_DIR}/linkwarden/data:/data/data
    depends_on:
      - postgres
    environment:
      - TZ=${TZ}
      - POSTGRES_USER=${POSTGRES_USER}
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
      - POSTGRES_HOST=${POSTGRES_HOST}
      - POSTGRES_DB=${POSTGRES_DB}
      - NEXTAUTH_URL=http://localhost:3000/api/v1/auth
      - NEXTAUTH_SECRET=${NEXTAUTH_SECRET}
      - DATABASE_URL=postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@${POSTGRES_HOST}:${POSTGRES_PORT}/${POSTGRES_DB}?schema=${POSTGRES_SCHEMA}
    restart: unless-stopped

Relevant environment variables are:

POSTGRES_USER=postgres
POSTGRES_PASSWORD=*redacted
POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DB=postgres
POSTGRES_SCHEMA=public

I have tried to switch from host “localhost”, to “0.0.0.0”, “172.19.0.24” (internal docker IP), “postgres”, “linkwarden-postgres”, etc., to no avail.

I bashed into the postgresql container and checked both user and database exists:

7373145f2d75:/# psql -U postgres
psql (16.11)
Type "help" for help.

postgres-# \l
                                                      List of databases
   Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules |   Access privileges   
-----------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |

How can I troubleshoot?

Thanks in advance.

2

Your postgres needs to listen on all IP addresses in the container, which must be the default in the official image. Then you can connect to it using “postgres” as hostname, since this is how your compose service is called, which is automatically registered in the internal DNS service.

If you use localhost as hostname, that works only in the following casees

  • Your application container also runs the postgres db server
  • You are using the host network in both the application container and the postgres container
  • You are using a shared network namespace between the application container and the postgres container.

You do none of those, so just use “postgres” instead of localhost.

I see you also pass POSTGRES_HOST to the postgres container. Is it supported by postgres? Even if it is, setting the same value for both doesn’t make sense to me. The app container needs to connect to a postgres server and the postgres server needs to listen on an IP, and not using a hostname.

You may want to use the POSTGRES_HOST_AUTH_METHOD as described in the image description

https://hub.docker.com/_/postgres

3

Thank you a lot for such detailed answer.

I removed the POSTGRES_HOST environment variable as it does not exist in the postgres documentation.

The DATABASE_URL variable ended like this

DATABASE_URL=postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@postgres:${POSTGRES_PORT}/${POSTGRES_DB}?schema=${POSTGRES_SCHEMA}

Now it connects normally.

4

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.