Dockerizing Databases

version: "3.8"

services:
  db:
    image: postgres:15-alpine
    # Use a specific version tag — never postgres:latest in production.
    # Alpine variant: smaller footprint, same functionality.
    environment:
      - POSTGRES_DB=payment_db
      - POSTGRES_USER=payment_user
      - POSTGRES_PASSWORD=${DB_PASSWORD}
      # Password from .env — never hardcoded. Lesson 33.
    volumes:
      - pgdata:/var/lib/postgresql/data
      # Named volume — data persists across container restarts and replacements.
      # This is the single most important line for any database container.
      - ./init:/docker-entrypoint-initdb.d/
      # Mount initialisation scripts — executed once on first startup only.
      # Useful for schema creation, seed data, extension installation.
    ports:
      - "5432:5432"
      # Expose only for development. Remove this line in production —
      # the database should only be reachable by containers on the same network.
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB}"]
      # pg_isready checks whether Postgres is ready to accept connections.
      # This is database-specific — it fails if the process is up but not yet
      # accepting queries (common during startup and crash recovery).
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 30s
      # start_period: give Postgres 30s to initialise before health checks begin.
      # Without this, the first few checks fail and the container is marked
      # unhealthy before it's had a chance to start.
    restart: unless-stopped
    logging:
      driver: json-file
      options:
        max-size: "20m"
        max-file: "3"
    deploy:
      resources:
        limits:
          memory: 1G
        reservations:
          memory: 512M

volumes:
  pgdata:
    # Named volume managed by Docker. Persists until explicitly deleted with
    # docker volume rm pgdata — not affected by docker-compose down.

# init/01-schema.sql — executed once on first Postgres startup
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- Install UUID generation extension before creating tables.

CREATE TABLE IF NOT EXISTS payments (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id     UUID NOT NULL,
  amount      NUMERIC(12, 2) NOT NULL,
  currency    CHAR(3) NOT NULL DEFAULT 'USD',
  status      VARCHAR(20) NOT NULL DEFAULT 'pending',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_payments_user_id ON payments (user_id);
CREATE INDEX IF NOT EXISTS idx_payments_status  ON payments (status);

-- init/02-seed.sql — executed second (files run in alphabetical order)
INSERT INTO payments (user_id, amount, currency, status)
VALUES
  ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 99.99,  'USD', 'completed'),
  ('b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a22', 149.50, 'GBP', 'pending')
ON CONFLICT DO NOTHING;
-- ON CONFLICT DO NOTHING prevents errors if seed runs on an already-populated DB.

version: "3.8"

services:
  db:
    image: mysql:8.0
    environment:
      - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
      # Root password — required. Used only for administrative operations.
      - MYSQL_DATABASE=payment_db
      - MYSQL_USER=payment_user
      - MYSQL_PASSWORD=${DB_PASSWORD}
      # Application user — this is what the app connects as, not root.
      # The official image creates this user automatically on first startup.
    volumes:
      - mysqldata:/var/lib/mysql
      # Named volume for MySQL data directory.
      - ./init:/docker-entrypoint-initdb.d/
    healthcheck:
      test: ["CMD", "mysqladmin", "ping",
             "-h", "localhost",
             "-u", "payment_user",
             "--password=${DB_PASSWORD}"]
      # mysqladmin ping — tests whether MySQL is accepting connections.
      # Tests with the application user, not root.
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 40s
      # MySQL takes longer to initialise than Postgres on first startup —
      # give it 40s before health checks begin.
    restart: unless-stopped
    deploy:
      resources:
        limits:
          memory: 1G

volumes:
  mysqldata:

version: "3.8"

