Re: pg_upgrade and logical replication

Hi,

On Thu, Mar 23, 2023 at 04:27:28PM +0900, Masahiko Sawada wrote:
>
> I might be missing something but is there any reason why you created a
> subscription before pg_upgrade?
>
> Steps like doing pg_upgrade, then creating missing tables, and then
> creating a subscription (with copy_data = false) could be an
> alternative way to support upgrading the server from the physical
> standby?

As I already answered to Nikolay, and explained in my very first email, yes
it's possible to create the subscriptions after running pg_upgrade. I
personally prefer to do it first to make sure that the logical replication is
actually functional, so I can still easily do a pg_rewind or something to fix
things without having to trash the newly built (and promoted) replica.

But that exact scenario is a corner case, as in any other scenario pg_upgrade
leaves the subscription in an unrecoverable state, where you have to truncate
all the underlying tables first and start from scratch doing an initial sync.
This kind of defeats the purpose of pg_upgrade.