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}

What steps are you taking when you make these changes? What else is in the Dockerfile? — David Maze, Apr 27 '19 at 19:38
@DavidMaze I added the complete `Dockerfile`, after each change I build to create a new image — akuma8, Apr 27 '19 at 19:44
How are you running the container? Is the underlying data in a volume? — David Maze, Apr 27 '19 at 19:57
I'm on windows 10, I had to create a volume before attach it to the container, the volume is empty since I just created it before attaching it to the container. — akuma8, Apr 27 '19 at 20:01
@DavidMaze The exact command is `docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=postgres -v postgres_data:/var/lib/postgresql/data/pgdata --name postgres my-postgres:11.2` — akuma8, Apr 27 '19 at 20:04
Does `docker volume rm postgres_data; docker volume create postgres_data` help? — David Maze, Apr 27 '19 at 20:07
@DavidMaze Is there a way to force this even if the volume is not empty? — akuma8, Apr 27 '19 at 20:22
Technically [it's gated on a `PG_VERSION` file](https://github.com/docker-library/postgres/blob/85aadc08c347cd20f199902c4b8b4f736341c3b8/11/docker-entrypoint.sh#L57). — David Maze, Apr 27 '19 at 20:27

score 2 · Accepted Answer · answered Apr 27 '19 at 20:25

2

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.

answered Apr 27 '19 at 20:25

David Maze

130,717
29
175
215

Thanks for these details. So in a case where we use an orchestrator like kubernetes and we ask it to start a new container, does that mean the new container will not initialize those databases? Since the volume that this new container will use won't be empty. – akuma8 Apr 27 '19 at 20:39
1

If you attach a persistent volume to a database pod in a stateful set, the first time you launch the container it will be empty and you'll get this initialization. But looking into what sort of migration system your local database tooling supports is probably a good next step. (Have the application create or update the database schema on startup, rather than having the database initialize it itself.) – David Maze Apr 27 '19 at 20:44
It's not an easy task to let the application creating the database with postgres. – akuma8 Apr 27 '19 at 21:09
My remark was a bit stupid, because if a new container is created using the existant volume, it doesn't make sense to launch the `init.sh` script since databases already exist in that volume and all datastructure will be already available. My bad – akuma8 Apr 29 '19 at 20:37

Create multiple databases at PostgreSQL container startup

1 Answers1