Re: pg_upgrade and logical replication

On Tue, Feb 28, 2023 at 4:43 PM Julien Rouhaud <rjuju123(at)gmail(dot)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.

Usually, people perform upgrades involving logical using full
initialization at logical level – at least all posts and articles I
could talk about that. Meanwhile, on one hand, for large DBs, logical
copying is hard (slow, holding xmin horizon, etc.), and on the other
hand, physical replica can be transformed to logical (using the trick
with recover_target_lsn, syncing the state with the slot's LSN) and
initialization at physical level works much better for large
databases. But there is a problem with logical replication when we run
pg_upgrade – as discussed in this thread. So I just wanted to mention
that if we change the order of actions and first run pg_upgrade, and
only then create publication, there should not be a problem anymore.