services:
  redis:
    image: redis:7-alpine
    command: >
      redis-server
      --requirepass ${REDIS_PASSWORD}
      --appendonly yes
      --appendfsync everysec
      --maxmemory 256mb
      --maxmemory-policy allkeys-lru
      --save 900 1
      --save 300 10
      --save 60 10000
    # --requirepass           → password authentication — from Lesson 33
    # --appendonly yes        → AOF persistence: every write logged to disk
    # --appendfsync everysec  → flush to disk once per second — balanced mode
    #                           "always" = safest, "no" = fastest, "everysec" = default
    # --maxmemory 256mb       → hard cap on memory usage
    # --maxmemory-policy allkeys-lru → when full, evict least-recently-used keys
    #                                  correct for a cache — wrong for a job queue
    # --save 900 1            → RDB snapshot if ≥1 key changed in 900 seconds
    # --save 300 10           → RDB snapshot if ≥10 keys changed in 300 seconds
    # --save 60 10000         → RDB snapshot if ≥10000 keys changed in 60 seconds
    volumes:
      - redisdata:/data
      # /data is where Redis writes both AOF and RDB files.
    healthcheck:
      test: ["CMD", "redis-cli",
             "-a", "${REDIS_PASSWORD}",
             "ping"]
      # redis-cli ping returns PONG when Redis is ready.
      # Must include -a for password-protected instances.
      interval: 10s
      timeout: 3s
      retries: 3
      start_period: 10s
    restart: unless-stopped
    deploy:
      resources:
        limits:
          memory: 300M
          # Slightly above maxmemory to allow Redis overhead without OOM kill.

volumes:
  redisdata:

version: "3.8"

services:
  api:
    image: acmecorp/payment-api:${GIT_SHA}
    depends_on:
      db:
        condition: service_healthy
        # Do not start the API until db reports healthy via pg_isready.
        # Without this, the API starts in parallel with Postgres and
        # almost certainly attempts a connection before Postgres is ready.
      redis:
        condition: service_healthy
        # Same for Redis — wait for redis-cli ping to succeed.
    environment:
      - DB_HOST=db
      - REDIS_HOST=redis
    ports:
      - "3000:3000"

  db:
    image: postgres:15-alpine
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U payment_user -d payment_db"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 30s
    volumes:
      - pgdata:/var/lib/postgresql/data
    environment:
      - POSTGRES_DB=payment_db
      - POSTGRES_USER=payment_user
      - POSTGRES_PASSWORD=${DB_PASSWORD}

  redis:
    image: redis:7-alpine
    command: redis-server --requirepass ${REDIS_PASSWORD}
    healthcheck:
      test: ["CMD", "redis-cli", "-a", "${REDIS_PASSWORD}", "ping"]
      interval: 10s
      timeout: 3s
      retries: 3
      start_period: 10s
    volumes:
      - redisdata:/data

volumes:
  pgdata:
  redisdata:

# Postgres — pg_dump to a compressed backup file
docker exec postgres-db pg_dump \
  -U payment_user \
  -d payment_db \
  -F c \
  -f /tmp/backup.dump
# -F c → custom format (compressed, supports selective restore)
# -f   → output file path inside the container

# Copy the backup off the container and off the host:
docker cp postgres-db:/tmp/backup.dump ./backups/payment_db_$(date +%Y%m%d).dump
aws s3 cp ./backups/payment_db_$(date +%Y%m%d).dump \
  s3://acmecorp-backups/postgres/

# Restore from backup — to a new or existing container:
docker exec -i postgres-db pg_restore \
  -U payment_user \
  -d payment_db \
  --clean \
  --if-exists \
  < ./backups/payment_db_20240115.dump
# --clean     → drop existing objects before restoring
# --if-exists → skip errors if objects don't exist yet (safe for fresh DB)

# Redis — BGSAVE triggers a background RDB snapshot immediately:
docker exec redis redis-cli -a ${REDIS_PASSWORD} BGSAVE
Background saving started

# Copy the RDB file off the container:
docker cp redis:/data/dump.rdb ./backups/redis_$(date +%Y%m%d).rdb
aws s3 cp ./backups/redis_$(date +%Y%m%d).rdb \
  s3://acmecorp-backups/redis/