Are there any guidelines on how often you should be doing restore tests?
It probably depends on the criticality of the data, but if you test say every 2 week, you can still fall in the OPs case, right?
At what size/criticality should you have a daily restore test? maybe even a rolling restore test? so you check today's backup, but then check it again every month or something?
Ideally it should be immediately after a logical backup.
For physical backups (ex: wal archiving), a combination of read replicas that are actively queried against, rebuilt from base backups on a regular schedule, and staging master restores lesa frequent yet still regular schedule, will give you a high level of confidence.
Rechecking old backups isn't necessary if you save the hashes of the backup and can compare they still match.
Not immediately (imo); you should push the backup to wherever it's being stored. Then your db test script is the same as your db restore script: both start by downloading the most recent backup. The things you'll catch here are, eg, the process that uploads the dump to s3 deciding to time out after uploading for an hour, NOT fail, silently truncate, and instead exit with 0!
It probably depends on the criticality of the data, but if you test say every 2 week, you can still fall in the OPs case, right?
At what size/criticality should you have a daily restore test? maybe even a rolling restore test? so you check today's backup, but then check it again every month or something?