Running Databases on Kubernetes Complete Guide: StatefulSet, Operators, Backup and Recovery

K8s DB operations decision matrix:

When you should:                        When you should not:
+ Multi-cloud/hybrid environments       - Managed DB service available
+ Infrastructure consistency matters    - Lack of DBA resources
+ GitOps/IaC pipeline integration       - Very large single DB instances
+ Dev/test environment automation       - Extremely low latency required
+ Cost optimization is essential        - Insufficient K8s experience
+ Data sovereignty regulations          - Simple architecture is enough

K8s compatibility ranking:

Highly suitable:
  - PostgreSQL (excellent CloudNativePG ecosystem)
  - MongoDB (ReplicaSet structure fits K8s naturally)
  - Redis (Sentinel/Cluster mode)

Suitable:
  - MySQL (Percona/Oracle Operator available)
  - Elasticsearch (ECK Operator)
  - Cassandra (K8ssandra Operator)

Use with caution:
  - Oracle DB (licensing, complexity)
  - SQL Server (Windows container limitations)
  - Large single-instance DBs

Deployment:                    StatefulSet:
- Pod names: random (abc-xyz)  - Pod names: sequential (db-0, db-1, db-2)
- Parallel create/delete       - Sequential create/delete (0 -> 1 -> 2)
- Shared volume or none        - Dedicated PVC per Pod (auto-created)
- Interchangeable Pods         - Pods with unique identity
- Suited for stateless apps    - Suited for stateful apps (DB)

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: postgres
  namespace: database
spec:
  serviceName: postgres-headless  # Headless Service name
  replicas: 3
  podManagementPolicy: OrderedReady  # Sequential creation (default)
  updateStrategy:
    type: RollingUpdate
    rollingUpdate:
      maxUnavailable: 1  # K8s 1.24+
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      terminationGracePeriodSeconds: 120  # Enough time for DB shutdown
      securityContext:
        fsGroup: 999       # postgres group
        runAsUser: 999     # postgres user
      containers:
        - name: postgres
          image: postgres:16-alpine
          ports:
            - containerPort: 5432
              name: postgresql
          env:
            - name: POSTGRES_DB
              value: myapp
            - name: POSTGRES_USER
              valueFrom:
                secretKeyRef:
                  name: postgres-secret
                  key: username
            - name: POSTGRES_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: postgres-secret
                  key: password
            - name: PGDATA
              value: /var/lib/postgresql/data/pgdata
          resources:
            requests:
              cpu: "500m"
              memory: "1Gi"
            limits:
              cpu: "2"
              memory: "4Gi"
          volumeMounts:
            - name: data
              mountPath: /var/lib/postgresql/data
          livenessProbe:
            exec:
              command:
                - pg_isready
                - -U
                - postgres
            initialDelaySeconds: 30
            periodSeconds: 10
          readinessProbe:
            exec:
              command:
                - pg_isready
                - -U
                - postgres
            initialDelaySeconds: 5
            periodSeconds: 5
  volumeClaimTemplates:
    - metadata:
        name: data
      spec:
        accessModes: ["ReadWriteOnce"]
        storageClassName: fast-ssd
        resources:
          requests:
            storage: 100Gi

# OrderedReady (default): Sequential create/delete
# Pod 0 Ready -> Pod 1 created -> Pod 1 Ready -> Pod 2 created
podManagementPolicy: OrderedReady

# Parallel: All Pods created/deleted simultaneously
# Be careful with initial bootstrapping (DBs typically use OrderedReady)
podManagementPolicy: Parallel

updateStrategy:
  type: RollingUpdate
  rollingUpdate:
    # Partition: Only Pods with ordinal at or above this value are updated
    # Useful for canary deployments (update only Pod 2 first)
    partition: 2

# OnDelete: Updates only when Pods are manually deleted
# Provides fine-grained control during DB upgrades
updateStrategy:
  type: OnDelete

# StorageClass definition (AWS EBS gp3)
apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
  name: fast-ssd
provisioner: ebs.csi.aws.com
parameters:
  type: gp3
  iops: "5000"
  throughput: "250"    # MB/s
  encrypted: "true"
reclaimPolicy: Retain  # MUST be Retain for DB data!
volumeBindingMode: WaitForFirstConsumer
allowVolumeExpansion: true  # Allow online volume expansion

# StorageClass definition (GCP PD SSD)
apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
  name: fast-ssd
provisioner: pd.csi.storage.gke.io
parameters:
  type: pd-ssd
  replication-type: regional-pd  # Regional PD (high availability)
reclaimPolicy: Retain
volumeBindingMode: WaitForFirstConsumer
allowVolumeExpansion: true

