Automatically create databases with docker compose and postgres

Postgres docker image creates, by default, a user called postgres with postgres password (this changes in postgres 12: you need to explicitly specify a password) and a database called postgres

This means that in most of situations, what you need is:

DATABASE_URL: postgres://postgres:postgres@postgres:5432/postgres

# Or, if you change the password:
DATABASE_URL: postgres://postgres:secret@postgres:5432/postgres

Create databases using psql #

But in some scenarios you need at least a couple of databases for each project: one for development, other for testing.

One option is to run psql inside the docker container:

docker exec -it <project-name>_postgres_1 psql -U postgres

... and create them manually:

CREATE DATABASE <database>
GRANT ALL PRIVILEGES ON DATABASE <database> to postgres

Create databases automatically #

The postgres docker image will run any *.sh file inside /docker-entrypoint-initdb.d.

So the idea is to add an initialization file using a docker container volumes:

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:

And the init file connects to posqgres via psql and run the initialization:

init-database.yml:

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