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:

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

Thank you in advance.

Please consider to upvote, accept or comment the answer if this solved your problem. It's The best way to say thanks to other people here at SO after they invested some time in posting them. Thanks! — Yannic Hamann
– Yannic Hamann, Commented Mar 2, 2018 at 8:56

Yannic Hamann · Accepted Answer · 2018-02-28 12:20:48Z

10

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/
# ...

edited Feb 28, 2018 at 12:20

answered Feb 28, 2018 at 8:25

Yannic Hamann

5,2651 gold badge37 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dopatraman Over a year ago

Thank you for this detailed answer. Is adding POSTGRES_DB to my compose file enough to create a database from nothing?

Yannic Hamann Over a year ago

Yes, in fact, you could also leave this out because it defaults to POSTGRES_USER which defaults to postgres.

Mazel Tov · Accepted Answer · 2018-02-28 08:08:01Z

2

The environment variable you are using are wrong. Try this

version: '3.3'

services:  

  db:
    image: postgres:9.4
    restart: always
    environment: 
        - POSTGRES_USER=docker
        - POSTGRES_PASSWORD=password
        - POSTGRES_DB=web
    volumes:
      - db_data:/var/lib/postgresql/data
    # optional port
    ports: ["5555:5432"]

volumes:
  db_data:

then from any other docker-compose service you can access the DB at db:5432 and from your host machine you can access postgres on localhost:5555 if you also add the ports

answered Feb 28, 2018 at 8:08

Mazel Tov

2,21217 silver badges26 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related