pg_upgrade_replica: avoid full re-clone of standbys after pg_upgrade

Short version: after pg_upgrade on a primary, resyncing its standbys today
means a full re-clone, because there's no shared WAL history across the
upgrade boundary for pg_rewind or incremental pg_basebackup to use. For a
multi-terabyte database that's a real problem. Attached are three patches:

v1-0001-fe_utils-extract-shared-backup_manifest-writer.patch
v1-0002-pg_upgrade-write-manifest-of-unchanged-relation-f.patch
v1-0003-Add-pg_upgrade_replica-a-standalone-tool-to-rebui.patch

Same 3 commits as a branch/PR if you'd rather read it on GitHub:
https://github.com/mnencia/postgres/pull/1

0001 is preparatory: it moves pg_combinebackup's own small backup_manifest
writer to fe_utils, unchanged, since 0003's own tool needs the same thing
too. 0002 is a small addition to pg_upgrade that records which relation
files it transferred unchanged, in a small format of its own. 0003 is a
new standalone tool, pg_upgrade_replica, that turns that record into a
real backup_manifest and drives pg_basebackup --incremental and
pg_combinebackup from it, so only what actually changed gets fetched over
the wire. I'll register this on the CommitFest once it's had an
independent review pass; see Status below for where that stands.

Problem

