Every operations team has backups, and a disquieting number of them have never restored one. The archive accumulates on schedule, the success emails arrive nightly, and the assumption underneath is that a restore will work because the backup ran. The assumption is the most expensive sentence in operations, because backups fail in the specific ways that success reports do not mention, the archive with a gap, the base backup without the log that completes it, the encrypted archive without the key, the restore that takes a weekend nobody has, and every one of them is discovered during the disaster it was purchased to prevent. This article builds the discipline that prevents the discovery, the two numbers that design the strategy, the backup methods with their honest costs, the monitoring that watches the backup itself, and the restore drill that converts an archive into a capability.
RTO and RPO as the two numbers that decide everything
Recovery time objective, how long until the service serves data again, and recovery point objective, how much recent data may be lost, are the two numbers that price the whole strategy. The RPO is decided by the backup mechanism's granularity: a nightly dump loses up to twenty four hours, a log archive that ships continuously loses seconds to minutes. The RTO is decided by the restore mechanics: the method's raw speed, the size of the data, the rehearsed procedure, and the person who has actually run it. Every choice below is a position on these two axes, and the numbers are business decisions, agreed with the product owner in writing, because they determine budget and architecture, and the team that never asks inherits the defaults, a daily dump and a restore time measured in wishful hours.
The tiers fall out of the numbers. A development database tolerates a nightly dump and a day of loss. The billing system tolerates neither, and its tier is the log archived within minutes and the restore rehearsed quarterly. The same fleet can hold both tiers, the strategy is per database not per organization, and the honest backup policy names each database, its RPO, its RTO, and the mechanism that meets them, a table the disaster tests whether it was ever written.
Physical base backup plus the log archive as the backbone
For the databases that matter, PostgreSQL and MySQL alike, the backbone is physical: a complete copy of the data files plus the ongoing log that makes it current. The PostgreSQL pattern is the base backup plus the write ahead log archive:
pg_basebackup -h primary -U replicator -D /backup/base -X stream -P
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'The base backup is a consistent image, the archive is the running history, and together they give point in time recovery, the restore to any second within the retention, the mechanism that turns a dropped table from a scandal into an inconvenience. The restore is two steps, base first, then log replay to the chosen moment, and the moment is chosen precisely, before the mistake or after it, which is the capability that no dump schedule provides.
The tooling above the mechanism earns its place by industrializing it. pgBackRest manages the base backups, the archive, the retention, the compression and the verification in one pipeline, and its restore commands are the ones the drill uses. On the MySQL side, Percona XtraBackup takes the physical copy while InnoDB runs, and the binlog plays the role of the archived log, shipped to storage and replayed to any position. The commercial and managed equivalents, the cloud snapshots and the vendor appliances, implement the same two pieces, and the evaluation of any of them asks the same two questions, what is the RPO of the shipped log and what is the restore procedure step by step, because the answers are the numbers that matter.
Logical dumps and their honest limits
The logical dump, pg_dump, mysqldump, the human readable export, is the tool everyone reaches for first and the tool whose costs are worth naming. Its strengths are real: portability across versions and platforms, selectivity by table and schema, and a file that a human can inspect. For small databases it is a complete strategy, a nightly dump to object storage is simple, cheap and restorable by anyone who can read the documentation.
The limits arrive with scale and with the failure mode of the restore. A logical dump of a large database restores slowly, every row is reinserted through the SQL layer, every index rebuilt from scratch, and a dump that took twenty minutes to take takes many hours to give back. The dump runs against the live database, holding locks or connections, and the application notices. The point in time capability is the coarser one, the dump's schedule, with everything since the last dump lost. The production pattern that respects the limits is the combination: logical dumps for the small databases, the dev tier and the migrations insurance, and the physical backbone for everything whose loss would be a headline. The team that backs up the terabyte billing database with mysqldump nightly has made an RPO and an RTO decision without noticing, a day of data and a weekend of restore, and the drill is where they find out.
Managed backups and the questions the vendor still requires
The managed database services run the backbone for the team, continuous backups with point in time recovery on a slider, snapshots retained on a policy, and the convenience is real enough that it changes the job description, from running the pipeline to auditing it. The managed restore is a console action and an API call, the retention window is a configuration number, and the discipline that remains is the one this article has been building all along, because the vendor's success reports describe the pipeline the same way a self run pipeline describes itself, accurately and uselessly, without ever asserting the restore.
The questions the team still owns on a managed service are the drill questions. Does anyone know the restore procedure on a Saturday, has anyone run it against the production sized copy, and does the restore land in a network the application can actually reach. The managed point in time recovery has limits worth testing on purpose, the retention window is finite, the restore speed is the vendor's infrastructure shared with the neighbors, and the cross region copy is frequently an option that was never enabled. The quarterly drill on a managed database is shorter than the self hosted one, and the finding rate is just as high, the option that was off, the role that lacked the restore permission, the window that was shorter than the compliance requirement, and every one of them is a checkbox that would have failed its first audit at the worst possible moment.
The backup as a security object
The backup is a full copy of everything, which makes it the widest attack surface in the system and the object the security work must treat accordingly. The encryption is the baseline, encrypted at rest in the storage tier, encrypted in transit on the way there, and the key management is the part that fails the drills, keys rotated on a schedule the backup system never learned, or held by the same account that a compromised administrator can reach. The separation principle is the design that survives, the backup system's credentials are distinct, its storage is distinct, and the ability to delete archives is held by a role that no production automation uses, because the first move of the intruder who reaches production is to delete the backups, and the industry's ugly word for that pattern is the reason the deletion log is monitored like the backup log.
The restore drill closes the security loop as well as the operations one, because the restore requires the keys, the permissions and the network path, and a key that expired or a policy that changed is a security finding discovered in a Tuesday drill instead of a Saturday incident. The backup that the team can restore, quickly and by rehearsal, is the only backup that survives both halves of the disaster story, the failure that takes the primary away and the adversary that comes for the copies, and the second half is the one the checklist below prices into the same calendar entry.
The restore drill on a calendar with a stopwatch
The drill is the article's title made procedural, and its shape is the same everywhere. Once per quarter, per critical database, the team restores from the production archive into an isolated environment, on infrastructure that resembles production, and runs the application against the result. The restore is timed against the promised RTO, the data is verified against the source with checksums and counts, the application's smoke tests prove the database actually serves, and the findings, the missing log segment, the key that expired, the step the documentation forgot, become the next month's work. The drill's output is not a restored copy, it is a corrected procedure and a measured capability, and the first drill in any organization reliably finds a defect that would have been the disaster's second act.
The isolation rule matters as much as the schedule. The restore target must be unable to touch production, separate credentials, separate network, the classic drill accident is the restore that overwrote the live database because the connection string had one character wrong. The drill is rehearsed under pressure conditions deliberately, at least sometimes with the person who has never done it, because the disaster will not schedule itself around the one senior engineer who has the procedure memorized, and the runbook that only that engineer can execute is not a runbook, it is a dependency.
Monitoring the backup pipeline itself
The backup that stops backing up is silent, and the monitoring exists because of the specific silence. The freshness check alarms when the newest archive object is older than its schedule, the gap check verifies the log's continuity, WAL segments in sequence, binlog positions advancing, because the archive with a hole restores only to the hole and the hole is usually discovered by the drill if the team is lucky, the disaster if it is not. The integrity check hashes the objects on write and samples them for verification on read, and the size sanity check alarms on the backup that shrank, the nightly dump that went from five gigabytes to fifty megabytes because a schema change broke the export mid month.
The end to end check is the strongest of all and the cheapest to build, a scheduled job in the monitoring tier that restores the latest archive into a scratch instance, starts the database, runs a single query, and tears it down. It is a miniature weekly drill, it converts the pipeline's health from an assumption into an assertion, and the teams that run it discover their backup defects on Tuesdays with coffee instead of Saturdays with the incident channel open.
The checklist that defines a working backup
The capability is a list of properties, each one testable, and the list is short:
- Every database has written RPO and RTO numbers, agreed with the product owner;
- The critical tier uses physical backup plus a continuous log archive, PITR is the capability;
- Logical dumps cover the small and the portable, never the only copy of the large;
- Freshness, continuity, integrity and size are alarmed, silence is treated as failure;
- The quarterly drill restores into isolation, times it, verifies it and fixes its findings;
- The weekly miniature restore runs end to end, unattended, and pages when it cannot.
The archive that no one has restored is an untested assumption, and the team that drills turns it into infrastructure. The difference between the two is one calendar entry per quarter, a stopwatch, and the willingness to find out on a quiet afternoon what would otherwise be found out on the worst one.
The numbers, the mechanisms and the drills assemble into a capability that has one measurable property, the team can lose the database and be serving again inside the promised window with the promised data, proven recently enough to trust. Every component of this article is a way to make that sentence true and to keep it true, and the sentence is the only deliverable a backup strategy was ever really asked for.