MIME-Version: 1.0
References: <CAEcMXh=PtUgwj5exvV8PpyvY0H=eOtUkn-wAPcn7p35vkeHzmg@mail.gmail.com>
In-Reply-To: <CAEcMXh=PtUgwj5exvV8PpyvY0H=eOtUkn-wAPcn7p35vkeHzmg@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Thu, 1 May 2025 09:19:25 -0400
Message-ID: <CANzqJaCn9MyCSsoOaf+Qa=2oT7b==+O1TAHgK9RUwApCoRdaYQ@mail.gmail.com>
Subject: Re: Upgrading PG11 to PG17 without dump/restore
To: Postgres General <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000009b8b75063412e27c"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaCn9MyCSsoOaf%2BQa%3D2oT7b%3D%3D%2BO1TAHgK9RUwApCoRdaYQ%40mail.gmail.com>
Precedence: bulk

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

On Thu, May 1, 2025 at 9:06=E2=80=AFAM Durumdara <durumdara@gmail.com> wrot=
e:

> Hello!
>
> There is a heavily used server, with older debian, and PG11.
> The data is more than 1,2 TB.
> The PG_Upgrade is not possible because of lesser space and too old debian=
.
>
> As we see now we have only one way to move this server.
> 1.) Installing a new server with actual debian.
> 2.) Installing the newest PG (17) on it.
> 3.) Stop work on one database. Dump it in the old, restore it in the new
> and start the work with that. So we can move them one by one.
>
> But this seems to be very hard, because we need to do this through an
> internet connection, and the data is too much.
>

The new server isn't in the same data center?


> I have a question about it - is there a better way to do this?
>
> For example we make a new cluster element (a read only slave) with newest
> debian/PG, and use it to move the data in the background (replication).
> And then we rename it to master. But I don't know if it's possible or not=
.
> Maybe the slaves must be the same version as the master.
>
> The main problem is that debian is too old, and we are afraid to use
> PG_Upgrade because of too many version differences (11 < 17).
>

The fear is justified, since collation changes might corrupt text indices.


> But maybe you have some good advice, how to do this with less complicatio=
n.
> Users can tolerate short downtimes, but not longer ones.
>

Logical replication, not physical replication.  Works great if there's not
many DDL changes.

Another possibility is COPY TO / COPY FROM in chunks.  This will work if
most of the 1.2TB is inserted, and then never updated or deleted.  That
way, at cutover, you only need to COPY TO / COPY FROM and dump/restore
(which is really a wrapper around COPY TO / COPY FROM) and "pg_restore
--section=3Dpost-data".

You (hopefully) know your system better than we do, so you'd have to decide
which is better.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, May 1, 2025 at 9:06=E2=80=AFAM Du=
rumdara &lt;<a href=3D"mailto:durumdara@gmail.com">durumdara@gmail.com</a>&=
gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquot=
e class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px s=
olid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">Hello!<br><br>Ther=
e is a heavily used server, with older debian, and PG11.<div>The data is mo=
re than 1,2 TB.</div><div>The PG_Upgrade is not possible because of lesser =
space and too old debian.</div><div><br></div><div>As we see now we have on=
ly one way to move this server.</div><div>1.) Installing a new server with =
actual debian.</div><div>2.) Installing the newest PG (17) on it.</div><div=
>3.) Stop work on one database. Dump it in the old, restore it in the new a=
nd start the work with that. So we can move them one by one.</div><div><br>=
</div><div>But this seems to be very hard, because we need to do this throu=
gh an internet connection, and the data is too much.</div></div></blockquot=
e><div><br></div><div>The new server isn&#39;t in the same data center?</di=
v><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0p=
x 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div d=
ir=3D"ltr"><div>I have a question about it - is there a better way to do th=
is?</div><div><br></div><div>For=C2=A0example we make a new cluster element=
 (a read only slave) with newest debian/PG, and use it to move the data in =
the background (replication).<br>And then we rename it to master. But I don=
&#39;t know if it&#39;s possible or not.</div><div>Maybe the slaves must be=
 the same version as the master.</div><div><br></div><div>The main problem =
is that debian is too old, and we are afraid to use PG_Upgrade=C2=A0because=
 of too many version differences=C2=A0(11 &lt; 17).</div></div></blockquote=
><div><br></div><div>The fear is justified, since collation changes might c=
orrupt text indices.</div><div>=C2=A0</div><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"><div>But maybe you have some good advice=
, how to do this with less complication.</div>Users can tolerate short down=
times, but not longer ones.</div></blockquote><div><br></div><div>Logical r=
eplication, not physical replication.=C2=A0 Works great if there&#39;s not =
many DDL changes.</div><div><br></div><div>Another possibility is COPY TO /=
 COPY FROM in chunks.=C2=A0 This will work if most of the 1.2TB is inserted=
, and then never updated or deleted.=C2=A0 That way, at cutover, you only n=
eed to COPY TO / COPY FROM and dump/restore (which is really a wrapper arou=
nd COPY TO / COPY FROM) and &quot;pg_restore --section=3Dpost-data&quot;.</=
div><div><br></div><div>You (hopefully) know your system better than we do,=
 so you&#39;d have to decide which is better.</div><div><br></div></div><sp=
an class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D=
"gmail_signature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sa=
uce.<div>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redacted&=
gt; lobster!</div></div></div></div></div></div>

--0000000000009b8b75063412e27c--