pg_upgrade preserves the relfilenode of every heap/index/toast relation
it transfers unchanged from the old cluster to the new one. That's
exactly the information you'd need to reuse a standby's existing files
after the upgrade, but pg_upgrade doesn't expose it, and nothing else
lets you reconstruct it. pg_rewind needs a common WAL history between
the two clusters to find where they diverged, which doesn't exist here.
pg_basebackup's incremental mode needs an unbroken chain of WAL
summaries from a prior backup's LSN forward to now; pg_upgrade resets
WAL entirely (new system identifier, fresh WAL history), so there's no
such chain across the upgrade boundary for it to use either. The
documented rsync procedure (pg_upgrade docs, "Upgrade streaming
replication and log-shipping standby servers") solves this today, but
needs rsync/ssh access between primary and standby filesystems, which
isn't available when they run in separate containers with nothing
shared.

The idea came out of CloudNativePG's own in-place major-version
upgrade, where cutting the resource and time cost of a full re-clone
after an upgrade matters directly. The problem itself isn't
Kubernetes-specific though: any primary and standby that don't share a
filesystem hit the same wall.

Design

Three parts.

pg_combinebackup already has a small, generic backup_manifest writer
(write_manifest.c/.h) with no dependency on anything specific to it. The
first patch moves it, unchanged in behavior, to fe_utils, so
pg_upgrade_replica can use it too instead of adding its own copy. This
is a pure file move: same functions, same signatures, same output; only
the include path changes for its one existing caller.

pg_upgrade already knows, from gen_db_file_maps(), exactly which
relations it's about to transfer unchanged. The second patch has it
record that inventory to $PGDATA/pg_upgrade_manifest in the new cluster:
a header line with the old cluster's own system identifier and shutdown
checkpoint, one "db_oid relfilenumber" line per relation transferred
unchanged, and a trailing line with the new cluster's own post-restore
checkpoint. This deliberately isn't a real backup_manifest: nothing
generic ever reads this file, only the third patch's own tool does, so
JSON would only buy code reuse in the writer, not actual interoperability
-- and forcing this file's own identity fields to describe two different
clusters at once (the old one it inventories, the new one it happens to
sit in) is exactly the kind of format misuse a real backup_manifest
shouldn't be asked to carry. I tried the JSON route first and backed out
of it for this reason; the third patch's own forged manifest, which a
generic backup_manifest consumer genuinely does need to read, stays real
JSON throughout.

I looked seriously at whether a client could derive this same
information itself, by diffing the old and new cluster's catalogs, to
avoid touching pg_upgrade at all. It doesn't work: gen_db_file_maps() is
constructive, not comparative, it writes the old relfilenode into the map
and never reads the new one, so a live catalog diff would get real false
positives (sequences under the default transfer modes get their
relfilenode forced to match without the file ever being copied, since
their data is replayed via SETVAL at restore time) and has no real
access to the old cluster's catalog after the fact anyway, since the old
primary is typically gone by the time anyone resyncs a standby. The
manifest is the only place this information exists while both catalogs
are still simultaneously known.

The third patch is pg_upgrade_replica, a new src/bin/ tool. It fetches
pg_upgrade's own manifest over the connection to the new primary, and
for each relation it lists, walks --old-replica's own data directory to
find that relation's actual files there. Whatever survives that check
(pg_upgrade's manifest is only an inventory of what the old primary
itself had -- an unlogged relation's main fork is a real example of a
file that's genuinely absent on a caught-up standby even though the old
primary transferred it unchanged) becomes a forged, real backup_manifest
(JSON, version 2, the same common/parse_manifest.c reader pg_basebackup
and pg_combinebackup already use), with the tablespace paths rewritten
from the old cluster's own catalog-version directory to the new one's.
It anchors a symlink tree next to it, so
that manifest looks like the output of a prior, already-complete backup,
then runs pg_basebackup --incremental against that to fetch only what
has actually changed on the new primary since the upgrade, and
pg_combinebackup to assemble the new standby's data directory from that
incremental backup plus --old-replica's own files. It locates both
siblings next to its own binary and refuses to run if their version
doesn't match its own, the same way pg_createsubscriber already does for
pg_ctl. --link passes -k through to pg_combinebackup, same hardlink
tradeoff pg_upgrade's own --link already makes.

Two correctness properties do the actual work, both enforced
automatically rather than left to the operator to remember:

- The standby being resynced must have stayed connected and streaming
through the primary's pre-upgrade shutdown. Checked by comparing the
standby's own pg_control against the manifest's recorded system
identifier and checkpoint. Get this wrong and a lagging standby's
reused files are stale forever, silently: recovery against the new
cluster only ever replays forward from the new checkpoint, never
backward to fill in what the standby missed. Reading --old-replica's
own pg_control for this needs its own version-matched pg_controldata
(a new --old-bindir option, same idea as pg_upgrade's own), the same
way pg_upgrade itself reads the old cluster's control data: pg_control
is not guaranteed byte-compatible across major versions, so this
tool's own build can't just parse an older cluster's copy of it
directly. A real cross-version run (see Status) is what caught this;
a same-version self-upgrade test never exercises it, since both sides
share one binary.

- For every file the forged manifest lists, --old-replica's copy is
reused outright wherever the new primary's own WAL summarizer reports
the relevant blocks unchanged since the anchor checkpoint, no
independent size or checksum check against the new primary. That's the
same trust any stopped data directory gets when starting recovery from
it, just applied at block granularity now instead of whole-file,
courtesy of core's own incremental-backup bookkeeping rather than
anything this tool tracks itself.

full_page_writes doesn't need checking: BASE_BACKUP forces it
server-side for the duration of any backup, incremental ones included,
so that's core's problem, not this tool's. And the assembled standby's
backup_label is the one pg_basebackup itself produces, anchored at
wherever the new primary happened to be when the tool ran, not at the
old pg_upgrade checkpoint recorded in the manifest (that checkpoint
only surfaces in the incremental backup's own "incremental from"
field, which pg_combinebackup consumes and doesn't carry into the
final result). So there's no operational requirement to retain WAL on
the new primary for as long as some standby is still waiting to be
resynced.

What does need to be retained instead is WAL summary coverage:
summarize_wal has to be on from the new primary's first post-upgrade
startup (a coverage gap left before enabling it is never backfilled),
and the summaries themselves get pruned after wal_summary_keep_time (ten
days by default). Miss that window and pg_basebackup fails plainly with
an incomplete-summaries error, not silent corruption, but the only way
forward at that point is a full re-clone.

pg_upgrade_manifest itself is excluded from ordinary base backups
(src/backend/backup/basebackup.c's own excludeFiles list, alongside
backup_manifest for the same reason): it describes the old cluster, not
whatever directory it happens to be sitting in, so it has no business
riding along into a future backup of the new primary.

Status

Built and tested against a same-version self-upgrade (this project's own
established convention for exercising pg_upgrade without a second major
version installed), including tablespace coverage (an in-place
tablespace specifically, since pg_upgrade's own same-catalog-version
restriction on real tablespaces rules out an external one under this
testing trick) and a new-primary-side write to a reused relation made
before the sync runs.

Also tested against a real cross-major-version upgrade (a real
built-from-source PG 17.10 to current dev tip, not same-version), with a
real external tablespace rather than an in-place one: correct row counts
across the growth writes, --old-replica's own tablespace copy left under
its own old catalog-version directory and completely untouched, the
assembled standby's copy correctly placed under the new catalog
version's own subdirectory, and live streaming replication working
immediately after boot.

Each of the first two patches builds and passes its own tests standing
alone: 0001 alone still passes pg_combinebackup's own 120 tests,
unchanged; 0002 alone (it doesn't actually depend on 0001, since the
format it writes isn't a real backup_manifest and needs none of that
patch's shared writer) still passes pg_upgrade's suite (140 tests,
including the new manifest assertions it adds), plus pg_basebackup's
own 339, including a new assertion of its own confirming a dummy
pg_upgrade_manifest file is actually excluded from a base backup, not
just that basebackup.c still builds. 0003 carries 37 tests of its own.
The series applies cleanly to current master.

What I want feedback on: the case for pg_upgrade_manifest being a small
format of its own rather than a real backup_manifest, and whether this
belongs as a core src/bin/ tool the way I've built it. What I'm not
asking for yet: a commit. This is a first pass, not a request to merge.

On the src/bin/ question specifically: this isn't really a core-vs-contrib
choice, a backend extension can't write to a separate host's filesystem
at all, so contrib was never actually on the table. The real choice is
core vs. an external libpq client, the same one pg_rewind faced before
9.5 and pg_upgrade faced as external pg_migrator before 9.0. What pushes
this toward core for me now is sharper than it was: the tool locates and
runs pg_basebackup and pg_combinebackup as version-matched sibling
binaries, the same way pg_createsubscriber locates pg_ctl, and it
depends on common/parse_manifest.c and fe_utils's GenerateRecoveryConfig()
besides. All of that is technically reachable from an out-of-tree client
(the sibling binaries via $PATH, the two via the same PGXS mechanism
pg_rewind itself used before 9.5), but src/common and src/fe_utils carry
no cross-version API/ABI stability guarantee, and this tool's whole
purpose is spanning a major-version boundary, so an external build would
be chasing all of that across every release. Happy to be argued out of
that.

Marco Nenciarini
EnterpriseDB