[Patch]The Case For WAL-Logging pg_upgrade

Hi all,

I would like to propose a patch that *WAL-logs pg_upgrade*.

The detailed proposal including the workflow and a real use case, etc. is
available in the GitHub branch below:

https://github.com/LeeBohyun/postgres/blob/wal-upgrade-patch/The_Case_For_WAL-Logging_pg_upgrade.pdf
<https://github.com/LeeBohyun/postgres/tree/wal-upgrade-patch>

Below is a brief summary of the proposal.

*____________________________________________________________________________________*

*Problem of stock pg_upgrade.*

Current pg_upgrade writes the files of the new cluster directly, emitting
no WAL for the changes it makes. This creates two fundamental limitations.

The first is a *replication gap*. Since the upgrade never enters the WAL
stream, standbys cannot adopt it through replication and must be taken
offline and resynchronized from the primary via rsync.

The second is a *durability gap* in the backup chain. The upgraded cluster
begins with a new WAL history that is disconnected from the history of the
old cluster. Consequently, if the upgraded primary fails before a new base
backup is taken, transactions committed after the upgrade cannot be
recovered. This window may remain open for a long time, if the database is
large and taking a backup requires a long time.

*Closing replication and durability gaps.*

The patch closes both gaps by atomically WAL-logging the after-images
generated by pg_upgrade upon successful completion. The upgrade becomes
part of the WAL stream and can be propagated to standbys through standard
streaming replication, eliminating the need for offline rsync-based
resynchronization.

Furthermore, because the upgraded cluster continues the existing WAL
history rather than creating a new one, the upgrade and all subsequent
modifications remain recoverable through WAL replay, preserving durability
even before a new base backup is taken.

*Workflow with primary/standby with the proposed interface.*

(1) pg_upgrade *--wal-upgrade-signal-handoff* triggers the primary to
shutdown and standbys to pause.

(2) pg_upgrade *--wal-upgrade* performs the upgrade

(3) and *captures it as WAL*.

(4) The primary goes live on restart with the new version.

(5) Each standby comes up on the new version over its retained data
directory, streams and replays the upgrade WALs

(6) and becomes a hot standby once the WALs are fully replayed.

*Recovery workflow with primary/backup with the proposed interface.*

(1) Restore the last pre-upgrade base backup.

(2) PITR-replay archived WAL up to the upgrade point.

(3) Restart on the new version and replay upgrade WALs.

(4) Keep replaying the new-version WAL to recover the post-upgrade
transactions.

(5) Promote, fully recovered without a new base backup.

*Expected benefits. *Upgrading a replicated cluster reduces to upgrading
the primary with minimal cluster coordination. Standbys catch up by
themselves over their existing replication link, without offline rsync or a
rebuild. It also guarantees durability of post-upgrade modifications even
without a new base backup.

These benefits suggest that perhaps *--wal-upgrade* should even be the *default
behavior*.

*______________________________________________________________________*
*______________*

I’ve attached the patch file, and the implementation is in the same
repository.

https://github.com/LeeBohyun/postgres/tree/wal-upgrade-patch

Any discussion or feedback is welcome, especially from those who run
pg_upgrade on PostgreSQL clusters in practice.

Best regards,

Bohyun