Multiple PostgreSQL Databases in Docker

When using Postgres in docker-compose sometimes I have a need to create multiple databases for a demo project, but sadly only a POSTGRES_DB environment variable is available for a single database.

Luckily, it's possible to define an initialization script that can help us out. The only inconvenience is in mounting a whole directory, since there is no easy way to mount a single file in Docker.

Here's how my docker-compose.yml looks:

• A directory pg-init-scripts that contains the initialization script is mounted into the docker container
• A custom environment variable POSTGRES_MULTIPLE_DATABASES is created with database names to be initialized

version: "3.8"
services:
  postgres:
    image: "postgres:13.2-alpine"
    container_name: postgres
    ports:
      - 5432:5432
    volumes:
      - ./pg-init-scripts:/docker-entrypoint-initdb.d
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=root
      - POSTGRES_MULTIPLE_DATABASES=accounts,bills,deposits

Now inside pg-init-scripts directory there is a script create_dbs.sh:

• Inside the script POSTGRES_USER is used as root user
• While POSTGRES_MULTIPLE_DATABASES is split on ,

#!/bin/bash

set -e
set -u

function create_user_and_database() {
    local database=$1
    echo "Creating user and database '$database'"
    psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
      CREATE USER $database;
      CREATE DATABASE $database;
      GRANT ALL PRIVILEGES ON DATABASE $database TO $database;
EOSQL
}

if [ -n "$POSTGRES_MULTIPLE_DATABASES" ]; then
    echo "Creating databases: $POSTGRES_MULTIPLE_DATABASES"
    for db in $(echo "$POSTGRES_MULTIPLE_DATABASES" | tr ',' ' '); do
        create_user_and_database "$db"
    done
    echo "Multiple databases created"
fi

Try it out and see if it helps you.

Here's the reference to PostgreSQL Docker image: https://hub.docker.com/_/postgres/ with more information about initialization scripts.