public inbox for [email protected]
help / color / mirror / Atom feedFrom: Hayato Kuroda (Fujitsu) <[email protected]>
To: 'vignesh C' <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: RE: Include sequences in publications created by pg_createsubscriber
Date: Mon, 22 Jun 2026 12:39:47 +0000
Message-ID: <OS9PR01MB121494E86188F022BA604BCEAF5EF2@OS9PR01MB12149.jpnprd01.prod.outlook.com> (raw)
In-Reply-To: <CALDaNm2RYQRLjexRJOpiZOFkspp8PRgoh6S7d2y003TJ_TSSZA@mail.gmail.com>
References: <CALDaNm2RYQRLjexRJOpiZOFkspp8PRgoh6S7d2y003TJ_TSSZA@mail.gmail.com>
Dear Vignesh,
> Recently, commit 96b37849734673e7c82fb86c4f0a46a28f500ac8 added
> support for publishing ALL SEQUENCES, and commit
> 5509055d6956745532e65ab218e15b99d87d66ce introduced sequence
> synchronization support. These features can be leveraged by
> pg_createsubscriber to include sequences in the publication and
> synchronize their state.
> With sequence synchronization, the workflow becomes:
> Step 1) Run pg_createsubscriber to convert physical replication
> cluster to logical replication cluster:
> Step 2) Run pg_upgrade to upgrade Node-B to new PG19 version:
> Step 3) Stop connections/data changes in Node-A
> Step 4) Synchronize the incremental data changes made on Node-A during
> the upgrade process to Node-B.
> Step 5) Run ALTER SUBSCRIPTION ... REFRESH SEQUENCES to synchronize
> the state of all subscribed sequences.
> Step 6) Redirect Writes to Node-B after the catchup
> Step 7) Decommission Node-A
> Step 8) Create Standby node using pg_basebackup, Node-C from Node-B
IIUC, there is an alternative to run the below command after the step 1:
ALTER PUBLICATION ... SET FOR ALL TABLES, ALL SEQUENCES.
But your point is that there are no specific benefits not to include sequences
and to run the ALTER command by themselves, right?
Sounds reasonable for me.
Two questions:
1)
Do you think we should run ALTER SUBSCRIPTION REFRESH SEQUENCES command within
the command to synchronize sequences? It may need some additional checks since
publications specified by the user may not be the FOR ALL SEQUENCES.
2)
There was a proposal to allow the automatic synchronization of sequences [1].
What if both your proposal and [1] are pushed? Is there any down sides if the
sequencesnc worker exists during the convertion?
[1]: https://commitfest.postgresql.org/patch/6514/
Comments for the patch:
```
- does not copy the initial table data. It does only the synchronization phase,
- which ensures each table is brought up to a synchronized state.
+ does not copy the initial table or sequence data. It does only the
+ synchronization phase, which ensures each table and sequence is brought up
+ to a synchronized state.
```
I don't think it's correct, sequences is not synchronized during the command
for now.
```
- point.
+ point. The subscription is configured to include sequences. Sequence
+ values can be synchronized at any time by running
+ <link linkend="sql-altersubscription-params-refresh-sequences">
+ <command>ALTER SUBSCRIPTION ... REFRESH SEQUENCES</command></link>, which
+ fetches the current sequence state from the publisher and advances the
+ corresponding subscriber sequences.
```
I don't think the description should be added, because here just describes what
the pg_createsubscriber does.
Best regards,
Hayato Kuroda
FUJITSU LIMITED
view thread (23+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: RE: Include sequences in publications created by pg_createsubscriber
In-Reply-To: <OS9PR01MB121494E86188F022BA604BCEAF5EF2@OS9PR01MB12149.jpnprd01.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox