Design a disaster recovery plan for a critical database that must survive the loss of a whole cloud region.

What the interviewer wants to hear

• Start from business RPO and RTO for a regional loss
• Cross-region asynchronous replica or managed global database, plus backups copied to another region
• Clear failover decision process, DNS or endpoint switch and application config
• Handle data loss window, split-brain and failback
• Regular DR drills that measure real RPO and RTO

Model answer

I would start by agreeing RPO and RTO for a regional disaster, which are usually looser than for a single server failure. Inside the main region I would keep HA across availability zones. For the region loss I would run an asynchronous replica in a second region, or a managed option like Aurora Global Database, because synchronous replication across regions adds too much commit latency. Backups and WAL or binlog archives would also be copied to the second region, encrypted, with separate credentials so one compromised account cannot delete both. The runbook defines who declares a disaster, how we promote the remote replica, switch DNS or endpoints and start application servers there, and how we fence the old primary to avoid split-brain. I would also plan failback and run drills twice a year, measuring the real RPO and RTO.

More hard Database Administrator questions

• Design a high-availability setup for a critical PostgreSQL database with RPO near zero and RTO under one minute.
• Your single database is reaching its limits. How do you decide whether to shard, and how would you do it?
• How would you upgrade a large production PostgreSQL database to a new major version with minimal downtime?
• The application is timing out and you suspect lock contention in production. Walk through how you diagnose and fix it.
• Someone ran DELETE without a WHERE clause on an important production table 20 minutes ago. What do you do?
• How would you migrate a 5 TB on-premises PostgreSQL database to a managed cloud service with less than 15 minutes of downtime?
• A critical 1 TB PostgreSQL table is 60 percent bloat and queries are slowing. How do you fix it without downtime and stop it coming back?
• The business expects traffic to grow ten times over the next 18 months. How do you plan database capacity?
• Your company must meet compliance rules (such as PCI DSS or GDPR) for a database holding personal and card data. What controls do you put in place?