Horton

Thread

Commits

  1. Add sequence synchronization for logical replication.

  2. Add "ALL SEQUENCES" support to publications.

  1. Include sequences in publications created by pg_createsubscriber

    vignesh C <vignesh21@gmail.com> — 2026-06-19T13:59:03Z

    Hi,

pg_createsubscriber is primarily used to facilitate major-version
upgrades of physical replication clusters by converting a physical
standby into a logical subscriber.
The current upgrade workflow is:
Step 1) Run pg_createsubscriber to convert physical replication
cluster to logical replication cluster:
Step 2) Run pg_upgrade to upgrade Node-B to the 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) Redirect Writes to Node-B after the catchup
Step 6) Decommission Node-A
Step 7) Create Standby node using pg_basebackup, Node-C from Node-B

However, there is a gap in this workflow: while table data is kept
synchronized through logical replication, sequence state is not. As a
result, sequence values on the upgraded node can lag behind the
publisher and must be synchronized manually.

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

Compared to the earlier upgrade workflow, the only additional
requirement is Step 5, which synchronizes sequence state between the
publisher and subscriber.

The attached patch modifies pg_createsubscriber to include sequences
in the publication it creates, enabling this workflow.
Thoughts?

Regards,
Vignesh

  2. RE: Include sequences in publications created by pg_createsubscriber

    Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com> — 2026-06-22T12:39:47Z

    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

  3. Re: Include sequences in publications created by pg_createsubscriber

    Peter Smith <smithpb2250@gmail.com> — 2026-06-23T01:03:23Z

    On Sat, Jun 20, 2026 at 1:59 AM vignesh C <vignesh21@gmail.com> wrote:
...
> The attached patch modifies pg_createsubscriber to include sequences
> in the publication it creates, enabling this workflow.
> Thoughts?
>

Hi Vignesh,

+1 to make this change.

Some review comments for patch 0001.

======
Commit Message

1.
This change modifies pg_createsubscriber to include all sequences in the
publication it creates. Administrators can then run ALTER SUBSCRIPTION
... REFRESH SEQUENCES after the upgrade to synchronize sequence state,
ensuring a more complete and reliable upgrade workflow.

~

/include all sequences/include ALL SEQUENCES/

======
doc/src/sgml/ref/pg_createsubscriber.sgml

2.
-   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.
   </para>

I felt "does not copy the initial [...] sequence data" should not be
said for sequences. AFAIK each sequence has only 1 value anyway, so
bringing it "to a synchronized state" is effectively the same thing as
copying the initial data for sequences, so saying in the first
sentence that copy does not happen seems a contradiction.

IIUC what above means is really just the same as chapter 29.7, which
says "use CREATE SUBSCRIPTION to initially synchronize the published
sequences.".

~~~

3.
       linkend="sql-createpublication-params-for-all-tables"><literal>FOR ALL
-      TABLES</literal></link>.  If the <option>--publication</option> option
+      TABLES</literal></link> and <link
+      linkend="sql-createpublication-params-for-all-sequences"><literal>FOR ALL
+      SEQUENCES</literal></link>.  If the <option>--publication</option> option
       is not specified, the publication has the following name pattern:

Maybe just say "ALL SEQUENCES" here instead of "FOR ALL SEQUENCES"?

~~~

4.
      <para>
       Enable the subscription for each specified database on the target server.
       The subscription starts applying transactions from the replication start
-      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.
      </para>

IIUC, all this "How it works" section is describing the internal logic
of what pg_subscriber is internally. OTOH, this "REFRESH SEQUENCES" is
just a note about what the Administrator can do manually later...

So, maybe you can still write this note somewhere, but just don't
include it buried in the internal "How it works" steps.

======
src/bin/pg_basebackup/pg_createsubscriber.c

5.
Patch needs also to update the function comment for
create_publication() because currently it still says "includes all
tables".

======
Kind Regards,
Peter Smith.
Fujitsu Australia

  4. Re: Include sequences in publications created by pg_createsubscriber

    vignesh C <vignesh21@gmail.com> — 2026-06-23T10:19:10Z

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

  5. Re: Include sequences in publications created by pg_createsubscriber

    vignesh C <vignesh21@gmail.com> — 2026-06-24T07:30:25Z

    On Tue, 23 Jun 2026 at 06:33, Peter Smith <smithpb2250@gmail.com> wrote:
>
> On Sat, Jun 20, 2026 at 1:59 AM vignesh C <vignesh21@gmail.com> wrote:
> ...
> > The attached patch modifies pg_createsubscriber to include sequences
> > in the publication it creates, enabling this workflow.
> > Thoughts?
> >
>
> Hi Vignesh,
>
> +1 to make this change.
>
> Some review comments for patch 0001.
>
> ======
> Commit Message
>
> 1.
> This change modifies pg_createsubscriber to include all sequences in the
> publication it creates. Administrators can then run ALTER SUBSCRIPTION
> ... REFRESH SEQUENCES after the upgrade to synchronize sequence state,
> ensuring a more complete and reliable upgrade workflow.
>
> ~
>
> /include all sequences/include ALL SEQUENCES/

