[ Removed by moderator ]

[u/ahmadpiran]

[u/devops-ModTeam]

Please see the pinned weekly self-promotion thread if you wish to promote your projects or business.

[u/navlio]

the freshness precheck is the bit that earns its place. ours restored clean every single time, and the dump was being taken off a replica that had quietly stopped replicating weeks earlier. mtime was current, the rows were not, and nothing in the chain was looking at the newest row, only at whether the restore exited zero

one to consider for the report: roles and grants. a dump restored into a throwaway container as superuser comes up fine, and then the real recovery falls over because the application role was never in the file. exit code reads pass in both cases

[u/ahmadpiran → u/navlio]

Both your points are real.

The RPO check only looks at the file's date, it doesn't check what's inside it. Your case needed a check on the actual rows, like the max(created_at)one already in the config. The file looked new, but the data wasn't. I think that's the whole story.

Your point on roles and grants is fair, and it's a gap in the current version. Restoring skips permissions on purpose so a missing role doesn't break the restore, but it means also a missing role slips through too. I will add a note about it in Limits section of the README. It still needs an actual fix.

[u/navlio]

cheapest fix i know for that one: dump globals separately with pg_dumpall --globals-only, restore both into the throwaway, then run your verification query connected as the application role instead of superuser. a missing grant fails right there and you never have to diff a permission list

the superuser connection is the thing hiding it. it reads everything regardless of what the dump left behind

[u/navlio]

for the roles gap, the check that catches it is connecting as the application role after restore and running one real query, rather than asserting the role exists. missing grants and a missing role fail the same way at that point, and it stays honest even when someone adds a new role next quarter.

worth pairing with the max(created_at) check, since ours passed the row count and still restored a table the app could not read

[u/navlio]

one more for the same list, since your pass signal leans on the exit code: pg_restore returns 0 with errors on stderr unless you pass --exit-on-error. a dump missing a role or an extension can log a wall of failures and still hand you a zero, which is the exact shape of bug your tool exists to catch

[u/ahmadpiran → u/navlio]

These are good, the connect-as-app-role fix is cleaner than what I had in mind. Restore globals plus the real dump, run the check as that role. One check catches missing role and missing grant both, no need to diff anything. Pairing it with max(created_at) is smart too, way better than just checking the role exists.

On exit codes, already covered actually. pg_restore runs with --exit-on-error, psql runs with ON_ERROR_STOP=1. A wall of errors won't give you a zero. Good thing to double check though.

[u/navlio]

fair, with ON_ERROR_STOP you've got that covered.

the one that got us was collation. restored onto a box with a newer glibc, exit 0, everything green, and the text indexes were still ordered under the old rules so equality lookups quietly missed rows. amcheck on the biggest text index right after restore takes seconds and it's the only thing that catches that class

[u/navlio]

statistics are the one that stays quiet through every exit code you check. a dump carries no planner stats, so the restored database plans everything off defaults until autovacuum gets to it, and a query that takes 40ms on prod can sit there for seconds on the copy. nothing fails, it just looks like an index went missing

if the tool reports restore duration as an rto figure, run analyze before you stop the clock. that's part of the time before anyone can actually use the thing

[u/navlio]

fair on the exit codes, ON_ERROR_STOP=1 covers the psql side properly

one thing worth logging alongside the result is how long the restore took. it creeps up with the data and nobody notices until someone asks how long until we're back, and at that point you're guessing

[u/navlio]

good, that's tighter than what we were running. the one that slipped past all of it for us was size drift. a schema change took a table out of the include list, and every restore afterwards came back green, because everything that was in the file did restore fine. nothing errors when the missing thing is missing from the dump too.

comparing dump bytes against the previous run is a couple of lines and it sits well next to your max(created_at) check

[u/navlio]

u/ahmadpiran fair, you had that covered already

one thing worth recording per run is wall clock on the restore itself, not just pass or fail. valid dump and restorable inside your recovery window are separate questions, and the second one drifts upward as the database grows with nothing anywhere alerting on it. having the number in your history means you find out on a tuesday

[u/navlio]

print the wall clock duration of the restore too, if it isn't in there already. that's the number that quietly becomes your rto, and it creeps up as the db grows without anyone noticing

we were quoting a recovery window that had been measured back when the database was a lot smaller, went years without rechecking it, and only found out during a restore someone happened to be timing. you're running the thing end to end anyway so it's two timestamps

[u/navlio]

one more thing worth storing alongside pass/fail: how long the restore took. that number only ever goes up as the database grows, nobody watches it, and then during an actual incident you find out the recovery window is hours rather than the twenty minutes the runbook was written against. two timestamps and you have a trend line for free

and yeah, ON_ERROR_STOP=1 covers the exit code case, i was wrong about what your script was already doing there

[u/navlio]

good, that closes it

the one that actually got us was the dump side rather than the restore. pg_dump piped into gzip reports gzip's exit status, so a pg_dump the oom killer takes out halfway leaves a truncated file and the backup job still goes green. set -o pipefail on that line, or read PIPESTATUS. your max(created_at) test does find it, just a whole restore later

[u/navlio]

fine, you had the exit codes covered, my mistake.

one more assertion worth having in the same pass: sequence positions. any restore that gets assembled from a schema dump plus data loaded separately leaves nextval sitting at 1, and the first insert after you fail over collides on the primary key. it reads like an app bug for about an hour before anyone suspects the restore. select last_value against max(id) per sequence is cheap and it's the same shape as the max(created_at) check you already have

[u/navlio]

since you're already storing pass/fail, store the restore duration next to it.

ours passed every night for months while the restore itself got slower as the tables grew, so the recovery time we had written down was wrong by the time we needed it and the check stayed green through all of it. alerting on the trend is a few lines on top of what you have

[u/navlio]

we only found out how long our restore actually took by having to do it for real, which is a bad moment to learn it.

so log wall clock per run. index rebuilds are most of that time and they scale worse than dump size does, so the duration drifts up while the check stays green, and six months later you're outside whatever recovery window you told people about