1. LandOffer
  2. Blog
  3. All articles
  4. Database Administrator Portfolio: Prove a Restore Meets a Recovery Requirement

Job search

Database Administrator Portfolio: Prove a Restore Meets a Recovery Requirement

An isolated PostgreSQL recovery exercise proves the intended transaction state and rejects a faster late-point restore with explicit data acceptance checks.

9 min readLandOffer team

A database administrator portfolio should prove that a restored database contains the required state, not just that a backup command succeeded. Define the recovery point, data checks and timing boundary before running the exercise. Preserve the commands, server logs, data snapshots and timing record so a reviewer can challenge the outcome.

This guide reports an actual isolated PostgreSQL 18.6 recovery experiment using original synthetic inventory and order data. It includes a deliberately wrong recovery point that started successfully but failed acceptance. No existing database or customer system was touched. Use LandOffer.ai to compare relevant roles while keeping this local project distinct from production recovery experience.

An administrator rebuilds a small inventory shelf from preserved components and checks its contents beside a timer.

Define the requirement before choosing a backup command

The exercise’s requirement was to preserve one valid sale while excluding a later destructive demonstration. The restored state had to contain inventory item 1 with nine units, item 2 with twenty units, and order 101 for one unit of item 1. The marker history had to include baseline and valid_sale, without destructive_demo.

We also required zero orphan orders and zero negative inventory values. Those checks matter, but they are not sufficient: a database containing zero stock and no orders can satisfy them while losing the required business state. Compare the exact expected rows as well as structural consistency.

The valid sale couples an inventory change with an order insertion in one transaction. Checking only the inventory row count would miss the later zero quantities, because both items still exist. Checking only referential integrity would miss the deleted order, because an empty order table contains no orphan. The acceptance contract therefore compares complete key-and-value pairs, the required order and the marker sequence together. A different application would need its own declared invariants, such as balances or posting relationships; these fixture checks should not be copied into an unrelated system and called complete.

The local timing limit was 120 seconds, declared before execution. Its clock starts before copying a prepared base backup into a new restore directory and ends after recovery reaches its paused target and the SQL validation queries finish. It excludes backup preparation, failure detection, remote storage retrieval and reopening an application for writes.

That is a bounded lab requirement, not a production recovery time objective. A real service’s definition may include authorization to recover, provisioning, application dependencies and business-owner acceptance. Identify those boundaries before comparing a measured duration with a target.

Match the backup method to the recovery point

PostgreSQL 18’s continuous-archiving documentation describes recovery from a physical base backup and the necessary continuous WAL sequence. Logical pg_dump output is not a base for this WAL-replay method. The documentation also calls for testing the archive procedure and inspecting the recovered data.

This project used the existing Homebrew PostgreSQL, pg_basebackup and psql executables, all reporting version 18.6. The source cluster, backup, archive and two restore directories belonged solely to this exercise. Each server listened only on loopback, with Unix sockets disabled, and all owned instances were stopped afterward.

The source used wal_level=replica and an enabled archive command. A small local helper preserved completed WAL files; its code rejects conflicting overwrites and permits an existing file only when its bytes match. The helper, configuration and actual command trace are retained. This demonstrates this archive path; it does not test off-site storage, encryption, access segregation or disaster survival.

The pg_basebackup reference explains physical cluster backups and the streamed WAL option. Our actual command used plain format, streamed WAL and a fast checkpoint. The prepared backup contained 979 files totaling 41,020,770 bytes in the recorded inventory. The archive inventory contained three WAL segments and one backup-history file.

Create a recovery point that preserves the right transaction

The synthetic source began with two inventory rows totaling thirty units and a baseline marker. After the base backup completed, a transaction changed item 1 from ten to nine units, inserted order 101 and added the valid-sale marker. Only then did we create the intended named restore point:

SELECT pg_create_restore_point('keep_valid_sale');

The recorded WAL location was 0/300AFF0. A later committed transaction deleted the order, set both inventory quantities to zero and added the destructive marker. A second named point, too_late, was created afterward at 0/300B1D8. These are actual results from the synthetic source, not a transcript invented for the article.

A named point makes the intended boundary explicit in this planned exercise. It does not show that a DBA could identify an unknown production incident’s correct point from incomplete evidence. Keep that skill separate from replaying to a point you deliberately created.

The source switched WAL and waited for the required segment to appear in the archive before stopping. Both restore logs later recorded retrieval of archived segments and the named stopping point. The backup and archive hashes remained unchanged across the restores. Keep the named-point log entries with the backup and archive inventories so a reviewer can trace the recovery path.

Worked asset one: actual intended-point recovery receipt

We copied the prepared physical backup into a new directory, configured archive retrieval and selected the named point. The meaningful recovery settings were:

recovery_target_name = 'keep_valid_sale'
recovery_target_action = 'pause'

The restore directory also contained recovery.signal and its own restore_command. These settings refer to this isolated lab; the complete scoped configuration and commands are in the experiment record. We did not replace files in an existing installation or reuse another cluster’s data directory.

PostgreSQL’s recovery-target documentation describes pausing at a target so its contents can be inspected. We verified that replay was paused and the instance was still in recovery before accepting the read results. The exercise did not promote it or reopen a writable application.