Modified

> ======
> doc/src/sgml/ref/pg_createsubscriber.sgml
>
> 2.
> -   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.
>    </para>
>
> I felt "does not copy the initial [...] sequence data" should not be
> said for sequences. AFAIK each sequence has only 1 value anyway, so
> bringing it "to a synchronized state" is effectively the same thing as
> copying the initial data for sequences, so saying in the first
> sentence that copy does not happen seems a contradiction.
>
> IIUC what above means is really just the same as chapter 29.7, which
> says "use CREATE SUBSCRIPTION to initially synchronize the published
> sequences.".

pg_createsubscriber does the following:
1) We get the consistent_lsn from create_logical_replication_slot.
2) Then we set recovery_target_lsn = consistent_lsn from above
3) Then advance replication origin of each subscription to this lsn
4) Then enable subscription
5) stop standby server

Here setting recovery_target_lsn to consistent_lsn and recovering it
till there. I mean sequences are brought to sync state.
Create subscription will not do any data copy in case of pg_createsubscriber.

I meant the above here, let me know if you have better wording for this.

> ~~~
>
> 3.
>        linkend="sql-createpublication-params-for-all-tables"><literal>FOR ALL
> -      TABLES</literal></link>.  If the <option>--publication</option> option
> +      TABLES</literal></link> and <link
> +      linkend="sql-createpublication-params-for-all-sequences"><literal>FOR ALL
> +      SEQUENCES</literal></link>.  If the <option>--publication</option> option
>        is not specified, the publication has the following name pattern:
>
> Maybe just say "ALL SEQUENCES" here instead of "FOR ALL SEQUENCES"?

Modified

> ~~~
>
> 4.
>       <para>
>        Enable the subscription for each specified database on the target server.
>        The subscription starts applying transactions from the replication start
> -      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.
>       </para>
>
> IIUC, all this "How it works" section is describing the internal logic
> of what pg_subscriber is internally. OTOH, this "REFRESH SEQUENCES" is
> just a note about what the Administrator can do manually later...
>
> So, maybe you can still write this note somewhere, but just don't
> include it buried in the internal "How it works" steps.

Moved it along with description

> ======
> src/bin/pg_basebackup/pg_createsubscriber.c
>
> 5.
> Patch needs also to update the function comment for
> create_publication() because currently it still says "includes all
> tables".

Modified

The attached v2 version patch has the changes for the same.

Regards,
Vignesh

  6. Re: Include sequences in publications created by pg_createsubscriber

    Peter Smith <smithpb2250@gmail.com> — 2026-06-25T00:58:57Z

    Hi Vignesh.

Some comments for v2.

======
doc/src/sgml/ref/pg_createsubscriber.sgml

1.
    fresh logical replication setup.  The main difference between the logical
    replication setup and <application>pg_createsubscriber</application> is how
    the data synchronization is done.
<application>pg_createsubscriber</application>
-   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.

1a.
OK. Thanks for the explanation on what was meant. Personally, I found
the wording "sequence is brought up to a synchronized state"
ambiguous, because in my mind the sequence value is a part of the
sequence state.

Below is some alternate wording for consideration:

SUGGESTION:
pg_createsubscriber performs only the synchronization phase, which
prepares each table and sequence for replication. It does not copy
table data or synchronize sequence values from the publisher.

~~~

1b.
While reviewing this patch I also referred to section "29.7.
Replicating Sequences #". [1] for more information. But there I was
tricked by some (misleading?) wording.
I have created another thread [2] to discuss/patch that. Please see it
and post your thoughts.

~~~

2.
+  <para>
+   Note that although the publication includes sequences, sequence value
+   changes made on the publisher after reaching consistent point are not
+   synchronized incrementally. To synchronize sequence values, run
+   <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.
   </para>

I wonder if the last sentence is overly descriptive. Isn't it enough
to just say:
To synchronize sequence values, run <link
linkend="sql-altersubscription-params-refresh-sequences"><command>ALTER
SUBSCRIPTION ... REFRESH SEQUENCES</command></link>.

~~~

3.
      <para>
       Enable the subscription for each specified database on the target server.
       The subscription starts applying transactions from the replication start
-      point.
+      point. The subscription is configured to include sequences.
      </para>

This implies there was some special subscription "configuration" for
getting sequences, but it just gets sequences because sequences were
published. Maybe you don't need to say anything here. Or, maybe reword
to just say "The subscription will include sequences".

======
[1] https://www.postgresql.org/docs/19/logical-replication-sequences.html#LOGICAL-REPLICATION-SEQUENCES
[2] https://www.postgresql.org/message-id/CAHut%2BPsrVntfrJ8gV45%2B6%3D1bs5dTWD5mU7Q6aKrBnf1vmyZQkQ%40mail.gmail.com

