Thread

1. Recovery Verification

   dolan@directdemocracysolutions.com — 2026-02-23T22:07:47Z

   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.
   
   -Dolan
   

2. Re: Recovery Verification

   Ron Johnson <ronljohnsonjr@gmail.com> — 2026-02-24T13:40:38Z

   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!
   

3. Re: Recovery Verification

   Adrian Klaver <adrian.klaver@aklaver.com> — 2026-02-24T15:50:33Z

   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
   
   
   
   

4. Re: Recovery Verification

   Ron Johnson <ronljohnsonjr@gmail.com> — 2026-02-24T19:16:41Z

   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!
   

5. Re: Recovery Verification

   dmurvihill@gmail.com — 2026-02-25T18:03:32Z

   Thanks! That was very helpful.
   On Feb 24, 2026 at 11:17 -0800, Ron Johnson <ronljohnsonjr@gmail.com>, wrote:
   > 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!