MIME-Version: 1.0
References: <8386a590-62e4-4ce9-8c62-37dfa496f464@Spark>
In-Reply-To: <8386a590-62e4-4ce9-8c62-37dfa496f464@Spark>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Tue, 24 Feb 2026 08:40:38 -0500
Message-ID: <CANzqJaDO2LOaAGiyvyQN54AJBs8BRZ1CkWXWTwPdG1WM7ttbBg@mail.gmail.com>
Subject: Re: Recovery Verification
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000000bbe12064b9209f2"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaDO2LOaAGiyvyQN54AJBs8BRZ1CkWXWTwPdG1WM7ttbBg%40mail.gmail.com>
Precedence: bulk

--0000000000000bbe12064b9209f2
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Feb 24, 2026 at 1:12=E2=80=AFAM <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 yo=
u
> 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.

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

--0000000000000bbe12064b9209f2
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Feb 24, 2026 at 1:12=E2=80=AFAM &=
lt;<a href=3D"mailto:dolan@directdemocracysolutions.com">dolan@directdemocr=
acysolutions.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_=
container"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.=
8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">


<div>
<div name=3D"messageBodySection">
<div>Hi folks,</div>
<div>=C2=A0</div>
<div>When performing database recovery tests, after restoring from backup i=
s 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? W=
hat metadata do you capture and where do you save the test report? Do you u=
se automation? Is it built in-house, off-the-shelf, or open-source?</div>
<div>=C2=A0</div>
<div>Thanks, I&#39;m not a DBA but no one else works at my company so any p=
ointers would be appreciated.</div></div></div></blockquote><div></div></di=
v><div><br></div><div>If using pg_backup/pg_restore, then something like th=
is is perfectly adequate:</div><div><span style=3D"color:rgb(0,0,0);font-si=
ze:14.4px"><font face=3D"monospace">pg_backup ... $DB 2&gt; backup_</font><=
/span><span style=3D"color:rgb(0,0,0);font-family:monospace;font-size:14.4p=
x">$(date +&quot;%F_%T&quot;)</span><span style=3D"font-family:monospace;co=
lor:rgb(0,0,0);font-size:14.4px">.log || mail -s &quot;ERROR: backup failed=
 at $(date +\&quot;%F %T\&quot;)&quot; <a href=3D"mailto:dolan@example.com"=
>dolan@example.com</a></span></div><div><font face=3D"monospace"><span styl=
e=3D"color:rgb(0,0,0);font-size:14.4px">pg_restore=C2=A0</span><span style=
=3D"background-color:rgb(248,249,250);color:rgb(0,0,0);font-size:14.4px">--=
exit-on-error</span>=C2=A0... $DB=C2=A0</font><span style=3D"color:rgb(0,0,=
0);font-size:14.4px"><font face=3D"monospace">2&gt; restore_</font></span><=
span style=3D"color:rgb(0,0,0);font-family:monospace;font-size:14.4px">$(da=
te +&quot;%F_%T&quot;)</span><span style=3D"font-family:monospace;color:rgb=
(0,0,0);font-size:14.4px">.log=C2=A0</span><span style=3D"color:rgb(0,0,0);=
font-family:monospace;font-size:14.4px">|| mail -s &quot;ERROR: restore fai=
led at $(date +\&quot;%F %T\&quot;)&quot; <a href=3D"mailto:dolan@example.c=
om">dolan@example.com</a></span></div><div><span style=3D"color:rgb(0,0,0)"=
><font face=3D"arial, sans-serif" style=3D""><br></font></span></div><div><=
span style=3D"color:rgb(0,0,0)"><font face=3D"arial, sans-serif" style=3D""=
>Then you know to check the log file to see what happened.=C2=A0</font></sp=
an></div><div><span style=3D"color:rgb(0,0,0)"><font face=3D"arial, sans-se=
rif" style=3D""><br></font></span></div><div><span style=3D"color:rgb(0,0,0=
)"><font face=3D"arial, sans-serif" style=3D"">My business users don&#39;t =
trust that, so I created a simple, fast, imperfect script which I run at th=
e same time as the backup:</font></span></div><div><span style=3D"color:rgb=
(0,0,0)"><font face=3D"monospace">BEGIN;</font></span></div><div><span styl=
e=3D"color:rgb(0,0,0)"><font face=3D"monospace">SELECT COUNT(*) FROM table_=
1;</font></span></div><div><span style=3D"color:rgb(0,0,0)"><font face=3D"m=
onospace">SELECT COUNT(*) FROM table_2;</font></span></div><font face=3D"mo=
nospace">...</font><div><div><span style=3D"color:rgb(0,0,0)"><font face=3D=
"monospace">SELECT COUNT(*) FROM table_N;</font></span></div><div><span sty=
le=3D"color:rgb(0,0,0)"><font face=3D"monospace">COMMIT;</font></span></div=
><div><span style=3D"color:rgb(0,0,0)"><font face=3D"arial, sans-serif" sty=
le=3D""><br></font></span></div><div><span style=3D"color:rgb(0,0,0)"><font=
 face=3D"arial, sans-serif" style=3D"">Run the same script on the restored =
database.=C2=A0 The=C2=A0two log=C2=A0files have always been identical.</fo=
nt></span></div><div><span style=3D"color:rgb(0,0,0)"><font face=3D"arial, =
sans-serif" style=3D""><br></font></span></div><span class=3D"gmail_signatu=
re_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div di=
r=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil m=
e, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div><=
/div></div></div></div></div>

--0000000000000bbe12064b9209f2--