Skip to main content

Restoring a PostgreSQL Dump to CNPG with k8up, Restic & pv

This tutorial covers how to restore a PostgreSQL database dump into a CloudNativePG (CNPG) cluster using k8up for backup management, Restic for snapshot retrieval, and pv for streaming the restore with progress visibility.

Prerequisites​

  • kubectl configured and pointing at your cluster
  • restic installed locally
  • pv installed locally (sudo dnf install pv on Fedora)
  • k8up operator installed in the cluster
  • Access to the Restic repository (S3, MinIO, etc.) used by k8up
  • A running CNPG cluster (this tutorial uses the <namespace> namespace)

Overview​

Restic Snapshot → restic restore → backup.sql → pv | kubectl exec → CNPG primary pod

Step 1 — Configure Restic Repository Access​

k8up stores backups in a Restic repository. Export the credentials so you can access it locally.

# Export Restic environment variables
export RESTIC_REPOSITORY="s3:https://your-minio-endpoint/your-bucket"
export RESTIC_PASSWORD="your-restic-password"
export AWS_ACCESS_KEY_ID="your-access-key"
export AWS_SECRET_ACCESS_KEY="your-secret-key"

Adjust the secret name and backend type (s3:, b2:, azure:, etc.) to match your k8up configuration.

Step 2 — List Available Snapshots​

restic snapshots

Example output:

ID        Time                 Host        Tags        Paths
-----------------------------------------------------------------------
a1b2c3d4  2024-06-01 02:00:00  k8up        <namespace>     /data/backup.sql
e5f6g7h8  2024-06-02 02:00:00  k8up        <namespace>     /data/backup.sql

Note the snapshot ID you want to restore from.

Step 3 — Restore the Dump from Restic​

Restore the SQL dump file to your local machine:

restic restore a1b2c3d4 --target ./

Step 4 — Identify the CNPG Primary Pod​

Always restore to the primary pod. Never restore to a replica — it will cause replication conflicts.

kubectl get pods -n <namespace> -l cnpg.io/instanceRole=primary

Example output:

NAME       READY   STATUS    RESTARTS   AGE
cnpg-1     1/1     Running   0          2d

Step 5 — Restore the Dump with pv​

Use pv to stream the SQL file into the CNPG pod with a live progress bar, transfer speed, and ETA.

pv <namespace>-postgres.sql | kubectl exec -n <namespace> -it cnpg-1 -- psql -U postgres -d app

What each part does:

PartPurpose
pv <namespace>-postgres.sqlStreams the file while showing progress
kubectl exec -n <namespace> -it cnpg-1Executes a command in the CNPG primary pod
psql -U postgres -d appConnects to the app database as the postgres superuser

pv output example:

512MiB 0:01:03 [8.12MiB/s] [===========>    ] 74% ETA 0:00:21

Step 6 — (Optional) Dynamically Target the Primary​

To avoid hardcoding the pod name (useful in scripts or after a failover), resolve the primary dynamically:

pv <namespace>-postgres.sql | kubectl exec -n <namespace> -it \
  $(kubectl get pod -n <namespace> -l cnpg.io/instanceRole=primary -o jsonpath='{.items[0].metadata.name}') \
  -- psql -U postgres -d app

Step 7 — Verify the Restore​

Connect to the database and check the restored data:

kubectl exec -n <namespace> -it cnpg-1 -- psql -U postgres -d app
-- Check tables exist
\dt

-- Check row counts
SELECT COUNT(*) FROM your_table;

-- Exit
\q

Full Script​

#!/bin/bash
set -e

NAMESPACE="<namespace>"
DATABASE="app"
SNAPSHOT_ID="a1b2c3d4"   # replace with your snapshot ID
RESTORE_PATH="/tmp/restic-restore"
DUMP_FILE="$HOME/<namespace>-postgres.sql"

# Restic credentials
export RESTIC_REPOSITORY="s3:https://your-minio-endpoint/your-bucket"
export RESTIC_PASSWORD="your-restic-password"
export AWS_ACCESS_KEY_ID="your-access-key"
export AWS_SECRET_ACCESS_KEY="your-secret-key"

# Restore snapshot locally
restic restore "$SNAPSHOT_ID" --target "$RESTORE_PATH"
cp "$RESTORE_PATH/data/backup.sql" "$DUMP_FILE"

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

echo "Restoring to pod: $PRIMARY"

# Stream restore with progress
pv "$DUMP_FILE" | kubectl exec -n "$NAMESPACE" -it "$PRIMARY" \
  -- psql -U postgres -d "$DATABASE"

echo "Restore complete."

See Also​