Re: pg_upgrade and logical replication

On Wed, Nov 08, 2023 at 10:52:29PM +0530, vignesh C wrote:
> Upgrading logical replication nodes requires multiple steps to be
> performed. Because not all operations are transactional, the user is
> advised to take backups.
> Backups can be taken as described in
> https://www.postgresql.org/docs/current/backup.html

There's a similar risk with --link if the upgrade fails after the new
cluster was started and the files linked began getting modified, so
that's something users would be OK with, I guess.

> Upgrading 2 node logical replication cluster:
> 1) Let's say publisher is in Node1 and subscriber is in Node2.
> 2) Stop the publisher server in Node1.
> 3) Disable the subscriptions in Node2.
> 4) Upgrade the publisher node Node1 to Node1_new.
> 5) Start the publisher node Node1_new.
> 6) Stop the subscriber server in Node2.
> 7) Upgrade the subscriber node Node2 to Node2_new.
> 8) Start the subscriber node Node2_new.
> 9) Alter the subscription connections in Node2_new to point from Node1
> to Node1_new.

Do they really need to do so in an pg_upgrade flow?  The connection
endpoint would be likely the same for transparency, no?

> 10) Enable the subscriptions in Node2_new.
> 11) Create any tables that were created in Node1_new between step-5
> and now and Refresh the publications.

How about the opposite stance, where an upgrade flow does first the
subscriber and then the publisher?  Would this be worth mentioning?
Case 3 touches that as nodes hold both publishers and subscribers.

> Steps to upgrade cascaded logical replication clusters:
> 1) Let's say we have a cascaded logical replication setup
> Node1->Node2->Node3. Here Node2 is subscribing to Node1 and Node3 is
> subscribing to Node2.
> 2) Stop the server in Node1.
> 3) Disable the subscriptions in Node2 and Node3.
> 4) Upgrade the publisher node Node1 to Node1_new.
> 5) Start the publisher node Node1_new.
> 6) Stop the server in Node1.
> 7) Upgrade the subscriber node Node2 to Node2_new.
> 8) Start the subscriber node Node2_new.
> 9) Alter the subscription connections in Node2_new to point from Node1
> to Node1_new.

Same here.

> 10) Enable the subscriptions in Node2_new.
> 11) Create any tables that were created in Node1_new between step-5
> and now and Refresh the publications.
> 12) Stop the server in Node3.
> 13) Upgrade the subscriber node Node3 to Node3_new.
> 14) Start the subscriber node Node3_new.
> 15) Alter the subscription connections in Node3_new to point from
> Node2 to Node2_new.
> 16) Enable the subscriptions in Node2_new.
> 17) Create any tables that were created in Node2_new between step-8
> and now and Refresh the publications.
>
> Upgrading 2 node circular logical replication cluster:
> 1) Let's say we have a circular logical replication setup Node1->Node2
> & Node2->Node1. Here Node2 is subscribing to Node1 and Node1 is
> subscribing to Node2.
> 2) Stop the server in Node1.
> 3) Disable the subscriptions in Node2.
> 4) Upgrade the node Node1 to Node1_new.
> 5) Start the node Node1_new.
> 6) Enable the subscriptions in Node1_new.
> 7) Wait till all the incremental changes are synchronized.
> 8) Alter the subscription connections in Node2 to point from Node1 to Node1_new.
> 9) Create any tables that were created in Node2 between step-2 and now
> and Refresh the publications.
> 10) Stop the server in Node2.
> 11) Disable the subscriptions in Node1.
> 12) Upgrade the node Node2 to Node2_new.
> 13) Start the subscriber node Node2_new.
> 14) Enable the subscriptions in Node2_new.
> 15) Alter the subscription connections in Node1 to point from Node2 to
> Node2_new.
> 16) Create any tables that were created in Node1_new between step-10
> and now and Refresh the publications.
>
> I have done basic testing with this, I will do further testing and
> update it if I find any issues.
> Let me know if this idea is ok or we need something different.

I have not tested, but having documentation among these lines is good
because it becomes clear what the steps one needs to do are.

Another thing that I doubt is worth mentioning is the schema changes
that may happen.  We could just say that the schema should be fixed
while running an upgrade, which is kind of fair to expect in logical
setups for tables replicated anyway?

Do you think that there would be an issue in automating such tests
once support for the upgrade of subscribers is done (hopefully)?  The
first scenario may not need extra coverage if we have already
003_logical_slots.pl and a second file to test for the subscriber
part, though.
--
Michael