Database Chaos Engineering — PostgreSQL, MySQL & Redis Resilience

# pg-pool-chaos.yaml
services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: secret
      POSTGRES_DB: testdb
    ports:
      - "5432:5432"
  app:
    image: python:3.11
    command: >
      sh -c "pip install psycopg2-binary &&
             python /scripts/pool-test.py"
    depends_on:
      - postgres
    environment:
      DB_HOST: postgres
      DB_PASSWORD: secret
    volumes:
      - ./scripts:/scripts

# Reduce max_connections to trigger exhaustion quickly
docker exec postgres psql -U postgres -c "ALTER SYSTEM SET max_connections = 10;"
docker exec postgres psql -U postgres -c "SELECT pg_reload_conf();"

# Open 15 connections to exhaust the pool
python3 pool-exhaust.py

#!/usr/bin/env python3
"""Connection pool exhaustion test."""
import psycopg2
import threading
import time
import sys

connections = []
exhausted = False

def open_connection(worker_id):
    global exhausted
    try:
        conn = psycopg2.connect(
            host="localhost",
            dbname="testdb",
            user="postgres",
            password="secret",
            connect_timeout=5
        )
        connections.append(conn)
        print(f"Worker {worker_id}: Connection opened successfully")
    except Exception as e:
        exhausted = True
        print(f"Worker {worker_id}: Connection FAILED - {e}")

# Open connections in parallel
threads = []
for i in range(15):
    t = threading.Thread(target=open_connection, args=(i,))
    threads.append(t)
    t.start()
    time.sleep(0.1)

for t in threads:
    t.join()

print(f"\nTotal connections opened: {len(connections)}")
print(f"Pool exhausted: {exhausted}")

for conn in connections:
    conn.close()

# Expected output:
# Worker 0: Connection opened successfully
# Worker 1: Connection opened successfully
# ...
# Worker 9: Connection opened successfully
# Worker 10: Connection FAILED - FATAL: sorry, too many clients already
# Worker 11: Connection FAILED - FATAL: sorry, too many clients already
# Total connections opened: 10
# Pool exhausted: True

# Set up streaming replication
docker exec postgres-primary psql -U postgres \
  -c "CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'repl_secret';"

# Add 500ms of latency between primary and replica
docker exec postgres-replica tc qdisc add dev eth0 root netem delay 500ms

# Check replication lag
docker exec postgres-primary psql -U postgres -c "
SELECT application_name,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;
"
# Expected output:
# application_name | lag_bytes
# walreceiver      | 31457280

# Stop the primary to trigger failover
docker stop postgres-primary

# Promote the replica to primary (simulating automated failover)
docker exec postgres-replica psql -U postgres -c "SELECT pg_promote();"
# Expected output:
# pg_promote
# t
# (The replica is now the new primary)

# Verify write capability on the promoted replica
docker exec postgres-replica psql -U postgres -c "CREATE TABLE failover_test (id int); INSERT INTO failover_test VALUES (1); SELECT * FROM failover_test;"
# Expected output:
# id
# 1
# The promoted replica accepts writes successfully

# Start a Redis cluster
docker run -d --name redis-cache -p 6379:6379 redis:7.2

# Set a cache entry
docker exec redis-cache redis-cli SET session:user1 "active" EX 3600
# Expected output:
# OK

# Simulate cache node failure
docker stop redis-cache

# Verify that the application falls back to the database
python3 cache-fallback-test.py

#!/usr/bin/env python3
"""Redis cache fallback test with chaos."""
import redis
import time

def get_user_session(user_id):
    cache = redis.Redis(host="localhost", port=6379, decode_responses=True)

    try:
        # Try cache first
        session = cache.get(f"session:{user_id}")
        if session:
            print(f"Cache HIT for user {user_id}: {session}")
            return session
    except redis.ConnectionError:
        print(f"Cache MISS - Redis unavailable, falling back to database")

    # Fallback to database
    session = query_database(user_id)
    print(f"Database fallback for user {user_id}: {session}")
    return session

def query_database(user_id):
    # Simulate database query
    time.sleep(0.1)
    return "active"

# Test 1: Cache is up
get_user_session("user1")
# Expected output:
# Cache HIT for user user1: active

# Test 2: Cache is down
get_user_session("user2")
# Expected output:
# Cache MISS - Redis unavailable, falling back to database
# Database fallback for user user2: active

# Start a primary and replica pair
docker compose -f db-pair.yaml up -d

# Write to primary
docker exec db-primary psql -U postgres -c "INSERT INTO orders VALUES (1, 'completed');"

# Immediately read from replica (may have lag)
docker exec db-replica psql -U postgres -c "SELECT * FROM orders WHERE id = 1;"
# Expected output (if no lag):
# id | status
# 1  | completed

# Add 10 seconds of replication lag
docker exec db-replica tc qdisc add dev eth0 root netem delay 10000ms

# Write to primary
docker exec db-primary psql -U postgres -c "INSERT INTO orders VALUES (2, 'pending');"

# Read from replica immediately
docker exec db-replica psql -U postgres -c "SELECT * FROM orders;"
# Expected output:
# id | status
# 1  | completed
# Order 2 is not visible on the replica yet due to replication lag