How a Successful pg_upgrade Silently Loses Your Logically Replicated Data

Your subscription has a running apply worker, zero lag, a clean log and every conflict counter at zero. How much of the publisher’s data is it actually keeping?

You cannot tell from any of that. A subscription in exactly that state can discard every change it receives, permanently, while telling the publisher each one was applied — no crash, no bad DELETE, no full disk, just logical replication doing what it was designed to do. I watched a few hundred committed rows disappear that way.

This is the third post in a series on production PostgreSQL upgrades — the first on my PG13 → PG18 playbook, the second on what happens to a subscriber’s replication origin during the upgrade window. This one is the same coin’s worse side: instead of replaying rows it already has, the subscriber throws away rows it never got.

This post follows the incident in the order it happened: what the operation was, what we found afterwards, and what pg_subscription_rel turned out to be. Then the mechanism itself, traced through the apply worker one step at a time — why an empty table list destroys data quietly enough that no monitoring system will ever mention it — and finally the two ways to keep it from happening.

Table of Contents

• The Operation: A pg_upgrade of a Logical Replication Subscriber
• What We Found After the Upgrade
• Ruling Out the Replication Origin
• What the Catalog Actually Showed
• The Object That Was Missing: What pg_subscription_rel Is
• The Real Problem, Step by Step in the Source
• How We Addressed It: Two Safe Ways to Re-Enable the Subscription
• The Other Ways a Table List Goes Incomplete
• What I Would Like to See Upstream
• Key Takeaways
• References

The Operation: A pg_upgrade of a Logical Replication Subscriber

Picture an ordinary shape, the one in the diagram below. Two independent PostgreSQL clusters, cluster-1 and cluster-2.

cluster-1 owns the write side. Its clients insert, update and delete there, and it publishes six tables — heartbeat, customers, orders, order_items, products and audit_log. cluster-2 subscribes to that publication, so the clients on its side read those same six tables locally instead of reaching across the network to cluster-1 for every query. Behind cluster-2 sits a streaming replica, which is a physical copy of cluster-2 and therefore inherits whatever cluster-2 ends up holding. That is the whole topology: one publication, one replication slot, one subscription, plus a physical standby on the read side.

The heartbeat table is the only unusual thing in that list. It takes one row per second with sequential ids and nothing reads it, so replication lag is obvious at a glance — and, later, so a gap is countable.

cluster-2 is on PG 14 and needs to be on PG 18. cluster-1 is not touched by any of this and keeps taking writes throughout — which matters, because those writes are what goes missing. The maintenance window follows the usual shape:

ALTER SUBSCRIPTION sub DISABLE;
SELECT * FROM pg_stat_subscription;   -- poll until empty: the worker must be gone

-- The subscriber's bookmark: how far into the publisher's WAL this
-- subscription had applied when it stopped. pg_upgrade does not carry it
-- across from a pre-17 cluster, so write it down. remote_lsn is the
-- position on the PUBLISHER; external_id is 'pg_<subscription OID>'.
SELECT external_id, remote_lsn, local_lsn FROM pg_replication_origin_status;

Then stop the cluster, run pg_upgrade, start it, ENABLE, REFRESH PUBLICATION. Order matters on that capture: read the origin while the worker is still running and you get a moving number that is stale the instant you write it down.

pg_upgrade reports success. The subscription comes back with the right connection string and the right publication list. pg_stat_subscription shows a live apply worker with no lag. New heartbeats land on the subscriber within a second of being written on the publisher. Every check anyone runs is green, and the window closes early.

What We Found After the Upgrade

The first hint that the window had not gone well came later, from cluster-2’s own log, well after the cluster had been handed back as healthy. One line, and nothing else anywhere:

2026-09-01 21:02:31.703 CEST [705456] LOG:  conflict detected on relation "public.heartbeat": conflict=update_missing
2026-09-01 21:02:31.703 CEST [705456] DETAIL:  Could not find the row to be updated.
        Remote row (31, 2026-09-01 21:02:31.698191+02); replica identity (id)=(31).
