PostgreSQL backup and restore runbook

Status: Approved procedure specification; scripts not yet implemented
RPO: 24 hours
RTO: two hours
Last updated: 2026-08-20

This runbook adapts Qlockify's logical dump, encrypted S3-compatible upload, local/remote retention, and restore flow while removing environment files from the backup and adding exact-destination/production confirmation guards.

Scope and exclusions

Back up authoritative PostgreSQL data and the minimum manifest required to validate it. Do not include:

Redis/AOF/cache/queue data
.env or secret files
deployment SSH and object-store credentials
backup decryption key
TLS private keys or ACME account data
raw challenge CSV archives
demo plaintext credential exports
frontend font/license files
Prometheus/Grafana runtime volumes or container logs

Redis loss is recovered through empty Redis plus idempotent analytics reconciliation/recomputation, not restore.

Backup package contract

Each encrypted artifact corresponds to one manifest with:

format_version
backup_id
created_at_utc
completed_at_utc
source_environment
source_host_id
postgres_server_version
dump_client_version
database_name
database_archive_sha256
database_archive_bytes
schema_migration_version
active_release_id
deployment_sha
backend_sha
frontend_sha
encryption_scheme_version

The manifest stores no password, connection URL, secret value, license identifier/text, raw font copy, CSV path/content, or merchant data sample. A checksum of the encrypted remote object is recorded separately from the plaintext archive checksum.

Scheduled backup procedure

Run daily at 02:00 UTC from the ubuntu user's reviewed host cron entry.

Acquire a backup lock that conflicts with restore and destructive migration but need not block ordinary application reads/writes.
Resolve and verify the exact production host/database identity. Validate free disk for temporary output plus the encrypted artifact.
Confirm PostgreSQL health, client/server major compatibility, active three-hash release id, and migration version.
Create a consistent PostgreSQL 18 plain-SQL dump using Qlockify's reviewed pg_dump --format=plain --clean --if-exists --no-owner --no-privileges pattern. Capture stderr and fail on a non-zero exit.
Reject an empty output, verify the expected PostgreSQL dump header/format, and calculate its SHA-256.
Build the non-secret manifest and calculate the plaintext archive SHA-256.
Encrypt the package before durable local retention with Qlockify's openssl enc -aes-256-cbc -salt -pbkdf2 -iter 200000 flow. The manifest records the scheme/version and SHA-256 integrity evidence because CBC is not authenticated encryption.
Upload over TLS to the private S3-compatible destination. Upload to a temporary object key, verify remote size/checksum/metadata, then finalize the immutable timestamped key.
Mark the backup successful only after remote verification. Record success timestamp, duration, and size in structured MVP evidence; export the same signals as metrics in the required post-MVP observability phase.
Retain the newest three verified encrypted local artifacts and seven verified encrypted remote artifacts. Delete stale artifacts only after the new remote object is verified; never delete the last known-good local or remote backup.
Release the lock and ensure temporary plaintext files are removed from the exact temporary directory.

A cleanup failure is a ticket, not a reason to mark the backup failed if the verified new copy exists. An upload/verification failure is a backup failure and preserves all prior copies.

Daily verification

last verified remote backup age is under 30 hours
archive/manifest/encrypted-object checksums and sizes are non-zero and agree
retention counts are at least one and no greater than approved limits after cleanup
backup duration/size are within expected bounds or investigated
no excluded filenames/classes appear in the package-content inspection
local backup directory and remote bucket are not publicly readable
decryption key is retrievable from separate escrow by authorized operators without exposing it in the check record

Clean-destination restore rehearsal

Run monthly and before any approved destructive migration.

