Re: Do we support upgrade of logical replication?

On Wed, Mar 10, 2021 at 4:03 PM vignesh C <vignesh21(at)gmail(dot)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.