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 1sh5v3-00CoCr-GO for pgsql-general@arkaria.postgresql.org; Thu, 22 Aug 2024 11:23:57 +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 1sh5v1-001iYU-Ls for pgsql-general@arkaria.postgresql.org; Thu, 22 Aug 2024 11:23:56 +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 1sh5v1-001iYM-9p for pgsql-general@lists.postgresql.org; Thu, 22 Aug 2024 11:23:55 +0000 Received: from mail-oo1-xc32.google.com ([2607:f8b0:4864:20::c32]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sh5ux-000wOM-3G for pgsql-general@lists.postgresql.org; Thu, 22 Aug 2024 11:23:55 +0000 Received: by mail-oo1-xc32.google.com with SMTP id 006d021491bc7-5d5e97b8adbso410310eaf.1 for ; Thu, 22 Aug 2024 04:23:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1724325830; x=1724930630; darn=lists.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=pfOR/xYXOd19FcIUEGpWRfX0G3Z9Wg/9sjA8AMt25LU=; b=IGePwA4CPF99rMXCR4CuYl0l5Zen+8Dtkp4gSNJ09kdBXj1HknZAPgI2t1LuIZoREi dE9OYaMfG6LsKytXCZ1SbFPCivz+qC2BCWtOAzkV6UQE30/X/Ow5a3IOTW/6bpG9rbvc Rq+pL+tbqJo8UW2PaAA7VWUO57m3mWe06LBCfrbHkV9Axm4YtLcdKNvhU+pcALTJGveJ 2xfkRXPFqGjwi+mlm/H7+MRdy+L0PXZpz2ina6FOrzMaAkvhohG4YBnrTW/WGmn+45tn PI3wfcbAjn3U2Lkx8gHPbh6/iFcfLk/U+0OOBuGgfzyccXxNcmqo8hsJ+YCEJwHYutK5 wRcA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1724325830; x=1724930630; 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=pfOR/xYXOd19FcIUEGpWRfX0G3Z9Wg/9sjA8AMt25LU=; b=RXgf+XBuA0RbGG0BiRQG9d+7L8SogV2MganrqfhcObZDNJzgDeUNBjPilafSaIIr5k mjsxK27DFzsnwacWQy9Qih233CwSzg2gX/p7kruNiG8uR6O3EE93EMGRG92LeZclDhds hfiSCIwGDXfoT6qxO0EP4r0mObWlVXZ95MyCX8Z6VPMEOxiVSazxvcX5wQV3cw6JfLfK gsGRbh9P71HLVm1CuSvWhThwm4wbonYKyayEPL+N3pP/ywiFBO7cPWErehTQ7GIXo7nl SjWLWgysFVLqjDVlSUuZLR2PBERpYpFG31/m04eUKfI8B0Ou5kjTlYN3mFMRFA2Gi+JW UBwA== X-Gm-Message-State: AOJu0YzzfQiYyJDEe4VFU78Mqo+Ow0Gxsk13Kpa95YP/gdzxSJb9L8Fv Ls/TU9OjAEenqzVqCZ5Chy8CVRQNHQHAMzw9U3BR8T8dhcAYg55ah5emhFIlDCe7ZnTewUZ9cDr rTZ/aA41ERrhUr+m4W8uIi852b57NqQ== X-Google-Smtp-Source: AGHT+IGug+h2XsT+jP9tsirDTJerbhbOe15z6j3vv98gGr9WPKvyVBS0NBzNLkoyQYGCm+lQo7Gm4iwb7dBiEgqNA0g= X-Received: by 2002:a05:6820:1ad5:b0:5da:a4a7:8f6a with SMTP id 006d021491bc7-5dcb667f890mr1957196eaf.4.1724325830076; Thu, 22 Aug 2024 04:23:50 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Thu, 22 Aug 2024 07:23:38 -0400 Message-ID: Subject: Re: How to validate restore of backup? To: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000943261062043e456" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000943261062043e456 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable That's great on small databases. Not so practical when they're big. On Thu, Aug 22, 2024 at 7:10=E2=80=AFAM Muhammad Usman Khan wrote: > Hi Vince, > 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. Adjust your port and > databases accordingly. > > On Thu, 22 Aug 2024 at 16:06, Vince McMahon > 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? >> >> How to properly handle the materialized views when backing up and >> restoring? >> >> Thanks. >> > --=20 Death to America, and butter sauce. Iraq lobster! --000000000000943261062043e456 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
That's great on small databases.=C2=A0 Not so pra= ctical when they're big.

On Thu, A= ug 22, 2024 at 7:10=E2=80=AFAM Muhammad Usman Khan <usman.k@bitnine.net> wrote:
Hi Vince,
For validation of databases, you can use the followin= g approach

/usr/pgsql-16/bin/pg_dump -d postgres -h local= host -p 5428 | md5sum > /var/lib/pgsql/db1.txt
/usr/pgsql-16/b= in/pg_dump -d postgres -h localhost -p 5420 | md5sum<= span style=3D"color:rgb(36,36,36);font-family:source-code-pro,Menlo,Monaco,= "Courier New",Courier,monospace;font-size:14px;letter-spacing:-0.= 308px;white-space:pre-wrap;background-color:rgb(249,249,249)"> > /var/li= b/pgsql/db2.txt
diff db1.txt db2.txt

= By executing above queries, if diff is null then it means there is no diffe= rence between source and destination databases. Adjust your port and databa= ses accordingly.

On Thu, 22 Aug 2024 at 16:06, Vince McMahon <sippingonesandzeros= @gmail.com> wrote:
Hi,

I have some questions When doing pg_restore of backup of a database to a= NEW server.=C2=A0=C2=A0

Is there a way to ensure the data integrity is in tact, and user ID and ac= cess works liked how it was in the old server?

<= div dir=3D"auto">How to properly handle the materialized views when backing= up and restoring?

Thank= s.


--
Death to America, and butter sauce.
Iraq lobster!
<= /div>
--000000000000943261062043e456--