MIME-Version: 1.0
References: <CAEcMXhmBGDWQnVwvhcg-2SPn4PM=PS+aZCouZiu=FEusevWskA@mail.gmail.com>
In-Reply-To: <CAEcMXhmBGDWQnVwvhcg-2SPn4PM=PS+aZCouZiu=FEusevWskA@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Tue, 13 Aug 2024 07:58:04 -0400
Message-ID: <CANzqJaB=_v+X-o8ubg5HniT4KH9gvsAAQHcDeJ65WoMK6kvJ1Q@mail.gmail.com>
Subject: Re: PG Dump on 11 - Restore on 16 - is possible?
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000241ff3061f8f5372"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaB%3D_v%2BX-o8ubg5HniT4KH9gvsAAQHcDeJ65WoMK6kvJ1Q%40mail.gmail.com>
Precedence: bulk

--000000000000241ff3061f8f5372
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Aug 13, 2024 at 5:47=E2=80=AFAM Durumdara <durumdara@gmail.com> wro=
te:

> Hello!
>
> We have to upgrade our server. The PG upgrade is impossible because of to=
o
> much data.
> So we will create a new server with PG 16, and we have to migrate the
> databases one by one (from a PG 11 server).
>
> Is it possible?
>
> Does PG16 read PG11 dumps and restore them correctly on the PG16 server?
>

https://www.postgresql.org/docs/release/16.0/
"See Section 19.6 <https://www.postgresql.org/docs/16/upgrading.html> for
general information on migrating to new major releases."

Section 19.6: https://www.postgresql.org/docs/16/upgrading.html
"Current releases of the dump programs can read data from any server
version back to 9.2."

The major flaw in Section 19.6 is that it repeatedly says "use pg_dumpall",
and only mentions pg_dump once.  Don't use pg_dumpall; use pg_dump in
"directory mode" with multiple threads.  ("pg_dumpall --globals" is
critical for roles and whatnot.)

This is how I recently migrated three multi-TB PG 9.6 RHEL6 servers to PG
14 on RHEL8.

--=20
Death to America, and butter sauce.
Iraq lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Aug 13, 2024 at 5:47=E2=80=AFAM D=
urumdara &lt;<a href=3D"mailto:durumdara@gmail.com">durumdara@gmail.com</a>=
&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_=
quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,=
204);padding-left:1ex"><div dir=3D"ltr">Hello!<div><br></div><div>We have t=
o upgrade our server. The PG upgrade is impossible because of too much=C2=
=A0data.</div><div>So we will create a new server with PG 16, and we have t=
o migrate the databases one by one (from a PG 11 server).</div><div><br></d=
iv><div>Is it possible?</div><div><br></div><div>Does PG16 read PG11 dumps =
and restore them correctly on the PG16 server?</div></div></blockquote><div=
><br></div><div><a href=3D"https://www.postgresql.org/docs/release/16.0/">h=
ttps://www.postgresql.org/docs/release/16.0/</a><br></div><div><span style=
=3D"color:rgb(0,0,0);font-family:&quot;Open Sans&quot;,sans-serif;font-size=
:14.4px">&quot;See=C2=A0</span><a class=3D"gmail-xref" href=3D"https://www.=
postgresql.org/docs/16/upgrading.html" title=3D"19.6.=C2=A0Upgrading a Post=
greSQL Cluster" style=3D"box-sizing:border-box;text-decoration-line:none;ba=
ckground-color:transparent;font-weight:600;font-family:&quot;Open Sans&quot=
;,sans-serif;font-size:14.4px">Section=C2=A019.6</a><span style=3D"color:rg=
b(0,0,0);font-family:&quot;Open Sans&quot;,sans-serif;font-size:14.4px">=C2=
=A0for general information on migrating to new major releases.&quot;</span>=
<br></div><div><span style=3D"color:rgb(0,0,0);font-family:&quot;Open Sans&=
quot;,sans-serif;font-size:14.4px"><br></span></div><div><span style=3D"col=
or:rgb(0,0,0);font-family:&quot;Open Sans&quot;,sans-serif;font-size:14.4px=
">Section 19.6:=C2=A0</span><a href=3D"https://www.postgresql.org/docs/16/u=
pgrading.html">https://www.postgresql.org/docs/16/upgrading.html</a></div><=
div>&quot;<span style=3D"color:rgb(0,0,0);font-family:&quot;Open Sans&quot;=
,sans-serif;font-size:14.4px">Current releases of the dump programs can rea=
d data from any server version back to 9.2.&quot;</span></div></div><div><b=
r></div><div>The major flaw in Section 19.6 is that it repeatedly says &quo=
t;use pg_dumpall&quot;, and only mentions pg_dump once.=C2=A0 Don&#39;t use=
 pg_dumpall; use pg_dump in &quot;directory mode&quot; with multiple=C2=A0t=
hreads.=C2=A0 (&quot;pg_dumpall --globals&quot; is critical for roles and w=
hatnot.)</div><div><br></div><div>This is how I recently migrated three mul=
ti-TB PG 9.6 RHEL6 servers to PG 14 on RHEL8.</div><div><br></div><span cla=
ss=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail=
_signature"><div dir=3D"ltr">Death to America, and butter sauce.<div>Iraq l=
obster!</div></div></div></div>

--000000000000241ff3061f8f5372--