Implement automated PostgreSQL backup and restore

Production databases fail. Disks corrupt silently, operators run destructive migrations, and ransomware encrypts volumes at 3 AM on a Saturday. The difference between a career-defining incident and a routine recovery is whether you built a backup pipeline that actually works—and tested it before the disaster arrived. This section builds an end-to-end automated backup system for PostgreSQL running on Kubernetes, using MinIO as the object storage backend, and implements point-in-time recovery (PITR) so you can restore your database to any arbitrary second within a retention window.

Introduction

Teams that treat backups as a checkbox task discover—only when something breaks—that an untested restore is not a backup at all. When a PostgreSQL volume is corrupted or held for ransom, what matters is how recently data was captured and how quickly it can be returned to a known state. By the end of this lesson you will be able to deploy a nightly pg_dump CronJob that ships compressed dumps to MinIO, configure continuous WAL archiving with archive_command for sub-five-minute RPO, and execute a point-in-time restore to a precise timestamp—turning a recovery scenario from a crisis into a practiced drill.

Key Terminology

• Logical backup — A SQL-level export produced by pg_dump; portable across PostgreSQL major versions and the foundation for the nightly CronJob you build below.
• WAL segment — A 16 MB write-ahead log file PostgreSQL fills as transactions commit; archived continuously to give point-in-time recovery sub-second granularity.
• Point-in-time recovery (PITR) — Restoring a base backup and replaying WAL forward to a specific timestamp; requires recovery.signal and recovery_target_time configured before PostgreSQL restarts.
• Recovery Point Objective (RPO) — The maximum tolerable data loss measured in time; nightly-only backups give an RPO of up to 24 hours, while WAL archiving with archive_timeout=300 caps RPO at five minutes.
• archive_command — The shell command PostgreSQL invokes for each completed WAL segment; the integration point that ships segments to MinIO and is what makes PITR possible at all.

Concepts

Backup Strategy: Logical vs. Physical

Before writing a single line of code, you must choose between two fundamentally different PostgreSQL backup approaches, each with distinct trade-offs that affect your Recovery Point Objective (RPO) and Recovery Time Objective (RTO).

• Logical backups use pg_dump to export SQL statements or archive-format files. They are portable across PostgreSQL major versions, allow selective table restoration, and produce human-readable output. However, they hold a snapshot of the database at dump-start time, meaning any writes after the dump begins are lost unless you layer additional mechanisms on top.

• Physical backups use pg_basebackup combined with WAL archiving to capture the entire data directory and subsequent write-ahead log segments. Physical backups enable point-in-time recovery because PostgreSQL can replay WAL segments forward from a base backup to any target timestamp. The cost is larger backup sizes and tighter coupling to the exact PostgreSQL major version.

A production-grade system uses both: nightly pg_dump for portable disaster recovery and continuous WAL archiving for PITR with sub-five-minute RPO. The CronJob-based pipeline you build in the lab exercises the logical backup path, while the WAL archiving configuration provides the continuous protection layer.

Testing Your Recovery Pipeline

A backup that has never been restored is not a backup—it is a hope. Schedule monthly recovery drills using a dedicated Kubernetes Job that spins up a temporary PostgreSQL pod, restores the latest backup, runs a validation query (such as counting rows in critical tables), and reports success or failure to your Prometheus push gateway. Track the backup_restore_duration_seconds and backup_restore_row_count metrics on a Grafana dashboard alongside your postgres-exporter metrics to detect silent backup degradation before it becomes a production incident.

Integrate these metrics with your existing monitoring stack: the postgres-exporter already exposes pg_stat_archiver metrics showing archived_count and failed_count, which tell you whether WAL archiving is healthy. Combine that with a custom metric from your backup CronJob that records backup_last_success_timestamp, and configure a Grafana alert that fires if the timestamp is more than 26 hours old (giving a two-hour grace period for a nightly job scheduled at 2 AM). This layered approach—CronJob for logical backups, WAL archiving for continuous PITR, MinIO for durable storage, Prometheus for observability, and regular restore drills for confidence—is what separates hobby-grade backups from production-grade data protection.

Code Walkthrough

Now that you understand the trade-offs between logical pg_dump backups and WAL-based physical backups, the next step is wiring both into a running Kubernetes cluster.

The CronJob executes a Python script that calls pg_dump, compresses the result, and uploads it to MinIO. The WAL archiving layer—driven by archive_command in PostgreSQL's configuration—runs continuously between nightly dumps, capping RPO at five minutes regardless of how long the database sits between scheduled jobs.

