On Mon, 22 Jun 2026 at 18:09, Hayato Kuroda (Fujitsu) <[email protected]> wrote: > > 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.
My point is that there does not seem to be a strong benefit in requiring users to perform that additional manual step themselves. Since pg_createsubscriber already creates the publication and subscription as part of the conversion process, it seems reasonable to include sequences in the publication automatically as well. > 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. I considered doing that while implementing the feature, but decided against it. The reason is that users will still need to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION (or REFRESH SEQUENCES) after Step 3 to synchronize any sequence changes that occurred between the initial conversion and the completion of the upgrade. Therefore, even if we perform a sequence refresh automatically during Step 1 when converting the physical replication cluster to a logical replication cluster, the user would still need to repeat the operation later to capture incremental sequence changes. > 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/ I think the automatic sequence synchronization would only occur after the publication has been updated to include sequences. Also, during the conversion process, the subscriptions are not enabled until the very end. As a result, the sequence synchronization worker would not be running during the conversion itself and would only start once the subscriptions are enabled. Given that, I don't see any downside if both proposals are committed. The sequence synchronization worker should not interfere with the conversion process, as it would only become active after the conversion has completed and the subscription is enabled. > > 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. I think the wording is still correct. pg_createsubscriber does not perform a separate sequence synchronization step, but it does bring sequences to a synchronized state as part of the physical-to-logical transition. The command obtains a consistent_lsn from CREATE_LOGICAL_REPLICATION_SLOT, sets recovery_target_lsn to that value, and allows recovery to proceed up to that LSN before enabling the subscriptions. Since the standby is recovered to the same consistent point, both table data and sequence state are synchronized at that point. What is not synchronized are sequence changes that occur after that LSN. Those require the additional sequence synchronization step after the upgrade. So while pg_createsubscriber does not explicitly copy or synchronize sequences via logical replication, it does ensure that the sequence state is synchronized up to the chosen consistent point. > ``` > - 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. > I felt it would be useful to mention it here so that users do not assume sequences behave the same way as tables. For tables, once the subscription is enabled, subsequent changes are replicated automatically. Sequences are different, as their state needs to be synchronized explicitly. Without some mention here, users might assume that including sequences in the subscription is sufficient and that they will stay synchronized automatically. That said, I can see the argument that this section should focus strictly on what pg_createsubscriber does. If you feel the sequence synchronization behavior is already documented sufficiently elsewhere, we can remove this text. My concern was mainly to make users aware of this behavioral difference at the point where sequence support is introduced in the workflow. Regards, Vignesh
