Thread: pg_upgrade vs. logical replication

We maintain c.50 logical replicas.  Typically the producer version is 12 or 13,
and the subscriber version is 14.  We intend to upgrade the subscribers to 15
using pg_upgrade.  However, we ran into an unexpected problem with that
approach.  I couldn't find much being mentioned about it on the web, so I'm
sending this message for a sanity check that what we are seeing is expected,
and, that the proposed solution make sense.

The problem we see is that after running pg_upgrade on the subscriber, details
about the subscription are lost; specifically, pg_subscription_rel is empty, and
the associated replication origin has no LSN information in
pg_replication_origin_status.

I found a thread on the hackers list that seems to call out this problem:

https://www.postgresql.org/message-id/flat/20230217075433.u5mjly4d5cr4hcfe%40jrouhaud

It is a very long thread.  TLDR; Julien describes a similar problem to what we
have (although in our case, it is a "pure" logical replica with no consideration
about switching between physical replication, or needing to cut over, etc), and
a fix has gone in to PG17 that sorts this problem.

However, we can't go to 17 yet, so need a solution for 15 and 16. We are doing
the following sequence of events:

(1)  Shutdown subscriber;
(2)  Startup subscriber with max_logical_replication_workers set to 0;
(3)  Copy the data in pg_replication_origin_status to a table;
(4)  Shutdown subscriber;
(5)  Run pg_upgrade;
(6)  Startup subscriber with max_logical_replication_workers set to 0;
(7)  Apply the LSNs recorded in (3) to the appropriate origin using
     pg_replication_origin_advance;
(8)  ENABLE, REFRESH PUBLICATION WITH (COPY_DATA=FALSE), & DISABLE the
     subscription;
(9)  Shutdown subscriber;
(10) Startup subscriber normally;
(11) ENABLE subscription.

Step 7 requires a mapping from old origin name to new origin name as the names
are derived from subscription OIDs.  Steps 2 and 6 are required to ensure no
activity occurs that could advance LSNs.

Unlike in the mentioned thread, we know we won't have to deal with tables that
are partially replicated i.e we know all tables will be 'r'.

We have done some testing with this and it seems to work.  We run a repeated
INSERT on the producer several times a second, and notice that after step 11,
we have all expected rows arrive on the subscriber without gaps.  However, I
thought I'd ping this list for a sanity check that what we are doing makes
sense.  And, if so, perhaps it'll be useful to other people in a similar
situation.

Comments / suggestions welcome.

-Joe