Feedback-ID: i0fe9450f:Fastmail
Date: Thu, 9 Nov 2023 07:56:47 +0900
From: Michael Paquier <michael@paquier.xyz>
To: vignesh C <vignesh21@gmail.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>,
	"Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>,
	Julien Rouhaud <rjuju123@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: pg_upgrade and logical replication
Message-ID: <ZUwSLzWrkTJCs08G@paquier.xyz>
References: 
 <CALDaNm1ZrbHaWpJwwNhDTJocRKWd3rEkgJazuDdZ9Z-WdvonFg@mail.gmail.com>
 <TYAPR01MB5866F1BA00D86EA7EA134348F5F1A@TYAPR01MB5866.jpnprd01.prod.outlook.com>
 <CALDaNm1JzqTreCUrhNu5E1gq7Q8r_u3+FrisyT7moOED=UdoCg@mail.gmail.com>
 <ZQvbV2sdzBY6WEBl@paquier.xyz>
 <CALDaNm0X1WnHot8YWWhpKmzwX-au+gqCSL9J3RicqvAmsm=u1Q@mail.gmail.com>
 <CALDaNm0jWM1N6U_=P75kxgWWVaLak8oxOu1XDQQhbe=1h2dy7w@mail.gmail.com>
 <ZUHXaatBQOHIk_K0@paquier.xyz>
 <CALDaNm2FzAiAjW_OaLZbK4baC6E3u_26VixvcukbZzGqY9_+VQ@mail.gmail.com>
 <CAA4eK1+DrWkEi3iukmyQZYELVZ8HtfcahO1UTVUjwnFAm3=0rQ@mail.gmail.com>
 <CALDaNm2pe7SoOGtRkrTNsnZPnaaY+2iHC40HBYCSLYmyRg0wSw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="Aw7+EurLXI6JczK3"
Content-Disposition: inline
In-Reply-To: 
 <CALDaNm2pe7SoOGtRkrTNsnZPnaaY+2iHC40HBYCSLYmyRg0wSw@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/ZUwSLzWrkTJCs08G%40paquier.xyz>
Precedence: bulk


--Aw7+EurLXI6JczK3
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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.
>=20
> 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 Nod=
e1_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.
>=20
> 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

--Aw7+EurLXI6JczK3
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmVMEi8ACgkQnvQgOdby
QH2SkA/9FnYgnaPfbH+ir6YUOMH4orD3Qm9e2UNs0c9LtTDqG29ypo9qgDIFwxZg
MrBpMkFud5cxZnQTMj/DAutsbnXIceuKg+/2Eaevd3IS7QdbwDbbb1VzgB6m/LTM
1bazCfYmOM9/awhlSL8M/uHoYqEmDZBShCKp22/cSItFmfLK1McqwJs9hod5bEl/
1JIlELP92VoeloglLigxfSDOY0jyIjaUyuuolgp9FkMpewx5+o+Kz0E0N1ZvaokQ
EfXHuqXS7PKyfpGx6ezA6cYHZAfQ70a95wSUIb6E+Afaoy8YEN6Y7Qk73T+jjXUg
MvnZvYc9cbz81Z9vgaadWGQMu+QqMYR93wWNRQEno7OmKs6H2ZhNEU4/J9Jdmwbs
rW6bd/MZv8uebidECImnpI4d6fAzCZDuTxnJjgNkDUMNv5zE64Evo/oZwGL4f8Wa
vFaj0LQhkJD2u1Ciz5VK8gU7N+WK+wU/UJdbGDoRJtg335e4y4GSJtqC9HT95DIS
kRaiqPsgtnaM6YbBbz++RVLKPI5wPofR4GyeTtyOBwQ3r9xBc/RGXURu3xvqD91q
/xn35AFPkVEdRld6Z4FK9TchUxjDl9QCQOujtM33v2B5xw7UwhFQDSy2V/0wFYMv
5l+mr5sQ2460WEdH0eEQ8axQYM2sY2CnHebG6WXcDdJVyWSwVH0=
=+kvZ
-----END PGP SIGNATURE-----

--Aw7+EurLXI6JczK3--