Restoring Postgres from an automated backup, and what breaks
The client had automated RDS snapshots running nightly for eleven months. The dashboard showed green. Nobody had ever restored one.
We did. It took forty minutes to get a writable database back, and another three hours to find the four things that only surface during a real drill.
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 read `DATABASE_URL` from Secrets Manager. That secret pointed at the production instance endpoint. The restored instance had a different endpoint. Nobody had write access to Secrets Manager except the lead developer, who was on leave that day. We had to rotate credentials through the AWS CLI and then trigger a redeploy so the app picked up the new value. In a real outage, this alone would have added twenty minutes.
Now we keep a `DATABASE_URL_STANDBY` secret in the same store, empty by default, that we populate 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 had `pg_trgm` and `uuid-ossp` enabled. The restored instance had them too — they ship with RDS. But a migration six months earlier had also added `pgcrypto`, and the snapshot we restored from was taken before that migration. The app's password-hashing function called `crypt()`, which lives in `pgcrypto`. Login started failing silently. The health check hit the database, ran `SELECT 1`, returned healthy, and the auth service kept returning 500s.
We now run `SELECT * FROM pg_available_extensions WHERE installed_version IS NOT NULL` as part of the drill and diff it against the list in our runbook. The runbook lives next to the code, not in a wiki nobody checks.
**Foreign data wrappers.** This one was subtle. The production database had a foreign server pointing at a legacy MySQL instance for read-only product catalog lookups. The FDW connection used a hardcoded IP allowlist on the MySQL side. The restored RDS instance had a different private IP. The FDW object existed, queries against it hung for thirty seconds and timed out. The catalog pages loaded but the search-as-you-type kept spinning.
We added the restored instance's IP to the MySQL allowlist during the drill. In a real failover we would need someone with MySQL access, which sits with a different team. We now have a documented contact and a runbook step that says: add the IP before you point the app at the restored instance.
**Application-level caches.** Redis was still serving stale lookups keyed by product IDs that no longer existed after the restore point. The cache had no TTL on those keys. Users saw products that the database said were deleted. We flushed Redis, which fixed it, but also wiped the rate-limit counters and logged everyone out.
The fix was not technical. It was 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 run this drill every quarter now. Each time we 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.