Re: pg_upgrade and logical replication

On Tue, Feb 28, 2023 at 08:02:13AM -0800, Nikolay Samokhvalov wrote:
> On Fri, Feb 17, 2023 at 7:35 AM Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
> >
> > Any table later added in the
> > publication is either already fully replicated until that LSN on the upgraded
> > node, so only the delta is needed, or has been created after that LSN. In the
> > latter case, the entirety of the table will be replicated with the logical
> > replication as a delta right?
>
> What if we consider a slightly adjusted procedure?
>
> 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 would you do that? If you need some new infrastructure it means that
you can only upgrade nodes starting from pg16+, while my approach can upgrade
any node that supports publications as long as the target version is pg16+.

It also raises some concerns: why prevent any DDL while e.g. creating a
temporary table shouldn't not be a problem, same for renaming some underlying
object, adding indexes... You would have to curate a list of what exactly is
allowed which is never great.

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?