2026-09-01 21:02:31.703 CEST [705456] CONTEXT:  processing remote data for replication origin "pg_16562" during message type "UPDATE" for replication target relation "public.heartbeat" in transaction 1607, finished at 0/72701B8

conflict=update_missing means the apply worker received an UPDATE for a row that does not exist locally. Row 31 had been inserted on cluster-1 during the upgrade window, while cluster-2 was stopped, and that INSERT was supposed to arrive first when replication resumed. Only the UPDATE ever did. One row, on a table that exists purely to be watched — easy to file under “logical replication is fiddly across major versions”.

Counting the rows is what stopped that. heartbeat takes a row per second with sequential ids, so the diff was trivial: it was not one missing row, it was every row written on the publisher between the DISABLE and the moment replication resumed — a contiguous block, gone. Row 31 was not special. It was the only one anybody had happened to UPDATE afterwards, which is the only reason it produced a log line at all.

That reframes the whole thing. The conflict is not the failure; it is a coincidence that exposed the failure. On a table where rows are inserted and never touched again — an append-only event log, an audit trail, most of what people actually replicate — the same loss produces no log line, no conflict, and no counter.

Worse, the rows were not merely absent. They were unreachable. The publisher had already recycled the WAL that held them, because it had been told they were applied. The clients on the right of the architecture diagram were now reading six tables quietly missing a slice of their history, and the streaming replica behind cluster-2 was faithfully reproducing the same gap.

Ruling Out the Replication Origin

Two of the four pieces of a subscription survive a pre-17 pg_upgrade badly, so there were two candidates for a contiguous block of missing rows. The replication origin is the obvious one, and it is where most people start. It turns out to be the wrong one, and ruling it out is what points at the real cause.

The origin is the known casualty of a pre-17 upgrade. Every piece of subscription-state preservation in pg_dump is gated on the old cluster being 17.0 or later (fout->remoteVersion >= 170000):

That last row is worth pausing on. check_old_cluster_subscription_state() is the function that would otherwise refuse to upgrade a subscription whose origin cannot be preserved — and it is itself gated on GET_MAJOR_VERSION(old_cluster.major_version) >= 1700. With a PG 14 source it does not execute at all, which is why pg_upgrade reported success without so much as a notice. The documentation is upfront about it: “Subscription dependencies on clusters before version 17.0 will silently be ignored.”

So the natural fix was to put the position back with pg_replication_origin_advance(). It could not have worked, for two reasons.

An enabled subscription holds the origin session for the apply worker’s entire lifetime, and replorigin_advance() refuses to move an origin that is in use — the call errors out with ERRCODE_OBJECT_IN_USE. Fine: disable, rewind, re-enable. But the publisher does not take orders from the subscriber’s origin. CreateDecodingContext() clamps any requested start position forward to the slot’s confirmed position:

else if (start_lsn < slot->data.confirmed_flush)
{
    elog(LOG, "%X/%08X has been already streamed, forwarding to %X/%08X", ...);
    start_lsn = slot->data.confirmed_flush;
}

Rewinding the subscriber’s origin below the publisher’s confirmed_flush_lsn achieves nothing. START_REPLICATION is silently forwarded past the rewind, at LOG level, on the publisher — where nobody upgrading a subscriber is looking. That line is the single most useful fingerprint in this whole story:

grep 'has been already streamed, forwarding to' /path/to/publisher/log/*

The same function settles the other half of the question, the one the pre-upgrade capture was built around. When the origin is gone entirely, the apply worker derives the name from the subscription’s current OID and creates a new one at zero (replorigin_create(), remote_lsn = 0/0). But 0/0 is not a position, and the publisher does not treat it as one; it substitutes the slot’s:

if (!XLogRecPtrIsValid(start_lsn))
    start_lsn = slot->data.confirmed_flush;   /* continue from last position */

Put those two branches together and they say one thing: whatever the subscriber asks for, the publisher’s confirmed_flush_lsn decides where the stream resumes. pg_upgrade never touches the publisher, so that number survived the upgrade untouched. It is also the right number, because a subscriber only acknowledges an LSN after everything before it has been applied and flushed to disk — it cannot be ahead of what the old subscriber durably had. The bookmark nobody could find had never been lost. It lives on the other server.

Which means restoring the origin buys nothing, and it is the one step that can make things worse: an LSN supplied ahead of the true position skips everything in between, silently. It also raised the real question. Rewinding was pointless because confirmed_flush_lsn had already moved past the gap. The subscriber had never applied those transactions — so why had the publisher been told it had?

What the Catalog Actually Showed

Because the origin was not the only thing pg_upgrade left behind. pg_dump still emits the subscription itself, as a plain catalog object:

CREATE SUBSCRIPTION sub CONNECTION '...' PUBLICATION pub
    WITH (connect = false, slot_name = '<original slot name>', ...);

One query shows the whole difference. It joins the subscription to its table list — and the LEFT JOIN is the important part, because an inner join makes this failure invisible: a subscription with an empty pg_subscription_rel simply drops out of the result set.

SELECT s.oid                AS subid,
       s.subname,
       s.subenabled,
       s.subslotname,
       s.subpublications,
       r.srrelid::regclass  AS table_name,
       r.srsubstate,
       CASE
           WHEN r.srsubstate IS NULL THEN 'NO ROW - changes are discarded'
           WHEN r.srsubstate = 'i'   THEN 'initialize'
           WHEN r.srsubstate = 'd'   THEN 'data copy'
           WHEN r.srsubstate = 'f'   THEN 'finished copy'
           WHEN r.srsubstate = 's'   THEN 'synchronized'
           WHEN r.srsubstate = 'r'   THEN 'ready'
       END                  AS state_desc,
       r.srsublsn
FROM pg_subscription s
LEFT JOIN pg_subscription_rel r ON r.srsubid = s.oid
ORDER BY s.subname, table_name;

On the PG 14 subscriber before the upgrade, that returns one row per published table — heartbeat, customers, products, orders, order_items, audit_log — every one of them in state 'r', ready:

This is what a correct subscription looks like: the pg_subscription row and the pg_subscription_rel list agree, and the six tables the publisher sends are the six tables the subscriber has agreed to keep.

The same query on the PG 18 cluster, immediately after a pg_upgrade that reported success and before the ENABLE:

 subid |                subname                | subenabled |        subslotname        |       subpublications        | table_name | srsubstate |           state_desc           | srsublsn
-------+---------------------------------------+------------+---------------------------+------------------------------+------------+------------+--------------------------------+----------
 16401 | logical_v2_sub_publication_subscriber | f          | benchmark_v2_primary_slot | {logical_v2_sub_publication} |            |            | NO ROW - changes are discarded |
(1 row)

Six rows became one. Everything that makes the subscription look correct survived — same name, same publication, same slot, still pointed at the same live publisher. And the six table rows are simply gone: table_name is NULL because there is nothing on the right side of the join to match.

The OID is new, because the subscription was recreated as an ordinary catalog object rather than preserved, and that matters more than it looks. The replication origin’s name is derived from it — pg_<subscription OID> — so this subscription’s origin on the new cluster is pg_16401, not the pg_16739 recorded before the upgrade. Neither exists. The external_id from the pre-upgrade capture is therefore not even a usable argument to pg_replication_origin_advance() afterwards — advancing pg_16739 would create an origin that nothing ever reads.

That is the state the cluster is in the moment before somebody types ENABLE. The subscriber was disabled, had no replication origin, was still bound to the original publisher slot — and had zero rows in pg_subscription_rel. That last one does the damage. The missing origin is a positioning problem; the empty table list is a fully wired-up subscription, pointed at a live source of changes, with no record that any of the tables it replicates belong to it. And the sequence used to bring it back up — ENABLE, then REFRESH PUBLICATION — meant the apply worker ran for a while with that catalog still empty.

So the diagnosis is one sentence long: a subscription is four objects, pg_upgrade restored three of them, and the one it dropped is the one with no symptoms.

The Object That Was Missing: What pg_subscription_rel Is

CREATE SUBSCRIPTION reads like it creates one thing. It does not. A working subscription is four separate objects that have to agree with each other, and they do not even live on the same server:

Monitoring watches the first two. The origin at least protests when it is wrong — duplicate keys, or a visible gap. The fourth has no symptom at all, and it is the one this incident turned on, so it is worth knowing properly.

pg_subscription_rel is the subscriber’s own answer to two questions: which tables does this subscription replicate, and how far has each one got through its initial sync. One row per (subscription, relation) pair — a subscription to a six-table publication has six rows, and a subscriber running three subscriptions keeps three independent sets of them. It is a shared-nothing local catalog: the publisher’s side of the arrangement lives in pg_publication_rel on the other server, and nothing in PostgreSQL reconciles the two lists.

Four columns, and none of them is decorative:

srsubstate is a small state machine, walked once per table at the moment that table joins the subscription — at CREATE SUBSCRIPTION, or at ALTER SUBSCRIPTION ... REFRESH PUBLICATION for a table that was not there before:

srsublsn is the handoff itself. While a table is being copied it is NULL; when the tablesync worker finishes it records the LSN at which it stopped, and the apply worker uses that to decide which changes for that relation it must skip (already in the COPY) and which it must apply (committed after it). The two workers never coordinate directly; they coordinate through this catalog.

Which is why it is the right place to watch a sync from. When you create a subscription or refresh one, this is the progress bar:

SELECT srrelid::regclass AS table_name, srsubstate, srsublsn
FROM pg_subscription_rel
ORDER BY srsubstate, table_name;

A table sitting on d for a long time means the initial COPY is slow or blocked — check pg_stat_activity for the tablesync worker and whatever it is waiting on. A table stuck on i means no worker has started it, usually because max_sync_workers_per_subscription is saturated by the other tables ahead of it. Once every row reads r, the subscription is fully in normal logical replication.

But notice what that catalog is doing here. It has two jobs, not one: during the initial sync it is a progress tracker, and after the sync it becomes the permanent membership list — the record that this relation belongs to this subscription and its changes are to be applied. The first job ends; the second one never does. And both are expressed in the same column, which is exactly where the trouble starts, because the apply worker asks that column a single question — is this relation ready? — and no row is not 'r'. An empty pg_subscription_rel is a subscription that has agreed to keep nothing, and it looks, from every angle except this catalog, like a healthy one.

That also makes the one check worth automating obvious. The subscriber’s list has to cover the publisher’s, and only you can compare them:

-- On the publisher: what is actually being sent.
SELECT schemaname, tablename FROM pg_publication_tables
WHERE pubname = '<publication>' ORDER BY 1, 2;

-- On the subscriber: what will actually be kept.
SELECT n.nspname, c.relname, r.srsubstate
FROM pg_subscription_rel r
JOIN pg_class c ON c.oid = r.srrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_subscription s ON s.oid = r.srsubid
WHERE s.subname = '<subscription>' ORDER BY 1, 2;

Any table in the first result that is absent from the second — or present but not 'r' — is a table whose changes are arriving on the subscriber and being dropped. That diff is the only signal this failure has.

The Real Problem, Step by Step in the Source

So the catalog was empty and the rows were gone. Between those two facts sit six steps in the apply worker, each individually correct. This is the whole mechanism, in the order the code executes it.

Step 1 — The worker asks the catalog about the relation, and gets nothing. When the apply worker opens a target relation, logicalrep_rel_open() fills in the relation’s sync state by calling GetSubscriptionRelState(). With no row in pg_subscription_rel for that (subscription, relation) pair, there is nothing to report, so it returns SUBREL_STATE_UNKNOWN. This is not an error condition. It is a legitimate answer, because a relation genuinely can be unknown to a subscription.

Step 2 — The gate rejects UNKNOWN, along with every other non-ready state. should_apply_changes_for_rel() is the single decision point for whether a change is applied or dropped:

case WORKERTYPE_APPLY:
    return (rel->state == SUBREL_STATE_READY ||
            (rel->state == SUBREL_STATE_SYNCDONE &&
             rel->statelsn <= remote_final_lsn));

