MIME-Version: 1.0
References: <CAKS+vVZSg=LDpFThxZQyemYE470Hbb15W-YZDHv=-fEZjML2QA@mail.gmail.com>
 <cd28b9d6-60ba-4d16-a1a2-8985e6a00a11@aklaver.com>
In-Reply-To: <cd28b9d6-60ba-4d16-a1a2-8985e6a00a11@aklaver.com>
From: Vince McMahon <sippingonesandzeros@gmail.com>
Date: Thu, 22 Aug 2024 17:31:41 -0400
Message-ID: <CAKS+vVaBQu12o==EUS9XsnNMAMU6Rh+xn_Vxpa79dU0oVN+RgA@mail.gmail.com>
Subject: Re: How to validate restore of backup?
To: "adrian.klaver@aklaver.com" <adrian.klaver@aklaver.com>
Cc: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000001ccdf406204c6331"
Archived-At: <https://www.postgresql.org/message-id/CAKS%2BvVaBQu12o%3D%3DEUS9XsnNMAMU6Rh%2Bxn_Vxpa79dU0oVN%2BRgA%40mail.gmail.com>
Precedence: bulk

--0000000000001ccdf406204c6331
Content-Type: text/plain; charset="UTF-8"

Hi, Adrian.

The largest one is 8 GB after compression.

I have a window of 8 hours to handle 30 GB total of backup at various sizes.


On Thu, Aug 22, 2024, 11:36 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> On 8/22/24 04:06, Vince McMahon wrote:
> > Hi,
> >
> > I have some questions When doing pg_restore of backup of a database to a
> > NEW server.
>
> How large a backup?
>
> >
> > 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?
>
> As to user access, write tests that cover that and run on the new cluster.
>
> Data is trickier and if that is possible to a degree of certainty is
> going to depend on answer to the first question above.
>
> >
> > How to properly handle the materialized views when backing up and
> restoring?
>
> create materialized view prj_mv(p_item_no, year) as select p_item_no,
> year from projection with data;
>
> pg_dump -d production -U postgres -h localhost -t projection -t prj_mv
> -f prj.sql
>
> In prj.sql:
>
> CREATE MATERIALIZED VIEW public.prj_mv AS
>   SELECT p_item_no,
>      year
>     FROM public.projection
>    WITH NO DATA;
>
> COPY public.projection ( ...
>
>
> [...]
>
> REFRESH MATERIALIZED VIEW public.prj_mv;
>
> It is done for you.
>
> >
> > Thanks.
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>

--0000000000001ccdf406204c6331
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto"><div dir=3D"auto">Hi, Adrian.</div><div dir=3D"auto"><br>=
</div><div dir=3D"auto">The largest one is 8 GB after compression.</div><di=
v dir=3D"auto"><br></div><div dir=3D"auto">I have a window of 8 hours to ha=
ndle 30 GB total of backup at various sizes.</div><div dir=3D"auto"></div><=
div dir=3D"auto"><br></div><div dir=3D"auto"><br></div><br><div class=3D"gm=
ail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, Aug 22, 2024, 11:3=
6 AM Adrian Klaver &lt;<a href=3D"mailto:adrian.klaver@aklaver.com" target=
=3D"_blank" rel=3D"noreferrer">adrian.klaver@aklaver.com</a>&gt; wrote:<br>=
</div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-l=
eft:1px #ccc solid;padding-left:1ex">On 8/22/24 04:06, Vince McMahon wrote:=
<br>
&gt; Hi,<br>
&gt; <br>
&gt; I have some questions When doing pg_restore of backup of a database to=
 a <br>
&gt; NEW server.<br>
<br>
How large a backup?<br>
<br>
&gt; <br>
&gt; Is there a way to ensure the data integrity is in tact, and user ID an=
d <br>
&gt; access works liked how it was in the old server?<br>
<br>
As to user access, write tests that cover that and run on the new cluster.<=
br>
<br>
Data is trickier and if that is possible to a degree of certainty is <br>
going to depend on answer to the first question above.<br>
<br>
&gt; <br>
&gt; How to properly handle the materialized views when backing up and rest=
oring?<br>
<br>
create materialized view prj_mv(p_item_no, year) as select p_item_no, <br>
year from projection with data;<br>
<br>
pg_dump -d production -U postgres -h localhost -t projection -t prj_mv <br>
-f prj.sql<br>
<br>
In prj.sql:<br>
<br>
CREATE MATERIALIZED VIEW public.prj_mv AS<br>
=C2=A0 SELECT p_item_no,<br>
=C2=A0 =C2=A0 =C2=A0year<br>
=C2=A0 =C2=A0 FROM public.projection<br>
=C2=A0 =C2=A0WITH NO DATA;<br>
<br>
COPY public.projection ( ...<br>
<br>
<br>
[...]<br>
<br>
REFRESH MATERIALIZED VIEW public.prj_mv;<br>
<br>
It is done for you.<br>
<br>
&gt; <br>
&gt; Thanks.<br>
<br>
-- <br>
Adrian Klaver<br>
<a href=3D"mailto:adrian.klaver@aklaver.com" rel=3D"noreferrer noreferrer" =
target=3D"_blank">adrian.klaver@aklaver.com</a><br>
<br>
</blockquote></div></div>

--0000000000001ccdf406204c6331--