Kind Regards,
Peter Smith.
Fujitsu Australia

  7. Re: Include sequences in publications created by pg_createsubscriber

    Ashutosh Sharma <ashu.coek88@gmail.com> — 2026-06-25T05:35:06Z

    Hi,

On Thu, Jun 25, 2026 at 6:29 AM Peter Smith <smithpb2250@gmail.com> wrote:
>
> 3.
>       <para>
>        Enable the subscription for each specified database on the target server.
>        The subscription starts applying transactions from the replication start
> -      point.
> +      point. The subscription is configured to include sequences.
>       </para>
>
> This implies there was some special subscription "configuration" for
> getting sequences, but it just gets sequences because sequences were
> published. Maybe you don't need to say anything here. Or, maybe reword
> to just say "The subscription will include sequences".
>

What if the subscription uses an existing publication that doesn't
include sequences? In that case, would it be correct to say that the
subscription will include sequences?

--
With Regards,
Ashutosh Sharma.

  8. Re: Include sequences in publications created by pg_createsubscriber

    Peter Smith <smithpb2250@gmail.com> — 2026-06-25T05:57:46Z

    On Thu, Jun 25, 2026 at 5:35 PM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
>
> Hi,
>
> On Thu, Jun 25, 2026 at 6:29 AM Peter Smith <smithpb2250@gmail.com> wrote:
> >
> > 3.
> >       <para>
> >        Enable the subscription for each specified database on the target server.
> >        The subscription starts applying transactions from the replication start
> > -      point.
> > +      point. The subscription is configured to include sequences.
> >       </para>
> >
> > This implies there was some special subscription "configuration" for
> > getting sequences, but it just gets sequences because sequences were
> > published. Maybe you don't need to say anything here. Or, maybe reword
> > to just say "The subscription will include sequences".
> >
>
> What if the subscription uses an existing publication that doesn't
> include sequences? In that case, would it be correct to say that the
> subscription will include sequences?
>

Oops. Fair point. Looks like the original patch text suffered similar issues.

A more descriptive explanation seems needed for this to talk about the
differences of internal publications versus user-supplied ones....
e.g. a user-publication may even be only FOR ALL SEQUENCES and not
even have tables in them... then what?

======
Kind Regards,
Peter Smith.
Fujitsu Australia.

  9. Re: Include sequences in publications created by pg_createsubscriber

    vignesh C <vignesh21@gmail.com> — 2026-06-25T10:00:20Z

    On Thu, 25 Jun 2026 at 06:29, Peter Smith <smithpb2250@gmail.com> wrote:
>
> Hi Vignesh.
>
> Some comments for v2.
>
> ======
> doc/src/sgml/ref/pg_createsubscriber.sgml
>
> 1.
>     fresh logical replication setup.  The main difference between the logical
>     replication setup and <application>pg_createsubscriber</application> is how
>     the data synchronization is done.
> <application>pg_createsubscriber</application>
> -   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.
>
> 1a.
> OK. Thanks for the explanation on what was meant. Personally, I found
> the wording "sequence is brought up to a synchronized state"
> ambiguous, because in my mind the sequence value is a part of the
> sequence state.
>
> Below is some alternate wording for consideration:
>
> SUGGESTION:
> pg_createsubscriber performs only the synchronization phase, which
> prepares each table and sequence for replication. It does not copy
> table data or synchronize sequence values from the publisher.

I have separated the table and sequence part to avoid confusion.

> ~~~
>
> 1b.
> While reviewing this patch I also referred to section "29.7.
> Replicating Sequences #". [1] for more information. But there I was
> tricked by some (misleading?) wording.
> I have created another thread [2] to discuss/patch that. Please see it
> and post your thoughts.

Thanks for working on this, I have shared a couple of comments at [1].

> ~~~
>
> 2.
> +  <para>
> +   Note that although the publication includes sequences, sequence value
> +   changes made on the publisher after reaching consistent point are not
> +   synchronized incrementally. To synchronize sequence values, run
> +   <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.
>    </para>
>
> I wonder if the last sentence is overly descriptive. Isn't it enough
> to just say:
> To synchronize sequence values, run <link
> linkend="sql-altersubscription-params-refresh-sequences"><command>ALTER
> SUBSCRIPTION ... REFRESH SEQUENCES</command></link>.

Modified

> ~~~
>
> 3.
>       <para>
>        Enable the subscription for each specified database on the target server.
>        The subscription starts applying transactions from the replication start
> -      point.
> +      point. The subscription is configured to include sequences.
>       </para>
>
> This implies there was some special subscription "configuration" for
> getting sequences, but it just gets sequences because sequences were
> published. Maybe you don't need to say anything here. Or, maybe reword
> to just say "The subscription will include sequences".

I have removed it from here and clarified this in step-3.

The attached v3 version patch has the changes for the same.

