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> <CAA5-nLC4o2YLeQnV2ts=CnzAHrMsMvmOukQiP2rwwzs-Mg3c=g@mail.gmail.com>
 <ZSK1YpWYcc-ADxIs@elch.exwg.net>
In-Reply-To: <ZSK1YpWYcc-ADxIs@elch.exwg.net>
From: Fabrice Chapuis <fabrice636861@gmail.com>
Date: Tue, 17 Oct 2023 12:37:05 +0200
Message-ID: <CAA5-nLAaVhGo3XWiUvOP9FANF8PaSiVZiUP9FyAuptuMBfvaNg@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="00000000000046eca90607e71b46"
Archived-At: <https://www.postgresql.org/message-id/CAA5-nLAaVhGo3XWiUvOP9FANF8PaSiVZiUP9FyAuptuMBfvaNg%40mail.gmail.com>
Precedence: bulk

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

Thanks for your feedback
> How would you know which part of WAL is needed for any specific
replication slot?
change are captured for each published table and written twice,  once in
the current wal and once in the slot-specific wal
> How would you handle multiple replications
for the same table
added information about from which publication a table belongs is entered
in the wal slot
> be it logical or physical replication, retains WAL up to
max_slot_wal_keep_size
ok but if max_slot_wal_keep_size is exceeded the changes are lost and all
of the replicated tables must be resynchronized

Regards

Fabrice

On Sun, Oct 8, 2023 at 3:57=E2=80=AFPM Christoph Moench-Tegeder <cmt@burggr=
aben.net>
wrote:

> ## Fabrice Chapuis (fabrice636861@gmail.com):
>
> > 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?
>
> I think that would open a wholly new can of worms.
> The most obvious point here is: that WAL is primarily generated for
> the operation of the database itself - it's our kind of transaction
> log, or "Redo Log" in other systems' lingo. Replication (be it physical
> or logical) is a secondary purpose (an obvious and important one, but
> still secondary).
> How would you know which part of WAL is needed for any specific
> replication slot? You'd have to decode and filter it, and already
> you're back at square one. How would you handle multiple replications
> for the same table (in the same publication, or even over multiple
> (overlapping) publications) - do you multiply the WAL?
>
> For now, we have "any replication using replication slots, be it logical
> or physical replication, retains WAL up to max_slot_wal_keep_size
> (or "unlimited" if not set - and on PostgreSQL 12 and before); and you
> need to monitor the state of your replication slots", which is a
> totally usabe rule, I think.
>
> Regards,
> Christoph
>
> --
> Spare Space
>

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

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div di=
r=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"lt=
r"><div>Thanks for your feedback</div><div dir=3D"ltr">&gt; How would you k=
now which part of WAL is needed for any specific<br>replication slot?</div>=
<div>change are captured for each published table and written twice,=C2=A0 =
once in the current wal and once in the slot-specific wal</div><div>&gt; Ho=
w would you handle multiple replications</div>for the same table=C2=A0</div=
><div dir=3D"ltr">added information about from which publication a table be=
longs is entered in the wal slot<br></div><div dir=3D"ltr">&gt; be it logic=
al or physical replication, retains WAL up to max_slot_wal_keep_size</div><=
/div><div>ok but if max_slot_wal_keep_size is exceeded the changes are lost=
 and all of the replicated tables must be resynchronized</div><div><br></di=
v><div>Regards</div><div><br></div><div>Fabrice</div></div></div></div></di=
v></div></div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=
=3D"gmail_attr">On Sun, Oct 8, 2023 at 3:57=E2=80=AFPM Christoph Moench-Teg=
eder &lt;<a href=3D"mailto:cmt@burggraben.net">cmt@burggraben.net</a>&gt; w=
rote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0p=
x 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">## Fabrice=
 Chapuis (<a href=3D"mailto:fabrice636861@gmail.com" target=3D"_blank">fabr=
ice636861@gmail.com</a>):<br>
<br>
&gt; From a conceptual point of view I think that specific wals per subscri=
ption<br>
&gt; should be used and stored in the pg_replslot folder in order to avoid<=
br>
&gt; working directly on the wals of the instance.<br>
&gt; What do you think about this proposal?<br>
<br>
I think that would open a wholly new can of worms.<br>
The most obvious point here is: that WAL is primarily generated for<br>
the operation of the database itself - it&#39;s our kind of transaction<br>
log, or &quot;Redo Log&quot; in other systems&#39; lingo. Replication (be i=
t physical<br>
or logical) is a secondary purpose (an obvious and important one, but<br>
still secondary).<br>
How would you know which part of WAL is needed for any specific<br>
replication slot? You&#39;d have to decode and filter it, and already<br>
you&#39;re back at square one. How would you handle multiple replications<b=
r>
for the same table (in the same publication, or even over multiple<br>
(overlapping) publications) - do you multiply the WAL?<br>
<br>
For now, we have &quot;any replication using replication slots, be it logic=
al<br>
or physical replication, retains WAL up to max_slot_wal_keep_size<br>
(or &quot;unlimited&quot; if not set - and on PostgreSQL 12 and before); an=
d you<br>
need to monitor the state of your replication slots&quot;, which is a<br>
totally usabe rule, I think.<br>
<br>
Regards,<br>
Christoph<br>
<br>
-- <br>
Spare Space<br>
</blockquote></div>

--00000000000046eca90607e71b46--