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 <[email protected]>
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/[email protected]/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/[email protected]/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
#!/bin/bash
#
# Reproduce: a standby with both primary_conninfo (+ slot) and a slow
# restore_command loses its streaming connection, falls back to the archive,
# and does not resume streaming while the primary keeps generating WAL.
#
# Needs an already built and installed PostgreSQL (nothing is built here), bash.
# From the source folder, install with the prefix set to ./build:
# meson setup build --prefix="$PWD/build" && ninja -C build install
# ./configure --prefix="$PWD/build" && make -s -j8 install # autoconf
# then run ./repro-standby-stuck-on-archive.sh
#
# Environment (all optional):
# BUILD_PATH install prefix to use (default: ./build next to this script)
# PGBIN bin directory to use instead of $BUILD_PATH/bin
# OUTAGE seconds the standby is kept off the primary (default 15)
# OBSERVE seconds to watch afterwards (default 60)
# RESTORE_DELAY seconds restore_command sleeps per segment (default 1)
# SLOT_KEEP_MB max_slot_wal_keep_size in MB, to see the slot get invalidated
# KEEP=1 keep the scratch directory (logs) on exit
#
# While it runs, "touch $WORKDIR/pause" blocks restore_command.
set -u
OUTAGE=${OUTAGE:-15}; OBSERVE=${OBSERVE:-60}; RESTORE_DELAY=${RESTORE_DELAY:-1}
SLOT_KEEP_MB=${SLOT_KEEP_MB:-}
BUILD_PATH=${BUILD_PATH:-$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)/build}
PGBIN=${PGBIN:-$BUILD_PATH/bin}
[ -x "$PGBIN/initdb" ] || { echo "error: no initdb in $PGBIN (see header)" >&2; exit 1; }
WORKDIR=$(mktemp -d)
export PATH="$PGBIN:$PATH" LC_MESSAGES=C # the greps below expect English logs
export PGHOST=127.0.0.1 PGUSER=postgres PGDATABASE=postgres
PP=54320; SP=54321 # primary and standby ports
P=$WORKDIR/primary; S=$WORKDIR/standby; A=$WORKDIR/archive; mkdir -p "$A"
pq() { psql -X -At -p $PP -c "$1"; }
sq() { psql -X -At -p $SP -c "$1"; }
say() { printf '\n[%s] %s\n' "$(date +%H:%M:%S)" "$*"; }
cleanup() {
trap - EXIT
[ -n "${BENCH:-}" ] && kill "$BENCH" 2>/dev/null
pg_ctl -D "$S" -m immediate stop >/dev/null 2>&1
pg_ctl -D "$P" -m immediate stop >/dev/null 2>&1
if [ "${KEEP:-0}" = 1 ]; then echo "kept: $WORKDIR"; else rm -rf "$WORKDIR"; fi
}
trap cleanup EXIT
# Slow archive: restore_command blocks while $WORKDIR/pause exists (this lets
# the primary build a backlog), sleeps per segment, and fails if the segment is
# not archived yet, which is what normally sends the standby back to streaming.
cat > "$WORKDIR/restore.sh" <<EOF
#!/bin/sh
while [ -e "$WORKDIR/pause" ]; do sleep 0.2; done
[ -f "$A/\$1" ] || exit 1
sleep $RESTORE_DELAY
echo "\$(date +%H:%M:%S) \$1" >> "$WORKDIR/restore.log"
cp "$A/\$1" "\$2"
EOF
chmod +x "$WORKDIR/restore.sh"
# Primary: local WAL archive (1MB segments keep the backlog cheap) and a slot.
say "initdb primary, workdir $WORKDIR"
initdb -D "$P" -U postgres --wal-segsize=1 --no-sync >/dev/null
cat >> "$P/postgresql.conf" <<EOF
port = $PP
listen_addresses = '127.0.0.1'
unix_socket_directories = ''
archive_mode = on
archive_command = 'test ! -f $A/%f && cp %p $A/%f'
max_wal_size = 4GB
fsync = off
synchronous_commit = off
log_line_prefix = '%m [%p] '
EOF
echo "host replication all 127.0.0.1/32 trust" >> "$P/pg_hba.conf"
pg_ctl -D "$P" -l "$WORKDIR/primary.log" -w start >/dev/null
pq "SELECT pg_create_physical_replication_slot('standby1', true)" >/dev/null
pgbench -i -s 5 -q -p $PP >/dev/null 2>&1
# Standby: streams from the primary through the slot. The restore_command
# above is only enabled later, after the control test.
pg_basebackup -D "$S" -h 127.0.0.1 -p $PP -X stream -S standby1 -c fast --no-sync >/dev/null
cat >> "$S/postgresql.conf" <<EOF
port = $SP
listen_addresses = '127.0.0.1'
unix_socket_directories = ''
primary_conninfo = 'host=127.0.0.1 port=$PP user=postgres application_name=standby1'
primary_slot_name = 'standby1'
wal_retrieve_retry_interval = 1s
fsync = off
log_line_prefix = '%m [%p] '
EOF
touch "$S/standby.signal"
pg_ctl -D "$S" -l "$WORKDIR/standby.log" -w start >/dev/null
streaming() { pq "SELECT count(*) FROM pg_stat_replication WHERE state='streaming'"; }
say "waiting for the standby to stream"
for _ in $(seq 60); do [ "$(streaming)" = 1 ] && break; sleep 1; done
[ "$(streaming)" = 1 ] || { echo "standby never streamed"; exit 2; }
# Load starts only now: with the primary busy from the start, the next segment
# is always already archived and the standby would never stream at all.
say "STREAMING: baseline established; starting pgbench"
pgbench -c 4 -j 4 -P 1 -T $((OUTAGE + OBSERVE + 60)) -p $PP >"$WORKDIR/pgbench.log" 2>&1 &
BENCH=$!; disown
if [ -n "$SLOT_KEEP_MB" ]; then # set now, or it would hit the slot during pgbench -i
pq "ALTER SYSTEM SET max_slot_wal_keep_size = '${SLOT_KEEP_MB}MB'" >/dev/null
pq "ALTER SYSTEM SET checkpoint_timeout = '30s'" >/dev/null
pq "SELECT pg_reload_conf()" >/dev/null
fi
report() {
local cur; cur=$(pq "SELECT pg_current_wal_lsn()")
printf '%s walsenders=%s slot_active=%s wal_status=%s retained=%sMB | standby: receiver=%s lag=%sMB last_restored=%s | tps=%s\n' \
"$(date +%H:%M:%S)" \
"$(pq "SELECT count(*) FROM pg_stat_replication")" \
"$(pq "SELECT active FROM pg_replication_slots WHERE slot_name='standby1'")" \
"$(pq "SELECT wal_status FROM pg_replication_slots WHERE slot_name='standby1'")" \
"$(pq "SELECT round(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)/1048576) FROM pg_replication_slots WHERE slot_name='standby1'")" \
"$(sq "SELECT count(*) FROM pg_stat_wal_receiver")" \
"$(sq "SELECT round(pg_wal_lsn_diff('$cur', pg_last_wal_replay_lsn())/1048576)")" \
"$(tail -n1 "$WORKDIR/restore.log" 2>/dev/null | cut -d' ' -f2)" \
"$(grep progress "$WORKDIR/pgbench.log" | tail -n1 | awk '{print $4}')"
}
# Baseline while everything is healthy: one walsender, one receiver, slot active.
sleep 2
report
# SIGTERM, not SIGKILL: a killed walreceiver would crash-restart the postmaster.
killwr() { kill -TERM "$(sq "SELECT pid FROM pg_stat_wal_receiver")"; }
# Control test: without a restore_command the archive can never serve anything,
# so after losing its connection the standby must go straight back to streaming.
say "CONTROL: no restore_command; killing the walreceiver, expecting streaming to resume"
killwr
for i in $(seq 30); do
sleep 1
[ "$(streaming)" = 1 ] && { say "control OK: streaming resumed ${i}s after the kill"; break; }
done
[ "$(streaming)" = 1 ] || { echo "control failed: standby did not resume streaming"; exit 2; }
report
# Now give the standby a (slow) restore_command, as in the real setup.
say "enabling the slow restore_command on the standby (reload, no restart)"
sq "ALTER SYSTEM SET restore_command = '$WORKDIR/restore.sh %f %p'" >/dev/null
sq "SELECT pg_reload_conf()" >/dev/null
# The problem: pause restore_command first, so that the standby falls back to
# the archive and blocks there while the primary archives a backlog.
say "TEST: restore_command paused, SIGTERM to the walreceiver; standby off for ${OUTAGE}s"
touch "$WORKDIR/pause"
killwr
for i in $(seq "$OUTAGE"); do sleep 1; [ $((i % 5)) = 0 ] && report; done
# Let the restore go on. Archive-first: the standby now replays the whole
# backlog at one segment per RESTORE_DELAY; it tries to stream again only when
# restore_command fails, i.e. once it has caught up with the archive.
say "restore_command resumed; observing up to ${OBSERVE}s with pgbench still running"
rm -f "$WORKDIR/pause"
RESUMED=
for i in $(seq "$OBSERVE"); do
sleep 1
[ "$(streaming)" = 1 ] && { RESUMED=$i; break; }
[ $((i % 5)) = 0 ] && report
done
SLOT=$(pq "SELECT 'wal_status=' || wal_status || ', retained=' || coalesce(round(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)/1048576)::text,'n/a') || 'MB' FROM pg_replication_slots WHERE slot_name='standby1'")
if [ -z "$RESUMED" ]; then
say "REPRODUCED: no walsender/walreceiver ${OBSERVE}s after restore_command resumed; slot: $SLOT"
else
say "streaming resumed ${RESUMED}s after restore_command resumed; slot: $SLOT"
fi
grep -c "restored log file" "$WORKDIR/standby.log" | sed 's/^/segments restored from archive: /'
grep -c "started streaming" "$WORKDIR/standby.log" | sed 's/^/times streaming started: /'
grep -h "invalidat\|has already been removed" "$WORKDIR/primary.log" "$WORKDIR/standby.log" | head -3
