Re: Include sequences in publications created by pg_createsubscriber

On Mon, 22 Jun 2026 at 18:09, Hayato Kuroda (Fujitsu)
<kuroda(dot)hayato(at)fujitsu(dot)com> 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