[1] - https://www.postgresql.org/message-id/CALDaNm1xY_Js5-jppiBzGq2HMqzCHk%2BLLpupfWp_cFcG%3DvJXmg%40mail.gmail.com

Regards,
Vignesh

  10. Re: Include sequences in publications created by pg_createsubscriber

    Peter Smith <smithpb2250@gmail.com> — 2026-06-25T22:19:38Z

    Some review comments for v3.

======
doc/src/sgml/ref/pg_createsubscriber.sgml

1.
   <para>
    <application>pg_createsubscriber</application> creates a new logical
    replica from a physical standby server.  All tables in the specified
-   database are included in the <link linkend="logical-replication">logical
-   replication</link> setup.  A pair of
-   publication and subscription objects are created for each database.  It
-   must be run at the target server.
+   database are included in the
+   <link linkend="logical-replication">logical replication</link> setup. The
+   publications created by <application>pg_createsubscriber</application> also
+   include all sequences, allowing their state to be synchronized after the
+   upgrade. A pair of publication and subscription objects are created for each
+   database. It must be run at the target server.
   </para>

This is not properly distinguishing between the internal publications
versus user-nominated publications. I think it needs to clarify
up-front that all of this "Description" part is really only talking
about the (default) behaviour of using the *internally* generated FOR
ALL TABLES, ALL SEQUENCE publications.

e.g. "All tables in the specified database are included" ... that
might not be true for user pubs.
e.g. "A pair of publication and subscription objects are created" ...
not for user pubs; they already exist.

~~~

2.
    replication setup and <application>pg_createsubscriber</application> is how
    the data synchronization is done.
<application>pg_createsubscriber</application>
    does not copy the initial table data. It does only the
synchronization phase,
-   which ensures each table is brought up to a synchronized state.
+   which ensures each table is brought up to a synchronized state. The
+   subscriptions are configured to include sequences, but sequence values are
+   not synchronized automatically.

Similar here.

"The subscriptions are configured to include sequences" may not be
true for the case of user-specified pubs. Also, (from my previous
review), subscriptions are not really "configured" to include
sequences -- if they subscribe to sequences, that is only because
their associated publication is publishing sequences.

======
Kind Regards,
Peter Smith.
Fujitsu Australia

  11. Re: Include sequences in publications created by pg_createsubscriber

    vignesh C <vignesh21@gmail.com> — 2026-06-29T08:49:06Z

    On Fri, 26 Jun 2026 at 03:50, Peter Smith <smithpb2250@gmail.com> wrote:
>
> Some review comments for v3.
>
> ======
> doc/src/sgml/ref/pg_createsubscriber.sgml
>
> 1.
>    <para>
>     <application>pg_createsubscriber</application> creates a new logical
>     replica from a physical standby server.  All tables in the specified
> -   database are included in the <link linkend="logical-replication">logical
> -   replication</link> setup.  A pair of
> -   publication and subscription objects are created for each database.  It
> -   must be run at the target server.
> +   database are included in the
> +   <link linkend="logical-replication">logical replication</link> setup. The
> +   publications created by <application>pg_createsubscriber</application> also
> +   include all sequences, allowing their state to be synchronized after the
> +   upgrade. A pair of publication and subscription objects are created for each
> +   database. It must be run at the target server.
>    </para>
>
> This is not properly distinguishing between the internal publications
> versus user-nominated publications. I think it needs to clarify
> up-front that all of this "Description" part is really only talking
> about the (default) behaviour of using the *internally* generated FOR
> ALL TABLES, ALL SEQUENCE publications.
>
> e.g. "All tables in the specified database are included" ... that
> might not be true for user pubs.
> e.g. "A pair of publication and subscription objects are created" ...
> not for user pubs; they already exist.
>
> ~~~
>
> 2.
>     replication setup and <application>pg_createsubscriber</application> is how
>     the data synchronization is done.
> <application>pg_createsubscriber</application>
>     does not copy the initial table data. It does only the
> synchronization phase,
> -   which ensures each table is brought up to a synchronized state.
> +   which ensures each table is brought up to a synchronized state. The
> +   subscriptions are configured to include sequences, but sequence values are
> +   not synchronized automatically.
>
> Similar here.
>
> "The subscriptions are configured to include sequences" may not be
> true for the case of user-specified pubs. Also, (from my previous
> review), subscriptions are not really "configured" to include
> sequences -- if they subscribe to sequences, that is only because
> their associated publication is publishing sequences.
>

Thanks for the comments, here is an updated version with the fixes for the same.

Regards,
Vignesh

  12. Re: Include sequences in publications created by pg_createsubscriber

    Shlok Kyal <shlok.kyal.oss@gmail.com> — 2026-06-29T14:36:52Z

    >
> Thanks for the comments, here is an updated version with the fixes for the same.
>
Hi Vignesh,

I reviewed the patch, here are some comments:

1. In pg_createsubscriber.sgml:
+   replica from a physical standby server.  By default,
+   <application>pg_createsubscriber</application> configures
+   <link linkend="logical-replication">logical replication</link> by
+   automatically creating internal publication and subscription objects. In
+   this default mode, a pair of publication and subscription objects are
+   created for each database, and all tables and sequences in the specified
+   databases. By default, a pair of publication and subscription objects are
+   created for each database. It must be run at the target server.

1a. Line "a pair of publication and subscription objects are created
for each database"
is repeated twice.

1b. This line looks like something is missed:
```
In this default mode, a pair of publication and subscription objects are
created for each database, and all tables and sequences in the specified
```
maybe we can rewrite it as:
```
In this default mode, a pair of publication and subscription objects are created
for each database, publishing all tables and sequences in the
specified database.
```

2. '--publication' should be inside <option></option> tag.
+      the source server.  If the --publication option is not specified,
+      <application>pg_createsubscriber</application> creates a publication
+      using <link linkend="sql-createpublication-params-for-all-tables">

3. For consistency, perhaps use "user-specified publications" here, as the later
 documentation already refers to them using the same term.
+   For user-nominated publications, sequences are only replicated if they were

4. In 040_pg_createsubscriber.pl:
+is( $result, qq(t|t),
+   "publication is created with both all-tables and all-sequences enabled"
+);
extra space before $result. I think we should run pg_perltidy on the test file.

Thanks,
Shlok Kyal

  13. Re: Include sequences in publications created by pg_createsubscriber

    vignesh C <vignesh21@gmail.com> — 2026-06-30T06:47:26Z

    On Mon, 29 Jun 2026 at 20:07, Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
>
> >
> > Thanks for the comments, here is an updated version with the fixes for the same.
> >
> Hi Vignesh,
>
> I reviewed the patch, here are some comments:
>
> 1. In pg_createsubscriber.sgml:
> +   replica from a physical standby server.  By default,
> +   <application>pg_createsubscriber</application> configures
> +   <link linkend="logical-replication">logical replication</link> by
> +   automatically creating internal publication and subscription objects. In
> +   this default mode, a pair of publication and subscription objects are
> +   created for each database, and all tables and sequences in the specified
> +   databases. By default, a pair of publication and subscription objects are
> +   created for each database. It must be run at the target server.
>
> 1a. Line "a pair of publication and subscription objects are created
> for each database"
> is repeated twice.
>
> 1b. This line looks like something is missed:
> ```
> In this default mode, a pair of publication and subscription objects are
> created for each database, and all tables and sequences in the specified
> ```
> maybe we can rewrite it as:
> ```
> In this default mode, a pair of publication and subscription objects are created
> for each database, publishing all tables and sequences in the
> specified database.
> ```
>
> 2. '--publication' should be inside <option></option> tag.
> +      the source server.  If the --publication option is not specified,
> +      <application>pg_createsubscriber</application> creates a publication
> +      using <link linkend="sql-createpublication-params-for-all-tables">
>
> 3. For consistency, perhaps use "user-specified publications" here, as the later
>  documentation already refers to them using the same term.
> +   For user-nominated publications, sequences are only replicated if they were
>
> 4. In 040_pg_createsubscriber.pl:
> +is( $result, qq(t|t),
> +   "publication is created with both all-tables and all-sequences enabled"
> +);
> extra space before $result. I think we should run pg_perltidy on the test file.

Thanks for the comments, Shlok. The attached v5 version patch has the
changes for the reported comments.

Regards,
Vignesh

  14. Re: Include sequences in publications created by pg_createsubscriber

    Peter Smith <smithpb2250@gmail.com> — 2026-07-01T02:46:39Z

    A review comment for patch v5-0001

======
doc/src/sgml/ref/pg_createsubscriber.sgml

1.
+   replica from a physical standby server.  By default,
+   <application>pg_createsubscriber</application> configures
+   <link linkend="logical-replication">logical replication</link> by
+   automatically creating internal publication and subscription objects. In
+   this mode, a publication and subscription are created for each database, and
+   the publications include all tables and sequences in their respective
+   databases. When user-specified publications are used, the subscriptions
+   replicate only the objects included in those publications. It must be run at
+   the target server.

In that last sentence, I felt it might be better to avoid saying "It"
which could be misunderstood as referring to something other than the
tool itself.

BEFORE
It must be run at the target server.
SUGGESTION
<application>pg_createsubscriber</application> must be run at the target server.

======
Kind Regards,
Peter Smith.
Fujitsu Australia

  15. Re: Include sequences in publications created by pg_createsubscriber

    vignesh C <vignesh21@gmail.com> — 2026-07-01T07:21:14Z

    On Wed, 1 Jul 2026 at 08:17, Peter Smith <smithpb2250@gmail.com> wrote:
