Multi-Database Orchestration

Summary

When multiple projects each need their own database, orchestrate them as independent containers with dedicated ports, volumes, networks, and health checks. This pattern covers the full lifecycle — from local development through production-grade hosting on a single server — and defines when to graduate to managed services.

Problem

Running multiple databases on one server creates compounding operational challenges beyond simple port conflicts.

• How do you isolate data, configuration, and PostgreSQL versions across projects?
• How do you spin one project’s database up or down without affecting others?
• How do you prevent one runaway query from consuming all server resources?
• How do you back up, monitor, and recover individual databases independently?
• How do you transition from “it works on my server” to reliable production hosting?

Solution

Use Docker Compose to define each database as an independent service with its own port, volume, health check, and resource constraints.

# docker-compose.yml
services:
  project_a_db:
    image: postgres:16
    container_name: project_a_db
    ports:
      - "5432:5432"
    volumes:
      - project_a_data:/var/lib/postgresql/data
    environment:
      POSTGRES_DB: project_a
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: ${PROJECT_A_DB_PASSWORD}
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U admin -d project_a"]
      interval: 10s
      timeout: 5s
      retries: 5
    deploy:
      resources:
        limits:
          memory: 512M
          cpus: "0.5"
    restart: unless-stopped

  project_b_db:
    image: postgres:15
    container_name: project_b_db
    ports:
      - "5433:5432"
    volumes:
      - project_b_data:/var/lib/postgresql/data
    environment:
      POSTGRES_DB: project_b
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: ${PROJECT_B_DB_PASSWORD}
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U admin -d project_b"]
      interval: 10s
      timeout: 5s
      retries: 5
    deploy:
      resources:
        limits:
          memory: 512M
          cpus: "0.5"
    restart: unless-stopped

  project_c_db:
    image: postgres:16
    container_name: project_c_db
    ports:
      - "5434:5432"
    volumes:
      - project_c_data:/var/lib/postgresql/data
    environment:
      POSTGRES_DB: project_c
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: ${PROJECT_C_DB_PASSWORD}
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U admin -d project_c"]
      interval: 10s
      timeout: 5s
      retries: 5
    deploy:
      resources:
        limits:
          memory: 256M
          cpus: "0.25"
    restart: unless-stopped

volumes:
  project_a_data:
  project_b_data:
  project_c_data:

Key mechanics

Independent lifecycle — Start or stop any single database without touching the others:

docker compose up -d project_a_db       # start one
docker compose stop project_b_db        # stop one without removing data
docker compose down -v --remove-orphans  # tear down everything (careful: -v deletes volumes)

Health checks — pg_isready confirms the database is accepting connections, not just that the container is running. Dependent services can use depends_on with condition: service_healthy.

Resource limits — Prevent any single database from monopolizing server memory or CPU. Size limits based on the project’s needs — a lightweight staging database might need 256M while a heavier workload gets 512M.

Connection strings — Each project’s application connects via its assigned host port:

# .env per project
DATABASE_URL=postgresql://admin:password@host:5432/project_a
DATABASE_URL=postgresql://admin:password@host:5433/project_b
DATABASE_URL=postgresql://admin:password@host:5434/project_c

Application-layer orchestration

While Docker Compose defines the infrastructure, applications need to connect, verify readiness, and manage lifecycle. Python’s Docker SDK allows programmatic container management and health checks:

import docker
import psycopg2
import time
from typing import Optional

class DatabaseCluster:
    """Manages multiple database containers and their connections."""
    
    def __init__(self, compose_file: str = "docker-compose.yml"):
        self.client = docker.from_env()
        self.compose_file = compose_file
        self.databases = {
            "project_a": ("project_a_db", "5432", "project_a"),
            "project_b": ("project_b_db", "5433", "project_b"),
            "project_c": ("project_c_db", "5434", "project_c"),
        }
    
    def wait_for_readiness(self, project: str, timeout: int = 30) -> bool:
        """Poll until a database is accepting connections."""
        container_name, port, db_name = self.databases[project]
        container = self.client.containers.get(container_name)
        
        start = time.time()
        while time.time() - start < timeout:
            health = container.attrs["State"]["Health"]["Status"]
            if health == "healthy":
                return True
            time.sleep(1)
        return False
    
    def connect(self, project: str) -> Optional[psycopg2.connection]:
        """Establish a connection to an orchestrated database."""
        container_name, port, db_name = self.databases[project]
        
        if not self.wait_for_readiness(project):
            raise RuntimeError(f"{project} failed health check")
        
        return psycopg2.connect(
            host="localhost",
            port=int(port),
            user="admin",
            password="password",  # load from env
            database=db_name,
        )
    
    def resource_usage(self, project: str) -> dict:
        """Inspect CPU and memory usage for a database container."""
        container_name, _, _ = self.databases[project]
        container = self.client.containers.get(container_name)
        stats = container.stats(stream=False)
        
        memory_usage = stats["memory_stats"]["usage"] / (1024 ** 2)  # MB
        cpu_percent = (
            stats["cpu_stats"]["cpu_usage"]["total_usage"] /
            stats["cpu_stats"]["system_cpu_usage"] * 100
        )
        
        return {"memory_mb": memory_usage, "cpu_percent": cpu_percent}

# Usage: connect to each database, verify health, and monitor resource isolation
cluster = DatabaseCluster()
for project in ["project_a", "project_b", "project_c"]:
    conn = cluster.connect(project)
    print(f"{project}: connected, using {cluster.resource_usage(project)}")
    conn.close()

The Docker SDK replaces manual shell scripting: health checks become callable methods, resource limits are verified programmatically, and lifecycle operations (start/stop/inspect) integrate naturally into application initialization and monitoring loops.

Production readiness checklist

Backups — Schedule per-database backups using pg_dump executed inside each container:

docker exec project_a_db pg_dump -U admin project_a > backup_a_$(date +%F).sql

Connection pooling — For production workloads, add PgBouncer as a sidecar container per database or as a shared pooler to manage connection limits and reduce PostgreSQL backend pressure.

Monitoring — Use Docker’s built-in health status (docker inspect --format='{{.State.Health.Status}}') and export metrics to your monitoring stack. Container-level CPU and memory usage is visible via docker stats.

When to graduate — Move to managed databases (RDS, Cloud SQL) or Kubernetes StatefulSets when you need:

• Automated failover and high availability
• Point-in-time recovery beyond manual pg_dump
• More databases than your single server can resource
• Multi-region replication

Live Playground

Experiment with the pattern below. The before version puts every project on one shared database instance: a runaway query in one project exhausts the shared memory pool, and restarting for one project’s maintenance takes every project offline. The after version gives each project its own instance with a memory limit and a health check. The runaway query only affects its own instance, and each database can be stopped on its own.

When to Use

• Multiple independent projects sharing a single development, staging, or small-scale production server
• Projects requiring different PostgreSQL versions or configurations
• Need to start and stop individual databases without affecting others
• Environments where managed database services are not justified by scale or budget

Avoid when:

• A single PostgreSQL instance with multiple logical databases provides sufficient isolation
• Running at scale where managed database services (RDS, Cloud SQL) are more appropriate
• Full orchestration (Kubernetes) is already in place — use StatefulSets and operators instead

Trade-offs

Related Patterns

• Docker Port Mapping — the foundational technique this pattern builds on for host port allocation
• Dependency Injection — connection strings are injected per environment, decoupling application code from infrastructure specifics
• Hexagonal Architecture — the database is an adapter behind a port, making it straightforward to swap between local containers and managed services