How do I create a database within a docker container using only the docker-compose file?

Question

I'm trying to create a database and connect to it within my container network. I don't want to have to ssh into a box to create users/databases etc, as this is not a scalable or easily distributable process.

This is what I have so far:

# docker-compose.yml
db:
    image: postgres:9.4
    volumes:
      - ./db/init.sql:/docker-entrypoint-initdb/10-init.sql
    environment:
      - PGDATA=/tmp
      - PGDATABASE=web
      - PGUSER=docker
      - PGPASSWORD=password

This is my init.sql file:

CREATE DATABASE web;
CREATE USER docker WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE web TO docker;

When I start up the container and try to connect to it, I get this error:

db_1   | FATAL:  role "docker" does not exist
db_1   |  done
db_1   | server started
db_1   | FATAL:  database "web" does not exist
db_1   | psql: FATAL:  database "web" does not exist

The first time this happened, I tried to create a role like this:

CREATE ROLE docker with SUPERUSER PASSWORD password;
GRANT web TO docker;

But it did not have any effect. To make matters even more confusing, when I use node-postgres to connect to the db, I get this error:

Error: connect ECONNREFUSED

But how can the connection be refused if the db service isnt even up??

In a nutshell, these are the questions I'm trying to solve:

1. How can I create a database using only the files in my project (i.e. no manual commands)?
2. How do I create a user/role using only the files in my project?
3. How do I connect to this database?

Thank you in advance.

Answer 1

How can I create a database using only the files in my project (i.e. no manual commands)?

The minimal docker-compose.yml config for you defined user and database is:

postgres:
    image: postgres:9.4
    environment:
      - POSTGRES_DB=web
      - POSTGRES_USER=myuser

How do I create a user/role using only the files in my project?

To execute scripts on database initialization take a look at the official docs for initdb. To get you started with a quick and dirty solution create a new file e.g. init_conf.sh in the same directory as your docker-compose.yml:

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d "$POSTGRES_DB" <<-EOSQL

    CREATE ROLE docker with SUPERUSER PASSWORD 'password';

EOSQL

And add the volumes directive to your docker-compose.yml.

volumes:
  - .:/docker-entrypoint-initdb.d

Recreate your container because otherwise, you wouldn't trigger a new database initialization. That means, docker stop and docker rm the old one first before executing docker-compose up again. STDOUT gives you now some information about our newly introduced script.

How do I connect to this database?

To connect to your database with docker exec via the terminal:

docker exec -ti folder_postgres_1 psql -U myuser -d web

---

A docker-compose.yml in one of my production environments looks like the following:

services:
  postgres:
    logging: &logging
      driver: json-file
      options:
        max-size: "10m"
        max-file: "5"
    build: ./docker/postgres  # path to custom Dockerfile
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - postgres_backup:/backups
    env_file: .env
    restart: always

  # ... other services like web, celery, redis, etc.

Dockerfile:

FROM postgres:latest

# ...
COPY *.sh /docker-entrypoint-initdb.d/
# ...