Question posted in
The official documentation can be found here.

Postgres Docker image sometimes fail to start

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.

Tags:

2

Answers


  1. 0

    I don’t use PostgreSQL under docker in production myself, but I suspect the variables are never getting replaced with values and that script always fails.

    However, if you restart the container then the db directory already has files so the script won’t be run and you don’t get an error the second time.

    The official docs don’t suggest that docker-specific parameters get substituted in the sql scripts (scroll down to "Initialization scripts")

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

    Login or Signup to reply.
  2. 0

    You are using a .sql file as initialization script. Postgres does not support environment variables in SQL queries like that. So the parsing error is correct. The docs suggest something like this:

    /docker-entrypoint-initdb.d/init-user-db.sh:

    #!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE USER docker;
    CREATE DATABASE docker;
    GRANT ALL PRIVILEGES ON DATABASE docker TO docker;
EOSQL

    Building on what @Richard Huxton said, I think there is something going on with your mounts and the data directory not being empty, so the script is not run every time you start the container.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search