Re: pg_upgrade and logical replication

On Wed, Mar 01, 2023 at 07:56:47AM -0800, Nikolay Samokhvalov wrote:
> On Tue, Feb 28, 2023 at 4:43 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
> >
> > On Tue, Feb 28, 2023 at 08:02:13AM -0800, Nikolay Samokhvalov wrote:
> > > 0. Temporarily, forbid running any DDL on the source cluster.
> >
> > This is (at least for me) a non starter, as I want an approach that doesn't
> > impact the primary node, at least not too much.
> ...
> > Also, how exactly would you ensure that indeed DDL were forbidden since a long
> > enough point in time rather than just "currently" forbidden at the time you do
> > some check?
>
> Thanks for your response. I didn't expect that DDL part would attract
> attention, my message was not about DDL... – the DDL part was there
> just to show that the recipe I described is possible for any PG
> version that supports logical replication.

Well, yes but I already mentioned that in my original email as "dropping all
subscriptions and recreating them" is obviously the same as simply creating
them later.  I don't even think that preventing DDL is necessary.

One really important detail you forgot though is that you need to create the
subscription using "copy_data = false".  Not hard to do, but that's not the
default so it's yet another trap users can fall into when trying to do a major
version upgrade that can lead to a corrupted logical replica.