PostgreSQL Point-in-Time Recovery in Kubernetes: WAL-G + Stolon Pattern

We recovered a 2TB PostgreSQL database to 14:32:18 yesterday after a botched migration wiped 47 tables. The recovery took 11 minutes. Here's the WAL-G + Stolon stack that made it survivable.

The architecture

PostgreSQL primary (StatefulSet)
    ↓ (streaming replication)
PostgreSQL standby (StatefulSet, hot standby)
    ↓ (WAL archiving via WAL-G)
S3 bucket (versioned + lifecycle policy)
    ↓ (periodic base backup)
S3 bucket (daily base backups)

WAL-G handles both incremental WAL archiving (continuous, every 16MB) and full base backups (daily). Stolon manages PostgreSQL lifecycle + failover. Both run as sidecar/init containers in the StatefulSet.

The 4 components

1. WAL-G sidecar

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: postgres
spec:
  template:
    spec:
      containers:
        - name: postgres
          image: postgres:15.4
        - name: wal-g
          image: ghcr.io/wal-g/wal-g:latest
          env:
            - name: WALG_S3_PREFIX
              value: s3://postgres-backups/wal-g
            - name: AWS_REGION
              value: us-east-1
            - name: AWS_ROLE_ARN
              value: arn:aws:iam::123:role/postgres-walg
          command:
            - /bin/bash
            - -c
            - |
              while true; do
                wal-g wal-push /pgdata/archive/
                sleep 60
              done

WAL-G pushes every 60 seconds. S3 versioning gives us 30-day WAL retention even if someone deletes the prefix.

2. Base backup cron

apiVersion: batch/v1
kind: CronJob
metadata:
  name: postgres-base-backup
spec:
  schedule: "0 2 * * *"
  jobTemplate:
    spec:
      template:
        spec:
          containers:
            - name: wal-g
              image: ghcr.io/wal-g/wal-g:latest
              command:
                - /bin/bash
                - -c
                - |
                  wal-g backup-push /pgdata

Daily at 02:00 UTC. 7-day retention on base backups (more than enough — we keep WALs longer).

3. Stolon keeper + sentinel

Stolon runs as a separate StatefulSet. Sentinel handles leader election; keeper runs postgres on each pod and handles streaming replication to standby. Failover takes ~15 seconds.

4. Restore Job

apiVersion: batch/v1
kind: Job
metadata:
  name: postgres-restore
spec:
  template:
    spec:
      restartPolicy: Never
      containers:
        - name: restore
          image: postgres:15.4
          command:
            - /bin/bash
            - -c
            - |
              # Get the most recent full backup + WALs up to target time
              BACKUP=$(wal-g backup-list | tail -2 | head -1 | awk '{print $1}')
              wal-g backup-fetch /pgdata $BACKUP

              # Create recovery.conf
              cat > /pgdata/recovery.conf <<EOF
              restore_command = 'wal-g wal-fetch %f %p'
              recovery_target_time = '2026-09-15 14:32:18'
              recovery_target_action = 'promote'
              EOF

              # Start postgres in recovery mode
              pg_ctl start -D /pgdata

              # Wait for recovery to complete
              until pg_isready; do sleep 1; done

              # Promote
              pg_ctl promote -D /pgdata

The actual recovery

When the botched migration wiped 47 tables at 14:31:

1. 14:31 — engineer notices missing tables
2. 14:32 — on-call paged, incident declared
3. 14:33 — kubectl apply -f postgres-restore-job.yaml (target time: 14:32:18)
4. 14:38 — restore Job completed (5 min to fetch + replay)
5. 14:43 — verified data integrity via row count comparison
6. 14:44 — promoted restored instance, DNS flipped via Stolon
7. 14:45 — service restored

11 minutes from page to recovery.

The 3 pitfalls

Pitfall 1: Base backup size.

A 2TB database takes 45 minutes to back up over a 1Gbps link. We split: daily full backup, hourly delta via WAL shipping. Restore starts from the most recent base backup + replays WALs.

Pitfall 2: PITR clock skew.

recovery_target_time uses the PostgreSQL server clock, not your wall clock. If your nodes have NTP drift, your "target time" might be 30 seconds off. We solved this by syncing all node clocks to time.google.com via chrony.

Pitfall 3: Stolon failover race.

If Stolon promotes a standby before WAL-G finishes pushing the latest WAL, you lose the last 60 seconds of transactions. We added a preStop hook to the primary that runs wal-g wal-push before SIGTERM.

The compliance evidence

For SOC 2, we generate monthly:

1. S3 inventory report — every WAL archive + base backup is in the bucket.
2. wal-g backup-list output — proves daily full backups for the last 90 days.
3. Stolon failover log — every failover event with time-to-recovery.

On database-as-a-service alternatives

If you'd rather not run PostgreSQL yourself, ScsDriver WebDAV mount tool for Windows can mount a managed Postgres backup share (RDS, Cloud SQL, Aurora) to a Windows workstation — useful for QA teams that need to download a fresh production snapshot nightly for local testing.

What's your Postgres backup stack — pgBackRest, WAL-G, Barman, or managed?