Re: pg_upgrade and logical replication

On Thu, 2 Nov 2023 at 17:01, Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Thu, Nov 2, 2023 at 3:41 PM vignesh C <vignesh21@gmail.com> wrote:
> >
> > I have slightly modified it now and also made it consistent with the
> > replication slot upgrade, but I was not sure if we need to add
> > anything more. Let me know if anything else needs to be added. I will
> > add it.
> >
>
> I think it is important for users to know how they upgrade their
> multi-node setup. Say a two-node setup where replication is working
> both ways (aka each node has both publications and subscriptions),
> similarly, how to upgrade, if there are multiple nodes involved?

I was thinking of documenting something like this:
Steps to upgrade logical replication clusters:
Warning:
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

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.
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.

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.
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.

Regards,
Vignesh