MIME-Version: 1.0
References: <CAGbX52FxL9eR=jmS3ACgRC=tEm=xj_xMmFoOxO+wVA+oURW=kA@mail.gmail.com>
 <1628637f-419f-4f6a-9cb6-07af90cd0bc4@aklaver.com> <CAGbX52HTgb2jYYO5eVDu=xB5-bjAra+34=AUFg4Xk0NpCRjnyg@mail.gmail.com>
 <1c0273f5-a90a-48f6-b51f-fe15c16fa1c6@aklaver.com> <CAGbX52EMG+d-BR5SzDP+rQsrdxT32=CXD5XCT2YCmkTpr3jesw@mail.gmail.com>
 <490e8a5c-f1f5-40fe-8243-f1c8c18d03f2@aklaver.com> <CAGbX52GF=n7-vi5VnfnMXKkf=CfbX_nAgmy3=eezLU6XWXqjfw@mail.gmail.com>
 <fe521e78-0bbb-4e33-97c7-948fd995bea6@aklaver.com>
In-Reply-To: <fe521e78-0bbb-4e33-97c7-948fd995bea6@aklaver.com>
From: Koen De Groote <kdg.dev@gmail.com>
Date: Tue, 11 Jun 2024 23:38:02 +0200
Message-ID: <CAGbX52FVdt4_LnPAa_C=kzx7Za9342JtHTfVs=ucvjXVMCAXVA@mail.gmail.com>
Subject: Re: Questions on logical replication
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: PostgreSQL General <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000438dc1061aa415b5"
Archived-At: <https://www.postgresql.org/message-id/CAGbX52FVdt4_LnPAa_C%3Dkzx7Za9342JtHTfVs%3DucvjXVMCAXVA%40mail.gmail.com>
Precedence: bulk

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

> Have you looked at pg_upgrade?:

I have, but I want to keep downtime to a minimum and from my understanding
the switching of a fully synced logical replica only requires updating your
sequences. Which should be possible in less than 60 seconds.

> 1 GB each?

Yes, each. Roughly around there.


On Sat, Jun 8, 2024 at 7:46=E2=80=AFPM Adrian Klaver <adrian.klaver@aklaver=
.com>
wrote:

> On 6/8/24 10:40, Koen De Groote wrote:
> > What I'm trying to do is upgrade a PG11 database to PG16, using logical
> > replication.
>
> Have you looked at pg_upgrade?:
>
> https://www.postgresql.org/docs/current/pgupgrade.html
>
> >
> > The PG11 has an active and a standby, there are a handful of databases.
> > On particular one has a few tables just over 100GB, then a few 100
> > tables near 1GB.
>
> 1 GB each?
>
> >
> > What I'd do is start a publication with no tables and add them 1 at a
> > time, refreshing subscription each time.
> >
> > This might take a long time, so my main questions relate to potential
> > network issues or various situations where the instance receiving the
> > logical replication, suddenly stop being able to receive.
> >
> > Resyncing, and the effects of WAL buildup, are my main concern.
> >
> > Accidentally sent a mail to only your email, sorry for that.
> >
> > Regards,
> > Koen De Groote
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>

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

<div dir=3D"ltr"><div>&gt; Have you looked at pg_upgrade?: <br></div><div><=
br></div><div>I have, but I want to keep downtime to a minimum and from my =
understanding the switching of a fully synced logical replica only requires=
 updating your sequences. Which should be possible in less than 60 seconds.=
</div><div><br></div><div>&gt;=20
1 GB each? <br></div><div><br></div><div>Yes, each. Roughly around there.<b=
r></div><div><br></div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr=
" class=3D"gmail_attr">On Sat, Jun 8, 2024 at 7:46=E2=80=AFPM Adrian Klaver=
 &lt;<a href=3D"mailto:adrian.klaver@aklaver.com">adrian.klaver@aklaver.com=
</a>&gt; wrote:<br></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">=
On 6/8/24 10:40, Koen De Groote wrote:<br>
&gt; What I&#39;m trying to do is upgrade a PG11 database to PG16, using lo=
gical <br>
&gt; replication.<br>
<br>
Have you looked at pg_upgrade?:<br>
<br>
<a href=3D"https://www.postgresql.org/docs/current/pgupgrade.html" rel=3D"n=
oreferrer" target=3D"_blank">https://www.postgresql.org/docs/current/pgupgr=
ade.html</a><br>
<br>
&gt; <br>
&gt; The PG11 has an active and a standby, there are a handful of databases=
. <br>
&gt; On particular one has a few tables just over 100GB, then a few 100 <br=
>
&gt; tables near 1GB.<br>
<br>
1 GB each?<br>
<br>
&gt; <br>
&gt; What I&#39;d do is start a publication with no tables and add them 1 a=
t a <br>
&gt; time, refreshing subscription each time.<br>
&gt; <br>
&gt; This might take a long time, so my main questions relate to potential =
<br>
&gt; network issues or various situations where the instance receiving the =
<br>
&gt; logical replication, suddenly stop being able to receive.<br>
&gt; <br>
&gt; Resyncing, and the effects of WAL buildup, are my main concern.<br>
&gt; <br>
&gt; Accidentally sent a mail to only your email, sorry for that.<br>
&gt; <br>
&gt; Regards,<br>
&gt; Koen De Groote<br>
<br>
-- <br>
Adrian Klaver<br>
<a href=3D"mailto:adrian.klaver@aklaver.com" target=3D"_blank">adrian.klave=
r@aklaver.com</a><br>
<br>
</blockquote></div>

--000000000000438dc1061aa415b5--