A Staging Database That Rebuilds Itself From Production Every Night
A Python script, a systemd timer and four checks that restore production into staging every night. The one that mattered most was counting the tables, because pg_restore exits 0 on an empty dump.

Most backup setups get tested once, on the day somebody actually needs them. On one platform I look after I didn't want to find out that way, so every night at half past three the staging database is thrown away and rebuilt from production's latest dump. If the backups ever stop being restorable, I hear about it in Slack the next morning, not in the middle of an outage.
It's a small Python script and a systemd timer. Most of the interesting parts are the checks in between.

The night, in order
Production is dumped at 02:40 and shipped to object storage at a different provider from the one the servers run on, with a 30-day retention lock. A stolen key can hide a backup but not delete it early, and that includes me with the wrong credentials on a bad day.
The refresh timer fires at 03:30, which leaves about 45 minutes after the dump lands. I measured when the dump actually lands instead of trusting the backup timer, because systemd's OnCalendar is local time and the object timestamps are UTC.
ini[Timer] OnCalendar=*-*-* 03:30:00 # If the box was down at 03:30, refresh on the next boot # instead of working against a stale copy all day. Persistent=true RandomizedDelaySec=180
The first thing the script checks is the age of the newest dump. If it's more than 26 hours old, production's backup hasn't run, and it refuses. Without that check a backup that silently stopped a month ago still gives you a staging box full of plausible data, and nothing anywhere says it's a month stale.
A restore can succeed and still be empty
After the restore it counts the tables, and this is the part I'd copy into any setup like it. pg_restore will happily exit 0 on a dump that contains nothing, so the exit code on its own tells you very little.
pythontables = count_tables(SCRATCH) live = count_tables(DB) if tables < MIN_TABLES: raise RuntimeError(f"restored only {tables} tables, below the floor of {MIN_TABLES}") if live and tables < live / 2: raise RuntimeError(f"restored {tables} tables but staging had {live}. " "Refusing to overwrite; check prod.")
There are two floors. A fixed one catches an empty or truncated dump. The second compares against what staging already has, because a dump that suddenly lost half its tables is a production problem worth shouting about, not something to copy over the top.
The fixed floor is 15, while production has 19 tables. Dropping a table while the platform is still being built should be a migration, not a page at 03:30.
Only when both pass does the script drop staging's database and rename the scratch one into its place. If anything fails before that, staging stays exactly as it was yesterday. A failed run also leaves the scratch database behind on purpose, since that's the thing you want to look at when working out why it was rejected.
Watching it fail
I don't trust a guard until I've seen it fire. When I raised the floor from 0 to 15, production's most recent dump had been taken before its first migration and held no tables at all, which was the perfect test. The run said restored only 0 tables, below the floor of 15, exited 1, and staging's 19 tables were untouched.
What the failure tests didn't catch was a bug on the success path. A rewrite had deleted a variable that the very last line still used, so a perfectly good restore reported itself as failed. Three deliberate failure tests went straight past it, because that line only runs when everything works. So now I test the happy path separately too, which sounds obvious written down.
It flipped the order I deploy in
This took me a moment to see. Staging is overwritten from production every night, so anything that only exists on staging is gone by morning, schema included. Migrate staging and not production, and the next refresh wipes the migration.
So production gets migrated first, every time. That's true even though production still doesn't run the application yet, only the one-shot migrator. It felt backwards for about a day. Now it's just how deploys work there, and staging can't drift away from what production really looks like.
Things to know before copying it
Staging now holds real customer data. It sits behind the same sign-in as production, with no shared test password and no public URL, and outbound mail from anything running there has to point at a test mailbox. If that isn't acceptable for your data you need a scrub step in the middle, and then the scrub needs testing the same way.
The job also runs at low CPU and IO weight, so it only slows down if someone is actually using the box at 03:30. And it needs somewhere to complain. A nightly job that fails quietly is worse than no job, because everyone assumes it works.
This is part of the infrastructure work I do for clients.