Date: Fri, 23 Aug 2024 08:13:40 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: How to validate restore of backup?
Message-ID: <20240823061340.hxsf3kfwweoez5tf@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <CAKS+vVZSg=LDpFThxZQyemYE470Hbb15W-YZDHv=-fEZjML2QA@mail.gmail.com>
 <CAPnRvGsmb57R7cD3KFjN6p=YrxXen-E_Xbs9QKHx7REKQfz--g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="h6urqo35rvuhiwhw"
Content-Disposition: inline
In-Reply-To: <CAPnRvGsmb57R7cD3KFjN6p=YrxXen-E_Xbs9QKHx7REKQfz--g@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20240823061340.hxsf3kfwweoez5tf%40hjp.at>
Precedence: bulk


--h6urqo35rvuhiwhw
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2024-08-22 16:09:47 +0500, Muhammad Usman Khan wrote:
> For validation of databases, you can use the following approach
>=20
> /usr/pgsql-16/bin/pg_dump -d postgres -h localhost -p 5428 | md5sum > /va=
r/lib/
> pgsql/db1.txt
> /usr/pgsql-16/bin/pg_dump -d postgres -h localhost -p 5420 | md5sum > /va=
r/lib/
> pgsql/db2.txt
> diff db1.txt db2.txt
>=20
> By executing above queries, if diff is null then it means there is no
> difference between source and destination databases.

But on the other hand, if the diff is null, it doesn't mean there is a
(meaningful) difference between the databases.

For example, pg_dump records version information at the start:

-- Dumped from database version 14.13 (Ubuntu 14.13-0ubuntu0.22.04.1)
-- Dumped by pg_dump version 14.13 (Ubuntu 14.13-0ubuntu0.22.04.1)

If your target machine uses a slightly different version of postgres or
a different OS, these will be different. And an md5sum only tells you
that something is different, not what is different.

There might also be small, inconsequential differences in the data.
While it is very likely that a dump just after a restore returns rows in
a table in the same order, it is not guaranteed. If either the source or
the destination database was in use after the restore, some data may
have changed. And so on.


> On Thu, 22 Aug 2024 at 16:06, Vince McMahon <sippingonesandzeros@gmail.co=
m>
> wrote:
>=20
>     Hi,
>=20
>     I have some questions When doing pg_restore of backup of a database t=
o a
>     NEW server.=A0=A0
>=20
>     Is there a way to ensure the data integrity is in tact, and user ID a=
nd
>     access works liked how it was in the old server?

And of course your method doesn't check at all whether "user ID and
access works liked how it was in the old server".

        hp

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--h6urqo35rvuhiwhw
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmbIKI4ACgkQ8g5IURL+
KF2zKBAAltwzroku7sBfc620jTgFtOLLlcatkMCr4wkRGQQnsz9UsEjHKGaGkM/k
7lmsOsW9VIDIf9/1MAQI6Hi2EXompQ5tPT0RK1FY0n2YHLl0zt8dJFil/SCl3LFg
af+fJe3+wQGBWH8gfGlv3GFxB4uAhC7/SSo/OKkSZYGbQ6uNvlWuoCl5DuCJCfa5
emk3VXNDLJGgrRF4EfnQEx2rNc4HdRYLeXmiKbDiWrVbpkp+Ahf/FTmXoLE92DKd
oqQwmi5dCmytMdIveH9C43qldWO69RS1D0+PAchdkrJ2Ih8/d/Pfb53DEDEvXtRy
R70jfu3g7SRPDLI0qFTs2//tMJRaZ7nHcWw0AuTiCom9C75uptodIcMiLP8Jpkmf
Ca9DiWtGjrF4HdkrqeHJI51VVmEI8O7mBs43pqptg+/qUhU41QVSP8wkVl77KFyQ
XLN5hXtBrH4dOdxigeuw/VZyB4Xvg0EPvzj/wJrY90aUaFmwHu0eiL5zN99GlrIh
m1OC/0CssUIBsfwbIefWEwRdEgm1+ykc9dDh1ttTKKeZa5gB8fbC7EynMvRS1V3z
JZy4nmxk2oMdO+yWqncsaoSRfQL6zYedNwFx84+sEUf/qwc3nSOAQHhzvtuz7+2/
cN9pJgurGSx1oSDkFezAeBlAAZlkY8nfPa25k7fPDJ0BXBEuQ3g=
=LaZQ
-----END PGP SIGNATURE-----

--h6urqo35rvuhiwhw--