public inbox for [email protected]
help / color / mirror / Atom feedpg_upgrade vs. logical replication
2+ messages / 2 participants
[nested] [flat]
* pg_upgrade vs. logical replication
@ 2024-12-09 11:42 Joe Wildish <[email protected]>
2024-12-09 13:06 ` Re: pg_upgrade vs. logical replication Greg Sabino Mullane <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Joe Wildish @ 2024-12-09 11:42 UTC (permalink / raw)
To: [email protected]
We maintain c.50 logical replicas. Typically the producer version is 12 or 13,
and the subscriber version is 14. We intend to upgrade the subscribers to 15
using pg_upgrade. However, we ran into an unexpected problem with that
approach. I couldn't find much being mentioned about it on the web, so I'm
sending this message for a sanity check that what we are seeing is expected,
and, that the proposed solution make sense.
The problem we see is that after running pg_upgrade on the subscriber, details
about the subscription are lost; specifically, pg_subscription_rel is empty, and
the associated replication origin has no LSN information in
pg_replication_origin_status.
I found a thread on the hackers list that seems to call out this problem:
https://www.postgresql.org/message-id/flat/20230217075433.u5mjly4d5cr4hcfe%40jrouhaud
It is a very long thread. TLDR; Julien describes a similar problem to what we
have (although in our case, it is a "pure" logical replica with no consideration
about switching between physical replication, or needing to cut over, etc), and
a fix has gone in to PG17 that sorts this problem.
However, we can't go to 17 yet, so need a solution for 15 and 16. We are doing
the following sequence of events:
(1) Shutdown subscriber;
(2) Startup subscriber with max_logical_replication_workers set to 0;
(3) Copy the data in pg_replication_origin_status to a table;
(4) Shutdown subscriber;
(5) Run pg_upgrade;
(6) Startup subscriber with max_logical_replication_workers set to 0;
(7) Apply the LSNs recorded in (3) to the appropriate origin using
pg_replication_origin_advance;
(8) ENABLE, REFRESH PUBLICATION WITH (COPY_DATA=FALSE), & DISABLE the
subscription;
(9) Shutdown subscriber;
(10) Startup subscriber normally;
(11) ENABLE subscription.
Step 7 requires a mapping from old origin name to new origin name as the names
are derived from subscription OIDs. Steps 2 and 6 are required to ensure no
activity occurs that could advance LSNs.
Unlike in the mentioned thread, we know we won't have to deal with tables that
are partially replicated i.e we know all tables will be 'r'.
We have done some testing with this and it seems to work. We run a repeated
INSERT on the producer several times a second, and notice that after step 11,
we have all expected rows arrive on the subscriber without gaps. However, I
thought I'd ping this list for a sanity check that what we are doing makes
sense. And, if so, perhaps it'll be useful to other people in a similar
situation.
Comments / suggestions welcome.
-Joe
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: pg_upgrade vs. logical replication
2024-12-09 11:42 pg_upgrade vs. logical replication Joe Wildish <[email protected]>
@ 2024-12-09 13:06 ` Greg Sabino Mullane <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Greg Sabino Mullane @ 2024-12-09 13:06 UTC (permalink / raw)
To: Joe Wildish <[email protected]>; +Cc: [email protected]
On Mon, Dec 9, 2024 at 6:43 AM Joe Wildish <[email protected]> wrote:
Overall, your solution seems okay, but:
> a fix has gone in to PG17 that sorts this problem.
>
> However, we can't go to 17 yet, so need a solution for 15 and 16.
Honestly, this would seem like a really, really strong reason to push for
v17.
Cheers,
Greg
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-12-09 13:06 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-12-09 11:42 pg_upgrade vs. logical replication Joe Wildish <[email protected]>
2024-12-09 13:06 ` Greg Sabino Mullane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox