How to execute docker-entrypoint-initdb.d/init.sql files AFTER database is created?

Question

I have a Django app with Docker

I want to initialize my database based on init.sql file when running docker-compose up

2 containers are correctly built and init.sql file is available in db_container

but docker logs db_container show an error indicating that database has not been migrated yet:

ERROR: relation table1 does not exist

Database is created when entrypoint.sh files is executed (command python manage.py migrate)

I do not understand when init.db is executed?

Dockerfile

FROM python:3.8.3-alpine

WORKDIR /usr/src/app

ENV PYTHONDONTWRITEBYTECODE 1
ENV PYTHONUNBUFFERED 1

RUN apk update && apk add postgresql-dev gcc python3-dev musl-dev
RUN apk --update add libxml2-dev libxslt-dev libffi-dev gcc musl-dev libgcc openssl-dev curl
RUN apk add jpeg-dev zlib-dev freetype-dev lcms2-dev openjpeg-dev tiff-dev tk-dev tcl-dev
RUN pip3 install psycopg2 psycopg2-binary

COPY requirements/ requirements/
RUN pip install --upgrade pip && pip install -r requirements/dev.txt

COPY ./entrypoint.sh .
COPY . .

# Run entrypoint.sh
ENTRYPOINT [ "/usr/src/app/entrypoint.sh" ]

entrypoint.sh

#!/bin/sh

if [ "$DATABASE" = "postgres" ]
then
    echo "Waiting for postgres..."
    # nc = netcap -z = scanning
    while ! nc -z $SQL_HOST $SQL_PORT; do
      sleep 0.1
    done

    echo "PostgreSQL started"
fi

python manage.py flush --no-input
python manage.py migrate

exec "$@"

docker-compose.yml

version: '3.7'

services:
    web:
        ...
        depends_on: 
            - db
    db:
        image: postgres:12.0-alpine
        restart: always
        volumes:
            - postgres_data:/var/lib/postgres/data/
            - ./imports/init.sql:/docker-entrypoint-initdb.d/init.sql
        environment:
            - POSTGRES_USER=user
            - POSTGRES_PASSWORD=user
            - POSTGRES_DB=db_dev
volumes:
    postgres_data:

init.sql

\c db_dev

INSERT INTO table1 (field1,field2,field3) VALUES ('value1','value2','value3');
...

Answer 1

/docker-entrypoint-initdb.d/init.sql is executed the moment your database container starts running, while your entrypoint.sh is executed the moment your web container starts running. Since your web container depends on your database container, the SQL script will always be executed ahead of your entrypoint.

In other words, what you want is impossible.

You need to either create the database and table1 in init.sql and tell Django not to attempt creating them if they already exists or somehow add your INSERT.. to the list of migrations to be run.

I've never used Django, so I do not know if either of the above is possible.

Answer 2

Things will generally run in the following order:

1. The web build: block and the Dockerfile you show execute. This will never have access to the database.
2. The db container is created.
3. The web container is created.
4. The application image's entrypoint script gets up to the nc -z loop, and effectively pauses here.
5. The postgres image's entrypoint script starts a temporary PostgreSQL instance.
6. The postgres image's entrypoint script creates the db_dev database, as named in the POSTGRES_DB environment variable.
7. The postgres image's entrypoint script runs all of the files in /docker-entrypoint-initdb.d (including the init.sql script you show).
8. The postgres image's entrypoint script stops the temporary PostgreSQL instance and starts the real network-accessible one.
9. The application image's entrypoint script succeeds in connecting to PostgreSQL and proceeds past the nc loop.
10. The application image's entrypoint script runs python manage.py migrate.
11. The application image's entrypoint script exec "$@" to run the main container command.

Put another way: the Django migration command won't run until after the PostgreSQL container has fully completed its first-time startup, including running the docker-entrypoint-initdb.d scripts. That means that tables that get created in migrations never exist.

Data that needs to get loaded into a database at initialization time, for example to run tests or for well-known "admin" users, is generally referred to as "seed data". How to seed Django project ? - insert a bunch of data into the project for initialization has examples of adding a custom manage.py command or a fixtures file to load the seed data. In the entrypoint script you show, you'd add the python manage.py seed or load data command after running the migrations, but before launching the main server at the end.

Answer 3

Thanks to both.

I use data migrations https://docs.djangoproject.com/en/3.1/howto/writing-migrations/

And I suppress docker-entrypoint-initdb.d scripts my database is initialzed when entrypoint is executed (manage.py migrate apply all migration)

Answer 4

Will this work for your case?

1) Firstly, create an init script in a folder db

db\01-init.sh

The init script is to create the db if not exists. You can add data insert here as well

#!/bin/bash

set -e
export PGPASSWORD=$POSTGRES_PASSWORD;
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
  CREATE USER $APP_DB_USER WITH PASSWORD '$APP_DB_PASS';
  CREATE DATABASE $APP_DB_NAME;
  GRANT ALL PRIVILEGES ON DATABASE $APP_DB_NAME TO $APP_DB_USER;
  \connect $APP_DB_NAME $APP_DB_USER
  BEGIN;
    CREATE TABLE IF NOT EXISTS event (
      id CHAR(26) NOT NULL CHECK (CHAR_LENGTH(id) = 26) PRIMARY KEY,
      xxx INT,
      yyy JSON NOT NULL,
      UNIQUE(xxx)
    );
  COMMIT;
EOSQL

2) Then in the docker compose file, volumes will map the host db folder to container docker-entrypoint-initdb.d to share the db init script

version: '3'

services:
  sql_app:
    ... ## Other settings
    depends_on:
      - postgres
    networks:
      - postgres-network

  postgres:
    image: postgres:13.1
    ... ## Other settings
    restart: always
    environment:
      - POSTGRES_USER=foooooo
      - POSTGRES_PASSWORD=foooooo
      - APP_DB_USER=foooooo
      - APP_DB_PASS=foooooo
      - APP_DB_NAME=foooooo
    volumes:
      - ./db:/docker-entrypoint-initdb.d/
    ports:
      - 5432:5432
    networks:
      - postgres-network

networks:
  postgres-network:
    driver: bridge

3) Remove the migration script in Dockerfile

FROM python:3.8.3-alpine

WORKDIR /usr/src/app

ENV PYTHONDONTWRITEBYTECODE 1
ENV PYTHONUNBUFFERED 1

RUN apk update && apk add postgresql-dev gcc python3-dev musl-dev
RUN apk --update add libxml2-dev libxslt-dev libffi-dev gcc musl-dev libgcc openssl-dev curl
RUN apk add jpeg-dev zlib-dev freetype-dev lcms2-dev openjpeg-dev tiff-dev tk-dev tcl-dev
RUN pip3 install psycopg2 psycopg2-binary

COPY requirements/ requirements/
RUN pip install --upgrade pip && pip install -r requirements/dev.txt

COPY . .