Restores that resurrect last month’s holes

Why restores regress posture

Consistency cuts one way by design: Tuesday’s rows cannot live inside Monday’s schema, so the engine replays one instant wholesale and security state rides as cargo. Teams discover this mid-incident, when the recovery run to survive downtime quietly reintroduces exposure. Discovery order compounds the pain: uptime returns first, relief follows, and the regression surfaces hours later when someone notices data behaving strangely — by which point the restore is load-bearing infrastructure nobody wants to touch again in a hurry.

Drift velocity compounds it. Fast-moving projects accumulate hardening continuously; the further back the target sits, the larger the bundle of undone fixes arriving alongside recovered data. A restore reaching back a week can unwind several rounds of tightening; one reaching back a month can unwind a quarter of it. The recovery runbook documents restoring data; it never mentions the second ledger of undone hardening travelling inside the same operation.

Building the post-restore checklist

Treat every restore as a schema event wearing a data-event costume. Before reopening traffic, inventory restored posture against expectation. Expectation is the operative word: without a written picture of intended posture, the inventory below produces a list you can read but not judge, so keep the last known-good capture somewhere a future recovery run can reach it:

-- After restore, compare posture to expectation
SELECT tablename, policyname, cmd
FROM pg_catalog.pg_policies
WHERE schemaname = 'public'
ORDER BY tablename;

Verifying from outside

Catalog queries tell you what the restored database believes; outside verification tells you what it actually serves, and the two disagree often enough after restores to warrant both. Probe the endpoints your public traffic uses as the anon caller and compare response shapes against pre-restore captures: a table that filtered rows before the restore and returns them after is announcing a regression no inventory query flagged.

Check storage the same way. Attempt an anonymous fetch against buckets that should be private; a successful download means the flip-back predates your restore target. Finish with the credentials question: list every service key older than the recovery target and rotate forward anything the restore may have resurrected, since key eras travel inside the snapshot like everything else.

Where monitoring earns its keep

Cross-scan matching makes this case almost automatic: findings whose stable keys existed historically and vanished recently return labelled regressed rather than created — accurate words, since these are old problems resurfacing, not new mistakes. The label lands within one scan cycle of the restore completing, with the original resolution date attached.

Wire it into the runbook and the checklist writes itself: restore completes, next scan fires, on-call reconciles the regression list before traffic returns. Finding history exists precisely so this reconciliation is reading, not archaeology. Practise the sequence once deliberately — restore into staging, let the scan fire, walk the regression list — and the real version becomes procedure instead of improvisation, performed by people who have seen the report format before.

Frequently asked

Does PITR really restore policies too?

Yes — recovery replays a consistent moment including DDL and catalog state. Policies, grants and buckets return exactly as they were at the target time, flaws faithfully included. That consistency is the entire guarantee and the entire hazard: you cannot take the data without the flaws, because the flaws live in the same catalog the data lives in. Verification after restore is therefore mandatory hygiene, not optional diligence.

How would we even notice regressions?

By diffing post-restore posture against recent history. Scheduled scanning automates the comparison and labels returned findings as regressions within one cycle of completion. The label matters operationally: regressed findings link their original resolution dates, so reconciliation becomes reading a short list instead of re-auditing the whole estate. Manual teams approximate this by keeping dated posture captures and comparing them after every recovery run.

Should we stop using restores?

Never — availability outranks nearly everything. Pair restores with verification so recovering uptime does not silently purchase a security incident instead. The correct instinct treats restores as schema events wearing data-event costumes: availability first, then a mandatory posture reconciliation before traffic fully returns. Skipping the second half converts one outage into an outage followed by a slower, stranger incident.

Do logical replicas have the same effect?

Read replicas mirror production continuously, so no rewind — but promote-a-replica disaster recovery behaves like a restore to replication lag time, same verification discipline applies. Promotion replays whatever the replica last received, which includes any hardening the primary gained moments earlier. Treat promotion completion exactly like PITR completion: verify posture from outside before reopening writes, because the lag window is a small restore target.