Re: How to validate restore of backup?

On 2024-08-22 16:09:47 +0500, Muhammad Usman Khan wrote:
> For validation of databases, you can use the following approach
>
> /usr/pgsql-16/bin/pg_dump -d postgres -h localhost -p 5428 | md5sum > /var/lib/
> pgsql/db1.txt
> /usr/pgsql-16/bin/pg_dump -d postgres -h localhost -p 5420 | md5sum > /var/lib/
> pgsql/db2.txt
> diff db1.txt db2.txt
>
> 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(at)gmail(dot)com>
> wrote:
>
> Hi,
>
> I have some questions When doing pg_restore of backup of a database to a
> NEW server.  
>
> Is there a way to ensure the data integrity is in tact, and user ID and
> 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

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