Postgres Pro
Downloads
Home   >   mailing lists

Thread: Verify data after backup and restore

Verify data after backup and restore

From
Rajesh Kumar
Date:
Hi all,

What are all things I need to verify once I took backup and restore?

Re: Verify data after backup and restore

From
Holger Jakobs
Date:
Am 13.12.23 um 14:50 schrieb Rajesh Kumar:
> Hi all,
>
> What are all things I need to verify once I took backup and restore?

None, if backup and restore didn't show any errors.

But feel free to write a little program or script interating over all 
tables in both databases to compare the values in them.

-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachment

Re: Verify data after backup and restore

From
Pepe TD Vo
Date:
reviewing the backup logs and reports for any errors, warnings, or failures; checking the backup files for size, date, and format; utilizing checksums or hashes to compare the backup files with the original data files and performing a test restore on a separate server or environment to confirm that the backup files are readable and restorable.

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love, and forgive more.EmojiEmojiEmoji
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success




On Wednesday, December 13, 2023 at 08:51:15 AM EST, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:


Hi all,

What are all things I need to verify once I took backup and restore?

Re: Verify data after backup and restore

From
Ron Johnson
Date:
On Wed, Dec 13, 2023 at 10:16 AM Pepe TD Vo <pepevo@yahoo.com> wrote:
checking the backup files for size, date, and format; utilizing checksums or hashes to compare the backup files with the original data files

 How does that work on constantly changing tables?

Re: Verify data after backup and restore

From
Rajesh Kumar
Date:
Backup and restore I mean during downtime only. I wanted to double check what all things we need to validate after a successful restore. Like table count , all 14 objects count, first and last row of important tables, row count of each table like dat?

On Wed, 13 Dec, 2023, 8:48 PM Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
On Wed, Dec 13, 2023 at 10:16 AM Pepe TD Vo <pepevo@yahoo.com> wrote:
checking the backup files for size, date, and format; utilizing checksums or hashes to compare the backup files with the original data files

 How does that work on constantly changing tables?

Re: Verify data after backup and restore

From
Ron Johnson
Date:

If you think that "grep -E 'ERROR:|WARN:' pg_dump_$DB.log" is insufficient, then write a script to get MIN(), MAX() and COUNT(*) of each table before you do a pg_dump.  After pg_restore, run the same script, but pointed at the new database.

SELE the "first" (presumably ordered by primary key) and "last" records
On Wed, Dec 13, 2023 at 1:49 PM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
Backup and restore I mean during downtime only. I wanted to double check what all things we need to validate after a successful restore. Like table count , all 14 objects count, first and last row of important tables, row count of each table like dat?

On Wed, 13 Dec, 2023, 8:48 PM Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
On Wed, Dec 13, 2023 at 10:16 AM Pepe TD Vo <pepevo@yahoo.com> wrote:
checking the backup files for size, date, and format; utilizing checksums or hashes to compare the backup files with the original data files

 How does that work on constantly changing tables?

Re: Verify data after backup and restore

From
Rajesh Kumar
Date:
Can I get the row count directly from pg_stat_user_indexes?

On Thu, 14 Dec 2023, 10:25 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:

If you think that "grep -E 'ERROR:|WARN:' pg_dump_$DB.log" is insufficient, then write a script to get MIN(), MAX() and COUNT(*) of each table before you do a pg_dump.  After pg_restore, run the same script, but pointed at the new database.

SELE the "first" (presumably ordered by primary key) and "last" records
On Wed, Dec 13, 2023 at 1:49 PM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
Backup and restore I mean during downtime only. I wanted to double check what all things we need to validate after a successful restore. Like table count , all 14 objects count, first and last row of important tables, row count of each table like dat?

On Wed, 13 Dec, 2023, 8:48 PM Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
On Wed, Dec 13, 2023 at 10:16 AM Pepe TD Vo <pepevo@yahoo.com> wrote:
checking the backup files for size, date, and format; utilizing checksums or hashes to compare the backup files with the original data files

 How does that work on constantly changing tables?

Re: Verify data after backup and restore

From
Ron Johnson
Date:
Try it, and find out.

On Thu, Dec 14, 2023 at 12:08 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
Can I get the row count directly from pg_stat_user_indexes?

On Thu, 14 Dec 2023, 10:25 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:

If you think that "grep -E 'ERROR:|WARN:' pg_dump_$DB.log" is insufficient, then write a script to get MIN(), MAX() and COUNT(*) of each table before you do a pg_dump.  After pg_restore, run the same script, but pointed at the new database.

SELE the "first" (presumably ordered by primary key) and "last" records
On Wed, Dec 13, 2023 at 1:49 PM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
Backup and restore I mean during downtime only. I wanted to double check what all things we need to validate after a successful restore. Like table count , all 14 objects count, first and last row of important tables, row count of each table like dat?

On Wed, 13 Dec, 2023, 8:48 PM Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
On Wed, Dec 13, 2023 at 10:16 AM Pepe TD Vo <pepevo@yahoo.com> wrote:
checking the backup files for size, date, and format; utilizing checksums or hashes to compare the backup files with the original data files

 How does that work on constantly changing tables?

Re: Verify data after backup and restore

From
"David G. Johnston"
Date:
On Wednesday, December 13, 2023, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
Can I get the row count directly from pg_stat_user_indexes?

That idea doesn’t make any sense.  What makes you think otherwise?

David J.

Re: Verify data after backup and restore

From
kaido vaikla
Date:
I'm wondering, what is your backup restore solution, when you have such suspicions.
br
Kaido

On Thu, 14 Dec 2023 at 07:15, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wednesday, December 13, 2023, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
Can I get the row count directly from pg_stat_user_indexes?

That idea doesn’t make any sense.  What makes you think otherwise?

David J.