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>
In-Reply-To: <490e8a5c-f1f5-40fe-8243-f1c8c18d03f2@aklaver.com>
From: Koen De Groote <kdg.dev@gmail.com>
Date: Sat, 8 Jun 2024 19:40:50 +0200
Message-ID: <CAGbX52GF=n7-vi5VnfnMXKkf=CfbX_nAgmy3=eezLU6XWXqjfw@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="0000000000006f8ebf061a646ba0"
Archived-At: <https://www.postgresql.org/message-id/CAGbX52GF%3Dn7-vi5VnfnMXKkf%3DCfbX_nAgmy3%3DeezLU6XWXqjfw%40mail.gmail.com>
Precedence: bulk

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

What I'm trying to do is upgrade a PG11 database to PG16, using logical
replication.

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.

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

On Fri, Jun 7, 2024 at 5:15=E2=80=AFPM Adrian Klaver <adrian.klaver@aklaver=
.com>
wrote:

> On 6/6/24 15:19, Koen De Groote wrote:
> > I'll give them a read, though it might take a few weekends
> >
> > Meanwhile, this seems to be what I'm looking for:
> >
> >  From
> >
> https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLI=
CATION-SLOTS
> <
> https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLI=
CATION-SLOTS
> >
> >
> > " Replication slots provide an automated way to ensure that the primary
> > does not remove WAL segments until they have been received by all
> > standbys, and that the primary does not remove rows which could cause a
> > recovery conflict
> > <
> https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONF=
LICT>
> even when the standby is disconnected."
> >
> > I'm reading that as: "if there is a replication slot, if the standby is
> > disconnected, WAL is kept"
> >
> > And if we know WAL is kept in the "pg_wal" directory, that sounds like
> > it could slowly but surely fill up disk space.
> >
> >
> > But again, I'll give them a read. I've read all of logical replication
> > already, and I feel like I didn't get my answer there.
>
> It would be a good idea to provide an a fairly specific outline of what
> you are trying to achieve, then it would be easier for folks to offer
> suggestions on what to do or not to do.
>
> >
> > Thanks for the help
> >
> >
> > Regards,
> > Koen De Groote
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>

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

<div dir=3D"ltr"><div>What I&#39;m trying to do is upgrade a PG11 database =
to PG16, using logical replication.<br></div><div><br></div><div>The
 PG11 has an active and a standby, there are a handful of databases. On=20
particular one has a few tables just over 100GB, then a few 100 tables=20
near 1GB.</div><div><br></div><div>What I&#39;d do is start a publication w=
ith no tables and add them 1 at a time, refreshing subscription each time.<=
/div><div><br></div><div>This
 might take a long time, so my main questions relate to potential=20
network issues or various situations where the instance receiving the=20
logical replication, suddenly stop being able to receive.</div><div><br></d=
iv><div>Resyncing, and the effects of WAL buildup, are my main concern.</di=
v><div><br></div><div>Accidentally sent a mail to only your email, sorry fo=
r that.<br></div><div><br></div><div>Regards,</div><div>Koen De Groote</div=
></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr"=
>On Fri, Jun 7, 2024 at 5:15=E2=80=AFPM Adrian Klaver &lt;<a href=3D"mailto=
:adrian.klaver@aklaver.com">adrian.klaver@aklaver.com</a>&gt; wrote:<br></d=
iv><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bord=
er-left:1px solid rgb(204,204,204);padding-left:1ex">On 6/6/24 15:19, Koen =
De Groote wrote:<br>
&gt; I&#39;ll give them a read, though it might take a few weekends<br>
&gt; <br>
&gt; Meanwhile, this seems to be what I&#39;m looking for:<br>
&gt; <br>
&gt;=C2=A0 From <br>
&gt; <a href=3D"https://www.postgresql.org/docs/current/warm-standby.html#S=
TREAMING-REPLICATION-SLOTS" rel=3D"noreferrer" target=3D"_blank">https://ww=
w.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION-SLOTS=
</a> &lt;<a href=3D"https://www.postgresql.org/docs/current/warm-standby.ht=
ml#STREAMING-REPLICATION-SLOTS" rel=3D"noreferrer" target=3D"_blank">https:=
//www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION-S=
LOTS</a>&gt;<br>
&gt; <br>
&gt; &quot; Replication slots provide an automated way to ensure that the p=
rimary <br>
&gt; does not remove WAL segments until they have been received by all <br>
&gt; standbys, and that the primary does not remove rows which could cause =
a <br>
&gt; recovery conflict <br>
&gt; &lt;<a href=3D"https://www.postgresql.org/docs/current/hot-standby.htm=
l#HOT-STANDBY-CONFLICT" rel=3D"noreferrer" target=3D"_blank">https://www.po=
stgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT</a>&gt; eve=
n when the standby is disconnected.&quot;<br>
&gt; <br>
&gt; I&#39;m reading that as: &quot;if there is a replication slot, if the =
standby is <br>
&gt; disconnected, WAL is kept&quot;<br>
&gt; <br>
&gt; And if we know WAL is kept in the &quot;pg_wal&quot; directory, that s=
ounds like <br>
&gt; it could slowly but surely fill up disk space.<br>
&gt; <br>
&gt; <br>
&gt; But again, I&#39;ll give them a read. I&#39;ve read all of logical rep=
lication <br>
&gt; already, and I feel like I didn&#39;t get my answer there.<br>
<br>
It would be a good idea to provide an a fairly specific outline of what <br=
>
you are trying to achieve, then it would be easier for folks to offer <br>
suggestions on what to do or not to do.<br>
<br>
&gt; <br>
&gt; Thanks for the help<br>
&gt; <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>

--0000000000006f8ebf061a646ba0--