Dedicated test coverage: streaming/physical replication (promotion, pg_upgrade-of-a-standby, permanently-empty _object_oid)

[jnasbyupgrade]

Summary

object_reference's redesign (#38) marks _object_reference._object_oid UNLOGGED, to close a confirmed logical-replication corruption vector (an independent database subscribed to it would silently overwrite its own OID cache with the publisher's meaningless OIDs). This has real consequences for physical (streaming/log-shipping) replication that haven't been tested or examined yet, and need a dedicated PR before #38's design can be considered complete. Kept as its own issue/PR rather than folded into the main implementation PRs, to keep those PRs a reasonable size.

Consequences of UNLOGGED

1. On a physical standby, _object_oid is permanently empty for as long as the server remains a standby — not just transiently empty the way it is after a primary's crash recovery. UNLOGGED tables generate no WAL, so they're never replicated at all. This is an ongoing structural condition, not a one-time transient state, and the sanity-check/repair logic needs to recognize "running on a standby" as its own normal case — not an anomaly to warn/error about, and not something a standby could ever repair itself (standbys are read-only).

2. Promotion (a standby becoming the new primary, e.g. a DR failover) is, in essence, the same situation as recovering from a crash on a regular writer: the newly-promoted primary's _object_oid is empty while _object_reference.object presumably is not. The same automatic detect-and-repair logic that already handles crash recovery should handle promotion too, transparently. This must not require any manual/extra action from the user as part of promotion — a DR failover needs to be fast and can't be gated behind a human remembering to run a repair command. (Contrast with pg_upgrade, which already has a deliberate pre_pg_upgrade()/post_pg_upgrade() bracket by design — that's fine there because pg_upgrade is already a planned maintenance operation with its own runbook; promotion is not, and must self-heal automatically.)

What needs testing

• (a) The sanity-check/repair logic correctly recognizes "running on a standby" as a normal state, not an anomaly, and doesn't attempt a doomed write there.
• (b) Promotion triggers automatic repair with zero required manual steps — verified with an actual promote-and-check test, not just reasoned about.
• (c) Whether any read path that currently relies on _object_oid beyond the sanity check itself would need a durable, replicated fallback for OID lookups on a standby, since the cache is structurally unusable there. Not yet confirmed whether _object_oid is used for anything beyond the sanity check — worth confirming during implementation, not something to resolve in this issue.
• (d) The pg_upgrade-of-a-standby-via-rsync interaction with UNLOGGED (see Investigation below).

Per pg_upgrade's own documentation ("Upgrade streaming replication and log-shipping standby servers"), there are two legitimate, documented paths for carrying a standby through a pg_upgrade, and both need coverage since they could plausibly behave differently here:

• The rsync/--link-mode path: rsync hard-links the upgraded primary's files onto each standby's data directory, servers stopped throughout.
• The simpler alternative the same doc section explicitly offers: skip rsync and just rebuild the standby from a fresh base backup of the already-upgraded, running primary.

Investigation: does the rsync path give the standby a populated _object_oid?

Checked against PostgreSQL source (src/bin/pg_upgrade, src/backend/access/transam/xlog.c, src/backend/storage/file/reinit.c):

• pg_upgrade itself has no special-case handling for UNLOGGED tables/relpersistence = 'u' at all — it copies/links their files like any other relation's.
• The rsync path copies the already-upgraded, populated primary's on-disk files verbatim, including the real content of _object_oid's main fork (unlike normal streaming replication, which never transmits UNLOGGED table contents at all).
• However, this doesn't leave the standby with a populated cache: xlog.c's StartupXLOG() calls ResetUnloggedRelations() unconditionally on every entry into recovery (if (InRecovery)), not just crash recovery — this includes a standby's own startup/archive-recovery path. That reset happens before Hot Standby connections are allowed, restoring every unlogged relation to its (empty) init-fork state regardless of what was physically copied onto disk.
• So: the rsync-copied populated file gets reset by the standby's own startup process before it's ever queryable. The "always empty on a standby" assumption holds even under this rsync-based pg_upgrade-of-a-standby procedure. Worth a test asserting this explicitly, since it's a "the on-disk file briefly looks populated" state that's easy to get confused about later, but not a design gap.

Lower priority, deferred: WAL-shipping (non-streaming) physical replication

WAL-shipping-based (not streaming) physical replication should technically get equivalent coverage eventually, since the same UNLOGGED-never-replicates reasoning applies. Treat this as optional/deferred relative to streaming replication — the repo owner is only lukewarm on prioritizing it right now. Filed here as a lower-priority note rather than a separate issue.

Keeping this issue current

Per #38's own "Next steps": this issue's scope/content needs to be kept fully up to date as implementation of #38 proceeds, not just accurate as of when it was opened — the same requirement already placed on #24.

References

• Design: automated OID-repair for pg_dump/pg_upgrade — consolidated plan + review findings #38 — main design issue (automated OID-repair for pg_dump/pg_upgrade)
• object_reference cannot survive binary pg_upgrade: _sentry_mv crashes it, and repair logic can't fix stale OIDs anyway #24 — underlying repair-logic bugs issue, carrying the same "keep fully up to date before implementation" requirement