Multiple Databases in One Postgres Container

Recently, some new Ruby Gems were released which aim to consolidate various supporting service for Rails such as Redis, into database backed systems. These gems are : solid_queue, solid_cache and solid_cable

Here's how I setup SolidQueue and SolidCache. Booko's databases run inside Docker containers now-a-days, so it makes sense for these new services to use Docker containers also. However, rather than having seperate hosts for each of SolidQueue and SolidCache, I want to use a single host. This lead to a new requirement for me - several databases inside a single PostgreSQL container. Running separate PostgreSQL containers per host makes no sense - attempting to split up the memory and CPUs across the two containers will invariably lead to wasted capacity. We'll be using the offical Postgres image, and the edoburu/pgbouncer images.

Cristian Angulo's post, has done most of the hard work for the Postgres setup. We create an extra init script, and mount it within the docker-entrypoint-initdb.d directory. The following script will be saved locally to multiple-databases.sh

I've updated it to set the password for the newly created users.

#!/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" --dbname "postgres" <<-EOSQL
        CREATE USER $database WITH PASSWORD '$POSTGRES_PASSWORD';
        CREATE DATABASE $database;
        GRANT ALL PRIVILEGES ON DATABASE $database TO $database;
EOSQL
}

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

# https://dev.to/nietzscheson/multiples-postgres-databases-in-one-service-with-docker-compose-4fdf

We mount this script within the container at /docker-entrypoint-initdb.d/multiple-databases.sh by using the volumes command.

services:
  solid-pg:
    restart: always
    image: postgres:17
    environment:
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_MULTIPLE_DATABASES: solid_cache, solid_queue
    ports:
    - "192.168.1.10:5432:5432"
    volumes:
      - ./postgresql.conf:/etc/postgresql/postgresql.conf:ro
      - ./multiple-databases.sh:/docker-entrypoint-initdb.d/multiple-databases.sh
      - postgres_solid_stuff_data:/var/lib/postgresql/data
    command: ["postgres", "-c", "config_file=/etc/postgresql/postgresql.conf"]

volumes:
  postgres_solid_stuff_data:

This will be run as part of the initialisation script for Postgres. You can see the new environment variable which is used by the script:

POSTGRES_MULTIPLE_DATABASES: solid_cache, solid_queue

These two database will be created, and owed by the postgres user. For Booko's primary databases, I mount an external volume to store the data directory, but for these, I'll make use of Docker's named mounts.

Next post, we'll see how to setup PGBouncer.