StatefulSet Design for Database Pods: Architecture & Implementation

Deployment (WRONG for databases):           StatefulSet (RIGHT for databases):

Pod names: random                           Pod names: stable
  myapp-7d9f8b-xk2pq                         postgres-0
  myapp-7d9f8b-ab3cd    (change on restart)   postgres-1  (stable across restarts)
  myapp-7d9f8b-ef5gh                          postgres-2

Storage: shared or ephemeral               Storage: dedicated per pod
  All pods share same PVC                    postgres-0 → data-postgres-0 PVC
  OR no persistent storage                   postgres-1 → data-postgres-1 PVC
                                             postgres-2 → data-postgres-2 PVC

DNS: load-balanced Service                 DNS: stable per-pod hostname
  postgres.ns.svc.cluster.local              postgres-0.postgres.ns.svc.cluster.local
  (any pod)                                  postgres-1.postgres.ns.svc.cluster.local
                                             (always resolves to same pod)

Startup/shutdown: parallel                 Startup/shutdown: ordered
  All pods start simultaneously              postgres-0 starts first
                                             postgres-1 waits for postgres-0 Ready
                                             postgres-2 waits for postgres-1 Ready

# storageclass-fast-ssd.yaml
apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
  name: fast-ssd
  annotations:
    storageclass.kubernetes.io/is-default-class: "false"
provisioner: ebs.csi.aws.com          # AWS EBS CSI driver
parameters:
  type: io2                            # Provisioned IOPS SSD (not gp3 for databases)
  iops: "10000"                        # 10,000 IOPS per volume
  throughput: "500"                    # 500 MB/s throughput
  encrypted: "true"                    # Encrypt at rest
  kmsKeyId: "arn:aws:kms:us-east-1:123456789:key/mrk-xxx"
reclaimPolicy: Retain                  # CRITICAL: Never auto-delete database volumes
allowVolumeExpansion: true             # Allow online expansion
volumeBindingMode: WaitForFirstConsumer # Create PV in same AZ as the Pod
mountOptions:
  - discard                            # Enable TRIM for SSDs

# headless-service.yaml
apiVersion: v1
kind: Service
metadata:
  name: postgres
  namespace: database-production
  labels:
    app: postgres
spec:
  clusterIP: None      # This makes it headless — DNS returns pod IPs directly
  selector:
    app: postgres
  ports:
    - port: 5432
      targetPort: 5432
      name: postgresql
---
# Regular Service for primary (write) connections
apiVersion: v1
kind: Service
metadata:
  name: postgres-primary
  namespace: database-production
spec:
  selector:
    app: postgres
    role: primary           # Only routes to the primary replica
  ports:
    - port: 5432
      targetPort: 5432
---
# Regular Service for replica (read) connections
apiVersion: v1
kind: Service
metadata:
  name: postgres-replica
  namespace: database-production
spec:
  selector:
    app: postgres
    role: replica           # Routes to read replicas
  ports:
    - port: 5432
      targetPort: 5432

# postgres-statefulset.yaml
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: postgres
  namespace: database-production
