A Staging Database That Rebuilds Itself From Production Every Night
Most backups get tested once, on the day you need them. Restoring production into staging every night means the restore path runs 365 times a year instead.
Most backup setups get tested exactly once, on the day somebody needs them. On one client's platform I wanted to avoid that, so the staging database is thrown away and rebuilt from production's latest dump every night at half past three. If the backups stop being restorable, I hear about it in Slack the next morning rather than in the middle of an outage.
What happens at 03:30
Production is dumped nightly and shipped to object storage at a separate provider, under a retention lock, so nothing can delete it early. That includes me with the wrong credentials on a bad day. The refresh job takes the newest dump, restores it into a scratch database on the staging box and checks the result. Only if the checks pass does it swap the scratch database in for the live one.
The swap matters more than it looks. If the restore dies halfway through, staging is left exactly as it was yesterday. Nobody starts the day on a half empty staging database wondering what happened.
A restore can succeed and still be empty
The check that earns its keep is a boring one. After restoring, the job counts the tables. If there are fewer than a set floor, it refuses to swap and posts a failure. pg_restore will quite happily succeed on a dump that contains nothing, so a green exit code on its own tells you very little.
I only trust a guard after I've watched it fire, so I made this one fail on purpose. Production's most recent dump had been taken before its first migration and held no tables at all. Run against that, the job reported restored only 0 tables, below the floor of 15, exited 1 and left staging's 19 tables alone. Exactly what it should do.
The floor sits at 15 and not the full 19 on purpose. While the platform is still being built, dropping a table should be a migration, not a page in the middle of the night.
It changes the order you deploy in
This part 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. Even at a stage when production ran no application at all, only the migrator. It feels backwards for about a day and then it's just how deploys work. It also means staging can't drift away from what production really looks like, and drift is how most staging environments quietly stop being useful.
Before you copy it
Staging now holds real customer data, so it sits behind the same sign-in as production, with no shared test password and no public URL. If that isn't acceptable for your data, you need a scrub step in the middle, and the scrub then has to be tested the same way.
It also needs somewhere to complain. A nightly job that fails silently is worse than no job, because everyone assumes it works.
What you get back is a restore path that runs every single night and a staging environment you can trust to look like production. For a small team that's a lot of confidence from one scheduled job.
This is part of the infrastructure work I do for clients.