Skip to content
Katabench
Try free
8 min read The Katabench team

Measure RTO and RPO in a database restore drill

A restored database can open on time and still miss its recovery targets. Use a restore drill to measure lost writes and the time until users can work.

At 10:19 the recovered database accepts a connection. The team running the exercise stops the timer and records a 19-minute recovery. The warehouse clerk still cannot scan a parcel. The application has the old database address, and nobody has compared the recovered dispatches with the receipts issued before the outage. Both tasks belong on the timer.

Suppose the warehouse can handle a picking stoppage of 30 minutes before the next truck misses its slot. Supervisors can manually reconstruct up to five minutes of missing scans from local scanner records. Those are useful starting points for a recovery time objective (RTO) of 30 minutes and a recovery point objective (RPO) of five minutes. Each number describes a different cost. You can meet one while missing the other. AWS reliability guidance on recovery objectives.

Give the exercise a failure and a finish line

For this drill, assume a sudden loss of the primary store and its local replica at 10:00, with no writes accepted after the interruption. An independently accessible base backup and archived transaction log survive. The job is to recover dispatch records through a known point and get a clerk through the normal scan-and-lookup workflow. A corruption that started on Tuesday and went undetected until Thursday deserves its own drill. Tuesday's clean snapshot might omit two days of legitimate work, even if it takes the same 19 minutes to load.

A replica and a backup are both copies with progress you have to reason about. The timeline below shows a short interval when a primary has acknowledged a write and a surviving replica cannot yet serve it. An archive has a different delivery path. Measure how far that archive can actually take a recovery. Borrowing the replica's lag measurement would give a reassuring number for a component this scenario has already lost. Database replication and failover.

Streaming replication

Acknowledged on the primary, visible on the standby later

asynchronous by default

Primary

accepts writes, flushes WAL

pg_current_wal_lsn()

Standby

receives, flushes, replays

pg_last_wal_replay_lsn()
  1. t+0 ms primary: INSERT row 42; commit acknowledged to the client
  2. t+40 ms replica read: SELECT row 42 returns 0 rows
  3. t+40 ms primary read: SELECT row 42 returns 1 row
  4. t+200 ms standby: replays the record; row 42 is now visible there

synchronous_commit decides what "acknowledged" means

local

acknowledged when the primary flushed the record

standby lags behind

on

acknowledged when a standby flushed it too

standby read may still miss it

remote_apply

acknowledged when a standby applied it

standby read returns it

"Committed" means the primary flushed it (local), a standby flushed it too (on), or a standby applied it (remote_apply). Only the last one makes the replica read return the row.

Run the scenario against an isolated copy with representative data volume and the credentials and resource limits the recovery team would have after the failure. Route a test scanner through the recovery setup. Keep the recovered application's email, dispatch and carrier integrations pointed at test destinations. Restoring the dispatch history should never book another truck. Include the same operator handoffs and routing steps a recovery would require, or label any steps the drill only simulates when you report the result.

Find the last complete recovery point

A backup created at 06:00 can support recovery at 09:57 if a complete sequence of archived changes covers the interval. Conversely, an archive job that uploaded something at 09:59 might have uploaded only the previous hour's pending log. Object timestamps describe the files' arrival. The team needs to know which committed transactions those files contain.

Use a controlled stream of dispatches with a known commit order. For this exercise a single writer submits numbered test dispatches serially, once per minute, and records each successful acknowledgement and its timestamp in a separate drill log. The reference clock is shared with the exercise controller. The test driver saves the response before it sends the next dispatch. That lets the operator compare the restored state against an independent list of operations the caller actually saw succeed. A real database sequence can contain gaps or allocate numbers out of commit order, so an arbitrary sequence column in a production table would be a poor substitute for this controlled input.

Text
Time     Test dispatch acknowledged      Found after recovery
09:55    W-615                           yes
09:56    W-616                           yes
09:57    W-617                           yes
09:58    W-618                           no
09:59    W-619                           no
10:00    primary becomes unavailable     writes stop