Code snippetpython
1import subprocess
2import gzip
3import os
4from datetime import datetime, timezone
5from minio import Minio
6
7MINIO_ENDPOINT = os.environ.get("MINIO_ENDPOINT", "minio.storage.svc:9000")
8MINIO_ACCESS_KEY = os.environ["MINIO_ACCESS_KEY"]
9MINIO_SECRET_KEY = os.environ["MINIO_SECRET_KEY"]
10PG_HOST = os.environ.get("PG_HOST", "postgres-0.postgres-headless.data.svc")
11PG_USER = os.environ.get("PG_USER", "postgres")
12PG_DATABASE = os.environ.get("PG_DATABASE", "appdb")
13BUCKET = "backups"
14PREFIX = "postgres"
15
16def run_backup():
17    timestamp = datetime.now(timezone.utc).strftime("%Y%m%dT%H%M%SZ")
18    dump_file = f"/tmp/nightly-{timestamp}.sql"
19    gz_file   = f"{dump_file}.gz"
20    object_name = f"{PREFIX}/nightly-{timestamp}.sql.gz"
21
22    result = subprocess.run(
23        ["pg_dump", "-h", PG_HOST, "-U", PG_USER, "-Fc", "-f", dump_file, PG_DATABASE],
24        capture_output=True, text=True, timeout=3600,
25    )
26    if result.returncode != 0:
27        raise RuntimeError(f"pg_dump failed: {result.stderr}")
28
29    with open(dump_file, "rb") as f_in, gzip.open(gz_file, "wb", compresslevel=6) as f_out:
30        while chunk := f_in.read(8 * 1024 * 1024):
31            f_out.write(chunk)
32
33    client = Minio(MINIO_ENDPOINT, MINIO_ACCESS_KEY, MINIO_SECRET_KEY, secure=False)
34    if not client.bucket_exists(BUCKET):
35        client.make_bucket(BUCKET)
36
37    stat = client.fput_object(BUCKET, object_name, gz_file)
38    print(f"Uploaded {object_name} | etag={stat.etag} | size={os.path.getsize(gz_file)}")
39    os.remove(dump_file)
40    os.remove(gz_file)
41    return object_name, stat.etag
42
43def verify_backup_integrity(object_name):
44    client = Minio(MINIO_ENDPOINT, MINIO_ACCESS_KEY, MINIO_SECRET_KEY, secure=False)
45    response = client.get_object(BUCKET, object_name, length=2)
46    magic_bytes = response.read()
47    response.close()
48    response.release_conn()
49    if magic_bytes != b"\x1f\x8b":
50        raise ValueError(f"Backup {object_name} is not valid gzip: {magic_bytes!r}")
51    print(f"Integrity check passed for {object_name}")
52    return True
53
54if __name__ == "__main__":
55    name, etag = run_backup()
56    verify_backup_integrity(name)

run_backup invokes pg_dump in custom-format mode (-Fc), streams the result through gzip, and uploads it to the backups/postgres/ prefix in MinIO. After the upload, verify_backup_integrity fetches the first two bytes of the stored object and confirms the gzip magic number (\x1f\x8b), catching silent upload corruption before the file enters the retention window.

To enable the WAL archiving side of the pipeline, add the following directives to your PostgreSQL postgresql.conf:

Code snippetconf
1archive_mode = on
2archive_command = 'mc cp %p minio-alias/wal-archive/%f'
3archive_timeout = 300

archive_command is invoked by PostgreSQL for each completed 16 MB WAL segment. Setting archive_timeout = 300 forces a segment switch every five minutes, which bounds RPO to five minutes even during quiet periods when segments would otherwise fill slowly.

You've completed this when the CronJob produces a date-stamped .sql.gz object under backups/postgres/ every morning, verify_backup_integrity returns True for the latest object, and WAL segments appear in wal-archive/ at intervals of five minutes or less.

Do's and Don'ts

Now that you have worked through the implementation, the practices below separate a durable approach from a fragile one.

Do's

1. ✓Do verify gzip magic bytes (\x1f\x8b) immediately after every fput_object call — verify_backup_integrity catches silent upload corruption before the object enters the retention window; a dump that fails this check is indistinguishable from a valid backup until restore time, when it is too late.
2. ✓Do set archive_timeout = 300 alongside archive_command in postgresql.conf — without the timeout, a quiet database may leave the current WAL segment open indefinitely, stretching RPO far beyond the five-minute target even though archiving is technically enabled.
3. ✓Do use pg_dump -Fc (custom format) rather than plain SQL — custom format supports parallel restore via pg_restore -j, compresses internally, and allows selective table restoration; plain SQL dumps require a full sequential replay and cannot be partially restored without manual surgery.

Don'ts

1. ✗Don't treat a successful fput_object upload as proof of a valid backup — the MinIO client confirms the HTTP transfer completed, not that the gzip stream is intact; skipping verify_backup_integrity's magic-byte check means a truncated or corrupted dump sits silently in backups/postgres/ until a restore is attempted.
2. ✗Don't omit archive_mode = on from postgresql.conf when relying on archive_command for sub-five-minute RPO — archive_command is silently ignored while archive_mode is off, so WAL segments never reach wal-archive/ and the nightly pg_dump becomes the only recovery point, potentially losing hours of transactions.
3. ✗Don't leave /tmp/nightly-*.sql and /tmp/nightly-*.sql.gz files on the CronJob pod after a failed upload — run_backup removes both temp files only on the success path; an unhandled exception before os.remove fills the pod's ephemeral storage, causing subsequent CronJob runs to fail with no space left on device.