Feedback-ID: i0fe9450f:Fastmail
Date: Thu, 7 Sep 2023 15:33:58 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Peter Smith <smithpb2250@gmail.com>,
	Julien Rouhaud <rjuju123@gmail.com>,
	"Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: pg_upgrade and logical replication
Message-ID: <ZPlu1ub1GltmRamu@paquier.xyz>
References: <20230327084955.m624nwfaf7mbzo6j@jrouhaud>
 <TYAPR01MB58667340763002108CEB5EB8F5919@TYAPR01MB5866.jpnprd01.prod.outlook.com>
 <20230407022802.jqmdhgjhrnj5lpcl@jrouhaud>
 <TYAPR01MB586682CF17623F6E71B9D3ADF59D9@TYAPR01MB5866.jpnprd01.prod.outlook.com>
 <20230424072224.qd3awkfrjcanw2pa@jrouhaud>
 <CAHut+PvYt0yVJ+eAg0AdTHyTmgoZVY9znk3uxe98jOytmeCwLA@mail.gmail.com>
 <ZLeODIhuoClIQTPR@paquier.xyz>
 <CAA4eK1LMK0DVtg7MB9tFOrGrNatTbMhzh8tRm7WBySq6bVx+Qw@mail.gmail.com>
 <ZPV85hix0dgcXl/j@paquier.xyz>
 <CAA4eK1+1W4kAY9Hig-MEhJxC-FDHwZJ5m5joR7uqUYaAvzHLbw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="nL+S3qo4L9YkUpDQ"
Content-Disposition: inline
In-Reply-To: 
 <CAA4eK1+1W4kAY9Hig-MEhJxC-FDHwZJ5m5joR7uqUYaAvzHLbw@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/ZPlu1ub1GltmRamu%40paquier.xyz>
Precedence: bulk


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

On Mon, Sep 04, 2023 at 02:12:58PM +0530, Amit Kapila wrote:
> Yeah, I agree that could be hacked quickly but note I haven't reviewed
> in detail if there are other design issues in this patch. Note that we
> thought first to support the upgrade of the publisher node, otherwise,
> immediately after upgrading the subscriber and publisher, the
> subscriptions won't work and start giving errors as they are dependent
> on slots in the publisher. One other point that needs some thought is
> that the LSN positions we are going to copy in the catalog may no
> longer be valid after the upgrade (of the publisher) because we reset
> WAL. Does that need some special consideration or are we okay with
> that in all cases?

In pg_upgrade, copy_xact_xlog_xid() puts the new node ahead of the old
cluster by 8 segments on TLI 1, so how would be it a problem if the
subscribers keep a remote confirmed LSN lower than that in their
catalogs?  (You've mentioned that to me offline, but I forgot the
details in the code.)

> As of now, things are quite safe as documented in
> pg_dump doc page that it will be the user's responsibility to set up
> replication after dump/restore. I think it would be really helpful if
> you could share your thoughts on the publisher-side matter as we are
> facing a few tricky questions to be answered. For example, see a new
> thread [1].

In my experience, users are quite used to upgrade standbys *first*,
even in simple scenarios like minor upgrades, because that's the only
way to do things safely.  For example, updating and/or upgrading
primaries before the standbys could be a problem if an update
introduces a slight change in the WAL record format that could be
generated by the primary but not be processed by a standby, and we've
done such tweaks in some records in the past for some bug fixes that
had to be backpatched to stable branches.

IMO, the upgrade of subscriber nodes and the upgrade of publisher
nodes need to be treated as two independent processing problems, dealt
with separately.

As you have mentioned me earlier offline, these two have, from what I
understand. one dependency: during a publisher upgrade we need to make
sure that there are no invalid slots when beginning to run pg_upgrade,
and that the confirmed LSN of all the slots used by the subscribers
match with the shutdown checkpoint's LSN, ensuring that the
subscribers would not lose any data because everything's already been
consumed by them when the publisher gets to be upgraded.

> The point raised by Jonathan for not having an option for pg_upgrade
> is that it will be easy for users, otherwise, users always need to
> enable this option. Consider a replication setup, wouldn't users want
> by default it to be upgraded? Asking them to do that via an option
> would be an inconvenience. So, that was the reason, we wanted to have
> an --exclude option and by default allow slots to be upgraded. I think
> the same theory applies here.
>=20
> [1] - https://www.postgresql.org/message-id/CAA4eK1LV3%2B76CSOAk0h8Kv0AKb=
-OETsJHe6Sq6172-7DZXf0Qg%40mail.gmail.com

I saw this thread, and have some thoughts to share.  Will reply there.
--
Michael

--nL+S3qo4L9YkUpDQ
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmT5btYACgkQnvQgOdby
QH0jPxAAh+GIVXwPoHIiXHS6ZC3wvF92y5QYLaYWLvLnS2M6bvaHlbagKIAS7mBa
iE3ILuec/7HEkVgRDgG8doPZFDGZGmkA5ajOtmirdMJgQu9qIt4ZfQBuSQCvYBmM
0qcmGVQIspABnz9xKfj6x5ICf4JD71cY/AZ7V96EVAal4N/Qk1UWo84ez7vLdXfn
5uUeh+gy+5Wdxwz0XFk7+0O+pT6ZTtx+ul0DHDsYju0NGxFLyvpgzvNYxjuOSyAZ
7VZMF2gRJ0i+H66CiPlh1ga3ivXy/FNHvDZtwTGSfWfIypLlJ7RMr9MDgz9Gu66c
IUwv+27EumEBzxp+T/LVCVLsuD2Ftr+BrD2rWUluw1qNU1tpg794TLhPnS2LlH1U
SWJdz/qPxJWMrdZIGRpGspEcjnjVzIaz4KfENZLGMrfplTDnxAqHksDBhoiBLGG5
arUOlCRmMxTNEtdRGB+YbjWe1729ITzbYSIJGy0O2ViOO7zJ1wbT0l0yYk/WJikA
9Wl0xGq5wG6xLS8rTThyFcrGjGfeFzYFCEI51mxeUfB5FbTZNEVHR/Tw9pNZzuWL
BC3SC92+WcJTgNSykqaBXRq23vSYCEhyB9R30OFt/oeCk3KDWJxOrIN8wZLMdWpS
zA6ZW+p20UwAGXGqdxmV4O6UtM6pXEcc9n45xzsCGQi5mAoj/yY=
=4C3d
-----END PGP SIGNATURE-----

--nL+S3qo4L9YkUpDQ--