The backup job is green. The file exists. Someone says, “Sorted, bro.”

Lovely. One tiny question before we all go for chai:

Can the application actually use what comes back out of that file?

A completed backup is useful evidence that a backup operation finished. Recovery needs more evidence: that you can access the backup, restore it into a suitable environment, validate the data and bring the service back within acceptable limits.

A charger in your bag is reassuring. Finding out it fits a different phone is a less enjoyable experience at 2% battery.

Here is a small PostgreSQL restore exercise you can adapt. It is a lab walkthrough, not a report of a production incident or a measured recovery benchmark.

What this exercise proves—and what it doesn't

We will create synthetic data, make a logical backup, restore it into a new database, and compare a few expected results.

It does not establish disaster recovery for a whole platform. It does not restore your operating system, object storage, application secrets or external services. A logical dump is one component of a recovery strategy; your needs may call for physical backups, WAL archiving or managed-service recovery features instead.

PostgreSQL documents these distinctions in its backup overview.

1. Start with a disposable environment

Use a non-production PostgreSQL instance and a database role allowed to create databases. The example assumes compatible PostgreSQL client tools are installed and that PGHOST, PGPORT and PGUSER point to that lab instance. Use your normal secure authentication method; keep passwords out of shell history and shared screenshots.

The commands below do not delete an existing database. Keep them in the same Bash session so their variables remain available.

set -euo pipefail umask 077 : "${PGHOST:?Set PGHOST to your disposable lab server}" : "${PGUSER:?Set PGUSER to your lab role}" LAB_DIR=$(mktemp -d) LAB_SUFFIX="$(date +%s)_$$" SOURCE_DB="restore_source_${LAB_SUFFIX}" RESTORE_DB="restore_target_${LAB_SUFFIX}" createdb --template=template0 "$SOURCE_DB"

Check the host before running this. “I thought that terminal was staging” is a sentence with surprisingly expensive punctuation.

See the createdb documentation for connection and permission details.

2. Create a small dataset with an actual relationship 

psql --dbname="$SOURCE_DB" --set=ON_ERROR_STOP=1 <<'SQL' CREATE TABLE customers ( id integer PRIMARY KEY, name text NOT NULL ); CREATE TABLE orders ( id integer PRIMARY KEY, customer_id integer NOT NULL REFERENCES customers(id), amount_paisa integer NOT NULL CHECK (amount_paisa >= 0) ); INSERT INTO customers VALUES (1, 'Lab customer A'), (2, 'Lab customer B'); INSERT INTO orders VALUES (101, 1, 1000), (102, 1, 2200), (103, 2, 1000); SQL

This gives us something more useful than checking that a database name exists: rows, a foreign-key relationship and a numeric total.

3. Create and inspect the archive 

pg_dump \ --format=custom \ --file="$LAB_DIR/application.dump" \ "$SOURCE_DB" pg_restore --list "$LAB_DIR/application.dump" \ > "$LAB_DIR/archive-contents.txt"

The first command produces an archive. The second shows its table of contents. Listing the archive is not a successful restore test. It is an intermediate inspection step.

pg_dump handles one database; global objects such as roles require separate planning. Keep client/server version compatibility in mind. PostgreSQL pg_dump reference

4. Restore into a fresh target 

createdb --template=template0 "$RESTORE_DB" pg_restore \ --dbname="$RESTORE_DB" \ --single-transaction \ --exit-on-error \ --no-owner \ --no-privileges \ "$LAB_DIR/application.dump"

For this small lab, one transaction helps avoid leaving a partially restored result if a restore command fails. Larger restores may need a different strategy; transaction scope, resource use and parallelism involve trade-offs.

The owner and privilege options deliberately simplify this lab. They also mean we have not tested restoration of the production permission model. Do not silently carry that assumption into a real recovery plan.

Only restore trusted archives. A database dump can contain executable database definitions. PostgreSQL pg_restore reference

5. Verify useful properties 

psql --dbname="$RESTORE_DB" --set=ON_ERROR_STOP=1 <<'SQL' SELECT count(*) AS customers FROM customers; SELECT count(*) AS orders FROM orders; SELECT sum(amount_paisa) AS total_paisa FROM orders; SELECT count(*) AS orphan_orders FROM orders o LEFT JOIN customers c ON c.id = o.customer_id WHERE c.id IS NULL; SQL

For this synthetic dataset, the expected results are 2 customers, 3 orders, 4,200 paisa and 0 orphan orders. These values were reproduced during draft verification in an isolated PostgreSQL 18 cluster using synthetic data. They are not results from a production recovery test.

Counts and totals are useful checks, but they do not prove every value is correct. For a real system, add domain-specific checks: representative records, critical relationships, known transactions and application workflows.

Then test through the application using its normal restricted role. A restore that works only for an administrator has not yet demonstrated that your service can operate.

The Technical PM part: define “recovered” before the incident

Ask the team to agree on:

  • Which customer journeys must work before recovery is declared complete?
  • How much data loss is acceptable? This informs the recovery point objective, or RPO.
  • How long can the service remain unavailable? This informs the recovery time objective, or RTO.
  • Who can access backups and keys if the usual operator is unavailable?
  • Who owns the decision to resume writes?

Measure retrieval, provisioning, restore, validation and application readiness separately. A stopwatch around pg_restore alone does not measure the full recovery experience.

Keep a dated record with the backup identifier, environment, tool versions, checks, timings, failures and follow-up owner. Redact sensitive information before sharing it.

The target isn't “a backup exists.” The target is a recovery process your team has evidence for.

What would you check first after a database restore: a row count, an application login, or the customer journey that makes the business money—and why?

Draft prepared with AI support. The synthetic-data walkthrough was checked in an isolated PostgreSQL 18 cluster during draft preparation. Author review and environment-specific testing are still required before publication.

#PostgreSQL #Linux #DevOps #Beginners