spec:
  serviceName: postgres       # Must match headless service name
  replicas: 3
  selector:
    matchLabels:
      app: postgres

  # CRITICAL: Control update behavior
  updateStrategy:
    type: RollingUpdate
    rollingUpdate:
      partition: 0            # Update all pods (set to 2 to update only postgres-2 first)
                              # Useful for canary: partition: 2 updates only postgres-2

  # Prevent all replicas from being deleted simultaneously
  podManagementPolicy: OrderedReady   # Default: wait for each pod to be ready
                                      # Alternative: Parallel (faster but not safe for DBs)

  template:
    metadata:
      labels:
        app: postgres
    spec:
      # Spread replicas across different nodes and AZs
      affinity:
        podAntiAffinity:
          # Hard rule: NEVER put two postgres pods on same node
          requiredDuringSchedulingIgnoredDuringExecution:
            - labelSelector:
                matchLabels:
                  app: postgres
              topologyKey: kubernetes.io/hostname
          # Soft rule: prefer different AZs
          preferredDuringSchedulingIgnoredDuringExecution:
            - weight: 100
              podAffinityTerm:
                labelSelector:
                  matchLabels:
                    app: postgres
                topologyKey: topology.kubernetes.io/zone

      # Ensure postgres process doesn't run as root
      securityContext:
        runAsUser: 999
        runAsGroup: 999
        fsGroup: 999
        fsGroupChangePolicy: OnRootMismatch   # Faster volume permission setup

      # Init container: configure replication for non-primary pods
      initContainers:
        - name: init-replication
          image: postgres:15-alpine
          command:
            - /bin/sh
            - -c
            - |
              set -e
              # Determine pod ordinal from hostname
              ORDINAL=${HOSTNAME##*-}

              if [ "$ORDINAL" = "0" ]; then
                echo "Pod 0 is primary — no replication init needed"
                exit 0
              fi

              # For replicas (pod 1, 2): clone from primary
              PRIMARY="postgres-0.postgres.database-production.svc.cluster.local"
              PGDATA="/var/lib/postgresql/data/pgdata"

              # Wait for primary to be ready
              until pg_isready -h $PRIMARY -U postgres; do
                echo "Waiting for primary..."
                sleep 2
              done

              # If data directory is empty, clone from primary
              if [ ! -f "$PGDATA/PG_VERSION" ]; then
                echo "Cloning from primary $PRIMARY..."
                pg_basebackup \
                  -h $PRIMARY \
                  -U replicator \
                  -D $PGDATA \
                  -P \
                  -Xs \
                  -R    # -R creates standby.signal and postgresql.auto.conf
                echo "Clone complete"
              else
                echo "Data directory exists, skipping clone"
              fi
          env:
            - name: PGPASSWORD
              valueFrom:
                secretKeyRef:
                  name: postgres-credentials
                  key: replication-password
          volumeMounts:
            - name: data
              mountPath: /var/lib/postgresql/data

      containers:
        - name: postgresql
          image: postgres:15-alpine
          ports:
            - containerPort: 5432
              name: postgresql

          env:
            - name: POSTGRES_USER
              value: postgres
            - name: POSTGRES_DB
              value: production
            - name: POSTGRES_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: postgres-credentials
                  key: postgres-password
            - name: PGDATA
              value: /var/lib/postgresql/data/pgdata
            - name: POD_NAME
              valueFrom:
                fieldRef:
                  fieldPath: metadata.name
            # PostgreSQL replication configuration
            - name: POSTGRES_INITDB_ARGS
              value: "--data-checksums"      # Enable data checksums for corruption detection

          # Custom postgresql.conf via ConfigMap
          args:
            - -c
            - config_file=/etc/postgresql/postgresql.conf

          resources:
            requests:
              cpu: "2"
              memory: "4Gi"
            limits:
              cpu: "4"
              memory: "8Gi"

          readinessProbe:
            exec:
              command:
                - /bin/sh
                - -c
                - pg_isready -U postgres -d production
            initialDelaySeconds: 10
            periodSeconds: 5
            failureThreshold: 6

          livenessProbe:
            exec:
              command:
                - /bin/sh
                - -c
                - pg_isready -U postgres
            initialDelaySeconds: 30
            periodSeconds: 10
            failureThreshold: 3

          lifecycle:
            preStop:
              exec:
                command:
                  - /bin/sh
                  - -c
                  - |
                    # Gracefully stop PostgreSQL before SIGTERM
                    # This ensures WAL is flushed and connections are closed cleanly
                    pg_ctl stop -D $PGDATA -m fast
                    sleep 5

          volumeMounts:
            - name: data
              mountPath: /var/lib/postgresql/data
            - name: postgres-config
              mountPath: /etc/postgresql
              readOnly: true
            - name: postgres-init-scripts
              mountPath: /docker-entrypoint-initdb.d
              readOnly: true

        # Sidecar: Metrics exporter for Prometheus
        - name: postgres-exporter
          image: prometheuscommunity/postgres-exporter:latest
          ports:
            - containerPort: 9187
              name: metrics
          env:
            - name: DATA_SOURCE_NAME
              valueFrom:
                secretKeyRef:
                  name: postgres-credentials
                  key: exporter-dsn
          resources:
            requests:
              cpu: "50m"
              memory: "64Mi"
            limits:
              cpu: "200m"
              memory: "128Mi"

      terminationGracePeriodSeconds: 60

      volumes:
        - name: postgres-config
          configMap:
            name: postgres-config
        - name: postgres-init-scripts
          configMap:
            name: postgres-init-scripts

  # volumeClaimTemplates: Each pod gets its own dedicated PVC
  # These are NEVER deleted when the StatefulSet is deleted (Retain policy)
  volumeClaimTemplates:
    - metadata:
        name: data
        labels:
          app: postgres
        annotations:
          mycompany.com/backup: "true"
      spec:
        accessModes: ["ReadWriteOnce"]   # Single node read-write
        storageClassName: fast-ssd
        resources:
          requests:
            storage: 500Gi

# postgres-config.yaml
apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-config
  namespace: database-production
data:
  postgresql.conf: |
    # Connection settings
    max_connections = 200
    superuser_reserved_connections = 3

    # Memory settings (tune to 25% of pod memory for shared_buffers)
    shared_buffers = 1GB               # 25% of 4Gi memory request
    effective_cache_size = 3GB         # 75% of total RAM
    work_mem = 20MB                    # Per-sort/hash operation
    maintenance_work_mem = 256MB       # For VACUUM, CREATE INDEX

    # WAL settings (critical for replication and crash recovery)
    wal_level = replica                # Enable streaming replication
    max_wal_senders = 5                # Max simultaneous replication connections
    wal_keep_size = 1GB               # Keep 1GB of WAL for replicas
    synchronous_commit = on            # Ensure durability (at cost of latency)
    checkpoint_completion_target = 0.9 # Spread checkpoint I/O over 90% of interval
    max_wal_size = 2GB
    min_wal_size = 256MB

    # Replication
    hot_standby = on                   # Allow reads on standby
    hot_standby_feedback = on          # Prevent query conflicts on standby

    # Logging (for audit and debugging)
    log_destination = 'stderr'
    logging_collector = off            # Let container capture stderr
    log_min_duration_statement = 1000  # Log queries taking >1 second
    log_connections = on
    log_disconnections = on
    log_lock_waits = on
    log_temp_files = 0                 # Log all temp file creation
    log_autovacuum_min_duration = 0    # Log all autovacuum runs

    # Performance
    random_page_cost = 1.1            # SSD: sequential and random costs are similar
    effective_io_concurrency = 200     # SSD: high concurrency is fine
    default_statistics_target = 100

    # Autovacuum tuning (critical for long-running production databases)
    autovacuum_vacuum_scale_factor = 0.05  # Vacuum when 5% of table changes
    autovacuum_analyze_scale_factor = 0.02  # Analyze when 2% changes
    autovacuum_vacuum_cost_delay = 2ms      # Reduce I/O throttling on SSDs

  pg_hba.conf: |
    # TYPE  DATABASE        USER            ADDRESS                 METHOD
    local   all             all                                     trust
    host    all             all             127.0.0.1/32            md5
    host    all             all             ::1/128                 md5
    # Allow replication connections from pod subnet
    host    replication     replicator      10.0.0.0/8              md5
    # Allow application connections from production namespace
    host    all             appuser         10.0.0.0/8              scram-sha-256

# postgres-pdb.yaml
apiVersion: policy/v1
kind: PodDisruptionBudget
metadata:
  name: postgres-pdb
  namespace: database-production
spec:
  # Always keep at least 2 out of 3 pods running
  # This means only 1 pod can be disrupted at a time
  minAvailable: 2
  selector:
    matchLabels:
      app: postgres

  # This PDB means:
  # - Node drain: Will only proceed if 2+ postgres pods remain Ready
  # - Cluster upgrade: Worker nodes are drained one at a time (PDB enforced)
  # - kubectl delete pod: Blocked if it would violate PDB

# postgres-backup-cronjob.yaml
apiVersion: batch/v1
kind: CronJob
metadata:
  name: postgres-backup
  namespace: database-production
spec:
  schedule: "0 2 * * *"          # Daily at 2 AM UTC
  concurrencyPolicy: Forbid       # Don't run if previous backup still running
  failedJobsHistoryLimit: 3
  successfulJobsHistoryLimit: 7
  jobTemplate:
    spec:
      template:
        spec:
          serviceAccountName: postgres-backup-sa
          restartPolicy: OnFailure
          containers:
            - name: backup
              image: postgres:15-alpine
              command:
                - /bin/sh
                - -c
                - |
                  set -euo pipefail

                  DATE=$(date +%Y%m%d-%H%M%S)
                  BACKUP_FILE="/backup/postgres-backup-$DATE.sql.gz"
                  PRIMARY="postgres-0.postgres.database-production.svc.cluster.local"

                  echo "Starting backup at $DATE"

                  # Full logical backup with pg_dump
                  pg_dump \
                    -h $PRIMARY \
                    -U postgres \
                    -d production \
                    --format=custom \
                    --compress=9 \
                    --no-password \
                    | gzip > $BACKUP_FILE

                  # Verify backup integrity
                  pg_restore --list $BACKUP_FILE > /dev/null
                  echo "Backup integrity verified"

                  # Upload to S3
                  aws s3 cp $BACKUP_FILE \
                    s3://mycompany-db-backups/postgres/$(date +%Y/%m/%d)/postgres-backup-$DATE.sql.gz \
                    --sse aws:kms \
                    --sse-kms-key-id $KMS_KEY_ID

                  echo "Backup uploaded to S3"

                  # Cleanup local file
                  rm -f $BACKUP_FILE

                  # Remove S3 backups older than 30 days
                  aws s3 ls s3://mycompany-db-backups/postgres/ --recursive | \
                    awk '{print $4}' | \
                    while read file; do
                      file_date=$(echo "$file" | grep -oP '\d{8}' | head -1)
                      cutoff=$(date -d "-30 days" +%Y%m%d)
                      if [[ "$file_date" < "$cutoff" ]]; then
                        aws s3 rm "s3://mycompany-db-backups/$file"
                        echo "Removed old backup: $file"
                      fi
                    done

                  echo "Backup complete"

              env:
                - name: PGPASSWORD
                  valueFrom:
                    secretKeyRef:
                      name: postgres-credentials
                      key: postgres-password
                - name: AWS_REGION
                  value: us-east-1
                - name: KMS_KEY_ID
                  valueFrom:
                    secretKeyRef:
                      name: postgres-credentials
                      key: backup-kms-key-id

              resources:
                requests:
                  cpu: "500m"
                  memory: "512Mi"
                limits:
                  cpu: "1"
                  memory: "1Gi"

#!/bin/bash
# postgres-runbook.sh — Common operational procedures

# =============================================================
# PROCEDURE 1: Rolling restart (e.g., after config change)
# =============================================================
rolling_restart() {
  echo "Starting rolling restart of postgres StatefulSet..."

  # StatefulSet rolling restart (goes in reverse order: 2, 1, 0)
  kubectl rollout restart statefulset/postgres -n database-production

  # Watch the rollout
  kubectl rollout status statefulset/postgres -n database-production --timeout=10m

  echo "Rolling restart complete"
}

# =============================================================
# PROCEDURE 2: Manual failover (promote replica to primary)
# =============================================================
manual_failover() {
  TARGET_REPLICA=${1:-"postgres-1"}
  echo "Promoting $TARGET_REPLICA to primary..."

  # Connect to the target replica and promote it
  kubectl exec -n database-production $TARGET_REPLICA -- \
    pg_ctl promote -D /var/lib/postgresql/data/pgdata

  # Update the primary Service selector to point to new primary
  # (In a real setup, use Patroni or pg_auto_failover for automatic failover)
  ORDINAL="${TARGET_REPLICA##*-}"
  kubectl patch service postgres-primary -n database-production \
    -p "{\"spec\":{\"selector\":{\"app\":\"postgres\",\"statefulset.kubernetes.io/pod-name\":\"$TARGET_REPLICA\"}}}"

  echo "Failover complete. New primary: $TARGET_REPLICA"
}

# =============================================================
# PROCEDURE 3: Expand PVC storage (online, no downtime)
# =============================================================
expand_storage() {
  NEW_SIZE=${1:-"1Ti"}
  echo "Expanding all postgres PVCs to $NEW_SIZE..."

  # Patch each PVC (StatefulSet doesn't manage PVC updates directly)
  for i in 0 1 2; do
    kubectl patch pvc data-postgres-$i \
      -n database-production \
      -p "{\"spec\":{\"resources\":{\"requests\":{\"storage\":\"$NEW_SIZE\"}}}}"
    echo "Patched data-postgres-$i"
  done

  # Trigger pod restart so PostgreSQL picks up new filesystem size
  # (Usually not needed — filesystem auto-expands on EBS)
  echo "PVC expansion triggered. Check PVC status:"
  kubectl get pvc -n database-production -l app=postgres
}

# =============================================================
# PROCEDURE 4: Restore from backup to a new database
# =============================================================
restore_from_backup() {
  BACKUP_FILE=$1
  TARGET_DB=${2:-"production_restore"}

  echo "Restoring from $BACKUP_FILE to database $TARGET_DB..."

  # Copy backup from S3 to a restore pod
  kubectl run restore-pod \
    --image=postgres:15-alpine \
    --restart=Never \
    --rm -it \
    --env="PGPASSWORD=$PGPASSWORD" \
    --command -- \
    /bin/sh -c "
      # Download from S3
      aws s3 cp s3://mycompany-db-backups/postgres/$BACKUP_FILE /tmp/backup.sql.gz

      # Create target database
      createdb -h postgres-0.postgres.database-production.svc.cluster.local \
        -U postgres $TARGET_DB

      # Restore
      gunzip -c /tmp/backup.sql.gz | \
        pg_restore \
          -h postgres-0.postgres.database-production.svc.cluster.local \
          -U postgres \
          -d $TARGET_DB \
          --no-owner \
          --role=postgres

      echo 'Restore complete'
    "
}

# =============================================================
# PROCEDURE 5: Check replication lag
# =============================================================
check_replication_lag() {
  echo "Checking replication status on primary..."

  kubectl exec -n database-production postgres-0 -- \
    psql -U postgres -c "
      SELECT
        client_addr,
        application_name,
        state,
        sent_lsn,
        write_lsn,
        flush_lsn,
        replay_lsn,
        (sent_lsn - replay_lsn) AS replication_lag_bytes,
        write_lag,
        flush_lag,
        replay_lag
      FROM pg_stat_replication
      ORDER BY replay_lag DESC;
    "
}

# Main menu
case "${1:-help}" in
  restart)    rolling_restart ;;
  failover)   manual_failover "$2" ;;
  expand)     expand_storage "$2" ;;
  restore)    restore_from_backup "$2" "$3" ;;
  lag)        check_replication_lag ;;
  *)
    echo "Usage: $0 {restart|failover|expand|restore|lag}"
    echo "  restart              - Rolling restart of all pods"
    echo "  failover <pod-name>  - Promote replica to primary"
    echo "  expand <size>        - Expand PVCs (e.g., expand 1Ti)"
    echo "  restore <file> <db>  - Restore from S3 backup"
    echo "  lag                  - Check replication lag"
    ;;
