Database Architecture on VPS with Docker Compose

Overview

This guide describes a pragmatic production architecture for running databases on a VPS using Docker Compose. It focuses on reliability, backups, observability, and secure exposure. The examples use Postgres, Redis, pgAdmin, and optional Prometheus/Grafana; you can swap components to fit MySQL or other engines.

Why Docker Compose?

Compose keeps services, networks, volumes, and environment in one declarative file. It simplifies provisioning, upgrades, backups, and recovery on small-to-medium deployments.

Architecture

• Core data services:
  • Postgres (postgres) with persistent volume, healthcheck, and tuned memory.
  • Redis (redis) for caching/queues with append-only file (AOF) persistence.
• Management:
  • pgAdmin (pgadmin) restricted to admin IPs via reverse proxy.
• Backups:
  • A backup sidecar runs scheduled pg_dump and rotates archives to /backups.
• Observability (optional):
  • postgres_exporter + Prometheus + Grafana.
• Networking:
  • Private db_net for inter-service communication.
  • Public access only via reverse proxy (Traefik/Nginx) on web_net.

Prerequisites

• A VPS (Ubuntu/Debian) with Docker and Docker Compose installed.
• A domain and reverse proxy (Traefik or Nginx) for TLS.
• Create an .env file with secrets.

.env

# Postgres
POSTGRES_DB=kbook
POSTGRES_USER=kbook
POSTGRES_PASSWORD=change_me_strong

# pgAdmin
PGADMIN_DEFAULT_EMAIL=admin@kbook.cloud
PGADMIN_DEFAULT_PASSWORD=change_me_strong

# Backup
BACKUP_CRON=*/30 * * * *
BACKUP_RETENTION_DAYS=7

Compose File

docker-compose.yml

version: "3.9"

services:
  postgres:
    image: postgres:16-alpine
    container_name: postgres
    environment:
      POSTGRES_DB: ${POSTGRES_DB}
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    volumes:
      - pg_data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U $$POSTGRES_USER -d $$POSTGRES_DB"]
      interval: 10s
      timeout: 5s
      retries: 5
    networks:
      - db_net

  redis:
    image: redis:7-alpine
    container_name: redis
    command: ["redis-server", "--appendonly", "yes"]
    volumes:
      - redis_data:/data
    healthcheck:
      test: ["CMD", "redis-cli", "PING"]
      interval: 10s
      timeout: 5s
      retries: 5
    networks:
      - db_net

  pgadmin:
    image: dpage/pgadmin4:8
    container_name: pgadmin
    environment:
      PGADMIN_DEFAULT_EMAIL: ${PGADMIN_DEFAULT_EMAIL}
      PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_DEFAULT_PASSWORD}
    depends_on:
      - postgres
    networks:
      - db_net
      - web_net
    # Expose via reverse proxy; do NOT bind to 0.0.0.0 directly in production.
    labels:
      - "traefik.enable=true"
      - "traefik.http.routers.pgadmin.rule=Host(`pgadmin.kbook.cloud`)"
      - "traefik.http.routers.pgadmin.tls=true"
      - "traefik.http.services.pgadmin.loadbalancer.server.port=80"

  backup:
    image: alpine:3.20
    container_name: pg_backup
    depends_on:
      - postgres
    environment:
      POSTGRES_DB: ${POSTGRES_DB}
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      BACKUP_RETENTION_DAYS: ${BACKUP_RETENTION_DAYS}
    volumes:
      - pg_backups:/backups
    networks:
      - db_net
    entrypoint: ["/bin/sh", "-c"]
    command: >-
      "apk add --no-cache postgresql-client busybox-suid &&
       crontab -l | { cat; echo \"${BACKUP_CRON} pg_dump -h postgres -U ${POSTGRES_USER} -d ${POSTGRES_DB} -F c -Z 9 -f /backups/$(date +\%Y\%m\%d\%H\%M).dump\"; } | crontab - &&
       echo '0 3 * * * find /backups -type f -mtime +${BACKUP_RETENTION_DAYS} -delete' | crontab - &&
       crond -f -l 2"

  # Optional observability stack
  postgres_exporter:
    image: prometheuscommunity/postgres-exporter:v0.15.0
    environment:
      DATA_SOURCE_NAME: "postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@postgres:5432/${POSTGRES_DB}?sslmode=disable"
    depends_on:
      - postgres
    networks:
      - db_net

  prometheus:
    image: prom/prometheus:v2.54.1
    volumes:
      - prometheus_data:/prometheus
      - ./monitoring/prometheus.yml:/etc/prometheus/prometheus.yml:ro
    networks:
      - db_net
      - web_net
    labels:
      - "traefik.enable=true"
      - "traefik.http.routers.prom.rule=Host(`prom.kbook.cloud`)"
      - "traefik.http.routers.prom.tls=true"
      - "traefik.http.services.prom.loadbalancer.server.port=9090"

  grafana:
    image: grafana/grafana:10.4.2
    volumes:
      - grafana_data:/var/lib/grafana
    networks:
      - db_net
      - web_net
    labels:
      - "traefik.enable=true"
      - "traefik.http.routers.grafana.rule=Host(`grafana.kbook.cloud`)"
      - "traefik.http.routers.grafana.tls=true"
      - "traefik.http.services.grafana.loadbalancer.server.port=3000"

networks:
  db_net:
    internal: true
  web_net:
    external: true
    name: web_net # provisioned by Traefik/Nginx stack

volumes:
  pg_data:
  redis_data:
  pg_backups:
  prometheus_data:
  grafana_data:

Prometheus scrape config

Create monitoring/prometheus.yml:

monitoring/prometheus.yml

global:
  scrape_interval: 15s
scrape_configs:
  - job_name: 'postgres'
    static_configs:
      - targets: ['postgres_exporter:9187']

Operations

# Start services
docker compose up -d

# Check health
docker compose ps

# View logs
docker compose logs -f postgres

# Run a manual backup
docker compose exec backup sh -lc "pg_dump -h postgres -U $POSTGRES_USER -d $POSTGRES_DB -F c -Z 9 -f /backups/manual.dump"

# Restore example (stop app, then):
docker compose exec postgres sh -lc "pg_restore -U $POSTGRES_USER -d $POSTGRES_DB /var/lib/postgresql/data/restore.dump"

Security Recommendations

• Keep db_net internal; expose admin UIs via reverse proxy with auth.
• Enforce strong passwords and rotate regularly; prefer docker secret on Swarm.
• Restrict inbound IP ranges for admin endpoints at reverse proxy and firewall.
• Enable TLS everywhere; terminate at Traefik/Nginx and use mTLS internally if needed.
• Automate offsite backups (e.g., rclone to S3) and test restore quarterly.

Adapting for MySQL

Swap postgres for mysql:8, adjust env and healthcheck to mysqladmin ping. Use mysqldump in the backup sidecar.

Troubleshooting

• Stuck healthcheck: ensure VPS memory >= 1 GB, swap enabled.
• Permission issues on volumes: set correct ownership (999:999 for postgres on Alpine).
• Backup jobs not running: check crond logs in backup container.

---

With this architecture, you can run production-grade databases on a VPS with auditable backups, minimal blast radius, and clear upgrade paths.