Database Backup and Recovery

Describe database backup strategies and how you would design a recovery plan for production databases.

Infrastructureintermediate

// interview question

Describe database backup strategies and how you would design a recovery plan for production databases.

Sample answer

Key backup types: 1) Full backups - complete database copy, resource-intensive. 2) Incremental - only changes since last backup. 3) Point-in-time recovery (PITR) - using transaction logs/WAL. Strategy: daily full backups + continuous WAL archiving for PITR. Store backups in separate region/account. Test restores regularly! Recovery plan: define RTO (Recovery Time Objective) and RPO (Recovery Point Objective), document restore procedures, automate where possible, and practice with chaos engineering.

Why this matters

Backups are worthless if you can't restore from them. Every organization has horror stories of corrupted backups or untested restore procedures. RTO defines how quickly you must recover, RPO defines maximum acceptable data loss. These requirements drive your backup strategy - if RPO is 5 minutes, you need continuous replication, not daily backups.

Code examples

PostgreSQL backup strategies

bash

# Logical backup (SQL dump)
pg_dump -h localhost -U postgres mydb > backup.sql

# Compressed backup
pg_dump -h localhost -U postgres -Fc mydb > backup.dump

# Physical backup with pg_basebackup
pg_basebackup -h localhost -U repl_user -D /backups/base \
    -Fp -Xs -P

# Restore from dump
pg_restore -h localhost -U postgres -d mydb backup.dump

# Enable WAL archiving in postgresql.conf
# archive_mode = on
# archive_command = 'aws s3 cp %p s3://bucket/wal/%f'

Kubernetes CronJob for backups

yaml

apiVersion: batch/v1
kind: CronJob
metadata:
  name: postgres-backup
spec:
  schedule: "0 2 * * *"  # Daily at 2 AM
  jobTemplate:
    spec:
      template:
        spec:
          containers:
          - name: backup
            image: postgres:15
            command:
            - /bin/sh
            - -c
            - |
              pg_dump -h $DB_HOST -U $DB_USER $DB_NAME | \
                gzip | aws s3 cp - s3://backups/$(date +%Y%m%d).sql.gz
          restartPolicy: OnFailure

Common mistakes to avoid

Never testing restore procedures until an actual disaster
Storing backups in the same region/account as production
Not encrypting backups containing sensitive data
Ignoring backup retention policies and running out of storage

Likely follow-ups

How do you test that backups are actually restorable?
What is the difference between RTO and RPO?
How do you handle backups for databases with terabytes of data?

Answer out loud first, then check yourself against the model answer.

Practice all Mid-Level questions More Infrastructure questions

#database#backup#disaster-recovery#postgres#devops

Also worth your time on this topic

Article

Auth for a Postgres App, Without a Separate Service

The usual way to add auth is to run a second system next to your database and spend forever keeping the two in sync. Neon Auth puts the auth server in the same project as Postgres: one line in a config file, one deploy, and the user who signs in is a row you can join to your own tables. Here is how it works and why the reconciliation tax disappears.

Checklist

High Availability Architecture Checklist

Comprehensive checklist for designing and implementing highly available systems with load balancing, failover, and redundancy.

90-120 minutes

Interview

Immutable Infrastructure

Explain immutable infrastructure and its benefits. How does it differ from traditional server management?

mid

Database Backup and Recovery

More Infrastructure interview questions

Also worth your time on this topic

Auth for a Postgres App, Without a Separate Service

High Availability Architecture Checklist

Immutable Infrastructure