esac

Q46 (CI/CD Security) → Q50 (Secret Incident Response) →
Q252 (Zero-Trust Networking) → S2 (RBAC Design) →
Q46 revisit (OPA Gatekeeper deep dive)

Q45 (Cluster Upgrades) → Q49 (Autoscaling) →
Q260 (etcd Optimization) → S1 (Multi-Cluster) →
Q251 (Distributed Debugging)

Q47 (Zero-Downtime Deployments) → Q48 (Argo Rollouts) →
S3 (StatefulSet Design) → Q253 (Custom Operators) →
Q251 (Distributed Debugging)

Q45 → Q46 → Q47 → Q48 → Q49 → Q50 →
S2 → S3 → Q251 → Q252 → Q253 → Q260 → S1

ADVANCED LEVEL — Production Readiness
☐ Q45  Kubernetes upgrade strategy (safe, multi-version)
☐ Q46  CI/CD security scanning and compliance pipeline
☐ Q47  Zero-downtime deployments (probes, preStop, PDB)
☐ Q48  Blue/Green and Canary with Argo Rollouts
☐ Q49  Cluster resource exhaustion: monitoring and scaling
☐ Q50  Secret exposure incident response

EXPERT LEVEL — Edge Cases & Optimization
☐ Q251 Debugging in highly distributed environments
☐ Q252 Zero-trust networking with NetworkPolicy + Istio
☐ Q253 Custom controllers and operators (Go/controller-runtime)
☐ Q260 etcd performance optimization and tuning

SCENARIO-BASED
☐ S1   Multi-cluster strategy for geo-distributed company
☐ S2   RBAC design for multi-team organization
☐ S3   StatefulSet design for production databases