# Backup & disaster recovery

> Set RPO and RTO, build immutable backups ransomware can't delete, restore a database to a point in time, and choose a DR strategy.

DevSecOps Engineer roadmap · Operate & recover · 20 min read · https://levelcode.org/roadmaps/devsecops/backup-and-recovery

Every other lesson in this roadmap tries to stop bad things happening. This one assumes they will: someone runs a `DELETE` against the wrong database, ransomware encrypts every server it can reach, or a region goes down. Whether that's a bad afternoon or the end of the company depends on whether you can get your data and systems back, how much you lose, and how long it takes. This lesson covers recovery objectives, kinds of backup, point-in-time recovery for PostgreSQL, immutable backups that survive ransomware, key management, restore testing, and disaster recovery strategies.

## RPO and RTO

Two numbers define "recoverable" for each system, and the business should set them:

- **Recovery point objective (RPO)**: the most data, measured in time, you can afford to lose. RPO 15 minutes means at most the last 15 minutes of writes may be gone.
- **Recovery time objective (RTO)**: the longest the service may be down. RTO 2 hours means customers wait at most 2 hours.

**Worked example: RPO.** Acme's orders database is snapshotted nightly at 02:00. At 17:40 a storage failure corrupts it, and 15 hours 40 minutes of orders are lost. The worst case, a failure just before 02:00, loses almost 24 hours: that is the real RPO of a nightly backup. If the business needs "at most 5 minutes", nightly backups can't deliver it at any price; you need continuous log archiving.

**Worked example: RTO.** RTO covers every step from the alarm to customers being served, not just copying a file. Budget it with speeds you've *measured*, here for an 800 GB database:

| Step | Estimate |
|---|---|
| Detect, declare an incident, decide to restore | 20 min |
| Provision a new database host | 10 min |
| Download and extract the base backup (800 GB at 250 MB/s) | 53 min |
| Replay 15 hours of archived write-ahead log | 40 min |
| Validate, repoint the application, smoke test | 20 min |
| **Total** | **about 2 h 25 min** |

If the RTO is 1 hour, this design fails, and you've learned it before the disaster rather than during it. Fixes include more frequent base backups (less log to replay) or a running standby. Set objectives per system: the payment ledger and the wiki don't need the same answer, and tighter costs more.

## Kinds of backup

**Full, incremental and differential** describe how much each backup copies:

| Type | Copies | To restore Thursday (full on Sunday) |
|---|---|---|
| Full | Everything, every time | Thursday's full |
| Incremental | Changes since the previous backup of any kind | Sunday full + Mon, Tue, Wed, Thu incrementals |
| Differential | Changes since the last full | Sunday full + Thursday differential |

