[bash] How to create User/Database in script for Docker Postgres

I have been trying to set up a container for a development postgres instance by creating a custom user & database. I am using the official postgres docker image. In the documentation it instructs you to insert a bash script inside of the /docker-entrypoint-initdb.d/ folder to set up the database with any custom parameters.

My bash script: make_db.sh

su postgres -c "createuser -w -d -r -s docker"
su postgres -c "createdb -O docker docker"

Dockerfile

FROM library/postgres

RUN ["mkdir", "/docker-entrypoint-initdb.d"]
ADD make_db.sh /docker-entrypoint-initdb.d/

The error I get from the docker logs -f db (db is my container name) is:

createuser: could not connect to database postgres: could not connect to server: No such file or directory

It seems that the commands inside of the /docker-entrypoint-initdb.d/ folder are being executed before postgres is started. My question is, how do I set up a user/database programmatically using the official postgres container? Is there any way to do this with a script?

With docker compose there's a simple alternative (no need to create a Dockerfile). Just create a init-database.sh:

#!/bin/bash
set -e

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

And reference it in the volumes section:

version: '3.4'

services:
  postgres:
    image: postgres
    restart: unless-stopped
    volumes:
      - postgres:/var/lib/postgresql/data
      - ./init-database.sh:/docker-entrypoint-initdb.d/init-database.sh
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
    ports:
      - 5432:5432

volumes:
  postgres:

By using docker-compose:

Assuming that you have following directory layout:

$MYAPP_ROOT/docker-compose.yml
           /Docker/init.sql
           /Docker/db.Dockerfile

File: docker-compose.yml

version: "3.3"
services:
  db:
    build:
      context: ./Docker
      dockerfile: db.Dockerfile
    volumes:
      - ./var/pgdata:/var/lib/postgresql/data
    ports:
      - "5432:5432"

File: Docker/init.sql

CREATE USER myUser;

CREATE DATABASE myApp_dev;
GRANT ALL PRIVILEGES ON DATABASE myApp_dev TO myUser;

CREATE DATABASE myApp_test;
GRANT ALL PRIVILEGES ON DATABASE myApp_test TO myUser;

File: Docker/db.Dockerfile

FROM postgres:11.5-alpine
COPY init.sql /docker-entrypoint-initdb.d/

Composing and starting services:

docker-compose -f docker-compose.yml up --no-start
docker-compose -f docker-compose.yml start

You can use this commands:

docker exec -it yournamecontainer psql -U postgres -c "CREATE DATABASE mydatabase ENCODING 'LATIN1' TEMPLATE template0 LC_COLLATE 'C' LC_CTYPE 'C';"

docker exec -it yournamecontainer psql -U postgres -c "GRANT ALL PRIVILEGES ON DATABASE postgres TO postgres;"

I add custom commands to a environment evoked in a CMD after starting services... I haven't done it with postgres, but with Oracle:

#set up var with noop command
RUN export POST_START_CMDS=":"
RUN mkdir /scripts
ADD script.sql /scripts
CMD service oracle-xe start; $POST_START_CMDS; tail -f /var/log/dmesg

and start with

docker run -d ... -e POST_START_CMDS="su - oracle -c 'sqlplus @/scripts/script' " <image>

.

You can now put .sql files inside the init directory:

From the docs

If you would like to do additional initialization in an image derived from this one, add one or more *.sql or *.sh scripts under /docker-entrypoint-initdb.d (creating the directory if necessary). After the entrypoint calls initdb to create the default postgres user and database, it will run any *.sql files and source any *.sh scripts found in that directory to do further initialization before starting the service.

So copying your .sql file in will work.

You need to have the database running before you create the users. For this you need multiple processes. You can either start postgres in a subshell (&) in the shell script, or use a tool like supervisord to run postgres and then run any initialization scripts.

A guide to supervisord and docker https://docs.docker.com/articles/using_supervisord/