>
> A review comment for patch v5-0001
>
> ======
> doc/src/sgml/ref/pg_createsubscriber.sgml
>
> 1.
> +   replica from a physical standby server.  By default,
> +   <application>pg_createsubscriber</application> configures
> +   <link linkend="logical-replication">logical replication</link> by
> +   automatically creating internal publication and subscription objects. In
> +   this mode, a publication and subscription are created for each database, and
> +   the publications include all tables and sequences in their respective
> +   databases. When user-specified publications are used, the subscriptions
> +   replicate only the objects included in those publications. It must be run at
> +   the target server.
>
> In that last sentence, I felt it might be better to avoid saying "It"
> which could be misunderstood as referring to something other than the
> tool itself.
>
> BEFORE
> It must be run at the target server.
> SUGGESTION
> <application>pg_createsubscriber</application> must be run at the target server.

The attached v6 version patch has the changes for the same.

Regards,
Vignesh

  16. Re: Include sequences in publications created by pg_createsubscriber

    Peter Smith <smithpb2250@gmail.com> — 2026-07-01T21:25:36Z

    Patch v6 LGTM.

======
Kind Regards,
Peter Smith.
Fujitsu Australia

  17. Re: Include sequences in publications created by pg_createsubscriber

    Shlok Kyal <shlok.kyal.oss@gmail.com> — 2026-07-02T06:29:45Z

    >
> The attached v6 version patch has the changes for the same.
>
Hi Vignesh,

I reviewed the v6 patch and tested it manually for some scenarios. It
is working fine.
And the patch mostly looks good to me.

A minor comment:
pg_perltidy run is required on the 040_pg_createsubscriber.pl patch.

Thanks,
Shlok Kyal

  18. Re: Include sequences in publications created by pg_createsubscriber

    Shlok Kyal <shlok.kyal.oss@gmail.com> — 2026-07-06T08:00:41Z

    On Thu, 2 Jul 2026 at 11:59, Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
>
> >
> > The attached v6 version patch has the changes for the same.
> >
> Hi Vignesh,
>
> I reviewed the v6 patch and tested it manually for some scenarios. It
> is working fine.
> And the patch mostly looks good to me.
>
> A minor comment:
> pg_perltidy run is required on the 040_pg_createsubscriber.pl patch.
>
I ran pg_perltidy on the  040_pg_createsubscriber.pl file and attached
the updated v7 version of patch.

Thanks
Shlok Kyal

  19. Re: Include sequences in publications created by pg_createsubscriber

    Ashutosh Sharma <ashu.coek88@gmail.com> — 2026-07-06T10:57:13Z

    Hi,

On Mon, Jul 6, 2026 at 1:31 PM Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
>
> On Thu, 2 Jul 2026 at 11:59, Shlok Kyal <shlok.kyal.oss@gmail.com> wrote:
> >
> > >
> > > The attached v6 version patch has the changes for the same.
> > >
> > Hi Vignesh,
> >
> > I reviewed the v6 patch and tested it manually for some scenarios. It
> > is working fine.
> > And the patch mostly looks good to me.
> >
> > A minor comment:
> > pg_perltidy run is required on the 040_pg_createsubscriber.pl patch.
> >
> I ran pg_perltidy on the  040_pg_createsubscriber.pl file and attached
> the updated v7 version of patch.
>

I have one small comment from the latest patch and here it is:

+# Verify that the publication is configured to publish all tables and
+# all sequences.

This sounds a bit ambiguous. Do we actually publish sequences in the
same sense as table changes? Other comments/docs describe sequences as
being included in a publication for synchronization, rather than being
published like tables. Maybe this should say something like:

"Verify that the publication is configured to include all tables and
all sequences."

--
With Regards,
Ashutosh Sharma.

  20. Re: Include sequences in publications created by pg_createsubscriber

    Peter Smith <smithpb2250@gmail.com> — 2026-07-07T06:20:23Z

    Hi Shlok.

Although I'd previously said "v6 LGTM", I decided to feed the v7 patch
through my AI to see what else it might come up with.

It got a bit pedantic, but OTOH the suggestions really *do* seem like
improvements to me, so I feel obliged to pass them on.

======
doc/src/sgml/ref/pg_createsubscriber.sgml

1.
+   replica from a physical standby server.  By default,
+   <application>pg_createsubscriber</application> configures
+   <link linkend="logical-replication">logical replication</link> by
+   automatically creating internal publication and subscription objects. In
+   this mode, a publication and subscription are created for each database, and
+   the publications include all tables and sequences in their respective
+   databases.

"In this mode" is vague — consider "When using internally generated
publications," to be consistent with the terminology used later in the
same paragraph.

~~~

2.
+   which ensures each table is brought up to a synchronized state. When using
+   internally generated publications, they automatically include all sequences
+   in the database; however, their values are not automatically synchronized.

"they" is ambiguous — could refer to publications or sequences.
Suggest: "however, sequence values are not automatically
synchronized."

~~~

3.
+   For user-specified publications, sequences are only replicated if they were
+   explicitly added to those publications prior to running the tool, and like
+   the default mode, their values will require manual synchronization after the
+   upgrade.

"their values will require manual synchronization" — "will require" is
slightly awkward. Suggest "their values must be manually synchronized
after the upgrade."

Also "like the default mode" is a slightly informal comparison —
consider "as with the default mode."

~~~

4.
+  <para>
+   To synchronize sequence values, run
+   <link linkend="sql-altersubscription-params-refresh-sequences">
+   <command>ALTER SUBSCRIPTION ... REFRESH SEQUENCES</command></link>.
+  </para>
```
This is quite abrupt as a standalone paragraph. It doesn't say *when*
to run it or *which* subscription to run it against. Something like:

"To synchronize sequence values after the upgrade, run `ALTER
SUBSCRIPTION ... REFRESH SEQUENCES` on each subscription at the target
server."

~~~

5.
+      specified, <application>pg_createsubscriber</application> creates a
+      publication using <link
linkend="sql-createpublication-params-for-all-tables">
+      <literal>FOR ALL TABLES</literal></link> and
+      <link linkend="sql-createpublication-params-for-all-sequences">
+      <literal>ALL SEQUENCES</literal></link>. Otherwise, it uses the
+      user-specified publication, which includes the objects defined by that
+      publication.  If the <option>--publication</option> option is not
+      specified, the publication has the following name pattern:

SUGGESTION avoid repeating --publication by rearranging like below:

Create a publication and replication slot for each specified database
on the source server. If the <option>--publication</option> option is
specified, the user-specified publication is used, which includes the
objects defined by that publication. Otherwise,
<application>pg_createsubscriber</application> creates a publication
using <link linkend="sql-createpublication-params-for-all-tables">
<literal>FOR ALL TABLES</literal></link> and <link
linkend="sql-createpublication-params-for-all-sequences"> <literal>ALL
SEQUENCES</literal></link>. This generated publication has the
following name pattern:
<quote><literal>pg_createsubscriber_%u_%x</literal></quote>
(parameter:
database <parameter>oid</parameter>, random <parameter>int</parameter>).

======
src/bin/pg_basebackup/t/040_pg_createsubscriber.pl

6.
+$result = $node_p->safe_psql($db2,
+ "SELECT puballtables, puballsequences FROM pg_publication");
+is($result, qq(t|t),
+ "publication is created with both all-tables and all-sequences enabled");

6a. The query has no WHERE clause — if there are multiple publications
it could return multiple rows and give a misleading result. Should
filter by publication name, e.g. WHERE pubname LIKE
'pg_createsubscriber_%'.

~

6b. Should there also be a test that verifies a user-specified
publication is not modified (i.e. does not have ALL SEQUENCES added)
when --publication is specified? That would give better coverage of
the two code paths.

======
GENERAL

7.
The commit message says "Administrators can then run `ALTER
SUBSCRIPTION ... REFRESH SEQUENCES` after the upgrade" but the
documentation does not make this prominent enough. Given this is the
key action administrators need to take, it deserves a more prominent
note — perhaps a `<note>` or `<tip>` block rather than a brief
standalone paragraph.

(I think this is related with point 4)

~~~

8.
There is no mention anywhere in the patch of what happens to sequence
values if the administrator forgets to run REFRESH SEQUENCES before
promoting the subscriber. A note about the risk (duplicate key
violations, incorrect nextval results) would make the documentation
more complete and help administrators understand why this step
matters.

(Again, I think this is related with point 4)

======
Kind Regards,
Peter Smith.
Fujitsu Australia

  21. Re: Include sequences in publications created by pg_createsubscriber

    vignesh C <vignesh21@gmail.com> — 2026-07-08T07:24:31Z

    On Tue, 7 Jul 2026 at 11:50, Peter Smith <smithpb2250@gmail.com> wrote:
>
> Hi Shlok.
>
> Although I'd previously said "v6 LGTM", I decided to feed the v7 patch
> through my AI to see what else it might come up with.
>
> It got a bit pedantic, but OTOH the suggestions really *do* seem like
> improvements to me, so I feel obliged to pass them on.
>
> ======
> doc/src/sgml/ref/pg_createsubscriber.sgml
>
> 1.
> +   replica from a physical standby server.  By default,
> +   <application>pg_createsubscriber</application> configures
> +   <link linkend="logical-replication">logical replication</link> by
> +   automatically creating internal publication and subscription objects. In
> +   this mode, a publication and subscription are created for each database, and
> +   the publications include all tables and sequences in their respective
> +   databases.
>
> "In this mode" is vague — consider "When using internally generated
> publications," to be consistent with the terminology used later in the
> same paragraph.

Modified

> ~~~
>
> 2.
> +   which ensures each table is brought up to a synchronized state. When using
> +   internally generated publications, they automatically include all sequences
> +   in the database; however, their values are not automatically synchronized.
>
> "they" is ambiguous — could refer to publications or sequences.
> Suggest: "however, sequence values are not automatically
> synchronized."

Modified

> ~~~
>
> 3.
> +   For user-specified publications, sequences are only replicated if they were
> +   explicitly added to those publications prior to running the tool, and like
> +   the default mode, their values will require manual synchronization after the
> +   upgrade.
>
> "their values will require manual synchronization" — "will require" is
> slightly awkward. Suggest "their values must be manually synchronized
> after the upgrade."
>
> Also "like the default mode" is a slightly informal comparison —
> consider "as with the default mode."

Modified

> ~~~
>
> 4.
> +  <para>
> +   To synchronize sequence values, run
> +   <link linkend="sql-altersubscription-params-refresh-sequences">
> +   <command>ALTER SUBSCRIPTION ... REFRESH SEQUENCES</command></link>.
> +  </para>
> ```
> This is quite abrupt as a standalone paragraph. It doesn't say *when*
> to run it or *which* subscription to run it against. Something like:
>
> "To synchronize sequence values after the upgrade, run `ALTER
> SUBSCRIPTION ... REFRESH SEQUENCES` on each subscription at the target
> server."

We haven't stated anywhere that pg_createsubscriber is intended to be
used only for upgrades, so I don't think we should mention upgrades
here. I've incorporated the rest of the suggested changess

> ~~~
>
> 5.
> +      specified, <application>pg_createsubscriber</application> creates a
> +      publication using <link
> linkend="sql-createpublication-params-for-all-tables">
> +      <literal>FOR ALL TABLES</literal></link> and
> +      <link linkend="sql-createpublication-params-for-all-sequences">
> +      <literal>ALL SEQUENCES</literal></link>. Otherwise, it uses the
> +      user-specified publication, which includes the objects defined by that
> +      publication.  If the <option>--publication</option> option is not
> +      specified, the publication has the following name pattern:
>
> SUGGESTION avoid repeating --publication by rearranging like below:
>
> Create a publication and replication slot for each specified database
> on the source server. If the <option>--publication</option> option is
> specified, the user-specified publication is used, which includes the
> objects defined by that publication. Otherwise,
> <application>pg_createsubscriber</application> creates a publication
> using <link linkend="sql-createpublication-params-for-all-tables">
> <literal>FOR ALL TABLES</literal></link> and <link
> linkend="sql-createpublication-params-for-all-sequences"> <literal>ALL
> SEQUENCES</literal></link>. This generated publication has the
> following name pattern:
> <quote><literal>pg_createsubscriber_%u_%x</literal></quote>
> (parameter:
> database <parameter>oid</parameter>, random <parameter>int</parameter>).

Modified

> ======
> src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
>
> 6.
> +$result = $node_p->safe_psql($db2,
> + "SELECT puballtables, puballsequences FROM pg_publication");
> +is($result, qq(t|t),
> + "publication is created with both all-tables and all-sequences enabled");
>
> 6a. The query has no WHERE clause — if there are multiple publications
> it could return multiple rows and give a misleading result. Should
> filter by publication name, e.g. WHERE pubname LIKE
> 'pg_createsubscriber_%'.

The verification done here was not correct, I have changed it to
verify the command from the log contents.

> ~
>
> 6b. Should there also be a test that verifies a user-specified
> publication is not modified (i.e. does not have ALL SEQUENCES added)
> when --publication is specified? That would give better coverage of
> the two code paths.

I felt this is not required as we are not doing any ALTER PUBLICATION.

> ======
> GENERAL
>
> 7.
> The commit message says "Administrators can then run `ALTER
> SUBSCRIPTION ... REFRESH SEQUENCES` after the upgrade" but the
> documentation does not make this prominent enough. Given this is the
> key action administrators need to take, it deserves a more prominent
> note — perhaps a `<note>` or `<tip>` block rather than a brief
> standalone paragraph.
>
> (I think this is related with point 4)

I don't think this needs to be mentioned in the pg_createsubscriber
documentation. The behavior of sequence synchronization, including the
need to run ALTER SUBSCRIPTION ... REFRESH SEQUENCES is more
appropriately documented with the REFRESH SEQUENCES command and the
logical replication documentation. Since we have given a link to
REFRESH SEQUENCES, it should be enough.

> ~~~
>
> 8.
> There is no mention anywhere in the patch of what happens to sequence
> values if the administrator forgets to run REFRESH SEQUENCES before
> promoting the subscriber. A note about the risk (duplicate key
> violations, incorrect nextval results) would make the documentation
> more complete and help administrators understand why this step
> matters.
>
> (Again, I think this is related with point 4)

Same reason as above, it need not be mentioned here.

Thanks for the comments, the attached v8 version patch has the changes
for the same. This patch also includes the changes to handle
Ashutosh's comment from [1] with appropriate rewording.

[1] - https://www.postgresql.org/message-id/CAE9k0P%3D9rReCa4CMTWfaYe3ZhE8PqrNCRBnv6QcVPLQm_%2BRFQw%40mail.gmail.com

Regards,
Vignesh