Re: Switching XLog source from archive to streaming when primary available

Hi,

On Thu, Aug 29, 2024 at 6:32 AM Bharath Rupireddy
<bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
> In synchronous replication setup, until standby finishes fetching WAL
> from the archive, the commits on the primary have to wait which can
> increase the query latency. If the standby can connect to the primary
> as soon as the broken connection is restored, it can fetch the WAL
> soon and transaction commits can continue on the primary. Is my
> understanding correct? Is there anything more to this?
>

Yup, if you're running with synchronous_commit = 'on' with
synchronous_replicas, then you can
have the replica continue streaming changes into pg_wal faster than
WAL replay so commits
may be unblocked faster.

> I talked to Michael Paquier at PGConf.Dev 2024 and got some concerns
> about this feature for dealing with changing timelines. I can't think
> of them right now.

I'm not sure what the risk would be if the WAL/history files we sync
from streaming is the same as
we replay from archive.

> And, there were some cautions raised upthread -
> https://www.postgresql.org/message-id/20240305020452.GA3373526%40nathanxps13
> and https://www.postgresql.org/message-id/ZffaQt7UbM2Q9kYh%40paquier.xyz.

Yup agreed. I need to understand this area a lot better before I can
do a more in-depth review.

> Interesting. Yes, the restore script has to be smarter to detect the
> broken connections and distinguish whether the server is performing
> just the archive recovery/PITR or streaming from standby. Not doing it
> right, perhaps, can cause data loss (?).

I don't think there would be data-loss, only replay is stuck/slows down.
It wouldn't be any different today if the restore-script returned a
non-zero exit status.
The end-user could configure their restore-script to return a non-zero
status, based on some
condition, to move to streaming.

> > > However,
> > > + * exhaust all the WAL present in pg_wal before switching. If successful,
> > > + * the state machine moves to XLOG_FROM_STREAM state, otherwise it falls
> > > + * back to XLOG_FROM_ARCHIVE state.
> >
> > I think I'm missing how this happens. Or what "successful" means. If I'm reading
> > it right, no matter what happens we will always move to
> > XLOG_FROM_STREAM based on how
> > the state machine works?
>
> Please have a look at some discussion upthread on exhausting pg_wal
> before switching -
> https://www.postgresql.org/message-id/20230119005014.GA3838170%40nathanxps13.
> Even today, the standby exhausts pg_wal before switching to streaming
> from the archive.
>

I'm getting caught on the word "successful". My rough understanding of
WaitForWALToBecomeAvailable is that once you're in XLOG_FROM_PG_WAL, if it was
unsuccessful for whatever reason, it will still transition to
XLOG_FROM_STREAMING.
It does not loop back to XLOG_FROM_ARCHIVE if XLOG_FROM_PG_WAL fails.

> Nice catch. This is a problem. One idea is to disable
> streaming_replication_retry_interval feature for slot-less streaming
> replication - either when primary_slot_name isn't specified disallow
> the GUC to be set in assign_hook or when deciding to switch the wal
> source. Thoughts?

I don't think it's dependent on slot-less streaming. You would also run into the
issue if the WAL is no longer there on the primary, which can occur
with 'max_slot_wal_keep_size'
as well.
IMO the guarantee we need to make is that when we transition from
XLOG_FROM_STREAMING to
XLOG_FROM_ARCHIVE for a "fresh start", we should attempt to restore
from archive at least once.
I think this means that wal_source_switch_state should be reset back
to SWITCH_TO_STREAMING_NONE
whenever we transition to XLOG_FROM_ARCHIVE.
We've attempted the switch to streaming once, so let's not continually
re-try if it failed.

Thanks,

--
John Hsu - Amazon Web Services