Do we support upgrade of logical replication?

On Wed, Mar 10, 2021 at 4:03 PM vignesh C <vignesh21@gmail.com> wrote:

Hi,

I was reviewing logical decoding of two-phase transactions feature,
while reviewing the feature I was checking if there is any impact on
publisher/subscriber upgrade.

I checked the existing pg_upgrade behaviour with logical replication.
I made a logical replication data instance with publisher and
subscriber with subscriber subscribed to a table. Then I tried
upgrading publisher and subscriber individually. After upgrade I
noticed the following:

1) Pg_logical/mappings files were not copied in the upgraded data folder:
------------------------------------------------------------------
Pg_logical contents in old data folder:
publisher/pg_logical/replorigin_checkpoint
publisher/pg_logical/mappings:
map-32cb-4df-0_1767088-225-225
publisher/pg_logical/snapshots:
0-1643650.snap

New upgraded data folder:
publisher1/pg_logical/replorigin_checkpoint
publisher1/pg_logical/mappings:
publisher1/pg_logical/snapshots:

2) Replication slots were not copied:
select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary |
active | active_pid | xmin | catalog_xmin | restart_lsn |
confirmed_flush_lsn | wal_status | safe_wal_size | t
wo_phase
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+---------------+--
---------
(0 rows)

3) The subscriber is in disabled mode in the upgraded data:
select * from pg_subscription;
oid | subdbid | subname | subowner | subenabled | subbinary |
substream | subtwophase | subconninfo |
subslotname | subsynccommit | subpublicati
ons
-------+---------+---------+----------+------------+-----------+-----------+-------------+------------------------------------------+-------------+---------------+-------------
----
16404 | 16401 | mysub | 10 | f | f | f
| f | host=localhost port=5432 dbname=postgres | mysub
| off | {mypub}
(1 row)

4) The pg_subscription_rel contents also were not copied:
select * from pg_subscription_rel;
srsubid | srrelid | srsubstate | srsublsn
---------+---------+------------+----------
(0 rows)

5) While logical decoding of transactions, the decoded changes will be
serialized based on logical_decoding_work_mem configuration. Even
these files were not copied during upgrade.

Do we support upgrading of logical replication, if it is supported
could someone point me to the document link on how to upgrade logical
replication?

As far as I can understand, the main reason we don't copy all these
things is that we can't retain the slots after upgrade. I think the
reason for the same is that we reset WAL during upgrade and slots
might point to some old WAL location. Now, one might think that we can
try to copy WAL as well to allow slots to work after upgrade but the
WAL format can change in newer version so that might be tricky.

So users need to probably recreate the slots and then perform the
tablesync again via Alter Subscription ... Refresh Publication. Also,
that might require truncating the previous data. I am also not very
sure about the procedure but maybe someone else can correct me or add
more to it.

--
With Regards,
Amit Kapila.