pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedVerify data after backup and restore
10+ messages / 6 participants
[nested] [flat]
* Verify data after backup and restore
@ 2023-12-13 13:50 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-12-13 14:17 ` Re: Verify data after backup and restore Holger Jakobs <holger@jakobs.com>
2023-12-13 15:16 ` Re: Verify data after backup and restore Pepe TD Vo <pepevo@yahoo.com>
0 siblings, 2 replies; 10+ messages in thread
From: Rajesh Kumar @ 2023-12-13 13:50 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi all,
What are all things I need to verify once I took backup and restore?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Verify data after backup and restore
2023-12-13 13:50 Verify data after backup and restore Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2023-12-13 14:17 ` Holger Jakobs <holger@jakobs.com>
1 sibling, 0 replies; 10+ messages in thread
From: Holger Jakobs @ 2023-12-13 14:17 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../../e4bcfd1e-b9c5-edff-a833-523bd76d1b40@jakobs.com/2-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Verify data after backup and restore
2023-12-13 13:50 Verify data after backup and restore Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2023-12-13 15:16 ` Pepe TD Vo <pepevo@yahoo.com>
2023-12-13 15:18 ` Re: Verify data after backup and restore Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Pepe TD Vo @ 2023-12-13 15:16 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>; Rajesh Kumar <rajeshkumar.dba09@gmail.com>
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.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?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Verify data after backup and restore
2023-12-13 13:50 Verify data after backup and restore Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-12-13 15:16 ` Re: Verify data after backup and restore Pepe TD Vo <pepevo@yahoo.com>
@ 2023-12-13 15:18 ` Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-13 18:49 ` Re: Verify data after backup and restore Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Ron Johnson @ 2023-12-13 15:18 UTC (permalink / raw)
To: Pepe TD Vo <pepevo@yahoo.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>; Rajesh Kumar <rajeshkumar.dba09@gmail.com>
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?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Verify data after backup and restore
2023-12-13 13:50 Verify data after backup and restore Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-12-13 15:16 ` Re: Verify data after backup and restore Pepe TD Vo <pepevo@yahoo.com>
2023-12-13 15:18 ` Re: Verify data after backup and restore Ron Johnson <ronljohnsonjr@gmail.com>
@ 2023-12-13 18:49 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-12-14 04:54 ` Re: Verify data after backup and restore Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Rajesh Kumar @ 2023-12-13 18:49 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pepe TD Vo <pepevo@yahoo.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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?
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Verify data after backup and restore
2023-12-13 13:50 Verify data after backup and restore Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-12-13 15:16 ` Re: Verify data after backup and restore Pepe TD Vo <pepevo@yahoo.com>
2023-12-13 15:18 ` Re: Verify data after backup and restore Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-13 18:49 ` Re: Verify data after backup and restore Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2023-12-14 04:54 ` Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-14 05:07 ` Re: Verify data after backup and restore Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Ron Johnson @ 2023-12-14 04:54 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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?
>>
>>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Verify data after backup and restore
2023-12-13 13:50 Verify data after backup and restore Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-12-13 15:16 ` Re: Verify data after backup and restore Pepe TD Vo <pepevo@yahoo.com>
2023-12-13 15:18 ` Re: Verify data after backup and restore Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-13 18:49 ` Re: Verify data after backup and restore Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-12-14 04:54 ` Re: Verify data after backup and restore Ron Johnson <ronljohnsonjr@gmail.com>
@ 2023-12-14 05:07 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-12-14 05:09 ` Re: Verify data after backup and restore Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-14 05:15 ` Re: Verify data after backup and restore David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Rajesh Kumar @ 2023-12-14 05:07 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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?
>>>
>>>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Verify data after backup and restore
2023-12-13 13:50 Verify data after backup and restore Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-12-13 15:16 ` Re: Verify data after backup and restore Pepe TD Vo <pepevo@yahoo.com>
2023-12-13 15:18 ` Re: Verify data after backup and restore Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-13 18:49 ` Re: Verify data after backup and restore Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-12-14 04:54 ` Re: Verify data after backup and restore Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-14 05:07 ` Re: Verify data after backup and restore Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2023-12-14 05:09 ` Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Ron Johnson @ 2023-12-14 05:09 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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?
>>>>
>>>>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Verify data after backup and restore
2023-12-13 13:50 Verify data after backup and restore Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-12-13 15:16 ` Re: Verify data after backup and restore Pepe TD Vo <pepevo@yahoo.com>
2023-12-13 15:18 ` Re: Verify data after backup and restore Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-13 18:49 ` Re: Verify data after backup and restore Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-12-14 04:54 ` Re: Verify data after backup and restore Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-14 05:07 ` Re: Verify data after backup and restore Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2023-12-14 05:15 ` David G. Johnston <david.g.johnston@gmail.com>
2023-12-14 08:09 ` Re: Verify data after backup and restore kaido vaikla <kaido.vaikla@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: David G. Johnston @ 2023-12-14 05:15 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Verify data after backup and restore
2023-12-13 13:50 Verify data after backup and restore Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-12-13 15:16 ` Re: Verify data after backup and restore Pepe TD Vo <pepevo@yahoo.com>
2023-12-13 15:18 ` Re: Verify data after backup and restore Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-13 18:49 ` Re: Verify data after backup and restore Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-12-14 04:54 ` Re: Verify data after backup and restore Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-14 05:07 ` Re: Verify data after backup and restore Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-12-14 05:15 ` Re: Verify data after backup and restore David G. Johnston <david.g.johnston@gmail.com>
@ 2023-12-14 08:09 ` kaido vaikla <kaido.vaikla@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: kaido vaikla @ 2023-12-14 08:09 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2023-12-14 08:09 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-12-13 13:50 Verify data after backup and restore Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-12-13 14:17 ` Holger Jakobs <holger@jakobs.com>
2023-12-13 15:16 ` Pepe TD Vo <pepevo@yahoo.com>
2023-12-13 15:18 ` Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-13 18:49 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-12-14 04:54 ` Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-14 05:07 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-12-14 05:09 ` Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-14 05:15 ` David G. Johnston <david.g.johnston@gmail.com>
2023-12-14 08:09 ` kaido vaikla <kaido.vaikla@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox