# Database Backups: Testing Restores, Not Just Taking Them URL: https://www.devopsness.com/blog/database-backup-restoration-testing-restores Author: Kiril Urbonas Published: 2026-05-14 Updated: 2026-10-31 Categories: Infrastructure Tags: Database, Backups, Disaster Recovery, Postgres, Infrastructure Summary: Backups are easy. Restores are hard. A quarterly restore drill, what typically breaks during it, and the discipline that makes "we have backups" actually mean something. --- # Database Backups: Testing Restores, Not Just Taking Them Test backups by restoring them: once a quarter, restore a real production database to a fresh instance with point-in-time recovery, validate the data with queries, time every phase, and tear it down. A backup you have never restored is a hypothesis. The drill takes one engineer about half a day and turns "we have backups" into a measured recovery time. > [!VERIFIED] > PostgreSQL 18 (docs for 18.6), AWS CLI v2 `rds` commands, against the Amazon RDS point-in-time restore, backup retention and RestoreDBInstanceToPointInTime API docs and the PostgreSQL `pg_restore` reference. Checked October 2026. For a long time our honest answer to "when did you last restore one?" was "when we set it up." After a few near-misses we started drilling restores every quarter. This is what the drills found, and the exact commands we run. ## Three ways backups fail silently **The data inside is unusable.** A half-applied snapshot, a stale replica being backed up, a logical dump that errored halfway. The file exists, so every dashboard is green. **The restore path is broken.** Permissions drifted, the restore role can't decrypt the KMS key, the script calls a tool nobody installed on the new bastion. The backups are fine; you can't get the data out. **The data is too old.** The nightly snapshot ran at 02:00, the bad deploy shipped at 23:00, and nobody had practiced point-in-time recovery. That is an RPO mismatch, and the business only learns its real RPO during the incident. None of these show up until you restore. ## The drill, as commands This is the RDS for PostgreSQL version. The same shape works on Cloud SQL or self-managed Postgres with pgBackRest; only the restore command changes. First, check what you can actually restore to. RDS uploads transaction logs to S3 every five minutes, so `LatestRestorableTime` is usually a few minutes behind now: ``` $ aws rds describe-db-instances \ --db-instance-identifier orders-prod \ --query 'DBInstances[0].[BackupRetentionPeriod,LatestRestorableTime]' \ --output text 7 2026-10-31T09:40:00+00:00 ``` Then restore to a point one hour back, which exercises the snapshot plus log replay. Pass the subnet group, security group and parameter group explicitly, because the API docs say the restored instance otherwise gets the default security group, the default subnet group, the default parameter group, and single-AZ: ``` $ aws rds restore-db-instance-to-point-in-time \ --source-db-instance-identifier orders-prod \ --target-db-instance-identifier orders-drill-2026q4 \ --restore-time 2026-10-31T08:40:00Z \ --db-subnet-group-name prod-db-private \ --vpc-security-group-ids sg-0drill1234 \ --db-parameter-group-name orders-pg18 \ --no-publicly-accessible \ --tags Key=purpose,Value=restore-drill $ time aws rds wait db-instance-available \ --db-instance-identifier orders-drill-2026q4 ``` The `time` output is your first real RTO number. Write it down. Note that "available" is not "fast": AWS says restored volumes keep loading blocks from S3 in the background, so the first queries will be slow until initialization finishes. ## What "validated" means This is the step most teams skip, and it's the one that catches the bad backups. Run it against both the restored instance and a production replica, and compare: ``` -- drill/validate.sql SELECT 'orders' AS tbl, count(*) FROM orders UNION ALL SELECT 'customers', count(*) FROM customers UNION ALL SELECT 'payments', count(*) FROM payments; -- canary rows that must exist with known values SELECT id, status, total_cents FROM orders WHERE id IN (1000001, 4200042); -- every index and constraint came back SELECT count(*) AS indexes FROM pg_indexes WHERE schemaname = 'public'; SELECT conrelid::regclass, conname FROM pg_constraint WHERE NOT convalidated; -- the newest write you expect at the target time SELECT max(created_at) FROM orders; ANALYZE; ``` Row counts should match within the writes that happened after the target time. `max(created_at)` tells you whether you actually landed at 08:40 or at last night's snapshot. Any row from the `convalidated` query is a constraint that came back as `NOT VALID`. Then run `ANALYZE`, because planner statistics on a fresh restore are stale and your first application query will plan badly. Finally, time the teardown and write the one-page summary: ``` $ aws rds delete-db-instance \ --db-instance-identifier orders-drill-2026q4 \ --skip-final-snapshot --delete-automated-backups ``` ## Logical dumps need their own drill If you also keep `pg_dump` archives (we do, for single-table recovery and cross-version moves), restore one too. Use the custom format so `pg_restore` can list and parallelize it: ``` $ pg_dump -Fc -d "$PROD_URL" -f orders-2026-10-31.dump $ pg_restore --list orders-2026-10-31.dump | head $ pg_restore --jobs=4 --no-owner --exit-on-error \ -d "$DRILL_URL" orders-2026-10-31.dump ``` `--exit-on-error` matters. The PostgreSQL docs state the default is to continue and print an error count at the end, which is how a "successful" restore quietly skips a table. `--jobs` only works with custom or directory format and can't be combined with `--single-transaction`. ## Errors the drills surfaced - `KMSKeyNotAccessibleFault` ("An error occurred accessing an AWS KMS key"): the role running the restore can't use the key the snapshot was encrypted with. We now keep a dedicated drill role with the exact `kms:` grants, and test it every quarter. - `PointInTimeRestoreNotEnabled`: the source has `BackupRetentionPeriod` of 0. The RDS docs say an instance created via API or CLI without the setting defaults to one day, and the console defaults to seven. Check every instance; Terraform modules copied from examples are a common source of 0 or 1. - `DBInstanceAlreadyExists`: last quarter's drill instance was never deleted. Tag drill instances and alert on any older than a day. - `pg_restore: error: input file appears to be a text format dump. Please use psql.`: someone ran `pg_dump` without `-Fc`. Plain SQL dumps restore with `psql`, not `pg_restore`. - `ERROR: role "app_owner" does not exist` during `pg_restore`: ownership commands need roles that don't exist on the target. Use `--no-owner`, or create the roles first with `pg_dumpall --roles-only`. ## The cadence and the monitors Quarterly drills, plus one cross-region restore a year, because that path has its own surprises: snapshot copy time, cross-region KMS key policy, and the DNS or connection-string change the application needs. Between drills, four cheap monitors catch the easy failures: the backup job completed by a deadline, the newest backup is under 25 hours old, backup size hasn't dropped sharply (a truncated dataset looks like a smaller backup), and cross-region copy lag is under a day. These catch scheduler failures. Only restores catch corruption and broken restore paths. Replicas are not backups. A `DELETE` without a `WHERE` on the primary reaches every replica in milliseconds. You want replicas for availability and read scaling, and backups for rewinding time. If you want to make that delete less likely in the first place, we wrote up [guardrails against deleting a production database](/blog/prevent-deleting-production-database). ## Where this breaks RDS automated backups max out at 35 days of retention, so anything older needs manual snapshots or AWS Backup with its own drill. A PITR restore always creates a new instance; it never rewinds the existing one, so the application cutover (endpoint, secrets, connection pools) is a separate runbook you also have to practice. On very large databases the "half a day" estimate is wrong: restore time scales with size, and the lazy block loading means performance tests on a fresh restore understate production speed. And none of this covers data you never backed up: Redis, object storage, or the SaaS tool holding your customer configs. ## Questions people ask ### How often should you test database backup restores? Quarterly is the minimum we'd accept for production databases, with a cross-region restore once a year. Less often and the drill turns into a project because tooling and permissions have drifted. Run an extra drill after any change to encryption keys, IAM roles, backup retention, or the database's major version, since those are the changes most likely to break the restore path. ### How do I verify a Postgres backup is actually good? Restore it to a separate instance and query it. Compare row counts on key tables with production, check a few known canary records, confirm the index count matches, look for constraints with `convalidated = false`, and check `max(created_at)` to confirm the restore landed at the intended time. A completed restore job alone proves nothing about the data. ### What is the difference between RTO and RPO in a restore drill? RPO is how much data you can lose, measured as the gap between the last recoverable point and the failure. RTO is how long recovery takes, from decision to a working application. A drill measures both: `LatestRestorableTime` shows your achievable RPO, and timing restore, validation and cutover gives a real RTO instead of a guess. ## The call we'd make Put a quarterly restore drill on the calendar with a named owner, script it with the commands above, and publish the measured restore time to the teams that depend on the database. Drill point-in-time recovery specifically, because the snapshot path and the log-replay path fail independently. A sentence of the form "we restored orders-prod to 08:40 last Tuesday and validated it in three hours" means something. "We have backups" does not. ## Sources - [Amazon RDS: restoring a DB instance to a specified time](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PIT.html) - [Amazon RDS API: RestoreDBInstanceToPointInTime](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceToPointInTime.html) - [Amazon RDS: backup retention period](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithAutomatedBackups.BackupRetention.html) - [PostgreSQL documentation: pg_restore](https://www.postgresql.org/docs/current/app-pgrestore.html)