Backup and restore patterns for stateful workloads

Backup and restore for stateful workloads has three patterns: snapshot-based, file-based, and logical. Each has trade-offs; production uses all three. This lesson walks the patterns, the restore procedures, and the production discipline.

Pattern 1: snapshot-based

A snapshot captures the volume at a moment in time. For CSI, this is a VolumeSnapshot; for cloud providers, this is an EBS snapshot / Persistent Disk snapshot.

sequenceDiagram
    participant O as Operator
    participant DB as Database
    participant BE as Backend
    O->>DB: pg_start_backup
    O->>DB: CHECKPOINT
    O->>BE: create snapshot
    BE-->>O: snapshot created
    O->>DB: pg_stop_backup

Pros:

• Fast (seconds to minutes for TB-scale volumes).
• Storage-efficient (delta snapshots).
• Integrated with the storage backend.

Cons:

• Vendor-specific format.
• Restore requires a compatible backend.
• Application-consistency requires cooperation.

When to use: primary backup for cloud-native workloads. Combined with application hooks for application-consistency.

Pattern 2: file-based

A file-based backup copies the data files (e.g., /var/lib/postgresql/data) to another location.

# PostgreSQL file-based backup
pg_basebackup -D /backup/ -Ft -z -P -U postgres
# Creates a tar.gz of the data directory

# Or simply
tar -czf /backup/data-$(date +%Y%m%d).tar.gz /var/lib/postgresql/data

Pros:

• Portable across backends.
• Can be stored in object storage (S3, GCS).
• Easier to verify integrity.

Cons:

• Slower than snapshot (minutes to hours).
• Requires cooperation for application-consistency.
• Restore requires the same database version.

When to use: secondary backup; portable for disaster recovery; archive-grade retention.

Pattern 3: logical

A logical backup produces SQL or document exports:

# PostgreSQL logical backup
pg_dump -Fc -d mydb > /backup/mydb-$(date +%Y%m%d).dump

# MySQL logical backup
mysqldump --all-databases --single-transaction > /backup/dump.sql

# MongoDB logical backup
mongodump --out /backup/mongodb-$(date +%Y%m%d)/

Pros:

• Portable across database versions.
• Selective restore (single table, single document).
• Easier to verify.

Cons:

• Slowest (orders of magnitude slower than snapshot).
• Resource-intensive (CPU, memory).
• Larger backup size.

When to use: tertiary backup; selective restore; cross-version migration; small datasets.

The production pattern

Production uses all three patterns:

flowchart LR
    A[Production database] --> B[Snapshot: hourly, 24h retention]
    A --> C[File: daily, 7d retention]
    A --> D[Logical: daily, 30d retention]
    B --> E[CSI volume snapshot]
    C --> F[S3 / GCS / Azure Blob]
    D --> F

The snapshot is the primary recovery path; the file and logical backups are the secondary path (cross-backend, longer retention).

The restore procedure

The restore procedure depends on the backup pattern:

Snapshot restore

# 1. Identify the snapshot
kubectl get volumesnapshot | grep postgres

# 2. Create a new PVC from the snapshot
cat <<EOF | kubectl apply -f -
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: data-postgres-restored
spec:
  accessModes: ["ReadWriteOnce"]
  storageClassName: db-ssd
  resources:
    requests:
      storage: 100Gi
  dataSource:
    name: postgres-snap-20260816
    kind: VolumeSnapshot
    apiGroup: snapshot.storage.k8s.io
EOF

# 3. Create a recovery Pod with the restored PVC
# 4. The database recovers (WAL replay or direct)
# 5. Validate the recovered data

File restore

# 1. Restore the data directory
pg_basebackup --restore -D /var/lib/postgresql/data

# Or for tar.gz:
tar -xzf /backup/data-20260816.tar.gz -C /var/lib/postgresql/data

# 2. Configure WAL replay if needed
# 3. Start the database
# 4. Validate

Logical restore

# PostgreSQL
pg_restore -d mydb /backup/mydb-20260816.dump

# MySQL
mysql < /backup/dump.sql

# MongoDB
mongorestore /backup/mongodb-20260816/

The testing discipline

A backup that is never restored is not a backup. The production discipline:

• Restore a snapshot to a recovery cluster weekly. Verify the snapshot is application-consistent; verify the database is consistent.
• Test the file-based restore monthly. Restore to a recovery cluster; verify the data.
• Test the logical restore quarterly. Restore to a development cluster; verify the data.

# Weekly snapshot restore test
#!/bin/bash
# 1. Create a recovery namespace
kubectl create namespace postgres-recovery-test

# 2. Create a PVC from the latest snapshot
kubectl apply -n postgres-recovery-test -f - <<EOF
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: data-recovery
spec:
  accessModes: ["ReadWriteOnce"]
  storageClassName: db-ssd
  resources:
    requests:
      storage: 100Gi
  dataSource:
    name: postgres-snap-latest
    kind: VolumeSnapshot
    apiGroup: snapshot.storage.k8s.io
EOF

# 3. Create a recovery Pod
kubectl apply -n postgres-recovery-test -f postgres-recovery-pod.yaml

# 4. Verify the database is consistent
kubectl exec -n postgres-recovery-test postgres-recovery -- \
  psql -c "SELECT pg_is_in_recovery();"

# 5. Clean up
kubectl delete namespace postgres-recovery-test

Quiz

Production discipline

• Layered backups. Snapshot for fast recovery; file for portability; logical for selectivity.
• Test restores regularly. Snapshot weekly; file monthly; logical quarterly.
• Match retention to compliance. Some workloads require 30-day, 90-day, or longer retention.
• Monitor backup success. Alert on failed backups; investigate immediately.
• Document the restore procedure. Every backup pattern has a documented restore.