How would you design a basic backup strategy for a production database?
What the interviewer wants to hear
- Define RPO and RTO with the business
- Combine full backups with incremental backups or log archiving
- Store copies off-site or in another region, encrypted
- Set retention rules
- Test restores regularly
Model answer
I start by asking the business two questions: how much data can we afford to lose (RPO) and how long can we be down (RTO). For a typical system I would take a full physical backup daily or weekly, plus continuous WAL or binlog archiving so I can do point-in-time recovery. Tools like pgBackRest or Percona XtraBackup handle full and incremental backups well. Backups are encrypted and copied to a different location, such as object storage in another region, so one failure cannot destroy both the database and its backups. I set a retention policy, for example 30 days. Most important, I schedule automatic restore tests, because a backup that has never been restored is only a hope, not a backup.
Can you answer it under time pressure?
Take a timed easy round and get scored on every answer.
More easy Database Administrator questions
- What is a database index, and what are the costs of adding one?
- Explain the ACID properties of a database transaction with a simple example.
- What is normalization, and when would you choose to denormalize?
- How do you find the slowest queries on a database server?
- What checks would you do at the start of a normal day as a DBA?
- What is database replication, and what is the difference between synchronous and asynchronous replication?
- When would you use a logical backup like pg_dump or mysqldump, and when a physical backup?
- What basic steps would you take to secure a new production database?
- Explain the main SQL join types and give an example of when you would use each.