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

Hi Ashutosh, Bharath, all,

Thank you, Ashutosh and Bharath, for the work you have put into this patch
over
the years.

On Wed, 23 Sept 2026 at 11:51, Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com>
wrote:

> Here is the newer version of the patch, based on the earlier v24
> version shared by Bharath here: [1].
>
> I have slightly simplified the patch, while preserving its original
> semantics.
>
> The patch introduces a timeout-based server parameter,
> "streaming_replication_retry_interval". When a standby has been
> reading WAL from the archive for the configured interval, it attempts
> to switch to streaming replication. Before doing so, it first ensures
> that all WAL already available under the pg_wal directory has been
> consumed. If streaming starts successfully, it continues using the
> streaming source. Otherwise, it falls back to archive recovery.
>
> Here is how the flow looks:
>
> ARCHIVE
> |
> | timeout
> v
> DRAIN LOCAL pg_wal
> |
> | no local WAL remains
> v
> STREAMING
> |
> | streaming fails
> v
> ARCHIVE
>
> Please have a look and let me know your thoughts.
>
> [1] -
> https://www.postgresql.org/message-id/CALj2ACVVM8c9pbCgmyxvViFy%3Df%3D%3DQNHhPFSGjvq0Sz7Y0beZsw%40mail.gmail.com
>

I would like to add a data point in favour of this patch (v24 [2], v25 [1],
commitfest entry [4]): a reproducer that anyone can run from a source tree,
and the result of running it on master and with v25.

# Why this matters for CloudNativePG

In CloudNativePG (CNPG), whenever a backup (WAL archive) is configured,
every replica consistently gets a restore_command as a fallback to
streaming. This is our recommended architecture because it makes replicas
more resilient and robust: a replica that falls behind or cannot reach the
primary can still catch up from the archive. So the combination of
primary_conninfo and restore_command is not an exotic setup for us. It is
the standard shape for every replica in a cluster with backups. The
pgsql-admin report from 2025-11-24 [5] (15.7, CNPG) and the reproduction
behind a recent blog post [6] (18.x, CNPG, synchronous replication) both
come from such deployments.

This is not a CNPG-specific choice either. Streaming replication combined
with a WAL archive as a fallback has been the recommended architecture in
my earlier work with Barman, and it is a setup I have worked with since my
early time at 2ndQuadrant, including my contributions to Postgres on hot
standby more than 15 years ago.

# The reproducer

The attached script, repro-standby-stuck-on-archive.sh, needs only a built
and installed tree (no root, no containers, bash only). From the source
folder:

meson setup build --prefix="$PWD/build" && ninja -C build install
./repro-standby-stuck-on-archive.sh

It does the following:

1. Primary with a local WAL archive, 1MB segments, and a physical slot.
2. Standby that streams through that slot (baseline: 1 walsender, 1
receiver).
3. pgbench runs on the primary from this point.
4. Control test: with no restore_command, the walreceiver gets a SIGTERM,
and the standby is expected to resume streaming. It does, after about 1s.
5. The standby gets a slow restore_command (sleep per segment) by reload.
6. restore_command is paused, the walreceiver gets a SIGTERM, and the
primary builds an archive backlog for OUTAGE seconds (default 15).
7. restore_command is resumed, and the script watches whether streaming
comes back while pgbench is still running. It prints one status line every
5s (walsenders, slot_active, wal_status, retained WAL, receiver, lag, tps)
and ends with either REPRODUCED or "streaming resumed Ns after ...".

I used SIGTERM and not SIGKILL on the walreceiver, because a killed
walreceiver makes the postmaster crash-restart. The optional SLOT_KEEP_MB
setting applies max_slot_wal_keep_size, to watch the slot go from reserved
to unreserved and eventually get invalidated.

# Results

On a plain master build (5ffb8c74093), no patch:

walsenders=0 slot_active=f wal_status=reserved retained=104MB
| standby: receiver=0 lag=74MB
REPRODUCED: no walsender/walreceiver 30s after restore_command resumed

With v25 applied on the same commit (one conflict in
src/test/recovery/meson.build, resolved by keeping both sets of tests):

- streaming_replication_retry_interval left at its default (0):
REPRODUCED, same as without the patch.
- streaming_replication_retry_interval = 5s: "streaming resumed 6s after
restore_command resumed", and the slot goes back to 0MB retained.

So in this scenario, the patch does what it is meant to do.

