> On 13 May 2025, at 14:13, Fujii Masao <masao.fu...@oss.nttdata.com> wrote:
>
>
>
> On 2025/05/13 0:47, Andrey Borodin wrote:
>> Moved off from "Small fixes needed by high-availability tools"
>>> On 12 May 2025, at 01:33, Amit Kapila <amit.kapil...@gmail.com> wrote:
>>>
>>> On Fri, May 2, 2025 at 6:30 PM Andrey Borodin <x4...@yandex-team.ru> wrote:
>>>>
>>>> 3. Allow reading LSN written by walreciever, but not flushed yet
>>>>
>>>> Problem: if we have synchronous_standby_names = ANY(node1,node2), node2
>>>> might be ahead of node1 by flush LSN, but before by written LSN. If we do
>>>> a failover we choose node2 instead of node1 and loose data recently
>>>> committed with synchronous_commit=remote_write.
>
> In this case, doesn't the flush LSN typically catch up to the write LSN on
> node2
> after a few seconds? Even if the walreceiver exits while there's still written
> but unflushed WAL, it looks like WalRcvDie() ensures everything is flushed by
> calling XLogWalRcvFlush(). So, isn't it safe to rely on the flush LSN when
> selecting
> the most advanced node? No?
Well, we implemented this and made tests that do a lot of failovers. These
tests observed data loss in some infrequent cases due to wrong new primary
selection. Because "few seconds" is actually unknown random time.
>>>> Caveat: we already have a function pg_last_wal_receive_lsn(), which in
>>>> fact returns flushed LSN, not written. I propose to add a new function
>>>> which returns LSN actually written. Internals of this function are already
>>>> implemented (GetWalRcvWriteRecPtr()), but unused.
>
> GetWalRcvWriteRecPtr() returns walrcv->writtenUpto, which can move backward
> when the walreceiver restarts. This behavior is OK for your purpose?
It is OK, because:
1. It's strictly no worse than flushed LSN
2. synchronous_commit = remove_write assumes that you can loose data when
primary failed and standby is restarted simultaneously. The user is warned.
Best regards, Andrey Borodin.
