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, SolidCache and the SolidCable databases. 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 three containers will invariably lead to wasted capacity. We'll be using the offical Postgres image, and in the next post, the edoburu/pgbouncer image.

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:
  postgres:
    restart: always
    image: postgres:17
    environment:
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_MULTIPLE_DATABASES: solid_cache, solid_queue, solid_cable
    ports:
      - "${HOST_IP}:5432:5432"
    volumes:
      - ./postgresql.${HOST_KIND}.conf:/etc/postgresql/postgresql.conf:ro
      - ./multiple-databases.sh:/docker-entrypoint-initdb.d/multiple-databases.sh
      - ./pglogs:/var/log/postgresql
      - pgdata:/var/lib/postgresql/data
    command: ["postgres", "-c", "config_file=/etc/postgresql/postgresql.conf"]

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, solid_cable

These three database will be created, and owed by the postgres user. Depending on how valuable these are for your application, you may choose to mount them as on remote volume to persist the data directory, but for my app, Docker's named mounts will suffice.

You may also note the ports section includes a HOST_IP variable. Booko servers run within Tailscale and the databases bind to their Tailscale IP Addresses. These address are stored in the .env file under the shell variable HOST_IP, ensuring the database is only accessible to other Tailscale hosts, and is encrypted with Wireguard.

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