Обсуждение: Recovery Verification
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
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.
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!