Skip to main content

PostgreSQL backup & restore

Every database in the homelab is a CloudNativePG cluster built from the cnpg component, so the names are the same in every namespace: the cluster is cnpg, its pods are cnpg-1 and cnpg-2, the database is app, and the application's credentials are in the Secret cnpg-app.

This page has two parts. The first is the quick dump and restore with kubectl exec, for moving data around by hand. The second restores a database from the nightly dump that k8up stores in the backup repositories.

Find the primary​

With two instances, either pod can be the primary, and it changes on every failover or node drain. Always resolve it instead of assuming cnpg-1; a restore into a replica fails.

NS=<namespace>
PRIMARY=$(kubectl get pod -n "$NS" -l cnpg.io/instanceRole=primary \
  -o jsonpath='{.items[0].metadata.name}')

The commands below run pg_dump and psql inside that pod as the postgres user over the local socket, so no password is involved.

Ad-hoc dump and restore​

Dump the app database to the workstation:

kubectl exec -n "$NS" "$PRIMARY" -c postgres -- pg_dump -d app --clean > backup.sql

Load it back. Note -i without -t: a TTY cannot be combined with piped input.

kubectl exec -i -n "$NS" "$PRIMARY" -c postgres -- psql -d app < backup.sql

--clean makes the dump drop each object before recreating it, so it can be replayed into a database that already has the schema. Stop the application first (scale its Deployment to zero) so nothing writes while the restore runs.

Restore from a k8up snapshot​

Each backup run starts a pre-backup pod that streams pg_dump -d app --clean straight into Restic. The result is a snapshot containing a single file, /<namespace>-cnpg-pgdump.sql, in both repositories: Hetzner Object Storage and Backblaze B2.

snapshot → restic dump → <namespace>-cnpg-pgdump.sql → pv | kubectl exec → primary

You need restic and pv on the workstation, plus kubectl access to the cluster.

1. Point Restic at the repository​

Everything Restic needs is already in the app's namespace. The bucket name comes from the Schedule, the credentials from the Secrets it references:

BUCKET=$(kubectl get schedule schedule-hetzner -n "$NS" -o jsonpath='{.spec.backend.s3.bucket}')
secret() { kubectl get secret "$1" -n "$NS" -o jsonpath="{.data.$2}" | base64 -d; }

export RESTIC_REPOSITORY="s3:https://hel1.your-objectstorage.com/$BUCKET"
export RESTIC_PASSWORD=$(secret k8up-restic-password password)
export AWS_ACCESS_KEY_ID=$(secret k8up-s3-credentials AWS_ACCESS_KEY_ID)
export AWS_SECRET_ACCESS_KEY=$(secret k8up-s3-credentials AWS_SECRET_ACCESS_KEY)

To restore from Backblaze instead, the bucket and the Restic password are the same; only the endpoint and the key change:

export RESTIC_REPOSITORY="s3:https://s3.eu-central-003.backblazeb2.com/$BUCKET"
export AWS_ACCESS_KEY_ID=$(secret workspace-outputs backup_key_schedule_backblaze_id)
export AWS_SECRET_ACCESS_KEY=$(secret workspace-outputs backup_key_schedule_backblaze_secret)

2. Pick a snapshot​

The cluster already lists what each repository holds:

kubectl get snapshots.k8up.io -n "$NS" \
  -o custom-columns=ID:.spec.id,DATE:.spec.date,PATHS:.spec.paths,REPO:.spec.repository

Or ask Restic directly, filtered to the dump:

restic snapshots --path "/$NS-cnpg-pgdump.sql"

Snapshots of the data volumes live in the same repository under other paths; the filter leaves only the database dumps. Note the ID of the one to restore, or use latest.

3. Extract the dump​

restic dump writes the single file to stdout, without recreating a directory tree:

SNAPSHOT=latest
restic dump --path "/$NS-cnpg-pgdump.sql" "$SNAPSHOT" "/$NS-cnpg-pgdump.sql" > "$NS-cnpg-pgdump.sql"

A quick sanity check before touching the database: the file should end with -- PostgreSQL database dump complete.

tail -n 3 "$NS-cnpg-pgdump.sql"

4. Stop the application​

Scale the workload that uses the database to zero so it neither writes during the restore nor holds locks on the objects being dropped:

kubectl scale deployment/<app> -n "$NS" --replicas=0

5. Replay the dump​

pv shows progress, throughput and an ETA while the file streams into the primary:

pv "$NS-cnpg-pgdump.sql" | kubectl exec -i -n "$NS" "$PRIMARY" -c postgres -- psql -d app
512MiB 0:01:03 [8.12MiB/s] [===========>    ] 74% ETA 0:00:21

The replica follows through streaming replication; nothing has to be done on it.

6. Verify and start the application​

kubectl exec -it -n "$NS" "$PRIMARY" -c postgres -- psql -d app
\dt
SELECT COUNT(*) FROM your_table;
\q

Then scale the application back up and check its logs for a clean start.

The whole thing as a script​

#!/bin/bash
set -euo pipefail

NS="<namespace>"
APP="<deployment>"
SNAPSHOT="latest"
DUMP="$NS-cnpg-pgdump.sql"

secret() { kubectl get secret "$1" -n "$NS" -o jsonpath="{.data.$2}" | base64 -d; }
BUCKET=$(kubectl get schedule schedule-hetzner -n "$NS" -o jsonpath='{.spec.backend.s3.bucket}')

export RESTIC_REPOSITORY="s3:https://hel1.your-objectstorage.com/$BUCKET"
export RESTIC_PASSWORD=$(secret k8up-restic-password password)
export AWS_ACCESS_KEY_ID=$(secret k8up-s3-credentials AWS_ACCESS_KEY_ID)
export AWS_SECRET_ACCESS_KEY=$(secret k8up-s3-credentials AWS_SECRET_ACCESS_KEY)

restic dump --path "/$DUMP" "$SNAPSHOT" "/$DUMP" > "$DUMP"

PRIMARY=$(kubectl get pod -n "$NS" -l cnpg.io/instanceRole=primary \
  -o jsonpath='{.items[0].metadata.name}')
echo "Restoring into $NS/$PRIMARY"

kubectl scale "deployment/$APP" -n "$NS" --replicas=0
pv "$DUMP" | kubectl exec -i -n "$NS" "$PRIMARY" -c postgres -- psql -d app
kubectl scale "deployment/$APP" -n "$NS" --replicas=1

echo "Restore complete."

Limits​

The dump is logical and taken once a day, so a restore loses everything written since the last run; there is no WAL archive and no point-in-time recovery. When a pre-backup pod fails, that night's run has no dump at all while the volume backup still succeeds, so check the snapshot date before relying on latest. See backups-cnpg for how that failure shows up, and Backups end to end for the tiers around it.