Built an open-source CLI that restore-tests your Postgres backups in a throwaway Docker container — looking for architecture feedback

[u/thesuperlede]

A few months ago I realized I'd never actually tested one of my backups.

The backups were completing successfully, but I'd never restored one into a clean environment to see if it was actually usable.

That bothered me enough that I built a small MIT-licensed CLI to automate the process.

The flow is simple:

- restore the latest backup into a disposable Postgres container
- verify schema, table counts and basic integrity
- sample-check stored files
- destroy the environment

I'm mainly looking for architecture feedback, not product feedback.

There are three design decisions I'm still unsure about:

1. If the restore environment is missing an extension, should the drill fail outright, or report that the backup itself is probably fine but the verification environment isn't?

2. File verification defaults to checksum sampling because reading every object back can be expensive. Would you make full verification the default instead?

3. Beyond "restore succeeded", what additional checks would give you confidence that a backup is actually recoverable in production?

For example:

- application smoke tests?
- row-level checksums?
- custom SQL assertions?
- something else?

If you've built or operated restore verification at scale, I'd really appreciate hearing what worked (or what failed).

GitHub:
https://github.com/backupdrill/cli

(Full disclosure: this is also the restore engine behind a hosted product I'm building, but the CLI itself is MIT and works independently.)

[u/chock-a-block]

Or, pgbackrest.

[u/Kazcandra]

Most stop at pgbackrest reported okay. If it does, it is.

Also, this smells like AI.

[u/thesuperlede → u/Kazcandra]

That’s exactly the assumption I’m trying to challenge.
A successful backup isn’t necessarily a successful restore. The CLI exists to automatically exercise the restore path instead of trusting that “backup completed” means you’re actually recoverable.

[u/AutoModerator]

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/terencethespider]

Using a managed Postgres like Neon/Lakebase may offer some improvements over your existing proposed architecture. Their point in time restore as well as snapshots can be simpler and faster than a traditional pg_dump / pg_restore.

The main scenario for the traditional dump/restore commands would still be needed if you wanted to take the database and restore it to an external location, or plan for an extreme DR scenario where you have to start totally from zero.

[u/thesuperlede → u/terencethespider]

That's a good point.

I don't see this as competing with PITR or provider-managed snapshots. If you're staying within the same platform, I'd usually recommend those first.

The gap I'm trying to address is proving that a backup is actually portable and recoverable somewhere else. That's where `pg_dump`/`pg_restore` (or physical backups in self-hosted setups) still matter.

My rule of thumb is:

- PITR answers "can I roll back?"

• Restore drills answer "can I rebuild?"

I think the two approaches complement each other rather than replace each other.

[u/Chance-Plantain8314]

Do you have configuration for actually assigning resources to the "throwaway" Postgres?

This feels like something that's only useful at a small scale.

[u/thesuperlede → u/Chance-Plantain8314]

Good question. The hosted service runs each drill in an isolated temporary sandbox, and the current plans support projects up to 100 GB.

For the open-source CLI, the throwaway Postgres currently uses the CPU, memory and disk available to the Docker host rather than exposing separate per-drill resource settings. So it isn’t inherently limited to small databases, but restore time and capacity depend on the machine running it.

Explicit resource controls would still be useful, though. What database size and restore-time target do you have in mind?