MIME-Version: 1.0
References: <CAGbX52FxL9eR=jmS3ACgRC=tEm=xj_xMmFoOxO+wVA+oURW=kA@mail.gmail.com>
In-Reply-To: <CAGbX52FxL9eR=jmS3ACgRC=tEm=xj_xMmFoOxO+wVA+oURW=kA@mail.gmail.com>
From: Koen De Groote <kdg.dev@gmail.com>
Date: Wed, 5 Jun 2024 01:02:33 +0200
Message-ID: <CAGbX52FUV0iBekcJVC+qdWpSSYBDrWJ68yB3RhFr6WabZWUfVw@mail.gmail.com>
Subject: Re: Questions on logical replication
To: PostgreSQL General <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000009df0d4061a1872dd"
Archived-At: <https://www.postgresql.org/message-id/CAGbX52FUV0iBekcJVC%2BqdWpSSYBDrWJ68yB3RhFr6WabZWUfVw%40mail.gmail.com>
Precedence: bulk

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

Reading this:
https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICA=
TION-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-CONFL=
ICT>
even when the standby is disconnected. "

Am I to understand that a subscription is considered that same as a
standby, in this context?

On Wed, Jun 5, 2024 at 12:55=E2=80=AFAM Koen De Groote <kdg.dev@gmail.com> =
wrote:

> I recently read the entire documentation on logical replication, but am
> left with a question on the buildup of WAL
>
> On this page:
> https://www.postgresql.org/docs/current/logical-replication-subscription.=
html#LOGICAL-REPLICATION-SUBSCRIPTION-SLOT
>
> It is written: " When dropping a subscription, the remote host is not
> reachable. In that case, disassociate the slot from the subscription usin=
g ALTER
> SUBSCRIPTION before attempting to drop the subscription. If the remote
> database instance no longer exists, no further action is then necessary.
> If, however, the remote database instance is just unreachable, the
> replication slot (and any still remaining table synchronization slots)
> should then be dropped manually; otherwise it/they would continue to
> reserve WAL and might eventually cause the disk to fill up. Such cases
> should be carefully investigated."
>
>
> Assuming a situation where I add tables 1 at a time to the publisher, and
> refresh the subscription every time.
>
> What happens if I shut down the subscriber database for a while? The
> subscription isn't dropped, so am I reading it right that the disk on the
> publisher will slowly be filling up with WAL? Isn't that always the case =
if
> wall is enabled?
>
> This "cause disk to fill up" warning is quite concerning, and I'd like to
> understand what could cause it and how likely it is? I thought logical
> replication uses WAL by default, so doesn't that mean there has to be a l=
og
> of changes kept anyhow? Even if the WAL isn't written to disk by an
> "archive_command"?
>
> Regards,
> Koen De Groote
>

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

<div dir=3D"ltr"><div>Reading this: <a href=3D"https://www.postgresql.org/d=
ocs/current/warm-standby.html#STREAMING-REPLICATION-SLOTS">https://www.post=
gresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION-SLOTS</a></=
div><div><br></div><div>&quot;
Replication slots provide an automated way to ensure that the primary=20
does not remove WAL segments until they have been received by all=20
standbys, and that the primary does not remove rows which could cause a <a =
class=3D"gmail-link" href=3D"https://www.postgresql.org/docs/current/hot-st=
andby.html#HOT-STANDBY-CONFLICT" title=3D"27.4.2.=C2=A0Handling Query Confl=
icts">recovery conflict</a> even when the standby is disconnected.

&quot;<br></div><div><br></div><div>Am I to understand that a subscription =
is considered that same as a standby, in this context?<br></div></div><br><=
div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, Jun=
 5, 2024 at 12:55=E2=80=AFAM Koen De Groote &lt;<a href=3D"mailto:kdg.dev@g=
mail.com">kdg.dev@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gm=
ail_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>I recently read the entire=
 documentation on logical replication, but am left with a question on the b=
uildup of WAL</div><div><br></div><div>On this page: <a href=3D"https://www=
.postgresql.org/docs/current/logical-replication-subscription.html#LOGICAL-=
REPLICATION-SUBSCRIPTION-SLOT" target=3D"_blank">https://www.postgresql.org=
/docs/current/logical-replication-subscription.html#LOGICAL-REPLICATION-SUB=
SCRIPTION-SLOT</a></div><div><br></div><div>It is written: &quot;
When dropping a subscription, the remote host is not reachable. In that cas=
e, disassociate the slot from the subscription using <code>ALTER SUBSCRIPTI=
ON</code>
 before attempting to drop the subscription. If the remote database=20
instance no longer exists, no further action is then necessary. If,=20
however, the remote database instance is just unreachable, the=20
replication slot (and any still remaining table synchronization slots)=20
should then be dropped manually; otherwise it/they would continue to=20
reserve WAL and might eventually cause the disk to fill up. Such cases=20
should be carefully investigated.&quot;</div><div><br></div><div><br></div>=
<div>Assuming a situation where I add tables 1 at a time to the publisher, =
and refresh the subscription every time.</div><div><br></div><div>What happ=
ens if I shut down the subscriber database for a while? The subscription is=
n&#39;t dropped, so am I reading it right that the disk on the publisher wi=
ll slowly be filling up with WAL? Isn&#39;t that always the case if wall is=
 enabled?</div><div><br></div><div>This &quot;cause disk to fill up&quot; w=
arning is quite concerning, and I&#39;d like to understand what could cause=
 it and how likely it is? I thought logical replication uses WAL by default=
, so doesn&#39;t that mean there has to be a log of changes kept anyhow? Ev=
en if the WAL isn&#39;t written to disk by an &quot;archive_command&quot;?<=
/div><div><br></div><div>Regards,</div><div>Koen De Groote<br></div></div>
</blockquote></div>

--0000000000009df0d4061a1872dd--