Two branches. Either the table is fully 'r', or its tablesync has finished and this change committed after the handoff LSN — that second branch is what srsublsn exists for. UNKNOWN matches neither, so the function returns false.

To be fair to the code, this gate is correct and necessary in its intended context. It is how an apply worker avoids racing a tablesync worker that has not finished copying a table yet. The hazard is that at this point “this table is not synced yet” and “this subscription has no tables at all because pg_upgrade dropped them” are indistinguishable, and the code has no way to tell them apart.

Step 3 — Every change handler takes the same silent early exit. This is apply_handle_insert(); update and delete are identical:

if (!should_apply_changes_for_rel(rel))
{
    logicalrep_rel_close(rel, RowExclusiveLock);
    end_replication_step();
    return;
}

There is no log message on this path at any elevation. No conflict is raised. pg_stat_subscription_stats is not touched. Nothing is written anywhere that would let you count what was dropped, then or later.

Step 4 — A transaction is already open, and it stays open. This is the step that turns a discard into a loss. begin_replication_step() starts a local transaction before the relation-state check ever runs. So a transaction whose every change was discarded is still a live, empty transaction when the commit message arrives.

Step 5 — The empty transaction commits, and writes the remote LSN into the origin. Because a transaction is open, apply_handle_commit_internal() takes the IsTransactionState() branch:

if (IsTransactionState())
{
    ...
    replorigin_xact_state.origin_lsn = commit_data->end_lsn;   /* origin moves forward */
    ...
    CommitTransactionCommand();
    ...
    store_flush_position(commit_data->end_lsn, XactLastCommitEnd);  /* acked to publisher */
}

For every dropped transaction, an empty local transaction commits, durably recording the remote end_lsn in the replication origin. From the subscriber’s own point of view, that transaction was applied.

Step 6 — The publisher is told, and releases the WAL. store_flush_position() queues that LSN as flushed, send_feedback() reports it upstream, and the publisher advances the slot’s confirmed_flush_lsn. restart_lsn moves along with it, and within a checkpoint cycle or two those WAL segments are recycled.

That is the answer to the question left open earlier. The publisher was told the transactions were applied by the subscriber itself, one empty commit at a time, each an honest acknowledgement of work that never happened. Both endpoints durably agreed. Neither one retained the data.

Nothing before the ENABLE is destructive — the publisher’s slot had been pinning the WAL for those rows the whole time. The ENABLE is where they are thrown away, and where both ends are told they were applied.

Why the Safe Ordering Cannot Be Written in SQL

Given all that, the fix looks obvious: populate pg_subscription_rel first, then enable. That ordering is not expressible in SQL. AlterSubscription() rejects it outright:

