Horton

RE: pg_upgrade and logical replication

Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>

View thread View on postgr.es
From: "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>
To: 'Justin Pryzby' <pryzby@telsasoft.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>, vignesh C <vignesh21@gmail.com>, Masahiko Sawada <sawada.mshk@gmail.com>, Michael Paquier <michael@paquier.xyz>, Peter Smith <smithpb2250@gmail.com>, Julien Rouhaud <rjuju123@gmail.com>, "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Date: 2024-02-16T03:26:06Z
Lists: pgsql-hackers 
Dear Justin,

Thanks for replying!

> What optimizations?  I can't see them, and since the patch is described
> as rearranging test cases (and therefore already difficult to read), I
> guess they should be a separate patch, or the optimizations described.

The basic idea was to reduce number of CREATE/DROP statement,
but it was changed for now - publications and subscriptions were created and
dropped per testcases. 

E.g., In case of successful upgrade, below steps were done:

1. create two publications
2. create a subscription with failover = true
3. avoid further initial sync by setting max_logical_replication_workers = 0
4. create another subscription
5. confirm statuses of tables are either of 'i' or 'r'
6. run pg_upgrade
7. confirm table statuses are preserved
8. confirm replication origins are preserved.

New patch is available in [1].

[1]: https://www.postgresql.org/message-id/TYCPR01MB12077B16EEDA360BA645B96F8F54C2%40TYCPR01MB12077.jpnprd01.prod.outlook.com

Best Regards,
Hayato Kuroda
FUJITSU LIMITED
https://www.fujitsu.com/

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