Merging logical subscriptions

From: Michał Kłeczek <michal(at)kleczek(dot)org>
To: PG-General Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Merging logical subscriptions
Date: 2024-10-17 06:45:53
Message-ID: E9B39C33-3673-4FE3-8B3D-432088499A16@kleczek.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I have a question about an algorithm to “merge” two logical replication subscriptions. Here is the scenario:

We have a server with many tables replicated using logical replication to downstream replicas.
Each table is published with a separate publication.

At the moment each replica uses a single subscription with many publications.

The problem is that a subscription is a unit of concurrency in logical replication and sometimes we need to process replication streams in parallel.
“Splitting” subscriptions so that groups of publications are processed in parallel is (quite) easy:

1. Disable existing subscription
2. Clone replication slot
3. Create a new subscription with a subset of first subscription publications (don’t create a replication slot, use the replication slot created in step 2, don’t copy data)
4. Drop the subset of publications from the first subscription.
5. Enable and refresh the first subscription

But is there a procedure to “merge” subscriptions?

The problem I see is that there is no way to synchronise two subscriptions so that they stop at the same LSN.
We can skip transactions by advancing the LSN. But we cannot process transactions until a specific LSN.

Any ideas?

Thanks,
Michal

Browse pgsql-general by date

  From Date Subject
Next Message Koen De Groote 2024-10-17 09:07:19 Re: Logical replication disabled, recovery not possible because of 1 large transaction with schema changes?
Previous Message Asad Ali 2024-10-17 06:43:28 Re: Support for dates before 4713 BC