How to create User/Database in script for Docker Postgres

EDIT - since Jul 23, 2015

The official postgres docker image will run .sql scripts found in the /docker-entrypoint-initdb.d/ folder.

So all you need is to create the following sql script:

init.sql

CREATE USER docker;CREATE DATABASE docker;GRANT ALL PRIVILEGES ON DATABASE docker TO docker;

and add it in your Dockerfile:

Dockerfile

FROM library/postgresCOPY init.sql /docker-entrypoint-initdb.d/

But since July 8th, 2015, if all you need is to create a user and database, it is easier to just make use to the POSTGRES_USER, POSTGRES_PASSWORD and POSTGRES_DB environment variables:

docker run -e POSTGRES_USER=docker -e POSTGRES_PASSWORD=docker -e POSTGRES_DB=docker library/postgres

or with a Dockerfile:

FROM library/postgresENV POSTGRES_USER dockerENV POSTGRES_PASSWORD dockerENV POSTGRES_DB docker

for images older than Jul 23, 2015

From the documentation of the postgres Docker image, it is said that

[...] it will source any *.sh script found in that directory [/docker-entrypoint-initdb.d] to do further initialization before starting the service

What's important here is "before starting the service". This means your script make_db.sh will be executed before the postgres service would be started, hence the error message "could not connect to database postgres".

After that there is another useful piece of information:

If you need to execute SQL commands as part of your initialization, the use of Postgres single user mode is highly recommended.

Agreed this can be a bit mysterious at the first look. What it says is that your initialization script should start the postgres service in single mode before doing its actions. So you could change your make_db.ksh script as follows and it should get you closer to what you want:

NOTE, this has changed recently in the following commit. This will work with the latest change:

export PGUSER=postgrespsql <<- EOSQL    CREATE USER docker;    CREATE DATABASE docker;    GRANT ALL PRIVILEGES ON DATABASE docker TO docker;EOSQL

Previously, the use of --single mode was required:

gosu postgres postgres --single <<- EOSQL    CREATE USER docker;    CREATE DATABASE docker;    GRANT ALL PRIVILEGES ON DATABASE docker TO docker;EOSQL

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-alpineCOPY init.sql /docker-entrypoint-initdb.d/

Composing and starting services:

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

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.