Backup success is not recovery capability · PostgreSQL

Every organisation that has lost data had backups. The backups ran, the jobs reported success, and the monitoring was green. What they did not have was a demonstrated ability to reconstruct a working system.

This part is about the difference, and it starts with a measurement.

A backup that succeeds and cannot be restored

Data-loss riskhow many role definitions a pg_dump contains— Captured on 18.6. A role named reporter had been created before the dump was taken.

$ pg_dump -U postgres -F p postgres | grep -cE "^CREATE ROLE|^CREATE TABLESPACE|^ALTER ROLE"

Zero. pg_dump dumps one database, and roles and tablespaces are cluster-level objects. They are not in it.

Read-only / Safewhere they actually live— Captured on 18.6. The SCRAM verifiers appear in full in the real output and are redacted here.

$ pg_dumpall -U postgres --globals-only | grep -E "^CREATE ROLE|^ALTER ROLE"

CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'SCRAM-SHA-256$...';
CREATE ROLE reporter;
ALTER ROLE reporter WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$...';

The backup job that produced the first output exited zero. Its monitoring was green. A restore from it produces a database whose every GRANT references a role that does not exist — errors on each one, and a database that comes up with the privileges missing.

The job succeeded. The recovery capability did not exist.

pg_dumpall --globals-only output is a credential file

Note what is in that output: SCRAM password verifiers for every role in the cluster.

They are verifiers rather than plaintext passwords, so they cannot be replayed directly against the server — but they are the material an offline attack works against, and they are sufficient to authenticate if an attacker can inject them into a cluster they control.

Treat globals dumps as credentials:

Restricted file permissions from the moment they are written.
Encrypted at rest wherever they are stored.
Never in a repository, a ticket attachment, or a shared drive.
Included in whatever rotation policy covers the passwords themselves.

The routine failure here is a backup script that writes globals to the same world-readable directory as the schema dump.

The two numbers that define the requirement

Before choosing a method, the requirement has to be stated. Two numbers do it.

RPO — Recovery Point Objective. How much data may be lost, expressed as time. “Fifteen minutes” means a failure may lose up to fifteen minutes of committed transactions.

RTO — Recovery Time Objective. How long the system may be down. “Four hours” means recovery must be complete within four hours of the decision to recover.

They constrain different things:

	Constrained by
RPO	Backup frequency, WAL archive frequency, replication mode
RTO	Restore speed, replay speed, the procedure, and who is available

A team with hourly base backups and no WAL archiving has an RPO of one hour, whatever anyone intended. A team whose restore has never been timed has an unknown RTO, and “unknown” is not a number you can put in a service agreement.

Deriving the requirement rather than inventing it

Ask the business two questions, in these terms:

“If we lost the last N minutes of transactions, what would that cost?” Increase N until the answer becomes unacceptable. That boundary is your RPO.

“If the system were unavailable for N hours, what would that cost?” Same method. That is your RTO.

Both answers are usually surprising in one direction or the other. A finance system may tolerate four hours of downtime and zero data loss; a metrics platform may tolerate an hour of lost data and no downtime at all. The methods that satisfy those two are completely different, and choosing one before asking is how organisations end up with expensive backups that do not meet the requirement they actually have.

Write both numbers down, with the date and who agreed them. Re-ask annually, because they change.

What each method captures

Method	Granularity	Cluster objects	Point-in-time	Restore speed
`pg_dump`	One database	No	No	Slow — rebuilds indexes
`pg_dumpall`	Whole cluster	Yes	No	Slow
`pg_basebackup`	Whole cluster	Yes	Only with WAL archive	Fast — file copy
Base backup + WAL archive	Whole cluster	Yes	Yes	Fast
Filesystem snapshot	Whole cluster	Yes	Only with WAL archive	Fastest

The row that matters for most production systems is the fourth. Lessons XIII-03 through XIII-07 build it.

What a backup job proves

A backup job that exits zero proves:

A process ran.
It wrote a file.
It did not return an error.

It does not prove:

The file is complete.
The file is readable.
The file contains what you assume.
A database can be built from it.
Anyone knows how.
The result would pass an application’s own checks.

The failures a green backup job does not catch

Each of these has produced a real outage somewhere, and none of them turns a backup job red:

Scope. The pg_dump measured above, missing every role.

Silent truncation. A pipe to a compression tool whose exit status is not checked. In shell, pg_dump | gzip > file reports gzip’s status, not pg_dump’s, unless set -o pipefail is used.

A full destination. The write fails part way; if the script does not check, the truncated file is kept and the previous good one is rotated away.

Encryption without a recoverable key. Backups encrypted with a key stored only on the machine being backed up.

Archive gaps. WAL archiving that has been failing for a week. The base backups are fine and useless, because the WAL to roll them forward is missing.

Version drift. Backups from PostgreSQL 18 and a restore environment running 15. pg_dump output can move forward across versions; a physical backup cannot move at all.

Nobody has done it. The procedure exists as a document. The person who wrote it has left. Nothing in the document has been executed since it was written.

What a restore proves

Only a restore proves recoverability, and only a restore that is verified proves it fully:

The artefacts are readable. They were fetched and opened.
The database starts. It reached “ready to accept connections”.
The data is there. Row counts and checksums match expectation.
The procedure works. Somebody followed it, and it was correct.
The time is known. It was measured, so RTO is a fact.

Lesson XIII-08 makes this a routine rather than an event.

Why a replica is not a backup, and the failure that proves it

A streaming replica is continuously current, can be promoted in seconds, and satisfies availability requirements that no backup can. It is not a backup, and the distinction is not pedantic.

A replica replays everything, including the mistake. DROP TABLE orders on the primary is replicated to every standby within milliseconds. The table is gone everywhere. This is the failure that demonstrates the point, and it is the most common cause of data loss on well-run clusters — not hardware, not corruption, but a correct replication of an incorrect statement.

A replica has no history. It holds the current state. There is no “yesterday” to go back to.

Corruption replicates. Physical replication ships pages; a page corrupted by a storage fault on the primary arrives corrupted. Logical replication does not have this property, which is one of its attractions, but it has its own limits.

A replica shares failure domains. Same schema change, same bad deployment, same operator with the same fingers.

hot_standby_feedback and a delayed standby (recovery_min_apply_delay) narrow the first case — a standby held an hour behind gives you an hour to notice a DROP TABLE — but a delayed standby is a specialised tool with its own costs, not a general answer.

The correct position: replicas are for availability, backups are for recoverability, and a system needs both because they fail differently. A team with excellent replication and untested backups has solved the problem that was going to be fine anyway.

What to take from this

Measured: pg_dump of a database with roles contains zero role definitions. The job still succeeds.
pg_dumpall --globals-only output contains password verifiers. Handle it as a credential file.
State RPO and RTO explicitly, derived from the cost of loss, with a date and an owner.
A backup job proves a file was written. Nothing more.
Only a restore proves recoverability, and only a timed one gives you an RTO.
A replica is availability, not recoverability. It replays the DROP TABLE too.

Cross-course references

Linux for Production Sysadmins — Part XLVII (Backup strategy) and Part XLIX (Restore) cover the same distinction for the host: a backup job that exits zero has proved the job ran, not that the data returns.
Ceph & Distributed Storage — Part CV (Backup strategy) covers why replication inside a storage system is not a backup either, which is the same argument one layer down.
Observability for Production Sysadmins — Part XCI (Backup strategy) covers alerting on backup age rather than on the last job’s exit status.