I’m working on a Python project which uses Docker Compose with a Postgres service.

compose/postgresql/Dockerfile:

FROM postgres:16.1-bullseye

COPY init.sql /docker-entrypoint-initdb.d/init.sql

compose/postgresql/init.sql: (basic account setup)

-- Create database & user
CREATE DATABASE ${POSTGRES_DB};
CREATE USER ${POSTGRES_USER} WITH ENCRYPTED PASSWORD ${POSTGRES_PASSWORD};

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE ${POSTGRES_DB} TO ${POSTGRES_USER} WITH GRANT OPTION;
GRANT USAGE ON SCHEMA public TO ${POSTGRES_USER};
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ${POSTGRES_USER};

-- Alter default privileges
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO ${POSTGRES_USER};
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO ${POSTGRES_USER};

docker-compose.yaml: (using Docker Compose V2)

services:
  postgres:
    build:
      context: compose/postgresql
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_USER=app-user
      - POSTGRES_PASSWORD=S3cr3tP4ssw0rd
      - POSTGRES_DB=app-db

  python-service:
    [...]

The env variables above are provided in the docker-compose.yaml file when doing local tests. The Python service is booted 10s after the Postgres service, which is more than need as it only takes 2-3s to boot maximum. However it happens quite often (1 every 10 or so run) that the Postgres container fails to boot locally because it cannot parse the init.sql file:

postgres-1        | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init.sql
postgres-1        | psql:/docker-entrypoint-initdb.d/init.sql:2: ERROR:  syntax error at or near "$"
postgres-1        | LINE 1: CREATE DATABASE ${POSTGRES_DB};
postgres-1        |                         ^
postgres-1        | 2024-05-07 08:13:10.214 UTC [61] ERROR:  syntax error at or near "$" at character 17
postgres-1        | 2024-05-07 08:13:10.214 UTC [61] STATEMENT:  CREATE DATABASE ${POSTGRES_DB};
postgres-1 exited with code 3

which is it failing in such a non-deterministic way? If it’s a parsing error (like the error suggests) it should happen all the time or not at all, but not randomly.

It’s not really blocking me as I can just reboot the container but I’m curious about the cause of it so I could eventually fix it.