Re: Physical slot restart_lsn advances incorrectly requiring restore from archive

Hi Horiguchi-san,

I'll take a look at that thread and see if I can reproduce with the attached patch.
It seems like it would directly address this issue. Thanks for taking a look.

Cheers,
John H

On Thu, Jul 16, 2020 at 11:00 AM Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com> wrote:
Hello, John.

At Fri, 10 Jul 2020 20:44:30 +0000, "Hsu, John" <hsuchen(at)amazon(dot)com> wrote in
> Hi hackers,
> 
> We believe we’re seeing a problem with how physical slot’s restart_lsn is advanced leading to the replicas needing to restore from archive in order for replication to resume.
> The logs below are from reproductions against 10.13. I’m still working on reproducing it for 12.3.
> 
> WAL write spans two WAL segments .
> Write to first WAL segment is complete but not to the second segment.
> Write to first WAL segment is acknowledged as flushed from the Postgres replica.
> Primary restarts before the write to second segment completes. It also means the complete WAL was never written.
> Crash recovery finishes at a record before the incomplete WAL write.
> Though now replica start the slot at the next WAL segment, since the previous WAL was already acknowledged as flushed.
...
> Redo finishes at 0/2BFFFFB0 even though the flush we received from
> the replica is already at 0/2C000000.
> This is problematic because the replica reconnects to the slot
> telling it to start past the new redo point.

Yeah, that is a problem not only related to restart_lsn. The same
cause leads to aother issue of inconsistent archive as discussed in
[1].

1: https://www.postgresql.org/message-id/CBDDFA01-6E40-46BB-9F98-9340F4379505%40amazon.com

> The attached patch (against 10) attempts to address this by keeping
> track of the first flushLsn in the current segNo, and wait until we
> receive one after that before updating. This prevents the WAL from
> rotating out of the primary and a reboot from the replica will fix
> it instead of needing to restore from archive.

On the other hand we can and should advance restart_lsn when we know
that the last record is complete. I think a patch in the thread [2]
would fix your issue. With the patch primary doesn't send a
continuation record at the end of a segment until the whole record is
flushed into WAL file.

2: https://www.postgresql.org/message-id/20200625.153532.379700510444980240.horikyota.ntt%40gmail.com

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center