Performance comparison:

Local NVMe SSD:
  - Random reads: 500K+ IOPS
  - Latency: under 0.1ms
  - Drawback: No Pod migration, data loss risk on node failure

Cloud EBS gp3:
  - Baseline: 3,000 IOPS / 125 MB/s
  - Maximum: 16,000 IOPS / 1,000 MB/s
  - Advantage: Pod migration possible, snapshot support

Cloud EBS io2:
  - Maximum: 64,000 IOPS
  - 99.999% durability
  - Expensive but suitable for mission-critical DBs

# Expand PVC size (requires allowVolumeExpansion: true on StorageClass)
kubectl patch pvc data-postgres-0 -n database \
  -p '{"spec": {"resources": {"requests": {"storage": "200Gi"}}}}'

# Check expansion status
kubectl get pvc data-postgres-0 -n database -o yaml | grep -A 5 status

apiVersion: v1
kind: Service
metadata:
  name: postgres-headless
  namespace: database
spec:
  clusterIP: None  # The key to Headless Service
  selector:
    app: postgres
  ports:
    - port: 5432
      targetPort: 5432
      name: postgresql

DNS pattern:
  pod-name.service-name.namespace.svc.cluster.local

Examples:
  postgres-0.postgres-headless.database.svc.cluster.local
  postgres-1.postgres-headless.database.svc.cluster.local
  postgres-2.postgres-headless.database.svc.cluster.local

# Additional Services for read/write splitting
---
apiVersion: v1
kind: Service
metadata:
  name: postgres-primary
  namespace: database
spec:
  selector:
    app: postgres
    role: primary
  ports:
    - port: 5432
      targetPort: 5432
---
apiVersion: v1
kind: Service
metadata:
  name: postgres-replica
  namespace: database
spec:
  selector:
    app: postgres
    role: replica
  ports:
    - port: 5432
      targetPort: 5432

Tasks automated by Operators:

1. Cluster initialization (Primary + Replica setup)
2. Automatic failover (Replica promotion on Primary failure)
3. Backup/Recovery (scheduling, PITR)
4. Rolling upgrades (zero downtime)
5. Horizontal scaling (add/remove Replicas)
6. Monitoring integration
7. Certificate management (TLS)
8. Configuration changes (without restart)

# Install CloudNativePG
kubectl apply --server-side -f \
  https://raw.githubusercontent.com/cloudnative-pg/cloudnative-pg/release-1.24/releases/cnpg-1.24.1.yaml

# CloudNativePG Cluster definition
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: myapp-db
  namespace: database
spec:
  instances: 3
  imageName: ghcr.io/cloudnative-pg/postgresql:16.4

  postgresql:
    parameters:
      max_connections: "200"
      shared_buffers: "1GB"
      effective_cache_size: "3GB"
      work_mem: "16MB"
      maintenance_work_mem: "256MB"
      wal_buffers: "16MB"
      random_page_cost: "1.1"
      effective_io_concurrency: "200"
      max_wal_size: "2GB"
      checkpoint_completion_target: "0.9"

  bootstrap:
    initdb:
      database: myapp
      owner: app_user
      secret:
        name: myapp-db-credentials

  storage:
    size: 100Gi
    storageClass: fast-ssd

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

  backup:
    barmanObjectStore:
      destinationPath: "s3://my-backup-bucket/cnpg/"
      s3Credentials:
        accessKeyId:
          name: aws-creds
          key: ACCESS_KEY_ID
        secretAccessKey:
          name: aws-creds
          key: SECRET_ACCESS_KEY
      wal:
        compression: gzip
      data:
        compression: gzip
    retentionPolicy: "30d"

  monitoring:
    enablePodMonitor: true

# Install Percona Operator
kubectl apply -f https://raw.githubusercontent.com/percona/percona-xtradb-cluster-operator/v1.15.0/deploy/bundle.yaml

# Percona XtraDB Cluster definition
apiVersion: pxc.percona.com/v1
kind: PerconaXtraDBCluster
metadata:
  name: myapp-mysql
  namespace: database
