Recovery Verification

On Tue, Feb 24, 2026 at 1:12 AM <dolan@directdemocracysolutions.com> 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\")" dolan@example.com
pg_restore --exit-on-error ... $DB 2> restore_$(date +"%F_%T").log || mail
-s "ERROR: restore failed at $(date +\"%F %T\")" dolan@example.com

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!

On 2/24/26 5:40 AM, Ron Johnson wrote:

On Tue, Feb 24, 2026 at 1:12 AM <dolan@directdemocracysolutions.com

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

Where is pg_backup coming from?

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

--
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, Feb 24, 2026 at 10:50 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 2/24/26 5:40 AM, Ron Johnson wrote:

On Tue, Feb 24, 2026 at 1:12 AM <dolan@directdemocracysolutions.com

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

Where is pg_backup coming from?

Grrr. I meant to write pg_dump.

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

Thanks! That was very helpful.