Subject: Verify or dump pgdata files

From: Murthy Nunna <[email-masked]>
Date: Sat, 19 Oct 2024 23:45:22 +0000

Hi,

We are currently using pg_waldump to verify integrity of WAL files after archiving them in external storage. If the WAL is incomplete pg_waldump returns error, which is great.

Similarly, I am wondering if there is any utility that can be used against data files in /pgdata. Just to verify after "rsync" of /pgdata.

Thanks in advance for your response.

---

From: Muhammad Ikram <[email-masked]>
Date: Mon, 21 Oct 2024 08:33:31 +0500

Hi,

Could you use diff ? e.g.

diff -qr /var/lib/pgsql/15/data /tmp/data

Or

pg_checksums --verify --data-dir=/pgdata  (for checksum enabled databases)

or do checksum on source and target and take diff between two checksum
generated files e.g.

cd /pgdata
find . -type f -exec sha256sum {} \; > /tmp/source_checksums.txt

Regards,
Ikram

On Sun, Oct 20, 2024 at 4:45 AM Murthy Nunna <[email-masked]> wrote:

> Hi,
>
>
>
> We are currently using pg_waldump to verify integrity of WAL files after
> archiving them in external storage. If the WAL is incomplete pg_waldump
> returns error, which is great.
>
>
>
> Similarly, I am wondering if there is any utility that can be used against
> data files in /pgdata. Just to verify after “rsync” of /pgdata.
>
>
>

---

From: Murthy Nunna <[email-masked]>
Date: Mon, 21 Oct 2024 15:51:44 +0000

Hi Muhammad,

Thank you for responding to my query.

I don’t think checksum method will work in my situation because the source file would have changed by the time I run checksum. So, the checksum (or diff) will be different.
I am doing rsync of active cluster.

What I am looking for is physical (structural) verification of target file such as beginning block and end block, beginning of file and end of file etc. All I want to know is “completeness” and “readability” of the file by postgres.

Thanks.

From: Muhammad Ikram <[email-masked]>
Sent: Sunday, October 20, 2024 10:34 PM
To: Murthy Nunna <[email-masked]>
Cc: [email-masked]
Subject: Re: Verify or dump pgdata files

[EXTERNAL] – This message is from an external sender
Hi,

Or

or do checksum on source and target and take diff between two checksum generated files e.g.

cd /pgdata

Regards,
Ikram

On Sun, Oct 20, 2024 at 4:45 AM Murthy Nunna <[email-masked]<mailto:[email-masked]>> wrote:
Hi,

Similarly, I am wondering if there is any utility that can be used against data files in /pgdata. Just to verify after “rsync” of /pgdata.

---

From: Ron Johnson <[email-masked]>
Date: Mon, 21 Oct 2024 12:21:45 -0400

Use PgBackRest or pg_basebackup.  Not only do they correctly handle wal
files, but do checksums.

On Mon, Oct 21, 2024 at 11:51 AM Murthy Nunna <[email-masked]> wrote:

> I don’t think checksum method will work in my situation because the source
> file would have changed by the time I run checksum. So, the checksum (or
> diff) will be different.
>
> What I am looking for is physical (structural) verification of target file
> such as beginning block and end block, beginning of file and end of file
> etc. All I want to know is “completeness” and “readability” of the file by
> postgres.
>
>
>
> Thanks.
>
>
>
>
>
> *From:* Muhammad Ikram <[email-masked]>
> *Sent:* Sunday, October 20, 2024 10:34 PM
> *To:* Murthy Nunna <[email-masked]>
> *Cc:* [email-masked]
> *Subject:* Re: Verify or dump pgdata files
>
>
>

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

---

From: Alvaro Herrera <[email-masked]>
Date: Mon, 21 Oct 2024 20:31:31 +0200

On 2024-Oct-19, Murthy Nunna wrote:

> Similarly, I am wondering if there is any utility that can be used
> against data files in /pgdata. Just to verify after "rsync" of
> /pgdata.

Maybe you could use pg_verifybackup for this, assuming you do your
"rsync" with pg_basebackup.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/