spec:
  crVersion: "1.15.0"
  secretsName: myapp-mysql-secrets

  pxc:
    size: 3
    image: percona/percona-xtradb-cluster:8.0.36
    resources:
      requests:
        memory: 2G
        cpu: "1"
      limits:
        memory: 4G
        cpu: "2"
    volumeSpec:
      persistentVolumeClaim:
        storageClassName: fast-ssd
        resources:
          requests:
            storage: 100Gi
    affinity:
      antiAffinityTopologyKey: "kubernetes.io/hostname"

  haproxy:
    enabled: true
    size: 3
    image: percona/haproxy:2.8.5
    resources:
      requests:
        memory: 512M
        cpu: "500m"

  backup:
    image: percona/percona-xtradb-cluster-operator:1.15.0-pxc8.0-backup
    storages:
      s3-backup:
        type: s3
        s3:
          bucket: my-backup-bucket
          credentialsSecret: aws-creds
          region: ap-northeast-2
    schedule:
      - name: daily-backup
        schedule: "0 3 * * *"
        keep: 7
        storageName: s3-backup

# Install MongoDB Community Operator
kubectl apply -f https://raw.githubusercontent.com/mongodb/mongodb-kubernetes-operator/master/config/crd/bases/mongodbcommunity.mongodb.com_mongodbcommunity.yaml
kubectl apply -k https://github.com/mongodb/mongodb-kubernetes-operator/config/rbac/
kubectl create -f https://raw.githubusercontent.com/mongodb/mongodb-kubernetes-operator/master/config/manager/manager.yaml

# MongoDB ReplicaSet definition
apiVersion: mongodbcommunity.mongodb.com/v1
kind: MongoDBCommunity
metadata:
  name: myapp-mongodb
  namespace: database
spec:
  members: 3
  type: ReplicaSet
  version: "7.0.14"

  security:
    authentication:
      modes: ["SCRAM"]

  users:
    - name: app-user
      db: admin
      passwordSecretRef:
        name: mongodb-password
      roles:
        - name: readWrite
          db: myapp
        - name: clusterAdmin
          db: admin
      scramCredentialsSecretName: app-user-scram

  statefulSet:
    spec:
      template:
        spec:
          containers:
            - name: mongod
              resources:
                requests:
                  cpu: "1"
                  memory: 2Gi
                limits:
                  cpu: "2"
                  memory: 4Gi
      volumeClaimTemplates:
        - metadata:
            name: data-volume
          spec:
            storageClassName: fast-ssd
            resources:
              requests:
                storage: 100Gi

PostgreSQL HA architecture (CloudNativePG):

  [CNPG Operator]
       |
       v
  [Primary Pod]  ----Streaming Replication---->  [Replica Pod 1]
       |                                          [Replica Pod 2]
       |
  [Headless Service]
       |
  postgres-primary (writes) ----> Routes to Primary only
  postgres-replica (reads)  ----> Routes to Replicas only

Failover scenario:

1. Primary Pod failure occurs
2. Operator detects it (liveness probe failure)
3. Selects Replica with most recent LSN
4. Promotes selected Replica to Primary
5. Remaining Replicas follow the new Primary
6. Service endpoints updated
7. Failed Pod recreated and joins as new Replica

Entire process: typically under 30 seconds

apiVersion: policy/v1
kind: PodDisruptionBudget
metadata:
  name: postgres-pdb
  namespace: database
spec:
  minAvailable: 2  # Maintain at least 2 Pods
  selector:
    matchLabels:
      app: postgres

# Pod Topology Spread Constraints
spec:
  template:
    spec:
      topologySpreadConstraints:
        - maxSkew: 1
          topologyKey: topology.kubernetes.io/zone
          whenUnsatisfiable: DoNotSchedule
          labelSelector:
            matchLabels:
              app: postgres
        - maxSkew: 1
          topologyKey: kubernetes.io/hostname
          whenUnsatisfiable: DoNotSchedule
          labelSelector:
            matchLabels:
              app: postgres

Logical backup (pg_dump/mysqldump):
  + High portability (restore to different versions/platforms)
  + Individual table/schema backup possible
  - Slow for large DBs
  - Index rebuilding needed on restore

Physical backup (pgBackRest/xtrabackup):
  + Fast for large DBs
  + Incremental backup support
  + PITR (Point-in-Time Recovery) possible
  - Restore only within same major version
  - Whole cluster backup only

# CloudNativePG backup configuration
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: myapp-db
spec:
  backup:
    barmanObjectStore:
      destinationPath: "s3://my-backup-bucket/cnpg/myapp-db/"
      s3Credentials:
        accessKeyId:
          name: aws-creds
          key: ACCESS_KEY_ID
        secretAccessKey:
          name: aws-creds
          key: SECRET_ACCESS_KEY
      wal:
        compression: gzip
        maxParallel: 4
      data:
        compression: gzip
        immediateCheckpoint: true
    retentionPolicy: "30d"
---
# Scheduled backup
apiVersion: postgresql.cnpg.io/v1
kind: ScheduledBackup
metadata:
  name: myapp-db-daily
spec:
  schedule: "0 3 * * *"
  cluster:
    name: myapp-db
  backupOwnerReference: self
  method: barmanObjectStore

# Recover to a specific point in time with PITR
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: myapp-db-recovered
spec:
  instances: 3

  bootstrap:
    recovery:
      source: myapp-db-backup
      recoveryTarget:
        targetTime: "2026-03-24T10:30:00Z"  # Recovery target time

  externalClusters:
    - name: myapp-db-backup
      barmanObjectStore:
        destinationPath: "s3://my-backup-bucket/cnpg/myapp-db/"
        s3Credentials:
          accessKeyId:
            name: aws-creds
            key: ACCESS_KEY_ID
          secretAccessKey:
            name: aws-creds
            key: SECRET_ACCESS_KEY

# Install Velero
velero install \
  --provider aws \
  --bucket my-velero-bucket \
  --secret-file ./credentials-velero \
  --plugins velero/velero-plugin-for-aws:v1.10.0

# Namespace-level backup
velero backup create database-backup \
  --include-namespaces database \
  --snapshot-volumes=true \
  --volume-snapshot-locations default

# Restore
velero restore create --from-backup database-backup

# PostgreSQL Exporter (pg_exporter)
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres-exporter
  namespace: database
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgres-exporter
  template:
    metadata:
      labels:
        app: postgres-exporter
      annotations:
        prometheus.io/scrape: "true"
        prometheus.io/port: "9187"
    spec:
      containers:
        - name: exporter
          image: prometheuscommunity/postgres-exporter:0.15.0
          ports:
            - containerPort: 9187
          env:
            - name: DATA_SOURCE_URI
              value: "postgres-primary.database.svc:5432/myapp?sslmode=disable"
            - name: DATA_SOURCE_USER
              valueFrom:
                secretKeyRef:
                  name: postgres-secret
                  key: username
            - name: DATA_SOURCE_PASS
              valueFrom:
                secretKeyRef:
                  name: postgres-secret
                  key: password

DB monitoring essential metrics:

Performance:
  - Queries per second (QPS)
  - Query latency (p50, p95, p99)
  - Active connection count
  - Cache hit ratio (Buffer Cache Hit Ratio)

Replication:
  - Replication lag
  - WAL receive delay
  - Replica status

Storage:
  - Disk usage
  - IOPS / throughput
  - WAL size

Resources:
  - CPU utilization
  - Memory usage
  - Pod restart count

Operations:
  - Dead tuples ratio
  - Vacuum execution status
  - Lock wait count

# PMM Server installation
apiVersion: apps/v1
kind: Deployment
metadata:
  name: pmm-server
  namespace: monitoring
spec:
  replicas: 1
  selector:
    matchLabels:
      app: pmm-server
  template:
    spec:
      containers:
        - name: pmm-server
          image: percona/pmm-server:2
          ports:
            - containerPort: 443
          volumeMounts:
            - name: pmm-data
              mountPath: /srv
      volumes:
        - name: pmm-data
          persistentVolumeClaim:
            claimName: pmm-data

# DB Pod resource configuration guide
resources:
  requests:
    # CPU: Minimum guaranteed CPU
    # DBs are sensitive to CPU contention, so be generous
    cpu: "2"
    # Memory: shared_buffers + work_mem * max_connections + OS
    memory: "4Gi"
  limits:
    # CPU limit: Don't set or set generously
    # (throttling causes query latency)
    cpu: "4"
    # Memory limit: Set higher than request to prevent OOM Kill
    memory: "8Gi"

# DB Pod scheduling optimization
spec:
  template:
    spec:
      # Schedule only on DB-dedicated nodes
      nodeSelector:
        node-type: database

      # Or use Toleration for dedicated nodes
      tolerations:
        - key: "dedicated"
          operator: "Equal"
          value: "database"
          effect: "NoSchedule"

      # Distribute Replicas across different nodes/zones
      affinity:
        podAntiAffinity:
          requiredDuringSchedulingIgnoredDuringExecution:
            - labelSelector:
                matchExpressions:
                  - key: app
                    operator: In
                    values:
                      - postgres
              topologyKey: "kubernetes.io/hostname"

# Set kernel parameters via initContainer
spec:
  template:
    spec:
      initContainers:
        - name: sysctl-tuning
          image: busybox:1.36
          securityContext:
            privileged: true
          command:
            - sh
            - -c
            - |
              sysctl -w vm.swappiness=1
              sysctl -w vm.dirty_background_ratio=5
              sysctl -w vm.dirty_ratio=10
              sysctl -w vm.overcommit_memory=2
              sysctl -w net.core.somaxconn=65535
              sysctl -w net.ipv4.tcp_max_syn_backlog=65535

