Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t2vAR-008Z2P-61 for pgsql-admin@arkaria.postgresql.org; Mon, 21 Oct 2024 16:22:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1t2vAP-009JcF-Ac for pgsql-admin@arkaria.postgresql.org; Mon, 21 Oct 2024 16:22:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t2vAO-009Jc6-Q9 for pgsql-admin@lists.postgresql.org; Mon, 21 Oct 2024 16:22:01 +0000 Received: from mail-oi1-x22f.google.com ([2607:f8b0:4864:20::22f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t2vAM-002FWt-Uo for pgsql-admin@postgresql.org; Mon, 21 Oct 2024 16:22:00 +0000 Received: by mail-oi1-x22f.google.com with SMTP id 5614622812f47-3e5fef69f2eso2307079b6e.3 for ; Mon, 21 Oct 2024 09:21:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1729527716; x=1730132516; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=bTFyhDWIx2k9DleeyUP1vfObdCwjVh3JW41GtPRnD9w=; b=b2RKyXxRXmHKv4fjEE4o4JPePSd1pYf64aQQJkIErhOKrM5nDTPtYh4hLq672oVEKG yE5+FH7obuyMT5c91e4hh1dU3rOKbYZpvEyFD/nG8gfEf4GDrl264D3GcALID/zFyH7o WEXRBJyWRsLUiHkT0KIfjNmpo6Yb8p1ZtvrMr92Z9wyTBtBbW1GiF2KNbIOANCPj1lma L8j8pTL+G0Mc1Y9OLSC5qeV3k6NQxB/SLuFbcBFCwHCV3fcfEQ+1q5CXHCp4wAv+lE+6 IyibRjHYfTJ1aEnZb6Vtrp+JE+YnhwtDUchZKo493fp8DcpCLnFfyirmOoNvtJJEKPXh leeg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1729527716; x=1730132516; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=bTFyhDWIx2k9DleeyUP1vfObdCwjVh3JW41GtPRnD9w=; b=Ia2HYTyMMWQ/ZcDeGm7YG8nVR0gKJ+dWcVjxS87O9AFilQIv/tb8lQx6vEKWIbF2Sw 7fxJoAjqy9AqeZF9w+OpZSu9KDD81Ypeog8SOQAFAkDaZYG9eyRZqz+sRjnDccQEsmsk P5op0nH/54B6Rs0Hy0/Vz7KEMzr6YYcYxH8EGtHIx+7ElkBwjaij4K9wCjPL8DpmAVjk rZ9dLRAG/A0JQy7+7ljrCMUPqQMquAgXkcvO4yDRTBrxRyT64cmNH9HYG/G3knlnWDot 2lK8PrZggPgDWA8Zc1nsOLY8fNde2n4XJa7CQ7rAbFQuompfd4QqZNmrqguOR42Y1tf0 2Awg== X-Gm-Message-State: AOJu0YylYkb2UeoTIsmXDyWVgrWqi7dU0Ky0MX3ChLHaLiIkMygxNxE1 xWamzVTZmXe2WJM/cC0rxgnWVfdQv1O1ogjuwS26bsQVNmwsaI8no5ougvW2Hm51yE3PT4tZq5C vTqDA+crdYiPlWl63WO4HxKgOypGznw== X-Google-Smtp-Source: AGHT+IFin+sTP1zIpbgfnvZwZg/Zs3+YNmWBBzBTjx+RDXRX/1NCbJPfgpWzi9REDwN5GlcEVO1xVWvtnvFkuL7TgfA= X-Received: by 2002:a05:6808:1904:b0:3e3:9a29:c6f2 with SMTP id 5614622812f47-3e602c7a9f2mr10384365b6e.6.1729527716571; Mon, 21 Oct 2024 09:21:56 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Mon, 21 Oct 2024 12:21:45 -0400 Message-ID: Subject: Re: Verify or dump pgdata files To: "pgsql-admin@postgresql.org" Content-Type: multipart/alternative; boundary="0000000000002cf6340624ff0d3e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002cf6340624ff0d3e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Use PgBackRest or pg_basebackup. Not only do they correctly handle wal files, but do checksums. On Mon, Oct 21, 2024 at 11:51=E2=80=AFAM Murthy Nunna wro= te: > Hi Muhammad, > > > > Thank you for responding to my query. > > > > I don=E2=80=99t think checksum method will work in my situation because t= he 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 fil= e > such as beginning block and end block, beginning of file and end of file > etc. All I want to know is =E2=80=9Ccompleteness=E2=80=9D and =E2=80=9Cre= adability=E2=80=9D of the file by > postgres. > > > > Thanks. > > > > > > *From:* Muhammad Ikram > *Sent:* Sunday, October 20, 2024 10:34 PM > *To:* Murthy Nunna > *Cc:* pgsql-admin@postgresql.org > *Subject:* Re: Verify or dump pgdata files > > > > [EXTERNAL] =E2=80=93 This message is from an external sender > > Hi, > > > > Could you use diff ? e.g. > > > > diff -qr /var/lib/pgsql/15/data /tmp/data > > > > Or > > > > pg_checksums --verify --data-dir=3D/pgdata (for checksum enabled databas= es) > > > > 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=E2=80=AFAM Murthy Nunna wr= ote: > > 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 agains= t > data files in /pgdata. Just to verify after =E2=80=9Crsync=E2=80=9D of /p= gdata. > > > > Thanks in advance for your response. > > > > > > > > > > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. crustacean! --0000000000002cf6340624ff0d3e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Use PgBackRest or pg_basebackup.=C2=A0 Not only do th= ey correctly handle wal files, but do checksums.

On Mon, Oct 21, 2024 at 11:51=E2=80=AFAM Murthy Nunna <mnunna@fnal.gov> wrote:

Hi Muhammad,

=C2=A0<= /span>

Thank you for respond= ing to my query.

=C2=A0<= /span>

I don=E2=80=99t think= checksum method will work in my situation because the source file would ha= ve changed by the time I run checksum. So, the checksum (or diff) will be d= ifferent.

I am doing rsync of a= ctive cluster.

=C2=A0<= /span>

What I am looking for= is physical (structural) verification of target file such as beginning blo= ck and end block, beginning of file and end of file etc. All I want to know= is =E2=80=9Ccompleteness=E2=80=9D and =E2=80=9Creadability=E2=80=9D of the file by postgres.

=C2=A0<= /span>

Thanks.=

=C2=A0<= /span>

=C2=A0<= /span>

From: Muhammad Ikram <me.mikram@gmail.com>
Sent: Sunday, October 20, 2024 10:34 PM
To: Murthy Nunna <mnunna@fnal.gov>
Cc: = pgsql-admin@postgresql.org
Subject: Re: Verify or dump pgdata files

=C2=A0

[EXTERNAL] =E2=80=93 This message is from an external sender

Hi,

=C2=A0

Could you use diff ? e.g.

=C2=A0

diff -qr /var/lib/pgsql/15/data /tmp/data<= /u>

=C2=A0

Or

=C2=A0

pg_checksums --verify --data-dir=3D/pgdata=C2=A0 (fo= r checksum enabled databases)

=C2=A0

or do checksum on source and target and take diff be= tween two checksum generated files e.g.

=C2=A0

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

=C2=A0

=C2=A0

Regards,

Ikram

=C2=A0

=C2=A0

=C2=A0


--
Death to <Redacted>, and butter sauce.
Don&#= 39;t boil me, I'm still alive.
<Redacted> crustacean= !
--0000000000002cf6340624ff0d3e--