Suppose replay confirms a consistent archived log ending at the 09:57 commit, and comparison with the drill log confirms every preceding test dispatch. The recovery point gap is three minutes. Two acknowledged test dispatches are missing. The count and the interval answer separate questions. With thousands of ordinary dispatches per minute, the same three-minute gap could create a much larger reconciliation job. Record the actual missing business operations where an independent record exists. Five minutes of orders at the loading dock near closing time may cost much more to reconstruct than five minutes at opening.

At 10:00, a verified recovery through 09:57 leaves a three-minute gap against a five-minute RPO. The clock for recovery time keeps running until the clerk can scan and retrieve a parcel.

Look for holes before calling a recovery complete through 09:57. Finding W-617 while W-610 is missing is an integrity failure even if a query for the newest row looks fine. Compare counts and identifiers over the expected interval and reconcile by warehouse. A duplicate parcel at one site can hide a missing parcel at another in the global total. If the application keeps manifests in object storage, also open a sample through the application and verify that the referenced version matches the recovered dispatch.

Time the work on the critical path

Leave the stopwatch running while the operator discovers which credential can read the archive and while the application refreshes its connection pool. In this hypothetical run, the stages on the critical path are sequential. Some of the preparation for later stages can happen in parallel in a better design.

Text
10:00   Scanner can no longer record a dispatch.
10:04   Alert reaches the operator.
10:09   Recovery identity works and the candidate store is provisioned.
10:19   Base copy is loaded and the database opens in recovery.
10:26   Log replay reaches the verified point.
10:33   Reconciliation passes for the expected warehouse records.
10:38   Routing and scanner checks pass against the recovered service.

The measured recovery time is 38 minutes, eight minutes over the target. Moving the start to the 10:04 page or moving the finish to the 10:19 database milestone conceals part of the interruption. Tuning backup compression might save minutes loading data. Before doing that, investigate the seven minutes spent on reconciliation. Can the backup process prepare an independent manifest of expected dispatch counts and totals in advance? Can the recovery operator get access as soon as the page arrives? Both changes can reduce elapsed time while retaining the checks that keep bad data away from the warehouse.

If the team pre-provisions a warm target, put that assumption in the next drill. A warm target in the same region as the failed primary may disappear in a regional outage. Similarly, a pool of recovery machines with twice the intended throughput can make a large restore look cheap. Write down data volume, replay distance and available compute with the result so the next person can tell which parts scale as the warehouse grows. AWS guidance on testing recovery and verifying the restored data.

Rehearse the awkward cutover

Bring a test scanner through the normal route, create a uniquely marked dispatch and read it back through a second session. An administrator connecting directly to the candidate would miss a stale connection string in the scanner API. Read a handful of pre-failure dispatches from the same route. Replaying history and accepting the next write both belong in the service the warehouse was promised. Use the existing dispatch IDs when recovering the two missing records from the scanner journal, with a duplicate check in case an operator submits one twice.

Document the authority to select the recovered store for writes. The drill assumed that the old primary stopped accepting them. If it reappears after routing has switched, an old application pool with write access to both sides can split the history again. A separate exercise can rehearse that case with both copies in quarantine. The outcome of this drill is a 38-minute recovery to a point three minutes before the interruption, conditional on the archive surviving and the old write path staying closed. Those limits give the next exercise a concrete starting point.

Katabench's System Design Studio lets you draw the isolated restore path and the separate approval step before routing changes. The Recover a Data Platform sequence in the learning paths introduces those decisions in order. Take the approved design to a timed restore with representative data to learn what the clerk will actually experience.

Practice System Design

More like this: System Design Studio →

Get new katas and .NET tips in your inbox

A short note when fresh kata land, plus the C# and performance tricks behind the grading. No spam, unsubscribe anytime.