Caveats: this is a single run per case, on a small local setup, with a
single interval value and a local-disk archive that is slow only due to the
sleep. It tests the behaviour; it is not a code review, and I did not run
the patch's own TAP test. The synchronous replication and
hot_standby_feedback scenarios below were not tested either. I do not think
that is needed at this stage, since their consequences follow from the
behaviour shown here, but they are reasoning, not measurements.

# My opinion

1. I consider this a bug, not only a missing tuning option. A standby
configured to stream can stay off the primary indefinitely, for no reason
other than the order in which it tries its WAL sources. The stalled slot
and its effect on VACUUM and logical replication are already described in
the thread and in the v25 commit message [1][3]. I would add three
consequences that I find severe enough on their own:

a. Without a cap, the primary runs out of disk. The inactive slot's
restart_lsn does not move, and in the runs above the retained WAL grew by
about 12MB every 5 seconds under a small pgbench load. With the default
max_slot_wal_keep_size of -1, it keeps growing until the primary's disk is
full, which takes the primary down and not only the standby. With a cap the
primary is protected, but the slot gets invalidated and the standby loses
its protection on the primary. I did not run the disk-full case as it
doesn't add anything here.

b. With synchronous replication, it can mean downtime. Take two
standbys. One of them is restarted (a minor upgrade, for example) and comes
back behind, so it restores from the archive and, as shown above, does not
resume streaming until it has reached the end of the WAL available in the
archive. If the other, synchronous standby fails in that window, the
primary has no synchronous standby connected, and commits wait for one. The
downtime is then proportional to the lag of the standby that is restoring
from the archive.

c. hot_standby_feedback is not honoured while the standby is not
streaming, because feedback travels over the walreceiver connection. A user
who explicitly enabled it receives no feedback as long as the standby
remains on the archive. Without a slot, the xmin is released on disconnect,
so the primary can remove rows that queries on the standby still need, and
those queries get cancelled by recovery conflicts once replay reaches them.

2. A new GUC cannot be backported. Adding a setting to a stable branch is
not something we normally do, so a fix that depends on
streaming_replication_retry_interval would reach users only from the major
version in which it is committed. Everyone running a supported release
today, including all the existing clusters described above, would keep the
bug until they upgrade. Since I consider this a bug, I would like us to
look for a fix that can be backpatched, meaning one that changes the
behaviour without a new setting (for example by reusing existing settings,
or with a fixed conservative behaviour), and to keep any new GUC as an
optional tuning knob on top of it, for master only. Even then, with a
default of 0 the new behaviour would be off for everyone who does not set
it, so I would also document the symptom next to restore_command, with a
pointer to the setting.

3. The workarounds (making restore_command fail fast, or removing it) are
not obvious to users; they have to be applied before the problem occurs,
and removing restore_command gives up the resilience that the archive
fallback is there to provide. Tools like CNPG can, of course, apply such
workarounds on behalf of their users, but that solves the problem in the
wrong system, in my opinion: it is a workaround for the server's behaviour,
and the server is where it should be fixed.

Looking forward to your feedback.

Thanks,
Gabriele

[1] Ashutosh Sharma, v25 patch (2026-09-23)

https://www.mail-archive.com/pgsql-hackers(at)lists(dot)postgresql(dot)org/msg239885.html
[2] Bharath Rupireddy, v24 patch

https://www.postgresql.org/message-id/CALj2ACVVM8c9pbCgmyxvViFy%3Df%3D%3DQNHhPFSGjvq0Sz7Y0beZsw%40mail.gmail.com
[3] Bharath Rupireddy, reply on the impact on the primary (2026-09-11)

https://www.mail-archive.com/pgsql-hackers(at)lists(dot)postgresql(dot)org/msg238727.html
[4] Commitfest entry 3663
https://commitfest.postgresql.org/41/3663
[5] pgsql-admin, Kasper Fons, "restore_command on high-throughput cluster
never switches to streaming replication" (2025-11-24)

https://www.postgresql.org/message-id/CANOng2i1G_57nvZ4ip4uKKU87jtt%2BfzqWUFV_ou6L8N3bteSXQ%40mail.gmail.com
[6] Ardent Performance, "Postgres recovery_prefetch Considered Harmful and
Causing Outages" (2026-10-20)

https://ardentperf.com/2026/10/20/postgres-recovery_prefetch-considered-harmful-and-causing-outages/
--
Gabriele Bartolini
VP, Chief Architect, Kubernetes
enterprisedb.com / Prato, Italy