Re: pg_upgrade and logical replication

Hi,

On Fri, Apr 14, 2023 at 04:19:35AM +0000, Hayato Kuroda (Fujitsu) wrote:
>
> I have tested, but srsublsn became NULL if copy_data was specified as off.
> This is because when copy_data is false, all tuples in pg_subscription_rels are filled
> as state = 'r' and srsublsn = NULL, and tablesync workers will never boot.
> See CreateSubscription().
> Doesn't it mean that there is a possibility that LSN option is not specified while
> ALTER SUBSCRIPTION ADD TABLE?

It shouldn't be the case for now, as pg_upgrade will check first if there's a
invalid remote_lsn and refuse to proceed if that's the case. Also, the
remote_lsn should be set as soon as some data is replicated, so unless you add
a table that's never modified to a publication you should be able to run
pg_upgrade at some point, once there's replicated DML on such a table.

I'm personally fine with the current restrictions, but I don't really use
logical replication in any project so maybe I'm not objective enough. For now
I'd rather keep things as-is, and later improve on it if some people want to
lift such restrictions (and such restrictions can actually be lifted).