Timed database restore drills into an isolated target prove RTO and RPO

After you leave a Lovable or Bolt sandbox and own the backend, a backup file sitting in object storage is not a recovery plan. Recovery is a timed restore drill: restore into an isolated target, prove the app boots and critical reads succeed, then record measured RTO and RPO. Until that drill passes, the backup is fiction — a checkbox, not evidence.

This post is not “turn on daily backups” advice. It is the ops practice you owe once $DATABASE_URL points at infrastructure you control. Pair it with structured production logs so restore failures are triageable, and with audit trail events so who ran the drill is attributable. It is also not API client timeouts (outbound AI HTTP timeouts), uptime probes, or a kit how-to. The claim is narrow: restore on a schedule into a throwaway target, prove boot + reads, write the clock.

Why sandbox hosts hide restore risk

Sandbox platforms often advertise automatic snapshots and a restore button in their console. That is useful while the product lives on their host. Export the repo and you inherit schemas, migrations, and connection strings — plus a vague memory that “backups were on.” The first bad migration, dropped table, or region incident is when you learn nobody ever restored your dump into a fresh database and pointed a staging app at it.

Official database docs treat backup and restore as related but separate skills. PostgreSQL documents three families — SQL dumps, filesystem-level copies, and continuous archiving with point-in-time recovery — and warns that each has different restore assumptions (Backup and Restore). Managed providers similarly restore to a new instance, not by overwriting production in place (Amazon RDS restore from snapshot). Your drill should mirror that: isolated target first, production last.

Define RTO and RPO before you touch a dump

NIST contingency planning treats recovery objectives as outputs of a business impact analysis: how long the organization can tolerate downtime (RTO) and how much data loss it can accept (RPO), then select backup and recovery methods that meet those numbers (NIST SP 800-34 Rev. 1 overview; ITL bulletin on contingency planning). Testing is not optional theater — NIST calls plan testing a critical element that validates components and surfaces deficiencies before a real outage.

Write three numbers for your app, not generic “24h”:

1. RPO target — max acceptable data loss (for example 15 minutes of committed writes).
2. RTO target — max time from “declare restore” to “app serves critical reads.”
3. Drill cadence — how often you prove both (weekly for early production; at least after every backup-path change).

If your only backup is a nightly logical dump and you promise a 5-minute RPO, the numbers disagree. Fix the backup method or the promise — do not paper over the gap with a green “backup enabled” badge.

Prefer an isolated restore target every time

A restore that overwrites the live primary is not a drill; it is an incident. Managed snapshot restores create a new DB instance you name separately (RDS restore from snapshot). Logical dumps restore into a freshly created empty database. Keep credentials, network ACL, and app config for the drill under $RESTORE_TARGET_URL — never your production $DATABASE_URL.

Minimum isolation checklist:

• New database name / instance identifier (not the prod name).
• Separate credentials with restore rights only on the drill target.
• App process (or one-shot job) that can boot with DATABASE_URL=$RESTORE_TARGET_URL.
• Network path that cannot write back to prod (read-only prod snapshot source is fine; write ACL to prod is not).

Tear the target down after you record evidence. Leftover restore instances become stale secrets and surprise cost.

Run the timed drill with real commands

Pick one primary path and time it end-to-end. Start the clock when you begin restore; stop when the app health check and critical reads pass.

Logical dump path (good for smaller DBs and portable drills). PostgreSQL’s pg_dump produces consistent exports; custom format pairs with pg_restore for flexible reload (pg_dump):

# Capture (prod-read credentials; write only to backup object store)
pg_dump "$DATABASE_URL" -Fc -f "/tmp/app-$(date -u +%Y%m%dT%H%M%SZ).dump"
# Upload artifact to $BACKUP_OBJECT_URI (your object-store CLI — path/env only)

# Drill restore into empty target (never prod)
createdb "$RESTORE_DB_NAME"   # or provider equivalent against $RESTORE_TARGET_URL
pg_restore -d "$RESTORE_TARGET_URL" --clean --if-exists "/tmp/drill.dump"