# Restrict network access to DB Pods
apiVersion: networking.k8s.io/v1
kind: NetworkPolicy
metadata:
  name: postgres-network-policy
  namespace: database
spec:
  podSelector:
    matchLabels:
      app: postgres
  policyTypes:
    - Ingress
    - Egress
  ingress:
    - from:
        - namespaceSelector:
            matchLabels:
              name: application
          podSelector:
            matchLabels:
              app: backend
        - podSelector:
            matchLabels:
              app: postgres  # Allow inter-Pod replication
      ports:
        - port: 5432
          protocol: TCP
  egress:
    - to:
        - podSelector:
            matchLabels:
              app: postgres
      ports:
        - port: 5432
          protocol: TCP
    - to:  # Allow DNS
        - namespaceSelector: {}
      ports:
        - port: 53
          protocol: UDP
        - port: 53
          protocol: TCP

# Using External Secrets Operator
apiVersion: external-secrets.io/v1beta1
kind: ExternalSecret
metadata:
  name: postgres-secret
  namespace: database
spec:
  refreshInterval: 1h
  secretStoreRef:
    name: aws-secrets-manager
    kind: ClusterSecretStore
  target:
    name: postgres-secret
  data:
    - secretKey: username
      remoteRef:
        key: prod/database/postgres
        property: username
    - secretKey: password
      remoteRef:
        key: prod/database/postgres
        property: password

# Issue DB certificates with cert-manager
apiVersion: cert-manager.io/v1
kind: Certificate
metadata:
  name: postgres-tls
  namespace: database
spec:
  secretName: postgres-tls-secret
  duration: 8760h  # 1 year
  renewBefore: 720h  # Renew 30 days before expiry
  issuerRef:
    name: internal-ca
    kind: ClusterIssuer
  dnsNames:
    - postgres-primary.database.svc.cluster.local
    - postgres-headless.database.svc.cluster.local
    - "*.postgres-headless.database.svc.cluster.local"

Phase 1: Preparation
  1. Install DB Operator on K8s cluster
  2. Configure StorageClass, NetworkPolicy
  3. Create target DB cluster (empty)

Phase 2: Data migration
  Method A - Logical replication (minimal downtime):
    1. Set up logical replication on VM DB
    2. Configure K8s DB as Subscriber
    3. Initial sync + change streaming
    4. Application switchover (brief downtime)

  Method B - pg_dump/restore:
    1. Backup VM DB (pg_dump)
    2. Restore to K8s DB (pg_restore)
    3. Switch during downtime window

Phase 3: Switchover
  1. Update application DB connection strings
  2. DNS change or Service endpoint update
  3. Set old VM DB to read-only (rollback safety)

Phase 4: Cleanup
  1. Delete old VM DB after verification
  2. Update monitoring/alerts

-- VM DB (Publisher) setup
-- postgresql.conf
-- wal_level = logical
-- max_replication_slots = 10

CREATE PUBLICATION myapp_pub FOR ALL TABLES;

-- K8s DB (Subscriber) setup
CREATE SUBSCRIPTION myapp_sub
  CONNECTION 'host=vm-db.example.com port=5432 dbname=myapp user=repl_user password=secret'
  PUBLICATION myapp_pub;

K8s DB Production Checklist:

Storage:
  [ ] StorageClass has reclaimPolicy: Retain
  [ ] Volume size has 20%+ free space
  [ ] allowVolumeExpansion: true confirmed
  [ ] IOPS/throughput matches workload

High Availability:
  [ ] Minimum 3 instances (1 Primary + 2 Replica)
  [ ] PodDisruptionBudget configured
  [ ] Pod Anti-Affinity (spread across nodes/zones)
  [ ] Automatic failover tested

Backup:
  [ ] Automated backup schedule set (at least daily)
  [ ] WAL archiving enabled (for PITR)
  [ ] Backup restore tested
  [ ] Retention policy configured (30+ days)

Security:
  [ ] NetworkPolicy restricts access
  [ ] Secrets managed via External Secrets Operator
  [ ] TLS encryption enabled
  [ ] DB user permissions minimized (least privilege)

Monitoring:
  [ ] Prometheus + Grafana dashboard configured
  [ ] Alert rules set (replication lag, disk usage, connections)
  [ ] Log collection (Loki/EFK)
  [ ] Query performance monitoring

Performance:
  [ ] Resource requests/limits properly set
  [ ] DB parameters tuned (shared_buffers, work_mem, etc.)
  [ ] Kernel parameters optimized
  [ ] Dedicated nodes used (Taint/Toleration)