Restoring Postgres from an automated backup, and what breaks
Picture a typical setup: automated RDS snapshots running nightly for about a year. The dashboard shows green. Nobody has ever restored one.
Run a restore drill against that setup and the restore itself takes perhaps forty minutes to get a writable database back. Finding the things that only surface during a real drill takes the rest of the day. Here are four we have seen come up, in one form or another, more than once.
The restore itself is the easy part. Point-in-time recovery to a new RDS instance, wait for it to become available, connect. The work is everything around it.
**Connection strings.** The application reads `DATABASE_URL` from Secrets Manager. That secret points at the production instance endpoint. The restored instance has a different endpoint. And in a small team, the one person with write access to Secrets Manager is on leave that day. So you rotate credentials through the AWS CLI and then trigger a redeploy so the app picks up the new value. In a real outage, this alone adds twenty minutes.
The fix we recommend is to keep a `DATABASE_URL_STANDBY` secret in the same store, empty by default, that gets populated during a drill or a real failover. The application reads it on startup if present. One less decision to make at 2 AM.
**Extensions.** The production database has `pg_trgm` and `uuid-ossp` enabled. The restored instance has them too — they ship with RDS. But a migration six months earlier also added `pgcrypto`, and the snapshot you restored from was taken before that migration. The app's password-hashing function calls `crypt()`, which lives in `pgcrypto`. Login starts failing silently. The health check hits the database, runs `SELECT 1`, returns healthy, and the auth service keeps returning 500s.
We run `SELECT * FROM pg_available_extensions WHERE installed_version IS NOT NULL` as part of the drill and diff it against the list in the runbook. The runbook lives next to the code, not in a wiki nobody checks.
**Foreign data wrappers.** This one is subtle. Say the production database has a foreign server pointing at a legacy MySQL instance for read-only product catalog lookups. The FDW connection is allowed through a hardcoded IP allowlist on the MySQL side. The restored RDS instance has a different private IP. The FDW object exists, queries against it hang for thirty seconds and time out. The catalog pages load but the search-as-you-type keeps spinning.
During a drill you add the restored instance's IP to the MySQL allowlist and move on. In a real failover you need someone with MySQL access, which often sits with a different team. So the runbook needs a documented contact and a step that says: add the IP before you point the app at the restored instance.
**Application-level caches.** Redis is still serving stale lookups keyed by product IDs that no longer exist after the restore point. The cache has no TTL on those keys. Users see products that the database says are deleted. Flushing Redis fixes it, but also wipes the rate-limit counters and logs everyone out.
The fix is not technical. It is a line in the runbook: after restore, flush Redis, then expect a spike in login traffic and tell the on-call person to watch the auth service for ten minutes.
We recommend running this drill every quarter. Each time, expect to find something small — a Lambda that hardcodes the database endpoint, a Grafana panel that queries production by name, a cron job that writes back to the source instance. None of these show up in the backup dashboard. They only show up when you actually restore and point something at the result.
The drill takes half a day. The alternative is finding out during a real outage that your backup is technically valid and operationally useless.