A backup nobody has restored is not a backup: three tools from this week

In one week, three pieces of news about PostgreSQL backups and a Camunda release came out, and all four are about the same thing: a copy nobody has ever restored doesn't count as protection. I have run clients' servers since 2022 and think the same way. From all of this, a business owner needs one question for the contractor: when was the database copy last restored, and how long did it take.

What came out

PostgreSQL News on October 22 published the BackupDrill announcement. The tool takes a pg_dump of a database in Supabase, puts the archive in your S3-compatible bucket with a checksum, and then, with the command backupdrill drill, restores the latest copy into a throwaway PostgreSQL container and checks: the archive is intact, pg_restore finished, indexes and constraints were created, all tables are there, and the ones that had data are not empty. The restore time gets recorded. The CLI is under the MIT license; there is a paid scheduled service with a free plan. For now it works only with Supabase.

The same day, in the same place, SafeGrd came out for PostgreSQL 13 and newer. A daemon sits next to the database, connects with a read-only role, encrypts the copy with an age key while it is still on your host, and writes to an S3 bucket with Object Lock. They call the check a Fire Drill: the copy is either replayed in memory and the number of tables, rows and columns is compared with what was recorded at backup time, or restored into a sandbox, where you can add your own SQL checks. Every check leaves a signed record, and the records are chained together. The free plan is for one database: a copy once a day and an in-memory check once a week; paid plans start at $15 a month.

A little earlier, on October 18, pgBackRest 2.60.0 was released. The main change in behavior: a restore now stops with an error if a restored file has an invalid page checksum, and the expire command refuses to delete old backups if the oldest remaining one contains such errors. This is turned off with the option checksum-page-error=n.

Camunda on October 19 described restoring a cluster in version 8.10 Self-Managed. Before, you stopped the brokers, cleaned the directories by hand and ran a separate application; now it is a few REST API calls: switch the cluster to RECOVERING mode, restore the storage, call POST /v2/restore with the backup ID. I liked their wording: a restore plan nobody has run is only an estimate. They suggest restoring a nightly copy once a quarter into a cluster nobody depends on, and timing it.

Why this matters to a business

Take a service company with four years of requests, estimates and call history in its database. The contractor says the backups run every night. On Monday the virtual machine's disk dies. There are copies, but the last working one turns out to be three weeks old: a month ago the backup job started failing on one table, and nobody saw it. Three weeks of requests get rebuilt from email and from the managers' memory.

The only guarantee is a restore somebody did recently, on a separate machine, followed by counting the rows in the main tables. All four news items this week are about putting that step on a schedule.

How I do it

Almost all my projects run on MariaDB, so BackupDrill and SafeGrd, which are built for PostgreSQL, don't fit me directly, and I haven't tested them myself. The principle is the same; the tools are my own.

I have worked with a PDR company in the US since 2021. In 2022 I set up a server for them on Debian in Google Cloud, and I have run it myself since then: the server, the VPN, the network equipment and the telephony. The system has several layers of protection and backups. Since 2022 there has been no serious failure that needed a recovery. I don't put that down to luck: the copies exist, and I know what comes back up from them.

At the hydroponic farm, where I have been responsible for the server and the network since 2023, the MariaDB database sits on the same Debian virtual machine as the server, and data from 25 controllers at two sites comes into it. Losing that database would mean losing the history of readings that another team uses to build the automation.

On the studio server where the “Coffee Together” service runs, the SQLite database sits in a Docker volume. The server is shared, with 2 GB of memory, so the convenient way to check a copy there is to start a container next to it, let it read the copy, and shut it down. I don't publish restore-time figures.

What I would do

Ask the contractor where the copies are kept and how long ago one of them was restored. “They run automatically” is not an answer.

Pick a day. Once a month, the copy is restored into a separate container, and someone compares the row counts in three to five main tables: requests, customers, documents. As with SafeGrd, you can add your own queries, like “how many deals have no customer.”

Time it from the decision to restore to a working system. That is your real recovery time. Compare it with what the contract says.

Keep the copies somewhere other than where the database is. A separate bucket or another server, and make sure they can't be deleted with the same key that writes them. That is what Object Lock in SafeGrd is for.

Record the result: the date, who checked, how long it took, what didn't match. A year later those records show whether the database is growing faster than the recovery plan.

I wrote about why a system needs one shared center for its data and how it should survive failures in the article The server: data, access and process execution, and my approach to operations is collected on the page infrastructure you can ship and restore. Cases: technical partner to a PDR company and server and network for a hydroponic farm. On the same topic: how I check servers while the world patches a critical hole and what I would trust an AI on-call engineer with on a single server. Earlier I wrote that a copy becomes protection after a verified restore and that deployment retention should match the recovery plan. If you don't know whether your database would come back, describe where it runs: Discuss your task.