From: "Vitaly Davydov" <v.davydov@postgrespro.ru>
To: pgsql-hackers@postgresql.org
User-Agent: SOGoMail 5.11.2
MIME-Version: 1.0
Date: Thu, 31 Oct 2024 13:18:34 +0300
Subject: Slot's =?utf-8?q?restart=5Flsn?= may point to removed WAL segment after 
 hard restart unexpectedly
Message-ID: <1d12d2-67235980-35-19a406a0@63439497>
Content-Type: multipart/alternative; boundary="----=_=-_OpenGroupware_org_NGMime-1905362-1730369914.708235-13------"
Archived-At: <https://www.postgresql.org/message-id/1d12d2-67235980-35-19a406a0%4063439497>
Precedence: bulk

------=_=-_OpenGroupware_org_NGMime-1905362-1730369914.708235-13------
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Content-Length: 2214


Dear Hackers,
=C2=A0
I'd like to discuss a problem with replication slots's restart LSN. Phy=
sical slots are saved to disk at the beginning of checkpoint. At the en=
d of checkpoint, old WAL segments are recycled or removed from disk, if=
 they are not kept by slot's restart=5Flsn values.
=C2=A0
If an existing physical slot is advanced in the middle of checkpoint ex=
ecution, WAL segments, which are related to saved on disk restart LSN m=
ay be removed. It is because the calculation of the replication slot mi=
minal LSN is occured at the end of checkpoint, prior to old WAL segment=
s removal. If to hard stop (pg=5Fstl -m immediate) the postgres instanc=
e right after checkpoint and to restart it, the slot's restart=5Flsn ma=
y point to the removed WAL segment. I believe, such behaviour is not go=
od.
=C2=A0
The doc [0] describes that restart=5Flsn may be set to the some past va=
lue after reload. There is a discussion [1] on pghackers where such beh=
aviour is discussed. The main reason of not flushing physical slots on =
advancing is a performance reason. I'm ok with such behaviour, except o=
f that the corresponding WAL segments should not be removed.
=C2=A0
I propose to keep WAL segments by saved on disk (flushed) restart=5Flsn=
 of slots. Add a new field restart=5Flsn=5Fflushed into ReplicationSlot=
 structure. Copy restart=5Flsn to restart=5Flsn=5Fflushed in SaveSlotTo=
Path. It doesn't change the format of storing the slot contents on disk=
. I attached a patch. It is not yet complete, but demonstate a way to s=
olve the problem.
=C2=A0
I reproduced the problem by the following way:
 * Add some delay in CheckPointBuffers (pg=5Fusleep) to emulate long ch=
eckpoint execution. * Execute checkpoint and pg=5Freplication=5Fslot=5F=
advance right after starting of the checkpoint from another connection.=
 * Hard restart the server right after checkpoint completion. * After r=
estart slot's restart=5Flsn may point to removed WAL segment.
The proposed patch fixes it.
=C2=A0
[0] https://www.postgresql.org/docs/current/logicaldecoding-explanation=
.html
[1] https://www.postgresql.org/message-id/flat/059cc53a-8b14-653a-a24d-=
5f867503b0ee%40postgrespro.ru

------=_=-_OpenGroupware_org_NGMime-1905362-1730369914.708235-13------
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Content-Length: 2364

<html><p>Dear Hackers,</p><p>&nbsp;</p><p>I'd like to discuss a problem=
 with replication slots's restart LSN. Physical slots are saved to disk=
 at the beginning of checkpoint. At the end of checkpoint, old WAL segm=
ents are recycled or removed from disk, if they are not kept by slot's =
restart=5Flsn values.</p><p>&nbsp;</p><p>If an existing physical slot i=
s advanced in the middle of checkpoint execution, WAL segments, which a=
re related to saved on disk restart LSN may be removed. It is because t=
he calculation of the replication slot miminal LSN is occured at the en=
d of checkpoint, prior to old WAL segments removal. If to hard stop (pg=
=5Fstl -m immediate) the postgres instance right after checkpoint and t=
o restart it, the slot's restart=5Flsn may point to the removed WAL seg=
ment. I believe, such behaviour is not good.</p><p>&nbsp;</p><p>The doc=
 [0] describes that restart=5Flsn may be set to the some past value aft=
er reload. There is a discussion [1] on pghackers where such behaviour =
is discussed. The main reason of not flushing physical slots on advanci=
ng is a performance reason. I'm ok with such behaviour, except of that =
the corresponding WAL segments should not be removed.</p><p>&nbsp;</p><=
p>I propose to keep WAL segments by saved on disk (flushed) restart=5Fl=
sn of slots. Add a new field restart=5Flsn=5Fflushed into ReplicationSl=
ot structure. Copy restart=5Flsn to restart=5Flsn=5Fflushed in SaveSlot=
ToPath. It doesn't change the format of storing the slot contents on di=
sk. I attached a patch. It is not yet complete, but demonstate a way to=
 solve the problem.</p><p>&nbsp;</p><p>I reproduced the problem by the =
following way:</p><ul><li>Add some delay in CheckPointBuffers (pg=5Fusl=
eep) to emulate long checkpoint execution.</li><li>Execute checkpoint a=
nd pg=5Freplication=5Fslot=5Fadvance right after starting of the checkp=
oint from another connection.</li><li>Hard restart the server right aft=
er checkpoint completion.</li><li>After restart slot's restart=5Flsn ma=
y point to removed WAL segment.</li></ul><p>The proposed patch fixes it=
.</p><p>&nbsp;</p><p>[0] https://www.postgresql.org/docs/current/logica=
ldecoding-explanation.html<br>[1] https://www.postgresql.org/message-id=
/flat/059cc53a-8b14-653a-a24d-5f867503b0ee%40postgrespro.ru</p></html>

------=_=-_OpenGroupware_org_NGMime-1905362-1730369914.708235-13--------