Horton

Re: pg_upgrade and logical replication

Julien Rouhaud <rjuju123@gmail.com>

View thread View on postgr.es
From: Julien Rouhaud <rjuju123@gmail.com>
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>, pgsql-hackers@lists.postgresql.org
Date: 2023-03-23T07:41:54Z
Lists: pgsql-hackers 
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.

Commits

  1. pg_upgrade: Retrieve subscription count more efficiently.

  2. Fix random failure in 004_subscription.

  3. Fix 004_subscription.pl to allow its usage in --link mode.

  4. Allow upgrades to preserve the full subscription's state.

  5. Prevent startup of logical replication launcher during pg_upgrade