Incrementals are cheapest, but every link in the chain must be intact: one corrupt Tuesday incremental breaks every later restore. Deduplicating tools (restic, pgBackRest, Velero's data movers) make each backup logically full while storing only new data. PostgreSQL 17 added native incrementals: `pg_basebackup --incremental=<previous backup_manifest>` with `summarize_wal = on`, merged for restore with `pg_combinebackup`.

**How** you capture data matters as much:

- **Storage snapshots** (EBS, RDS, disk snapshots) copy blocks quickly, but they're *crash-consistent* unless the application is quiesced, and by default sit in the same account and region as the original. A snapshot the attacker can delete with the same credentials isn't a backup yet.
- **Logical backups** (`pg_dump`) export schema and rows. They're portable across versions and can restore one table, but are slow for large databases and capture only the moment the dump started.
- **Physical backups plus log archiving** copy the database files and then every change since, which enables point-in-time recovery.

```bash
pg_dump -Fc -d orders -f /backups/logical/orders-2026-10-11.dump
pg_restore -d orders_scratch -t customers /backups/logical/orders-2026-10-11.dump
```

The custom format (`-Fc`) is compressed and lets `pg_restore` pick objects, here just `customers` into a scratch database.

## Point-in-time recovery for PostgreSQL

PostgreSQL records every change in its **write-ahead log (WAL)** before changing data files. Keep a base backup and every WAL segment since, and you can replay history and stop at any moment: one second before the mistake. Enable archiving in `postgresql.conf`:

```ini
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/wal-archive/%f && cp %p /mnt/wal-archive/%f'
archive_timeout = 60
```

`%p` is the finished segment's path and `%f` its name; `test ! -f` refuses to overwrite. `archive_timeout = 60` forces a segment at least every minute, bounding RPO to about a minute. In production, use pgBackRest, WAL-G or Barman, which ship segments to object storage with compression, encryption and verification. Take base backups on a schedule:

```bash
pg_basebackup -h db1.internal -U replicator -D /backups/base/2026-10-08 \
  -Ft -z -Xs -P --checkpoint=fast
```

That writes compressed tar files, streams the WAL the backup needs to be consistent, shows progress, and starts without waiting for a checkpoint. Managed databases do this for you: RDS keeps continuous backups for up to 35 days, and `aws rds restore-db-instance-to-point-in-time` restores to any second in that window, always to a *new* instance.

### Worked example: recovering from an accidental DELETE

On 2026-10-08, a clean-up script meant for staging runs against production. The database log shows its `DELETE` committed at 14:32:07 UTC, removing 31,204 old orders and, through `ON DELETE CASCADE`, their items. New orders keep arriving.

Rolling the whole database back to 14:32 would also discard every legitimate order since. Instead, restore a **separate copy** to just before the mistake, pull the deleted rows out, and put them back. Step through the flow and notice that production never stops taking orders.

```sequence
title: Point-in-time restore after a bad DELETE
participants: Engineer, Production DB, WAL archive, Restore DB
note Production DB, WAL archive: 02:00 base backup; WAL archived every minute
Engineer -> Production DB: DELETE FROM orders WHERE ... (14:32:07)
> The script was meant for staging. 31,204 orders and their items are gone.
Production DB -> WAL archive: Segments containing the DELETE
> The archive records the mistake too. That's fine: we replay up to it, not past it.
Engineer -> Engineer: Declare incident, find commit time
> The time comes from the database log (log_statement = 'mod' or pgaudit) or the script's output.
Engineer -> Restore DB: Extract 02:00 base backup on new host
Engineer -> Restore DB: recovery_target_time = 14:32:00
Restore DB -> WAL archive: restore_command requests segments
WAL archive --> Restore DB: WAL from 02:00 to 14:32
Restore DB -> Restore DB: Replay, then pause at target
> The copy now looks exactly like production at 14:32:00, seven seconds before the DELETE.
Engineer -> Restore DB: \copy deleted rows to CSV
Engineer -> Production DB: \copy rows back, parents first
Production DB --> Engineer: 31,204 orders restored
> Orders placed after 14:32 were never touched, because production was never rolled back.
```

On a fresh host with PostgreSQL 17 (never the production server), extract the base backup:

```bash
sudo -u postgres mkdir -p /srv/restore/pgdata/pg_wal
sudo -u postgres chmod 700 /srv/restore/pgdata
sudo -u postgres tar -xzf /backups/base/2026-10-08/base.tar.gz -C /srv/restore/pgdata
sudo -u postgres tar -xzf /backups/base/2026-10-08/pg_wal.tar.gz -C /srv/restore/pgdata/pg_wal
```

Add recovery settings to the restored cluster's `postgresql.conf`. `archive_mode = off` matters: otherwise the copy would archive its own WAL into the *production* archive.

```ini
restore_command = 'cp /mnt/wal-archive/%f %p'
recovery_target_time = '2026-10-08 14:32:00+00'
recovery_target_action = 'pause'
archive_mode = off
port = 5433
```

Create the signal file that requests targeted recovery, and start the server:

```bash
sudo -u postgres touch /srv/restore/pgdata/recovery.signal
sudo -u postgres pg_ctl -D /srv/restore/pgdata -l /srv/restore/recovery.log start
```

```text
LOG:  starting point-in-time recovery to 2026-10-08 14:32:00+00
LOG:  restored log file "000000010000003A00000012" from archive
...
LOG:  recovery stopping before commit of transaction 9184431, time 2026-10-08 14:32:00.183+00
LOG:  pausing at the end of recovery
HINT:  Execute pg_wal_replay_resume() to promote.
```

Recovery stopped before the first commit after the target, an ordinary order at 14:32:00.183. While paused, the server answers read-only queries, which is all you need. Export the deleted rows, then load them into production, parent table first so foreign keys are satisfied:

```sql
-- psql -p 5433 -d orders   (the restored copy)
\copy (SELECT * FROM orders WHERE created_at < '2026-10-01') TO 'orders.csv' CSV
\copy (SELECT oi.* FROM order_items oi JOIN orders o ON o.id = oi.order_id WHERE o.created_at < '2026-10-01') TO 'order_items.csv' CSV

-- psql -h db1.internal -d orders   (production)
\copy orders FROM 'orders.csv' CSV
\copy order_items FROM 'order_items.csv' CSV
```

If the whole database were lost instead, you'd validate the copy, run `SELECT pg_wal_replay_resume();` to promote it, and point the application at it.

> [!TIP]
> Replicas are not backups: a streaming replica applies the `DELETE` within milliseconds. A **delayed replica** (`recovery_min_apply_delay = '1h'` on the standby) gives you an hour to stop replay before a mistake reaches it, but it complements PITR rather than replacing it.

## 3-2-1-1-0 and immutable backups

The classic rule is **3-2-1**: three copies, on two kinds of storage, one off-site. Ransomware added two digits: **1** copy offline, air-gapped or **immutable**, and **0** errors when you verify by restoring.

Ransomware operators target backups **first**. They often spend days or weeks inside a network beforehand, finding the backup console, deleting snapshots, shortening retention and disabling versioning. Only then do they encrypt production. So design as if the attacker holds production administrator credentials.

**Immutability.** S3 **Object Lock** stores objects as WORM (write once, read many): each version has a retention date before which nobody can delete or overwrite it.

```bash
aws s3api create-bucket --bucket acme-backups-vault --region eu-west-1 \
  --create-bucket-configuration LocationConstraint=eu-west-1 \
  --object-lock-enabled-for-bucket

aws s3api put-object-lock-configuration --bucket acme-backups-vault \
  --object-lock-configuration '{"ObjectLockEnabled": "Enabled", "Rule": {"DefaultRetention": {"Mode": "COMPLIANCE", "Days": 35}}}'
```

Enabling Object Lock at creation also turns on versioning, which it requires. Every new version is now locked for 35 days. **Governance** mode can be bypassed by principals with `s3:BypassGovernanceRetention`; **compliance** mode can't be shortened or bypassed by anyone, including the root user. Make retention longer than a likely dwell time plus time to notice. AWS Backup Vault Lock, Azure immutable blob storage and Google Cloud bucket locks offer the same guarantee.

> [!WARNING]
> Compliance mode means exactly that. A typo of 3,650 days instead of 35 locks you into paying for ten years of storage, and nobody can undo it. Try new settings in governance mode with a short retention first.

**Isolation.** Keep the vault in a **separate account** with its own administrators, reached only through break-glass (the cloud IAM lesson's pattern). Production gets a write-only path in, through the bucket policy:

```json
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ProductionBackupWriterPutOnly",
      "Effect": "Allow",
      "Principal": { "AWS": "arn:aws:iam::111122223333:role/orders-backup-writer" },
      "Action": "s3:PutObject",
      "Resource": "arn:aws:s3:::acme-backups-vault/orders/*"
    }
  ]
}
```

The production role can add backups but not list, read, delete or reconfigure anything, and new buckets enforce bucket-owner ownership, so its objects belong to the vault account. An SCP on the vault account stops even its own administrators weakening the protection outside break-glass:

```json
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ProtectBackupVault",
      "Effect": "Deny",
      "Action": [
        "s3:DeleteBucket",
        "s3:PutBucketPolicy",
        "s3:PutBucketVersioning",
        "s3:PutBucketObjectLockConfiguration",
        "s3:PutLifecycleConfiguration",
        "s3:BypassGovernanceRetention"
      ],
      "Resource": ["arn:aws:s3:::acme-backups-vault", "arn:aws:s3:::acme-backups-vault/*"],
      "Condition": {
        "ArnNotLike": { "aws:PrincipalArn": "arn:aws:iam::*:role/backup-breakglass" }
      }
    }
  ]
}
```

A true **air gap** (offline tape or disks) is the strongest isolation but makes restores slow; immutable storage in a separately administered account is the practical cloud equivalent.

## Encryption and key management

Backups hold everything an attacker wants, so encrypt them, client-side (pgBackRest, WAL-G and restic encrypt before upload) or with KMS. Then guard the keys as carefully as the data, because **a backup you can't decrypt is not a backup**.

- **Keep keys in the backup's security domain.** If vault backups use a KMS key in the production account, a production attacker can schedule its deletion (7 to 30 days' wait) and every backup becomes unreadable. Use a key owned by the vault account; give the production writer only `kms:GenerateDataKey` and the restore role `kms:Decrypt`.
- **Alert on key attacks**: `ScheduleKeyDeletion`, `DisableKey` and key policy changes. In 2025, attackers with stolen AWS keys re-encrypted S3 objects using customer-provided (SSE-C) keys only they held; deny SSE-C in bucket policies where you don't use it.
- **Plan for regional loss.** A KMS key lives in one region, so cross-region copies need a key in the destination region (AWS Backup copy jobs re-encrypt with the destination vault's key) or a multi-Region key.
- **Escrow passphrases offline**, in the break-glass safe with split custody. The engineer who set up the backup tool shouldn't hold the only copy.
- **Prove it on every restore test**, which is the only evidence the key still exists and works.

## Restore testing and game days

**A backup you haven't restored is a hope.** Jobs fail silently, archive commands break after upgrades, retention gets shortened, keys disappear, and runbooks reference servers that no longer exist. Test at three levels:

1. **Integrity checks**, continuously: job exit status, checksums, tool verification (`pgbackrest verify`, `restic check --read-data-subset=5%`). Alert when a job fails *or doesn't run*.
2. **Automated restore tests**, daily or weekly: restore into a scratch environment, check, time it, tear it down.
3. **Game days**, a few times a year: a full disaster scenario practised by the people on call.

This automated test for an RDS database measures what matters, the real restore time and the real data loss:

```bash
#!/usr/bin/env bash
# restore-test.sh: restore orders to its latest restorable time, check it, delete it.
set -euo pipefail
target="orders-restore-test-$(date -u +%Y%m%d)"
start=$(date +%s)
trap 'aws rds delete-db-instance --db-instance-identifier "$target" --skip-final-snapshot >/dev/null || true' EXIT

aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier orders \
  --target-db-instance-identifier "$target" \
  --use-latest-restorable-time \
  --db-subnet-group-name restore-test \
  --vpc-security-group-ids sg-0a1b2c3d4e5f67890 \
  --no-publicly-accessible --no-multi-az >/dev/null
aws rds wait db-instance-available --db-instance-identifier "$target"

host=$(aws rds describe-db-instances --db-instance-identifier "$target" \
  --query 'DBInstances[0].Endpoint.Address' --output text)
newest=$(psql "host=$host dbname=orders user=restore_check sslmode=require" -Atc \
  "SELECT extract(epoch FROM max(created_at))::bigint FROM orders")

echo "restore took $(( ($(date +%s) - start) / 60 )) min"
echo "newest order was $(( start - newest )) s before the test started"
(( start - newest < 900 )) || { echo "FAIL: data older than 15 minutes"; exit 1; }
```

The `trap` deletes the instance however the script exits; the read-only `restore_check` user's password comes from `PGPASSWORD` or `~/.pgpass`, loaded from a secrets manager. Run it on a schedule and alert if no test has *succeeded* in eight days. Restore into an isolated network: after ransomware, backups may contain the attacker's tooling, so scan restored systems before reconnecting them.

A **game day** tests what scripts can't: people, access and documentation. Pick a scenario ("the production account is lost", "the region is down", "someone dropped the `payments` schema"), give the on-call engineer only the runbook and break-glass access, and time it. You'll find a step needing a permission nobody has, or a restore twice as slow as estimated. Each finding becomes a tracked action item, as in incident response.

## DR strategies, infrastructure as code and GitOps

Backups protect data. **Disaster recovery (DR)** is the plan for running the whole service elsewhere when a site, region or account is lost:

| Strategy | Running in the recovery site | Typical RPO | Typical RTO | Cost |
|---|---|---|---|---|
| Backup and restore | Nothing; backups are copied there | Minutes to hours | Hours to a day | Lowest |
| Pilot light | Data replicated; core pieces (a database replica) running, app tier off | Seconds to minutes | Tens of minutes to hours | Low |
| Warm standby | A scaled-down but working copy, scaled up on failover | Seconds to minutes | Minutes | Medium |
| Multi-site active/active | Full capacity in two or more regions, all serving | Near zero | Near zero | Highest |

Choose per system from its RPO and RTO. Every strategy except the first relies on replication, which copies a bad `DELETE` or ransomware's encryption as faithfully as good data, so **every DR strategy still needs point-in-time, immutable backups**.

**Infrastructure as code is part of your backup.** Recovery means recreating networks, clusters, IAM, DNS and load balancers, and nobody remembers which console settings were clicked three years ago. If Terraform describes everything, rebuilding in a new region or account is `terraform apply` with different variables, provided you've prepared:

- **No click-ops drift**: anything created by hand can't be rebuilt. Catch drift with scheduled `terraform plan`.
- **Recovery code that doesn't depend on what it recovers**: Terraform state in a versioned bucket in another account, and Git repositories mirrored outside your primary Git host.
- **Parameterised regions and accounts**, with no hard-coded ARNs or AMI IDs.
- **Images and secrets in the recovery site**: registries and secrets replicated or restorable from the vault.
- **GitOps for workloads**: a new cluster pointed at the same repository by Argo CD or Flux converges on its own. Use Velero for what isn't in Git, such as persistent volumes:

```bash
velero schedule create orders-daily --schedule="0 2 * * *" \
  --include-namespaces orders --ttl 720h0m0s
velero backup get
velero restore create --from-backup orders-daily-20261008020012 --include-namespaces orders
```

- **A known order**: identity and break-glass access, then networking, data stores, the platform, applications, and finally DNS cut-over. Rebuild in that order on game days.

## Practice

Acme's `orders` service runs PostgreSQL 17 on a self-managed EC2 instance with 600 GB of data. A cron job runs `pg_dump` at 03:00 into an S3 bucket in the production account, versioning off. The business requires **RPO 5 minutes** and **RTO 2 hours**, and security requires backups to survive an attacker with production administrator access.

1. List every way the current setup fails.
2. Design a replacement, including the immutability configuration.
3. Restores run at a measured 300 MB/s and WAL replays at about 25 minutes per 10 hours of WAL. With daily base backups, is the RTO met? Show the budget.
4. Describe how you'll prove it keeps working.

**Solution**

**1. Failures.** A daily dump loses up to 24 hours of orders (RPO). Restoring a 600 GB logical dump, re-inserting rows and rebuilding every index, takes many hours (RTO). The bucket is in the production account with versioning off, so stolen admin credentials can delete every backup. And nothing proves the backups restore at all.

**2. Design.**

- WAL archiving with pgBackRest or WAL-G and `archive_timeout = 60` (RPO about a minute), plus daily physical base backups.
- Destination `acme-backups-vault` in a separate backup account, with Object Lock in compliance mode:

```bash
aws s3api create-bucket --bucket acme-backups-vault --region eu-west-1 \
  --create-bucket-configuration LocationConstraint=eu-west-1 \
  --object-lock-enabled-for-bucket
aws s3api put-object-lock-configuration --bucket acme-backups-vault \
  --object-lock-configuration '{"ObjectLockEnabled": "Enabled", "Rule": {"DefaultRetention": {"Mode": "COMPLIANCE", "Days": 35}}}'
```

- The production instance role may only `s3:PutObject` under `orders/*`; an SCP on the backup account denies policy, versioning, lock and lifecycle changes except for the break-glass role. Retention and pruning run from an identity in the backup account.
- Encryption with a key owned by the backup account (or client-side, passphrase escrowed in the break-glass safe), with alerts on `ScheduleKeyDeletion` and on failed or missed backup jobs.
- A cross-region copy for regional loss, and the `pg_dump` kept in the vault for single-table restores.

**3. RTO budget**, worst case just before the next base backup (about 24 hours of WAL):

| Step | Time |
|---|---|
| Detect and decide | 20 min |
| Provision host from Terraform | 10 min |
| Restore 600 GB at 300 MB/s (2,000 s) | 34 min |
| Replay 24 h of WAL (2.4 x 25 min) | 60 min |
| Validate and repoint the application | 20 min |
| **Total** | **about 2 h 24 min** |

It **misses** the RTO in the worst case. Base backups (or PostgreSQL 17 incrementals) every 6 hours cut replay to about 15 minutes and the total to about 1 h 40 min, which is the cheapest fix. A pilot-light replica in another availability zone would also help with hardware loss, but PITR is still needed for logical damage.

**4. Proof.** Alert if a base backup or WAL upload fails, or the newest archived segment is older than 5 minutes. Run a weekly automated restore into an isolated scratch instance with `recovery_target_action = 'pause'`, recording the achieved RPO (newest `created_at` against the test start) and RTO (elapsed time), and alert if no test has succeeded in 8 days. Hold a quarterly game day, "production account lost", restoring into a fresh account with only the runbook, break-glass access, the vault and Terraform, and track the findings as action items.

## Key takeaways

- RPO is how much data you can lose and RTO is how long you can be down; set them per system and check designs against them with measured numbers before a disaster.
- Snapshots, logical dumps and physical backups solve different problems; base backups plus archived WAL give point-in-time recovery to any second.
- Recover from a bad `DELETE` by restoring a separate copy to just before it and copying rows back, not by rolling production back over later good writes.
- Follow 3-2-1-1-0 and assume attackers hold production admin: keep compliance-mode, Object Lock copies in a separate account that production can only write to.
- Keep backup keys in the backup's own security domain and escrow passphrases offline; replication copies mistakes, so it never replaces backups.
- Test restores automatically and run game days; infrastructure as code, mirrored Git and GitOps make rebuilding a whole environment repeatable.

## Quiz

1. A database is backed up every night at 01:00 and nothing else is retained. What is its worst-case RPO?

   A. 1 hour
   B. Almost 24 hours
   C. The time it takes to restore the backup
   D. Zero, because the backup is complete

   **Answer: B.** A failure just before the next backup loses nearly a day of writes. RPO is measured by the worst case, not the average; restore duration is RTO, not RPO.

2. At 14:32 an engineer accidentally deletes 30,000 rows in production. Orders keep arriving. What is the best recovery approach?

   A. Fail over to the streaming replica, which still has the rows
   B. Restore production in place to 14:31, then ask customers to re-place later orders
   C. Restore a separate copy to just before 14:32 with PITR, export the deleted rows and insert them back into production
   D. Restore last night's pg_dump over production

   **Answer: C.** A side-by-side restore recovers the deleted rows without discarding legitimate writes made since. The replica applied the DELETE within milliseconds, and rolling production back loses good data.

3. Ransomware operators have production administrator credentials. Which backup design is most likely to survive?

   A. Daily EBS snapshots in the same account with a 30-day lifecycle
   B. A streaming replica in a second region
   C. Backups on a file share mounted on every server
   D. Backups in a separate account, under S3 Object Lock compliance mode, written by a role that can only PutObject

   **Answer: D.** Production credentials can delete same-account snapshots, the replica copies the encryption, and the mounted share gets encrypted too. Compliance-mode locks in a separately administered account can't be deleted even by an administrator.

4. Backups in the vault account are encrypted with a KMS key that lives in the production account. What is the main risk?

   A. An attacker in production can schedule the key for deletion, making every backup unreadable once it's gone
   B. KMS keys can't be used across accounts
   C. Restores will be slower because of cross-account calls
   D. Object Lock doesn't work with KMS-encrypted objects

   **Answer: A.** Immutable ciphertext is useless without its key. Keys belong in the backup's own security domain, with deletion alerts, and restore tests prove they still work.