In development I like to use containerized databases. Recently I've been working on a micro service project that uses Postgres as a datasource. Each service has it's own independent datasource. Running independent postgresql docker per service is resource consuming, so hack is to use a single postgres docker with multiple databases.

Maybe I'm wrong, but multiple databases are not available by default on docker postgres image.


Thanks to https://gist.github.com/seandearnaley/0ac5897a5af3ac9e973fce7292ab6317, fix is to include an initialization script in docker-compose.yml. All initialization scripts should be defined in the /docker-entrypoint-initdb.d folder of the container.

I did some modifications of the original gist, as I wanted a different user login per db, and not have to work with superuser in development.

services:
  db:
    image: postgres:15.0
    restart: always
    ports:
      - 5434:5432
    env_file:
      - .env
    volumes:
      - ./init-multi-postgres-databases.sh:/docker-entrypoint-initdb.d/init-multi-postgres-databases.sh

If you take a look at the .env file, it looks like this:

POSTGRES_PASSWORD=pg_pass 
POSTGRES_MULTIBLE_DATABASES=db1:test1,db2:test2,db3

POSTGRES_PASSWORD is a mandatory variable, and defines a superuser password for PostgreSQL (default superuser is postgres).

One new environment variable is POSTGRES_MULTIPLE_DATABASES. Databases are separated by comma, user is same named as the database, and password is optional (colon).

As for the ./init-multi-postgres-databases.sh, it looks like:

#!/bin/bash

# Exit immediately if a command exits with a non zero status
set -e
# Treat unset variables as an error when substituting
set -u

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


# POSTGRES_MULTIBLE_DATABASES=db1,db2
# POSTGRES_MULTIBLE_DATABASES=db1:password,db2
if [ -n "$POSTGRES_MULTIBLE_DATABASES" ]; then
  echo "Multiple database creation requested: $POSTGRES_MULTIBLE_DATABASES"
  for db in $(echo $POSTGRES_MULTIBLE_DATABASES | tr ',' ' '); do
    user=$(echo $db | awk -F":" '{print $1}')
    pswd=$(echo $db | awk -F":" '{print $2}')
    if [[ -z "$pswd" ]]
    then
      pswd=$user
    fi

    echo "user is $user and pass is $pswd"
    create_databases $user $pswd
  done
  echo "Multiple databases created!"
fi

Don't forget to chmod +x scripts/create-multiple-postgres-databases.sh.

If your databases are still not created

Scripts in /docker-entrypoint-initdb.d are only run if you start the container with a data directory that is empty. If you look at the docker logs, you should see that your script got ignored. Solution is to clean the data directory and try again.

Entire configuration is in a git repository at https://github.com/amarjanica/docker-multi-postgres-databases.