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>
In-Reply-To: <CAGbX52GF=n7-vi5VnfnMXKkf=CfbX_nAgmy3=eezLU6XWXqjfw@mail.gmail.com>
From: Justin <zzzzz.graf@gmail.com>
Date: Sat, 8 Jun 2024 16:33:34 -0400
Message-ID: <CALL-XeOZ36D2z5qcxhF0qf-m9+ArioRVXhN9KEUCgMM69UjMLA@mail.gmail.com>
Subject: Re: Questions on logical replication
To: Koen De Groote <kdg.dev@gmail.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, 
	PostgreSQL General <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000002c6101061a66d538"
Archived-At: <https://www.postgresql.org/message-id/CALL-XeOZ36D2z5qcxhF0qf-m9%2BArioRVXhN9KEUCgMM69UjMLA%40mail.gmail.com>
Precedence: bulk

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

On Sat, Jun 8, 2024 at 1:41=E2=80=AFPM Koen De Groote <kdg.dev@gmail.com> w=
rote:

> 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. O=
n
> particular one has a few tables just over 100GB, then a few 100 tables ne=
ar
> 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
>
>>
>>
This approach does not prevent WAL build up.

The WAL build up occurs during the initial sync worker once that table is
synced the WAL is replayed and released.   The parent worker then become
responsible for replaying the WAL for that table

The WAL build up is during the initial sync of the data by table NOT during
the entire synce of all the tables that have been published.

For 1 gb table the initial sync will be very fast so I doubt any individual
table will cause any significant WAL build up to put the publisher at risk
of of crashing

Once a table becomes synced the main subscriber worker keeps the WAL
replayed.  If there are any errors during the replay of WAL such as missing
indexes for Replica Identities during an Update or Delete  this will cause
the main subscriber worker slot on the publisher to start backing up WAL
files. If there are missing replica identities the affected tables will
have to be dropped from the publication and subscription refreshed.  The
WAL  file is already written with incorrect information so the table on the
subscriber table is most likely not in recoverable state.

I suggest confirming all tables have replica identities or primary keys
before going any further.    With PG 11 avoid REPLICA IDENTITY FULL as this
causes full table scan on the subscriber for PG 15 and eariler.  PG 16 on
the subsciber can use a different unique index that has NOT NULL for all
participating columns if the publisher is using  Replicate Identity FULL on
the published table

One must understand the above before deploying logical replication.

Hope this helps

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

<div dir=3D"ltr"><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"g=
mail_attr">On Sat, Jun 8, 2024 at 1:41=E2=80=AFPM Koen De Groote &lt;<a hre=
f=3D"mailto:kdg.dev@gmail.com">kdg.dev@gmail.com</a>&gt; wrote:<br></div><b=
lockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-le=
ft:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>What =
I&#39;m trying to do is upgrade a PG11 database to PG16, using logical repl=
ication.<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><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"><br></blockquote></div></blockquote><div><br><div dir=3D"ltr">Th=
is approach does not prevent WAL build up.<br><br>The WAL build up occurs d=
uring the initial sync worker once that table is synced the WAL is replayed=
 and released.=C2=A0 =C2=A0The parent worker then become responsible for re=
playing the WAL for that table<br><br></div>The WAL build up is during the =
initial sync of the data by table NOT during the entire synce=C2=A0of all t=
he tables that have been published.<br><br>For 1 gb table the initial sync =
will be very fast so I doubt any individual table will cause any significan=
t WAL build up to put the publisher at risk of of crashing<br><br>Once a ta=
ble becomes synced the main subscriber worker keeps the WAL replayed.=C2=A0=
 If there are any errors during the replay of WAL such as missing indexes f=
or Replica Identities during an Update or Delete=C2=A0 this will cause the =
main subscriber worker slot on the publisher to start backing up WAL files.=
 If there are missing replica identities the affected tables will have to b=
e dropped from the publication and subscription refreshed.=C2=A0 The WAL=C2=
=A0 file is already written with incorrect information so the table on the =
subscriber table is most likely not in recoverable state.=C2=A0<br>=C2=A0<b=
r>I suggest confirming all tables have replica identities or primary keys b=
efore going any further.=C2=A0 =C2=A0 With PG 11 avoid REPLICA IDENTITY FUL=
L as this causes full table scan on the subscriber for PG 15 and eariler.=
=C2=A0 PG 16 on the subsciber can use a different unique index that has NOT=
 NULL for all participating columns if the publisher is using=C2=A0 Replica=
te Identity FULL on the published table<br><br>One must understand the abov=
e before deploying logical replication.=C2=A0<br><br>Hope this helps<br><br=
></div></div></div>

--0000000000002c6101061a66d538--