Select an explicit backup id, not an unexamined implicit latest, and record why it was chosen.
Provision an empty non-production destination with compatible PostgreSQL and enough free space. Confirm its environment/hostname/database do not match production.
Download the exact encrypted object to a new restrictive temporary directory.
Verify remote-object checksum/size, decrypt, validate the package and plain-SQL checksum/header, and parse the manifest.
Treat the dump as trusted code only if it came from the approved bucket/path and passes provenance checks. PostgreSQL warns that restore executes statements controlled by the dump source.
Create a new empty database from template0 or the equivalent approved empty-cluster procedure. Do not reuse a partially populated database.
Restore with psql -X --set ON_ERROR_STOP=on and the reviewed transaction/owner/privilege behavior. Preserve complete stdout/stderr in a restricted evidence path without secrets.
Check out/build the selected release record if needed, then run its validation suite:
- expected migration/import manifests
- representative table/session/attempt counts and invariants
- at least two merchant logins and cross-tenant denial
- payer-card keys absent from API/log output
- Redis initially empty, analytics recomputation succeeds, four sections/SSE work
- frontend and API compatibility for the manifest's release
Record elapsed time, achieved RPO, achieved RTO, sizes/counts, validation result, operator, and remediation.
Destroy rehearsal resources only after evidence is retained and the exact destination is revalidated.

Production restore approval guard

Production restore is destructive/exclusive and always requires explicit human approval. The future implementation must require all of these independent inputs:

RESTORE_ENVIRONMENT=production
exact expected host identity matching the current provider instance
exact expected database name
explicit backup id/object key (no automatic latest)
a typed confirmation phrase containing both production and the backup id
active maintenance mode and deployment/restore lock
approved incident/change record and named operator
current-state backup when PostgreSQL is readable and doing so will not worsen the incident

If any input is absent or mismatched, exit before stopping writers, dropping/renaming a database, extracting over a target, or running psql.

Production restore sequence

Announce maintenance and stop public writes/new sessions. Preserve an operator-only health path.
Acquire the exclusive deployment/restore lock; stop worker before backend so no jobs write during restore.
Capture the active/previous three-hash release records, database identity, incident timestamps, and current-state backup when safe.
Perform all download/decrypt/checksum/archive/provenance checks from the rehearsal procedure before mutating database state.
Restore into a newly created empty database/cluster. Do not pipe an unchecked dump directly into the active production database and do not recursively delete a computed volume/path.
Validate restore completeness and application invariants using the three recorded repository commits compatible with the backup manifest. Apply only explicitly approved forward migrations.
Start with empty Redis. Start backend/worker, confirm readiness, idempotent stale-run reconciliation, cold-cache analytics, sessions, and SSE behavior.
Switch application connectivity to the validated restored destination using the approved implementation strategy.
Run public TLS/login/dashboard plus private database/queue/tenant smoke checks. Observe for 15 minutes.
Reopen traffic, mark the restore complete, and schedule a fresh backup. Retain the old database until the incident owner approves disposal under a separate exact-target procedure.

Do not automatically restore .env/secrets, TLS, Redis, or frontend assets from a database backup. Reconstruct platform configuration from Git/release records and secrets from separate escrow.

Failure handling

Failure	Action
Backup lock already held	Exit without starting a second backup; alert if stale
`pg_dump` non-zero, empty, or invalid plain-SQL output	Delete only the exact failed temporary artifact; keep prior backups; page on age threshold
Encryption failure	Do not persist/upload plaintext; secure exact temp path and investigate
Remote upload/checksum failure	Keep encrypted local artifact and all prior remote copies; bounded retry; alert
Retention cleanup failure	Keep extra copies; ticket; never broaden deletion globs/paths
Manifest/archive mismatch	Quarantine artifact and select another known-good backup
Restore SQL/object failure	Keep traffic closed, preserve logs/destination, do not rerun blindly into partial DB
Post-restore invariant failure	Do not switch/reopen; return to old database if safe or select another backup
Missing decryption key	Stop; encrypted backup is unrecoverable without the separately escrowed key
Wrong environment/host/database confirmation	Abort before mutation and record denied attempt

Acceptance evidence

Daily backup completed and verified remotely within 30 hours
Backup contains PostgreSQL archive plus non-secret manifest only
Three local/seven remote verified encrypted artifacts retained
Restore rehearsal uses an explicit backup id and clean non-production database
Archive trust/checksum/listing/compatibility checks precede restore
Application, tenant, session, cold-cache, worker, and SSE validations pass
Achieved RPO <=24 hours and RTO <=2 hours recorded
Every missing production confirmation factor aborts before mutation
No Redis, env, secrets, TLS keys, CSV, credentials, font/license, logs, or monitoring runtime data enter backup

10 KiB Raw Permalink Blame History