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 1sh5hd-00CmXV-Eg for pgsql-general@arkaria.postgresql.org; Thu, 22 Aug 2024 11:10:05 +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 1sh5hb-001TbF-C3 for pgsql-general@arkaria.postgresql.org; Thu, 22 Aug 2024 11:10:03 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sh5ha-001Tb7-Sb for pgsql-general@lists.postgresql.org; Thu, 22 Aug 2024 11:10:03 +0000 Received: from mail-pg1-x52c.google.com ([2607:f8b0:4864:20::52c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sh5hY-000tBk-4e for pgsql-general@lists.postgresql.org; Thu, 22 Aug 2024 11:10:02 +0000 Received: by mail-pg1-x52c.google.com with SMTP id 41be03b00d2f7-7cd9cfe4748so319840a12.2 for ; Thu, 22 Aug 2024 04:09:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=bitnine-net.20230601.gappssmtp.com; s=20230601; t=1724324999; x=1724929799; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=8Nzx9UcqS8gHSztOD8J286YfxBgh0MFlA+9zc20wmr0=; b=DIUT2HkzZHuzXtn9mdBwcQ6dUPiIKu9kPThE9YR03EtjJTtz0p4osFd1TrkOd7sXXl /FfmUFaj/03ULU9zAR8WDogozSS+xscSnpRZMx+O6qrYOJlVVgaFEJWlDd1eLKqajMeL DatK7cIBkc9kgNU7zQu73K2kySpHw1jvll3s70IeW/bSk19SatIMA7wDes+egvDf3v25 +HiuKAgIjKCDxxw9mqtz75U7Cct0BYHMK4LtKEHk1U6aX8tLCrffk/9kiLBjLKsnBKLM g6y3k/Kt69vCz9RDr5ZPLVnUKuYNyuQS1y98pZIY61GunGDHKQiDcyHp6pjxzlaijj7l jxJA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1724324999; x=1724929799; h=cc: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=8Nzx9UcqS8gHSztOD8J286YfxBgh0MFlA+9zc20wmr0=; b=v9lurcM7q/2LvGV3FykEg3F7XG+2YM7J1sx9XROUk+kfGmbZLLxcWABE4U1zyJv1S0 JqW0C2oK5A3Jo89MdHBPQenGzElSHw4MCQGOqW/tFCNR8q45IiHp9r2lL24v5+vYbx30 FUruIdtu0NCGNsi//MAaospn2JZf61HxaMBZIKbJns9fEoBwCVyDn+xkbnyuSifEgDIs Q0zIEIGbAxXY9hUn2/TM07rDpnIx44npkJ9n/qAobxp8aefjVNiiIZRTbeZLUqWlrgli 0SXZxKCeWctvjbz7/Q/z9Plds3JIot3RBIpEpe/WaMbSlOCPdwYjpWQHfHtAT+fCDowN 3WhA== X-Gm-Message-State: AOJu0Yz49mfw+/+sbJchwfMACeHI3M871CfF5Rm4/onjlzfa3SnupFRM YOelw8An12VGzzpGQNlv49LH4O5Zyb+Z+2xJqTJ/trSuVUlC9zm+bA78AlXM+UgeQq7l05ovxO+ L7JYQfiTKEJHQYKPcGCFyatKZ7shJnilayT9rDQ== X-Google-Smtp-Source: AGHT+IFsxoJ9mKScXjNaS2N8r9IOzIGM06eYZoiBpBCVvFPt08lrC7z9Jyl00J2GRS0EzByYCv4M4vtRag6FkQUz4Yc= X-Received: by 2002:a17:90a:f58c:b0:2d3:b9ee:a090 with SMTP id 98e67ed59e1d1-2d6166a590emr1638200a91.0.1724324998666; Thu, 22 Aug 2024 04:09:58 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Muhammad Usman Khan Date: Thu, 22 Aug 2024 16:09:47 +0500 Message-ID: Subject: Re: How to validate restore of backup? To: Vince McMahon Cc: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000060310062043b36c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000060310062043b36c Content-Type: text/plain; charset="UTF-8" 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. > --000000000000060310062043b36c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Vince,
For validation of databases, you can use the = following approach
<= div>
/usr/pgs= ql-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 l= ocalhost -p 5420 | md5sum > /var/lib/pgsql= /db2.txt
d= iff db1.txt db2.txt

By executing abov= e queries, if diff is null then it means there is no difference between sou= rce and destination databases. Adjust your port and databases accordingly.<= /div>
O= n 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 i= ntegrity is in tact, and user ID and access works liked how it was in the o= ld server?

How to properly han= dle the materialized views when backing up and restoring?

Thanks.
--000000000000060310062043b36c--