The completed receipt is:

Acceptance item Actual intended-point observation Result
Named stopping point Server log records keep_valid_sale Matches the selected point
Inventory Item 1: 9; item 2: 20 Exact rows match; total 29
Order Order 101, item 1, quantity 1 Required sale retained
Marker history baseline, valid_sale Later destructive marker excluded
Relationship and value checks Zero orphan orders; zero negative quantities Listed consistency checks pass
Recovery state Replay paused; server in recovery Read-only inspection boundary preserved
Copy-through-query duration 0.525211709 seconds Below the declared local 120-second limit

The UTC wall interval began October 24, 2026, at 00:16:27.675852 and ended at 00:16:28.201092. Locally, this was the afternoon of October 23. Duration was measured with a monotonic clock; the retained wall timestamps provide context rather than replacing that measurement.

The result meets the stated fixture requirement within its defined read-only boundary. The database is tiny, the backup and WAL are already local, and operating-system caching may affect the result. One fast restore says nothing reliable about a large remote backup, concurrent application load or a production recovery commitment.

Check backup integrity without substituting it for recovery

The actual pg_verifybackup execution returned “backup successfully verified.” Its retained receipt includes the command, exit status and backup-manifest hash. That supports a successful check of this physical backup, alongside the separate restore evidence.

The official pg_verifybackup documentation explicitly says it cannot perform every check a running server will make. It recommends test restores and checking that recovered databases contain the correct data. A verified backup and a successful startup are useful intermediate observations, not the final business-data acceptance.

Preserve those states independently in your project: backup created, integrity checked, archive available, target reached and data accepted. If any stage is missing, mark it missing rather than allowing the earlier green result to stand in for it. The second restore below demonstrates why this matters.

A reviewer compares two restored shelves and rejects an empty result even though its timer finished sooner.

Worked asset two: reject the faster wrong-point restore

The independent challenge used another fresh copy of the same prepared backup, with too_late as its target. It reached that named point, paused in recovery and accepted read-only queries. Its measured copy-through-query interval was 0.330131833 seconds, faster than the intended-point run.

It nevertheless failed the requirement:

Check Intended point Deliberately late point
Inventory rows 9 and 20 units 0 and 0 units: reject
Required order Order 101 present No orders: reject
Marker history baseline and valid_sale Also includes destructive_demo: reject
Orphan orders Zero Zero: structural pass
Negative quantities Zero Zero: structural pass
Under local timing limit Yes Yes: timing pass
Overall decision Accept for the declared lab requirement Reject despite startup, pause and speed

A separate acceptance script read the captured SQL snapshots and compared them with an explicit authored business contract. It did not trust the recovery runner’s generated acceptance flag. It independently rejected the late-point inventory, order and marker state despite passing structural and timing checks.

This is a substantive negative control. The restored database is readable and structurally plausible, but it contains the wrong state. The challenge tests the acceptance logic against an actually restored counterexample, rather than simply changing a result label in a spreadsheet.

Its scope is still finite. We did not test missing WAL, corrupted media, unavailable storage or every possible wrong target. Nor did a second human independently examine the database. The separate script is an additional review path over captured observations, and the raw commands and server logs remain available for scrutiny.

Distinguish the recovery point from elapsed recovery time

The named target preserved the required transaction and excluded the later demonstration change. That establishes a transaction boundary for this exercise. It does not calculate a production recovery point objective, a maximum time-based data-loss interval or zero data loss across a real service.

Likewise, the measured interval ends with paused read-only validation. It does not establish failover, application write readiness, successful client reconnection or user-visible service restoration. If your real requirement includes those steps, they need their own authorized checks and clock boundaries.

A named restore point, a time target and a transaction target also require different interpretation. Use the documentation for the installed version and the target actually chosen. Do not describe this named-point experiment as proof that timestamp selection, time-zone ambiguity or transaction inclusivity was tested.

Deliver a recovery packet a reviewer can inspect

Include the predeclared requirement, synthetic schema and transactions, source backup identity, archive inventory, recovery configuration, actual command trace, server logs, SQL snapshots, timing records and independent acceptance result. Retain the failure case alongside the successful one. It explains why the acceptance checks exist.

The experiment’s runner refuses to overwrite an existing lab directory and stops only the instances it created. Reproduction should use a fresh isolated location, not someone else’s database or the preserved frozen evidence. The final cleanup record contains no running PID files for the owned source and restore directories.

A truthful project description is: “Rehearsed PostgreSQL 18.6 physical-backup and archived-WAL recovery to a named point, checked required inventory and order state in paused recovery, and rejected a faster late-point restore with an independent acceptance contract.” It does not claim production incident ownership, a guaranteed RTO, high availability or application reopening.

Use the completed packet when comparing DBA opportunities on LandOffer.ai. Explain the recovery requirement, the state you recovered, the counterexample you rejected and the limits that remain. Keep each claim tied to its observed state and timing boundary.

Sources and Further Reading

Official documentation checked October 23, 2026. /current resolved to PostgreSQL 18 during research; actual local tools reported 18.6. The experiment used only owned synthetic clusters and preserved its command and cleanup records.