Your team says the database is backed up nightly. How would you establish whether you could really restore it?

Turn "nightly" into two numbers

"Backed up nightly" is a description of a cron entry, not of a guarantee, and the first thing to do is convert it into the two numbers the business is actually buying. Recovery point objective is how much data you are willing to lose; a nightly dump and nothing else sets that at up to twenty-four hours, whatever anyone believes. Recovery time objective is how long you may be down while restoring, and nobody knows it until somebody has timed a restore of the current data volume.

Ask those two questions and the answer often collapses the whole discussion. If the tolerable loss is minutes, a nightly job cannot deliver it at any level of care, and the conversation becomes one about continuous archiving instead of one about backup hygiene. In PostgreSQL that means a base backup plus archived write-ahead log, which lets you recover to a chosen point in time rather than to last midnight. In MySQL the equivalent pairing is a physical or logical backup plus the binary logs from that point forward. Either way the periodic copy is the floor and the log stream is what closes the gap.

Replicas and snapshots are not backups

The most common substitution is a replica. A streaming replica protects you from losing a machine, and it protects you from nothing else, because it faithfully reproduces the destructive statement. DROP TABLE replicates. A bad migration replicates. An application bug that overwrites a column for every row replicates, and it does so in under a second.

A delayed replica is a genuine partial answer here, and worth naming as a distinct control: PostgreSQL's recovery_min_apply_delay holds a standby a fixed interval behind the primary, so for the length of that window you have a live copy of the recent past and recovery is a promotion rather than a restore. Its limitation is exactly its window. Notice the damage after the delay has elapsed and the replica has already applied it.

Volume snapshots are the second substitution, and their correctness depends on atomicity. A snapshot of a single volume holding both the data directory and the log is equivalent to pulling the power cable: the engine treats it as a crash on startup and replays its log to a consistent state. The same snapshot taken across two volumes with no coordination between them is torn, and it will restore into something that appears to work and is subtly wrong. If snapshots are part of the plan, the thing to establish is whether the snapshot is atomic across everything the database writes to.

What a restore has to contain that a dump does not

The failure that surprises people during a real recovery is that the data restores and the system still does not run, because a logical dump is a dump of one database and not of the server it lived in. Roles and passwords, tablespace definitions, and cluster-wide settings are separate concerns; in PostgreSQL, pg_dump covers a single database while pg_dumpall --globals-only is what carries the roles. If extensions are in use, the target machine needs the extension binaries installed at compatible versions before the schema will load at all.

Then there is everything outside the database. Configuration that was hand-edited on the old host, the encryption key or KMS grant without which the backup file is noise, the object-storage credentials the restore path itself needs, and the DNS or connection-string change that points the application at the new instance. A restore plan that assumes the surviving infrastructure is the same infrastructure has not been tested against the scenario people most fear.

Five mechanisms, none of them a restore

In January 2017, a GitLab.com engineer working on replication ran a directory deletion against the wrong host and removed a live database directory, after which five separate backup or replication mechanisms turned out not to produce a usable restore. That is the case to keep in mind for this whole topic: an untested backup is a belief rather than a control, and the number of mechanisms you own says nothing about whether any one of them works.

The mechanical ways a backup silently produces nothing are worth being able to list, because each one passes every check that looks at the job rather than at the artefact. The exit status of the dump is discarded by a shell pipeline, so a truncated file is written and the job reports success. The schedule runs on a host that was replaced during a migration and never came back. Retention deletes the oldest copy on a fixed rule, so a corruption discovered three weeks late has already had its last clean copy expired. The archive destination silently stopped accepting writes, which in PostgreSQL has a second consequence people miss: when the archive command keeps failing, the server retains the log segments it cannot archive, and the disk fills on the primary you were trying to protect.

Verification is a restore you timed

The only evidence that counts is a restore. Automate it: take the most recent backup, restore it onto infrastructure built from scratch rather than onto the machine that already has the data, and then check the result rather than the exit code. Check that the object counts and the row counts of the largest tables match what the source reported, that the newest row is as recent as your RPO promises, and ideally that the application starts against it and serves a read. pg_verifybackup checking a base backup against the manifest written alongside it is a useful cheap gate, but it proves the files are intact, not that the cluster starts and the data is complete.

Record how long the whole thing took, because that number is your real RTO and it grows with the data. A logical restore in particular rebuilds every index, so its cost rises faster than the size of the dump suggests, and a plan that was comfortable at fifty gigabytes can be a multi-day outage at two terabytes. The metric worth alerting on is the age of the last verified restore, not the age of the last backup, because that is the one that fails loudly when the pipeline quietly stops working.

Finally, ask who can destroy the copies. If the credentials that run the application, or the account that owns the primary, can also delete the backup bucket, then one compromise or one confident script removes both sides at once. Separate the identity that writes backups from the one that can delete them, and prefer a retention lock the operator cannot lift on impulse.

Nobody has a backup, they have a restore they have or have not performed. Measure the age of the last verified restore and the time it took, and treat those two numbers as the actual guarantee.