Subject: Re: Recovery Verification

From: Ron Johnson <[email-masked]>
Date: Tue, 24 Feb 2026 08:40:38 -0500

On Tue, Feb 24, 2026 at 1:12 AM <[email-masked]> wrote:

> Hi folks,
>
> When performing database recovery tests, after restoring from backup is
> complete, what SOPs and tools do you use to sample your database contents
> and verify the data looks correct? Do you have a list of queries to run?
> What metadata do you capture and where do you save the test report? Do you
> use automation? Is it built in-house, off-the-shelf, or open-source?
>
> Thanks, I'm not a DBA but no one else works at my company so any pointers
> would be appreciated.
>

If using pg_backup/pg_restore, then something like this is perfectly
adequate:
pg_backup ... $DB 2> backup_$(date +"%F_%T").log || mail -s "ERROR: backup
failed at $(date +\"%F %T\")" [email-masked]
pg_restore --exit-on-error ... $DB 2> restore_$(date +"%F_%T").log || mail
-s "ERROR: restore failed at $(date +\"%F %T\")" [email-masked]

Then you know to check the log file to see what happened.

My business users don't trust that, so I created a simple, fast, imperfect
script which I run at the same time as the backup:
BEGIN;
SELECT COUNT(*) FROM table_1;
SELECT COUNT(*) FROM table_2;
...
SELECT COUNT(*) FROM table_N;
COMMIT;

Run the same script on the restored database.  The two log files have
always been identical.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 24 Feb 2026 07:50:33 -0800

On 2/24/26 5:40 AM, Ron Johnson wrote:
> On Tue, Feb 24, 2026 at 1:12 AM <[email-masked] 

> If using pg_backup/pg_restore, then something like this is perfectly 

Where is pg_backup coming from?

-- 
Adrian Klaver
[email-masked]