Create multiple databases at PostgreSQL container startup

Question

I would like to create multiple databases at container startup. I use postgres:11.2 as base image.

After reading the doc here https://hub.docker.com/_/postgres and this post How to create User/Database in script for Docker Postgres, I am still unable to create multiple databases when the container starts.

I tried these solutions:

1st with init.sh:

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE USER owner;

    CREATE DATABASE productservice;
    GRANT ALL PRIVILEGES ON DATABASE productservice TO owner;

    CREATE DATABASE usersservice;
    GRANT ALL PRIVILEGES ON DATABASE usersservice TO owner;

    CREATE DATABASE financeservice;
    GRANT ALL PRIVILEGES ON DATABASE financeservice TO owner;

    CREATE DATABASE relationshipservice;
    GRANT ALL PRIVILEGES ON DATABASE relationshipservice TO owner;

    CREATE DATABASE securityservice;
    GRANT ALL PRIVILEGES ON DATABASE securityservice TO owner;
EOSQL

And in the Dockerfile

ENV INIT_DIR /docker-entrypoint-initdb.d
RUN mkdir -p ${INIT_DIR}
COPY init.sh ${INIT_DIR}

I don't get any errors but neither the databases nor the user are created, only the default postgres database is there.

2nd with init.sql

CREATE USER owner;

CREATE DATABASE productservice;
GRANT ALL PRIVILEGES ON DATABASE productservice TO owner;

CREATE DATABASE usersservice;
GRANT ALL PRIVILEGES ON DATABASE usersservice TO owner;

CREATE DATABASE financeservice;
GRANT ALL PRIVILEGES ON DATABASE financeservice TO owner;

CREATE DATABASE relationshipservice;
GRANT ALL PRIVILEGES ON DATABASE relationshipservice TO owner;

CREATE DATABASE securityservice;
GRANT ALL PRIVILEGES ON DATABASE securityservice TO owner;

And changed the Dockerfile to

ENV INIT_DIR /docker-entrypoint-initdb.d
RUN mkdir -p ${INIT_DIR}
COPY init.sql ${INIT_DIR}

The result is the same as the first case.

Thanks for your help.

EDIT:

The complete Dockerfile

FROM postgres:11.2
ENV PGDATA /var/lib/postgresql/data/pgdata
ENV INIT_DIR /docker-entrypoint-initdb.d
RUN mkdir -p ${INIT_DIR}
COPY init.sql ${INIT_DIR}
RUN localedef -i fr_FR -c -f UTF-8 -A /usr/share/locale/locale.alias fr_FR.UTF-8
ENV LANG en_US.UTF-8
VOLUME ${PGDATA}

Answer 1

The various standard database containers do their setup only when they first run. This includes things like creating users, setting a root password, and running things in the /docker-entrypoint-initdb.d directory. They are also typically configured to store their underlying data in an anonymous volume, if you don't provide a volume at the docker run command line.

If your database is backed by a named volume, you need to delete and recreate that volume:

docker stop postgres
docker rm postgres
docker volume rm postgres_data
docker volume create postgres_data
docker run -d -p 5432:5432 -v postgres_data:/var/lib/postgresql/data my-postgres

If it's backed by a filesystem path (-v $PWD/pgdata:...) then you need to rm -rf and then mkdir that directory.

If you didn't specify a docker run -v option at all then Docker will automatically create an anonymous volume. When you delete the old container, docker rm -v will also delete the volume, or docker volume ls will help you find its long ID.