Managed snapshot path (good when the provider owns physical backups). Restore snapshot → wait until status is available → point the drill app at the new endpoint. RDS notes the instance may still lazy-load storage in the background after available; for critical tables, force a full scan so cold pages do not surprise you later (RDS restore from snapshot).

Continuous archive / PITR path (when RPO is minutes). PostgreSQL continuous archiving restores a base backup, then replays archived WAL to a recovery target — a different procedure than loading a pg_dump script (Continuous Archiving and PITR). If that is your production strategy, the drill must exercise that path, including restore_command and a stop before “now” so you prove intentional RPO, not only “latest.”

Record wall-clock for: fetch backup → restore complete → migrations check (expect none, or a documented no-op) → app boot → proof queries.

Prove app boot and critical reads — not just “psql connected”

A green SELECT 1 is necessary and insufficient. After restore, boot the same binary you ship, with env pointed at $RESTORE_TARGET_URL, and run a short proof suite your product owns:

export DATABASE_URL="$RESTORE_TARGET_URL"
# Start app (or migrate-check + one-shot server) against the restore target only
curl -fsS "$RESTORE_APP_HEALTH_PATH"   # path-only; host from env

# Critical reads — replace with your real invariants
psql "$RESTORE_TARGET_URL" -c "SELECT count(*) FROM users WHERE deleted_at IS NULL;"
psql "$RESTORE_TARGET_URL" -c "SELECT id FROM subscriptions WHERE status = 'active' LIMIT 5;"

Fail the drill if: restore errors, schema mismatch the app cannot start with, empty tables that should have rows, or health check fails. Pass only when boot + invariants succeed inside the RTO budget. Log correlation IDs on the drill run the same way you log production requests so agents can triage a failed restore without spelunking raw provider consoles.

Write RTO/RPO evidence you can cite later

NIST’s contingency process ends with testing, training, and maintenance — plans stay living documents updated when systems change (ITL bulletin). Your drill report is that maintenance artifact for the database layer. Store it beside the backup object (or in the same ops repo), not in a chat thread:

drill_id: 2026-09-20T15-10Z
backup_object: $BACKUP_OBJECT_URI
backup_taken_at: 2026-09-20T14:00:00Z
restore_started_at: 2026-09-20T15:10:00Z
restore_ready_at: 2026-09-20T15:27:00Z
app_boot_ok_at: 2026-09-20T15:29:00Z
proof_reads: users_count=18422 subscriptions_sample=5
measured_rto_minutes: 19
measured_rpo_minutes: 70   # wall clock from backup_taken_at to incident/drill declare
rto_target_minutes: 30
rpo_target_minutes: 15
result: FAIL_RPO   # backup cadence too coarse for target — fix schedule or method
operator: drill-bot
notes: isolated target only; prod untouched

A FAIL that surfaces “nightly dump cannot meet 15-minute RPO” is a successful drill. A PASS with no numbers is not. Keep product docs citation-ready when you publish internal SLOs so the team cites the same measured values.

Fold the drill into your launch checklist before strangers depend on the data: backup method chosen, isolated restore proven once, RTO/RPO recorded, next drill scheduled. When you want a starting kit that already owns auth, billing, and a real database boundary you can attach this practice to, browse OTF templates — the restore discipline still lives in your runbooks either way.

Own the clock. An object in $BACKUP_OBJECT_URI is inventory; a timed restore into $RESTORE_TARGET_URL with boot proof and written RTO/RPO is the recovery plan.

Sources

• PostgreSQL Backup and Restore
• PostgreSQL Continuous Archiving and PITR
• PostgreSQL pg_dump
• Amazon RDS: Restoring from a DB snapshot
• NIST SP 800-34 Rev. 1 — Contingency Planning Guide
• NIST ITL Bulletin: Contingency Planning for Information Systems (July 2010)