MIME-Version: 1.0
References: <CAA5-nLCrbc90DHbX_9Yav72hEXUetkZ9iO8zmKvCvYNZkDHx9g@mail.gmail.com>
 <ZRW-7CMzr8HW72ry@elch.exwg.net> <CAA5-nLB5axGwwj6ft6G5tHxKqEC932M5k-4Ytb2W1iQziqmuLQ@mail.gmail.com>
 <ZRqWLWXmz-4c9gOy@elch.exwg.net>
In-Reply-To: <ZRqWLWXmz-4c9gOy@elch.exwg.net>
From: Fabrice Chapuis <fabrice636861@gmail.com>
Date: Fri, 6 Oct 2023 12:49:49 +0200
Message-ID: <CAA5-nLC4o2YLeQnV2ts=CnzAHrMsMvmOukQiP2rwwzs-Mg3c=g@mail.gmail.com>
Subject: Re: wal recycling problem
To: Christoph Moench-Tegeder <cmt@burggraben.net>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000008e477606070a0029"
Archived-At: <https://www.postgresql.org/message-id/CAA5-nLC4o2YLeQnV2ts%3DCnzAHrMsMvmOukQiP2rwwzs-Mg3c%3Dg%40mail.gmail.com>
Precedence: bulk

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

Thanks Christoph for your message.
Now I understand why the wals are preserved if logical replication is
configured and enabled. The problem is that when a large volume of data is
loaded into a database, for example during a pg_restore, the wal sender
process associated with the logical replication slot will have to decrypt
all of the wals generated during this operation which will take a long time
and the restart_lsn will not be modified.
From a conceptual point of view I think that specific wals per subscription
should be used and stored in the pg_replslot folder in order to avoid
working directly on the wals of the instance.

What do you think about this proposal?

Regards

Fabrice


On Mon, Oct 2, 2023 at 12:06=E2=80=AFPM Christoph Moench-Tegeder <cmt@burgg=
raben.net>
wrote:

> Hi,
>
> ## Fabrice Chapuis (fabrice636861@gmail.com):
>
> > on the other hand there are 2 slots for logical replication which displ=
ay
> > status extended. I don't understand why given that the
> confirmed_flush_lsn
> > field that is up to date. The restart_lsn remains frozen, for what
> reason?
>
> There you have it - "extended" means "holding wal". And as long as the
> restart_lsn does not advance, checkpointer cannot free any wal beyond
> that lsn. My first idea would be some long-running (or huge) transaction
> which is in process (active or still being streamed). I'd recommend
> looking into what the clients on these slots are doing.
>
> Regards,
> Christoph
>
> --
> Spare Space
>

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

<div dir=3D"ltr">Thanks Christoph for your message.<div>Now I understand wh=
y the wals are preserved if logical replication is configured and enabled. =
The problem is that when a large volume of data is loaded into a database, =
for example during a pg_restore, the wal sender process associated with the=
 logical replication slot will have to decrypt all of the wals generated du=
ring this operation which will take a long time and the restart_lsn will no=
t be modified.<br>From a conceptual point of view I think that specific wal=
s per subscription should be used and stored in the pg_replslot folder in o=
rder to avoid working directly on the wals of the instance.=C2=A0</div><div=
><br></div><div>What do you think about this proposal?</div><div><br></div>=
<div>Regards=C2=A0</div><div><br></div><div>Fabrice<br><div><br></div></div=
></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr"=
>On Mon, Oct 2, 2023 at 12:06=E2=80=AFPM Christoph Moench-Tegeder &lt;<a hr=
ef=3D"mailto:cmt@burggraben.net">cmt@burggraben.net</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">Hi,<br>
<br>
## Fabrice Chapuis (<a href=3D"mailto:fabrice636861@gmail.com" target=3D"_b=
lank">fabrice636861@gmail.com</a>):<br>
<br>
&gt; on the other hand there are 2 slots for logical replication which disp=
lay<br>
&gt; status extended. I don&#39;t understand why given that the confirmed_f=
lush_lsn<br>
&gt; field that is up to date. The restart_lsn remains frozen, for what rea=
son?<br>
<br>
There you have it - &quot;extended&quot; means &quot;holding wal&quot;. And=
 as long as the<br>
restart_lsn does not advance, checkpointer cannot free any wal beyond<br>
that lsn. My first idea would be some long-running (or huge) transaction<br=
>
which is in process (active or still being streamed). I&#39;d recommend<br>
looking into what the clients on these slots are doing.<br>
<br>
Regards,<br>
Christoph<br>
<br>
-- <br>
Spare Space<br>
</blockquote></div>

--0000000000008e477606070a0029--