PostgreSQL Backup Restore Verification: Test the Answers

PostgreSQL backup restore verification needs more than two successful commands. A backup can finish, and a restore can finish, while your chosen application checks still reveal a problem. Our fictional course-enrolment lab separates those three kinds of evidence.

We built PostgreSQL 18.7 from the project’s source and restored one trusted logical archive into three new disposable databases. The faithful copy matched all 10 fixed checks. After controlled changes, the other copies matched seven and eight checks. All three still had exactly four students, three courses and five enrolments.

Why PostgreSQL backup restore verification matters now

The 12 November 2026 PostgreSQL update includes backup-related fixes. For users of incremental backups, the project recommends a new full backup after updating. Our experiment uses a logical pg_dump archive. It does not test incremental backups or measure how many existing backups are affected.

The release is the timely reason to examine recovery evidence. However, the lesson applies to a small backend project at any time: decide what must survive a restore, then test it in a separate environment. A release note cannot supply your application’s expected answers.

A fictional database with known answers

Our source has three linked tables: students, courses and enrolments. A view reports enrolments per course. Primary keys identify records. A foreign key rejects an enrolment for an unknown student, and a unique constraint rejects duplicate student-course pairs.

Before restoring, we fixed seven read-only expectations and three behaviour checks. The read checks cover table totals, two key sets, a joined list and the view. The behaviour checks test a foreign-key rejection, duplicate rejection and the next generated enrolment identifier. Expected failures are successful evidence only when the intended rule rejects the intended invalid input.

Three linked evidence stages: archive creation, isolated restoration and application-answer checks, each with separate records.
Keep command evidence and application evidence together. Each stage answers a different question about the recovery exercise.

How the isolated restore experiment ran

First, we created a fresh temporary cluster with no TCP listener and a private local socket. Next, we seeded only the fictional fixtures and ran pg_dump in custom format. Each destination was a new empty database created from template0; the source was never overwritten.

We restored with pg_restore, using exit-on-error and a single transaction. All three restore commands exited successfully. Only after those restores did we introduce the controlled changes. Therefore, the altered copies are tests of our verification checks, not evidence that PostgreSQL damaged this archive.

pg_dump -Fc --no-owner --no-acl -d lcu_source -f course-lab.dump
pg_restore --exit-on-error --single-transaction \
  --no-owner --no-acl -d NEW_DISPOSABLE_DATABASE course-lab.dump

Those are the lab’s core commands, not a complete production recovery procedure. Our runner also records connection settings and creates the isolated destinations. Use the official pg_restore options to interpret the flags. Restore only archives whose source you trust.

Same totals, different records

In the changed-record copy, we replaced enrolment key 1 with 99 and changed its student and course. That removes one expected record identity without changing the number of rows. The change still satisfies the remaining database constraints.

As a result, all three row-count checks passed. Yet the enrolment key set, joined answer and course-total view failed their expectations. A check that looked only for five enrolments would miss all three differences. Even matching key sets alone would not prove that every non-key field was correct.

Each scenario has four student rows, three course rows and five enrolment rows despite different verification results.
The row-count chart deliberately looks identical across scenarios. The unchanged totals are the reason to inspect identities, relationships and behaviour.

Correct rows do not prove that rules survived

For the third copy, we removed only the student foreign key and the student-course unique constraint after a faithful restore. Its stored records, keys, joins and view still matched. However, both deliberately invalid insert checks were accepted instead of rejected.

Each write test ran in a transaction that rolled back its records. The generated-identifier check also used a disposable transaction. PostgreSQL sequences are not transactional, so this runner tests each fresh copy once rather than treating a rollback as a sequence reset. These details matter when interpreting the retained logs.

Ten checks across three scenarios: faithful passes ten; changed record fails keys, joins and view; removed constraints fails foreign-key and duplicate rejection.
The matrix describes 30 designed checks. It is not an estimate of backup reliability or the probability that a production restore will fail.

Build a useful restore evidence ledger

Start with the scope: name the application data and behaviour you need to preserve. Then record the server and client versions, backup method, archive identity, command exit statuses and isolated destination. Finally, compare each observed result with an expectation written before the check.

A ledger entry should make “not checked” visible. For example, our archive exercise does not establish cluster-wide role recovery. The PostgreSQL SQL-dump guide distinguishes a single-database dump from cluster-wide information. Ownership, permissions and deployment-specific objects need their own applicable checks.

Tech Consulting Tailored to Your Coding Journey

Get expert guidance in coding with a personalized consultation. Receive unique, actionable insights delivered in 1-3 business days.

Get Started

For another example of separating evidence layers, see our C# union-type experiment: a successful build can still return the wrong answer. You can also explore software development practices for broader maintenance context.

Download the results and reusable checks

Methodology and limits

We ran the lab on 15 November 2026 with server and client version 18.7 on macOS arm64. The source archive matched the project’s published SHA-256. All records are fictional, and the altered copies were deliberately designed to expose selected checks. Their seven-of-ten and eight-of-ten results are not comparable scores of real backup products.

This exercise does not validate recovery time, retention, encryption, disaster recovery, concurrency, every object type or all application rules. It does not test incremental or physical backups. Add expectations that reflect your project, retain the evidence and repeat the exercise in a suitable isolated environment when relevant components change.