RDS / Aurora PITR + snapshot strategy — the runbook you actually drill

The customer says “we have snapshots, we’re covered.” The snapshots run nightly, retain for 7 days, and stay in the source region. The customer has never restored from one. Three months later, the wrong DBA dropped the wrong table at 14:30 on a Thursday and the application went sideways. The 02:00 snapshot is 12 hours old, the team has not done a restore drill, and the application owner needs the data back to within the last hour. PITR (Point-In-Time Recovery) is the feature that covers that gap, and the strategy around it — retention, cross-region, restore drills — is the difference between “we are covered” and “we are actually covered.”

What’s happening

RDS and Aurora ship four overlapping backup mechanisms. Each one solves a different recovery scenario.

Automated snapshots. Daily storage-level snapshots, kept for the BackupRetentionPeriod you set (1–35 days). Restored as a new database, point-in-time-of-snapshot, in the source region.

PITR (Point-In-Time Recovery). Enabled by default whenever BackupRetentionPeriod is at least 1. RDS keeps transaction logs continuously and lets you restore to any second inside the retention window. The restore is to a new instance — you cannot restore in-place. Restore time depends on the cluster size and the distance from the nearest snapshot.

Manual snapshots. On-demand snapshots that you keep until you explicitly delete them. They survive instance deletion, unlike automated snapshots which are deleted with the instance after a 7-day grace period. Used for milestones (pre-migration, pre-major-upgrade) and for long-term retention beyond the automated window.

Cross-region snapshot copies. Manual or automated copy of a snapshot to another region. Required for any DR scenario where the source region is unavailable. The copy is incremental after the first one, but the first one is a full copy and can take hours for large databases.

The recovery scenarios these cover:

The strategy fails in three common ways. First, the retention is too short — BackupRetentionPeriod of 1 day means “if we do not notice the problem by tomorrow morning, the data is gone.” Second, the snapshots stay in the source region — a regional event takes the database and the backups together. Third, no one has actually restored from a snapshot, so the day a real restore is needed, the team learns that the IAM permissions are missing, the security group references are stale, or the parameter group is gone.

How to verify

Check the current backup posture:

aws rds describe-db-instances \
  --db-instance-identifier prod-db-1 \
  --query 'DBInstances[].[DBInstanceIdentifier,BackupRetentionPeriod,LatestRestorableTime,BackupWindow,CopyTagsToSnapshot]' \
  --output table

For Aurora:

aws rds describe-db-clusters \
  --db-cluster-identifier prod-aurora-1 \
  --query 'DBClusters[].[DBClusterIdentifier,BackupRetentionPeriod,EarliestRestorableTime,LatestRestorableTime,PreferredBackupWindow]' \
  --output table

LatestRestorableTime minus EarliestRestorableTime is your real PITR window. If LatestRestorableTime is more than a few minutes behind now, the transaction log is lagging — common during heavy writes or during a snapshot operation, but worth investigating if it persists.

List the snapshots in source region:

aws rds describe-db-snapshots \
  --db-instance-identifier prod-db-1 \
  --snapshot-type automated \
  --query 'DBSnapshots[].[DBSnapshotIdentifier,SnapshotCreateTime,Status,EngineVersion]' \
  --output table

And in the DR region:

aws rds describe-db-snapshots \
  --region us-west-2 \
  --query 'DBSnapshots[?contains(DBSnapshotIdentifier, `prod-db-1`)].[DBSnapshotIdentifier,SnapshotCreateTime,Status,SourceRegion]' \
  --output table

If the DR-region query returns empty, you do not have cross-region recovery.

Confirm KMS keys cross-region (a frequent gotcha). Snapshots encrypted with a source-region CMK cannot be copied to another region without re-encrypting with a destination-region CMK:

aws rds describe-db-snapshots \
  --db-instance-identifier prod-db-1 \
  --query 'DBSnapshots[?Encrypted==`true`].[DBSnapshotIdentifier,KmsKeyId]' \
  --output table

The KMS key in the output is a source-region key. For cross-region copy, the destination needs a CMK in the destination region, and the copy command specifies it.

The fix

The quarterly restore drill — the runbook the team has to walk through whether they want to or not.

Step 1 — Pick a time and tell stakeholders. A drill restores to a new instance; it does not affect production. Schedule a 2-hour window. Notify the application owner so they do not panic when the drill instance appears in the console.

Step 2 — Pick the target. A point-in-time 4 hours ago. Recent enough to test real workload data, old enough that the PITR is testing the WAL replay path and not just “restore the latest snapshot.”

