The Backup That Worked Until We Tried to Restore It

Two years of green checkmarks on the nightly backup job. Then we actually needed to restore data, and discovered the backups were missing half the schema.

The Slack message came in on a Thursday afternoon: "Can we restore the invoices table to how it looked yesterday morning?" A developer had run a data migration script against the production database. The script was supposed to backfill a new column on about 40,000 rows. Instead, due to a missing WHERE clause, it overwrote the status field on every invoice in the system. 280,000 records, all set to pending.

No big deal, right? That's what backups are for.

The client — a mid-size logistics company running their platform on PostgreSQL 15, hosted on RDS — had a nightly backup job that had been running for over two years. Every morning at 3 AM UTC, a cron job triggered pg_dump, compressed the output, and shipped it to an S3 bucket. The monitoring dashboard showed a solid green line stretching back to 2024. Not a single failure.

I pulled up the backup configuration to start the restore. That's when things got interesting.

The first surprise

The pg_dump command in the backup script looked like this:

pg_dump --no-owner --no-acl \
  --exclude-schema=audit \
  --exclude-schema=analytics \
  --exclude-table-data='logs.*' \
  "$DATABASE_URL" | gzip > "$BACKUP_FILE"

Whoever set this up — a contractor who'd left 18 months earlier — had excluded the audit and analytics schemas entirely. That's not unreasonable on its own. Audit logs were huge and analytics data was regenerated from event streams. But at some point after the backup script was written, someone had moved the invoice_line_items table into the audit schema. The reasoning in the migration PR was that line items were "audit-adjacent" since they were mostly used for compliance reporting.

So the invoices table was backed up, but the line items that made those invoices meaningful were not. They'd been silently excluded from every backup for the past eleven months.

The second surprise

We still had the invoices table in the backup, so we could at least restore the corrupted status values. I downloaded the most recent backup from S3 — the one from 3 AM that morning, about 14 hours before the bad migration.

Except it was gone. The S3 bucket had a lifecycle policy that moved objects to Glacier after 3 days and deleted them after 7. It was Thursday. The most recent backup available without a Glacier retrieval was from the previous Friday.

Glacier retrieval for a 12GB compressed dump? The expedited option would take 1-5 minutes per request, but the bucket wasn't configured for provisioned retrieval capacity, and our file was large enough to be split across multiple retrieval jobs. In practice, we were looking at 3-5 hours to get the data back.

Meanwhile, every invoice in the system showed pending. Customers calling support. The billing team manually correcting records one by one.

The third surprise

We initiated the Glacier retrieval and, while waiting, I asked the team if they'd ever actually tested a restore. Blank stares. Two engineers had been at the company for three years, and neither had ever seen anyone restore from backup.

When the Glacier retrieval finally completed and we tried to restore, we hit a schema mismatch. The backup was from Friday. Since then, two migrations had run: one added a payment_method column with a NOT NULL constraint, and another renamed customer_ref to customer_reference. The dump file referenced columns and constraints that no longer matched the live schema.

pg_restore doesn't merge data into an existing schema gracefully. You either restore into a clean database (destroying current data) or you extract the specific table data and try to INSERT it with column mapping. We ended up writing a custom script to pull the invoices table data from the old dump, map the column names, provide a default for the new NOT NULL column, and insert it row by row while checking for conflicts.

That script took four hours to write and test. The whole incident, from "can we restore?" to "data is fixed," took about eleven hours.

What we actually fixed

The immediate fire was out, but the backup situation needed real work. Here's what we changed:

Stopped using schema exclusions in the dump. The full database dump went from 12GB to 19GB compressed. Storage costs went up by about $40/month. Nobody argued.

Changed the S3 lifecycle policy. Daily backups now keep for 30 days in standard storage, then 90 days in Glacier. Monthly snapshots keep for a year. The cost difference was roughly $15/month.

Added automated restore testing. This was the real win. Every night, after the backup completes, a separate job restores it into a throwaway RDS instance, runs a handful of SQL assertions (row counts on critical tables, schema comparison against the live database, a few checksum queries on recent data), and tears down the instance. If any assertion fails, it pages the on-call.

-- One of the restore verification queries
SELECT
  table_schema,
  table_name,
  (xpath('/row/count/text()',
    query_to_xml(format('SELECT count(*) FROM %I.%I', table_schema, table_name),
    false, true, ''))
  )[1]::text::int AS row_count
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
  AND table_type = 'BASE TABLE'
ORDER BY table_schema, table_name;

The first time the restore test ran, it caught two more issues: a table with a circular foreign key that pg_dump handled fine but pg_restore choked on, and an extension (pg_trgm) that wasn't installed on the test instance. Both would have been surprises during a real disaster.

The uncomfortable pattern

I've seen this exact scenario — in different shapes — at four different clients now. The backup job works. The monitoring is green. Nobody tests the restore because the backup is "working." Then the day comes when you actually need the data back, and you discover the gap between "backup completed successfully" and "we can restore our system from this."

The gap is always something mundane. A schema exclusion that was reasonable when it was written. A lifecycle policy that's too aggressive. A migration that moved tables around. Column renames. Nobody's being negligent. It's just that backup configurations are set-and-forget by nature, while databases evolve continuously.

RDS automated snapshots help, and this client should have been leaning on them more heavily. But snapshots have their own blind spots — they're full-instance, so you can't easily extract a single table, and the default retention is 7 days. Point-in-time recovery is better but still limited by the retention window.

What actually works is treating restore capability as a system property that you test continuously, like uptime or latency. Not something you verify once during initial setup and then assume will keep working forever.

I keep thinking about those green checkmarks. Two years of them. Every one technically accurate — the backup job did complete successfully. But "the backup ran" and "we can recover from failure" turned out to be very different statements.