case ALTER_SUBSCRIPTION_REFRESH_PUBLICATION:
{
    if (!sub->enabled)
        ereport(ERROR,
                (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
                 errmsg("%s is not allowed for disabled subscriptions",
                        "ALTER SUBSCRIPTION ... REFRESH PUBLICATION")));

Nor can the two statements be wrapped into one atomic unit, because the same path calls PreventInTransactionBlock(). So on a normally-configured cluster the only available order is ENABLE first, REFRESH second — and the ENABLE commit wakes the launcher, which starts the apply worker within milliseconds, with pg_subscription_rel still empty. The window between those two statements is the entire incident:

Step 3 is not destructive — the slot’s restart_lsn is still pinning everything from L0, which is exactly what a replication slot is for. Step 4 is where the rows die, one step before the command that would have prevented it is even legal to run. The one ordering the system permits is the one that loses data, and no amount of typing the two statements closer together helps: the worker starts at the ENABLE commit, not at your next keystroke. Which fixes the shape of the fix: the worker has to be made unable to start, because no ordering of statements will do it.

How We Addressed It: Two Safe Ways to Re-Enable the Subscription

So the requirement is fixed: pg_subscription_rel has to be populated, with every published table at 'r', before an apply worker ever runs — and since no ordering of SQL statements can guarantee that, the worker has to be prevented from starting at all. There are exactly two supported ways to do that, and neither requires touching catalogs by hand. Both are things you do during the upgrade window, before anything is lost. Pick one and put it in the runbook.

All three end with a healthy-looking subscription. Only the two on the right end with the data intact.

Method A: Bring the New Cluster Up With No Logical Replication Workers

max_logical_replication_workers = 0 prevents the launcher from being registered at all — ApplyLauncherRegister() returns early — so no apply or tablesync worker can start no matter what the catalog says. REFRESH is unaffected, which is the key insight: AlterSubscription_refresh() connects to the publisher from the backend running the statement, via walrcv_connect(), not from a worker. The GUC requires a restart, which is what makes it airtight, and costs nothing extra because the post-upgrade start is the cluster’s first start anyway:

# 1. Start the upgraded subscriber with logical replication disabled entirely
pg_ctl -D <newdata> -o "-c max_logical_replication_workers=0" start

-- 2. Confirm it took effect. Do not proceed on assumption.
SHOW max_logical_replication_workers;   -- must be 0
SELECT * FROM pg_stat_subscription;     -- must be empty

-- 3. Safe now: there is no launcher to act on this
ALTER SUBSCRIPTION sub ENABLE;
ALTER SUBSCRIPTION sub REFRESH PUBLICATION WITH (copy_data = false);

-- 4. Verify before you restart. Every published table must be present and 'r'.
SELECT srrelid::regclass, srsubstate FROM pg_subscription_rel;

# 5. Only after step 4 verifies clean
pg_ctl -D <newdata> restart

The ENABLE at step 3 is durable, so the exposure is deferred rather than removed. Verify at step 4, before the restart — the restart is what lets the worker run, and it is the last moment the situation is still harmless. Compare that table list against both the states recorded before the upgrade and the publisher’s pg_publication_tables. Any published table absent from pg_subscription_rel is a live data-loss source. And any table that was not 'r' before the upgrade, or that was added to the publication during the outage, holds no data on the subscriber at all — copy_data = false marks it 'r' regardless and it will never receive what it is missing. Those need a full re-copy.

Method B: Drop and Recreate the Subscription Against the Same Slot

If restarting twice is unacceptable, drop and recreate instead.

Where these steps go: on the upgraded PG 18 cluster, after pg_upgrade has finished and the new cluster has been started normally — and before any ALTER SUBSCRIPTION ... ENABLE. The window is the same as Method A’s, but the new cluster starts with its ordinary configuration: no GUC override, no second restart. The placement is not a detail. pg_upgrade hands the subscription back disabled, and that is both what makes SET (slot_name = NONE) legal — it is rejected on an enabled subscription — and what guarantees no apply worker has yet run against the empty pg_subscription_rel. Run any of this after an ENABLE and you are recovering from the loss, not preventing it.

It works because connect and enabled are independent options: CREATE SUBSCRIPTION will connect, fetch the table list and populate pg_subscription_rel while the subscription stays disabled. With copy_data = false, every published table enters straight into SUBREL_STATE_READY — no tablesync, no re-copy:

-- Run on the upgraded PG 18 subscriber, after pg_upgrade, with the
-- subscription still disabled exactly as pg_upgrade left it.

-- 1. Confirm nothing has started. Both must hold before you continue.
SELECT subname, subenabled FROM pg_subscription;   -- subenabled must be 'f'
SELECT * FROM pg_stat_subscription;                -- must be empty

-- 2. Detach the publisher slot so DROP does not delete it (this is
--    rejected unless the subscription is disabled)
ALTER SUBSCRIPTION sub SET (slot_name = NONE);
DROP SUBSCRIPTION sub;

-- 3. Recreate against the SAME slot: connected but not enabled. This fills
--    pg_subscription_rel with every published table in 'r', and creates a
--    fresh replication origin.
CREATE SUBSCRIPTION sub
    CONNECTION '<same conninfo>'
    PUBLICATION <same publications>
    WITH (slot_name = '<original slot>', create_slot = false,
          copy_data = false, enabled = false
          /* plus every non-default option the old subscription had */);

-- 4. Verify the table list, then start the worker
SELECT srrelid::regclass, srsubstate FROM pg_subscription_rel;
ALTER SUBSCRIPTION sub ENABLE;

One caveat, worth being blunt about: Method B prevents the loss, it does not repair one that has already happened. Its correctness depends on where the publisher slot is sitting. Before any apply worker has run, confirmed_flush_lsn is still at the pre-disable position, the slot’s restart_lsn has been pinning that WAL the whole time, and the recreated subscription’s origin at 0/0 resolves to it — the backlog streams through and applies. After a worker has already run against an empty pg_subscription_rel, confirmed_flush_lsn is past the gap and the WAL is very likely recycled; the recreated subscription reuses that advanced position and the gap is never re-delivered. Same commands, opposite outcomes. Compare the publisher’s confirmed_flush_lsn against the remote_lsn you recorded: if the slot has moved past it, you are recovering, not preventing.

One precondition applies to both methods: the publisher slot must still be valid. A slot left inactive across a long upgrade window can be invalidated by max_slot_wal_keep_size (RS_INVAL_WAL_REMOVED), which discards exactly the WAL the catch-up depends on. Check conflicting and invalidation_reason in pg_replication_slots before you start and again before you enable; an invalidated slot means a full re-copy regardless of method.

The trade-off: Method A keeps the subscription object, so no option, owner or ACL can be lost, but costs a restart. Method B avoids the restart, but the subscription is a genuinely new object — every non-default option (binary, streaming, synchronous_commit, disable_on_error, origin, two_phase) has to be restated, and ownership and ACLs are reset. If you automate Method B, do not retype those options from a runbook: build the CREATE SUBSCRIPTION statement with format() over the live pg_subscription row and execute it in the same session with \gexec, before the DROP. The options come off the catalog rather than off someone’s memory at 3am, and subconninfo — which contains a password — is read and re-executed in place, never parsed into a variable or a log line.

Neither method needs the captured remote_lsn to restore anything, for the reason the origin section gave: the publisher’s slot already holds the authoritative restart position. Capture it anyway. It is the number you compare against confirmed_flush_lsn to tell prevention from recovery, and it is how you measure the one real trade-off in starting from the slot instead of the origin. Resuming at confirmed_flush_lsn can replay transactions the old subscriber applied but had not yet confirmed — the duplicate-key scenario from the previous post. That is the deliberate choice: a loud, bounded, recoverable failure instead of a silent, unbounded, unrecoverable one. You can measure the replay beforehand instead of guessing, with the subscription disabled and its workers stopped:

-- On the publisher, before the upgrade window closes.
-- Anything above zero is exactly what will replay on resume.
SELECT confirmed_flush_lsn,
       pg_wal_lsn_diff('<remote_lsn captured on the subscriber>'::pg_lsn,
                       confirmed_flush_lsn) AS bytes_that_will_replay
FROM pg_replication_slots
WHERE slot_name = '<slot>';

In a cleanly stopped subscription this is normally zero or very close to it, because the worker confirms as it goes.

The Other Ways a Table List Goes Incomplete

No upgrade is needed to reproduce this, which is the clearest evidence that the empty catalog is the mechanism. On a single build: let a subscription reach 'r', DISABLE it, then DELETE FROM pg_subscription_rel; on the subscriber (superuser catalog write, test systems only) to simulate what the pre-17 upgrade leaves behind. INSERT rows on the publisher, ENABLE — the worker starts, no errors appear, the rows never arrive, and remote_lsn advances straight past them. REFRESH PUBLICATION WITH (copy_data = false) afterwards, then UPDATE those same rows upstream, and there is conflict=update_missing, exactly as above.

The all-tables-missing case is the dramatic one. In day-to-day operation the same gate fires on individual relations, and the loss is narrower but just as quiet:

• A table added to the publication, with no REFRESH PUBLICATION on the subscriber. The common one. ALTER PUBLICATION ... ADD TABLE is a publisher-side change and nothing on the subscriber notices; the publisher starts sending immediately, the subscriber has no row for that table, drops every change and acknowledges the LSN anyway. A FOR ALL TABLES publication does this for every newly created table.
• A table dropped from pg_subscription_rel but still published. REFRESH prunes rows for tables no longer in the publication; re-add the table upstream without another refresh and it stays missing on the subscriber.
• A tablesync that never reached 'r'. A relation stuck in 'i' or 'd' is legitimately skipped while it waits — the gate working correctly — but the wait can be indefinite, and its footprint is identical to the loss case.
• Any restore, clone or catalog surgery that recreates the subscription without its table list. A pg_dump/pg_restore of a subscriber, a subscription rebuilt from a runbook, a DR copy brought up and enabled.

The common shape: the publisher decides what to send, the subscriber decides what to keep, and nothing checks that those two lists agree.

What I Would Like to See Upstream

No single component here violates a guarantee it makes — the pre-17 limitation is documented, and the relation-state gate does exactly what it was designed to do. Two things around it are hard to defend in the field: the skip is invisible (no log line, no conflict, no counter, no catalog trace, while the origin and the slot advance past the data), and the safe ordering is not reachable in SQL. Three small hardenings would each have caught this:

1. Warn, or refuse, at ENABLE time. ALTER SUBSCRIPTION ... ENABLE on a subscription with zero pg_subscription_rel rows is almost always an operator error, and after a pre-17 pg_upgrade it is always one. A WARNING with a HINT pointing at REFRESH PUBLICATION catches it at the last harmless moment.
2. Log the skip. A DEBUG1, or a rate-limited WARNING, distinguishing “relation not yet synced” from “relation not in the subscription at all”. Cheap, and it turns an invisible failure into a greppable one.
3. Warn during pg_upgrade. All subscription validation is skipped for pre-17 old clusters; a notice when such a cluster contains subscriptions would put the warning where the operator is looking.

Key Takeaways

• A subscription is four objects, and the one with no symptoms is the table list. A running worker, zero lag, no errors and no conflicts are all fully compatible with every change being thrown away.
• The only check that distinguishes the two cases is pg_subscription_rel against the publisher’s pg_publication_tables. Make it a standing alert, not an incident-time query — this applies whether or not you are upgrading anything.
• Never let an apply worker run before pg_subscription_rel is populated and verified. One commit with an empty table list pushes both endpoints past the gap irreversibly. “Disabled” is not enough, because REFRESH requires an enabled subscription — the worker must be made unable to start.
• Adding a table to a publication is not enough. Until the subscriber refreshes, that table’s changes are received, discarded, and acknowledged as applied. FOR ALL TABLES hits this on every newly created table.
• The publisher’s slot is the authoritative restart position, not the subscriber’s origin. A fresh origin at 0/0 resolves to confirmed_flush_lsn correctly; restoring the origin is optional and is the one step that can silently skip data. Leave the slot alone — but do check its conflicting and invalidation_reason first.
• copy_data = false marks every table 'r' whether or not it holds data. Right for tables already 'r' before the upgrade, wrong for one mid-sync or added during the outage — those need a full re-copy.
• Prevention and recovery are different problems, separated by one question: has the publisher’s confirmed_flush_lsn already moved past the remote_lsn you recorded?

Nothing along this trail was broken in isolation. The slot pinned the WAL, the origin tracked progress, the feedback loop kept both ends in step, conflict detection caught the row that did not fit — every one of those signals answers “is the stream flowing correctly?”. None of them answers “is this subscription supposed to be keeping what it receives?”, and that is the question that had the wrong answer. Every other signal will keep reporting healthy right up until someone counts the rows.

References

• PostgreSQL Documentation: Upgrading a PostgreSQL Cluster with Logical Replication
• PostgreSQL Documentation: Replication Progress Tracking (§53.19)
• PostgreSQL Documentation: pg_replication_origin_status
• PostgreSQL Documentation: pg_stat_subscription_stats
• PostgreSQL Documentation: ALTER SUBSCRIPTION
• PostgreSQL Documentation: DROP SUBSCRIPTION
• PostgreSQL Documentation: pg_upgrade