Step 3 — Restore. For RDS:

aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier prod-db-1 \
  --target-db-instance-identifier prod-db-1-drill-$(date +%Y%m%d) \
  --restore-time "$(date -u -v-4H +%Y-%m-%dT%H:%M:%S)" \
  --db-subnet-group-name drill-subnet-group \
  --vpc-security-group-ids sg-drill \
  --db-parameter-group-name prod-db-1-pg \
  --no-publicly-accessible

For Aurora:

aws rds restore-db-cluster-to-point-in-time \
  --source-db-cluster-identifier prod-aurora-1 \
  --db-cluster-identifier prod-aurora-1-drill-$(date +%Y%m%d) \
  --restore-to-time "$(date -u -v-4H +%Y-%m-%dT%H:%M:%S)" \
  --db-subnet-group-name drill-subnet-group \
  --vpc-security-group-ids sg-drill

Followed by an instance creation inside the cluster:

aws rds create-db-instance \
  --db-instance-identifier prod-aurora-1-drill-writer \
  --db-cluster-identifier prod-aurora-1-drill-$(date +%Y%m%d) \
  --db-instance-class db.r6g.large \
  --engine aurora-postgresql

Step 4 — Verify. Connect, run a smoke test query, confirm the data is at the expected point in time:

SELECT max(created_at) FROM orders;
-- should be approximately 4 hours ago
SELECT count(*) FROM users;
-- should be close to but slightly less than production

Step 5 — Time it. Total time from API call to “application can connect and read data” is the recovery RTO for this scenario. Write it down. The drill that takes 90 minutes is informative; the drill that takes 8 hours because IAM and security groups were misconfigured is a problem to fix before the next drill.

Step 6 — Tear down. The drill instance is throwaway. Delete it with --skip-final-snapshot:

aws rds delete-db-instance \
  --db-instance-identifier prod-db-1-drill-$(date +%Y%m%d) \
  --skip-final-snapshot \
  --delete-automated-backups

Three things that bite during real restores:

The snapshot-during-failover gotcha. An automated snapshot scheduled to start at 02:00 cannot start if a failover is in progress. The snapshot is delayed, sometimes by hours. The LatestRestorableTime keeps advancing because PITR continues, but the new snapshot does not appear. If the team checks “we have a snapshot from this morning” without verifying the time, they discover the gap during the next incident.

Parameter group drift. A PITR restore creates a new instance with the default parameter group unless you pass --db-parameter-group-name. A production tuned with custom parameters restores to a vanilla configuration and exhibits different performance. The drill catches this; production restores under stress often miss it.

Cross-region KMS permissions. Cross-region snapshot copy requires KMS grants on both the source and destination keys. The IAM principal performing the copy needs kms:CreateGrant on the destination key, kms:Decrypt on the source key, and the destination key must allow the RDS service principal. About one in three cross-region setups we audit have one of those three missing.

The retention strategy we run as default for production:

• BackupRetentionPeriod: 14 days (the operational PITR window).
• Daily automated snapshot copy to a second region (the regional-disaster window).
• Weekly manual snapshot, retained for 12 months (the long-term-compliance window).
• Quarterly manual snapshot, retained for 7 years (the audit window).

The drill cadence:

• Quarterly: PITR drill to a 4-hour-ago point in source region. Time the recovery.
• Annually: Full cross-region restore drill from the DR-region snapshot. Verify network, IAM, application connectivity.
• Annually: Long-term-snapshot integrity check — pick a 6-month-old manual snapshot, restore, verify the schema and a sample of data is intact.

Three rules we hold across every backup engagement:

A snapshot you have not restored does not exist. The team only knows the restore works when they have walked through it. The first restore drill on a new environment exposes more problems than the next twenty.

Cross-region is not optional for production. The cost of a daily snapshot copy is small. The cost of being region-locked during a regional event is the business.

Retention is a function of how long you might not notice a problem. A team that ships nightly and watches monitoring closely needs 14 days. A team that does monthly accounting reconciliation against historical data needs 90 days. Pick the number based on the team’s ability to detect issues, not the AWS default.

The whole strategy is mechanical once it is built. The hard part is the discipline to drill it quarterly when the team has other priorities. That is the part that pays off when the day comes.

Stack Harbor designs and operates production RDS / Aurora topologies as part of Managed AWS and the ongoing Managed Cloud Ops layer. If your database posture is “Multi-AZ checkbox + hope,” start with an infrastructure consult.