public inbox for [email protected]
help / color / mirror / Atom feedInclude sequences in publications created by pg_createsubscriber
23+ messages / 5 participants
[nested] [flat]
* Include sequences in publications created by pg_createsubscriber
@ 2026-06-19 13:59 vignesh C <[email protected]>
0 siblings, 2 replies; 23+ messages in thread
From: vignesh C @ 2026-06-19 13:59 UTC (permalink / raw)
To: PostgreSQL Hackers <[email protected]>
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
Attachments:
[application/octet-stream] 0001-Include-sequences-in-publications-created-by-pg_crea.patch (7.2K, ../../CALDaNm2RYQRLjexRJOpiZOFkspp8PRgoh6S7d2y003TJ_TSSZA@mail.gmail.com/2-0001-Include-sequences-in-publications-created-by-pg_crea.patch)
download | inline diff:
From 21389f48285f798a5064cc1f1211dfe7da8c1ba8 Mon Sep 17 00:00:00 2001
From: Vignesh C <[email protected]>
Date: Fri, 19 Jun 2026 19:14:27 +0530
Subject: [PATCH] Include sequences in publications created by
pg_createsubscriber
pg_createsubscriber creates a publication on the publisher to establish
logical replication between the original primary and the converted
subscriber. Currently, the publication is created with FOR ALL TABLES,
which ensures that table data changes are replicated during the upgrade
process.
However, sequence state is not replicated as part of this workflow.
Recent additions to logical replication introduced support for publishing
all sequences and synchronizing sequence state through subscription
refresh. By creating publications with FOR ALL TABLES, ALL SEQUENCES,
pg_createsubscriber can take advantage of these capabilities and allow
sequence state to be synchronized before the upgraded node is promoted.
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.
---
doc/src/sgml/ref/pg_createsubscriber.sgml | 37 ++++++++++++-------
src/bin/pg_basebackup/pg_createsubscriber.c | 2 +-
.../t/040_pg_createsubscriber.pl | 8 ++++
3 files changed, 32 insertions(+), 15 deletions(-)
diff --git a/doc/src/sgml/ref/pg_createsubscriber.sgml b/doc/src/sgml/ref/pg_createsubscriber.sgml
index 3b3038d1891..82b389e6b94 100644
--- a/doc/src/sgml/ref/pg_createsubscriber.sgml
+++ b/doc/src/sgml/ref/pg_createsubscriber.sgml
@@ -48,11 +48,11 @@ PostgreSQL documentation
<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.
+ replica from a physical standby server. All tables and sequences 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.
</para>
<para>
@@ -60,8 +60,9 @@ PostgreSQL documentation
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.
</para>
<para>
@@ -275,10 +276,10 @@ PostgreSQL documentation
<listitem>
<para>
<literal>publications</literal>:
- The <literal>FOR ALL TABLES</literal> publications established for this
- subscriber are always dropped; specifying this object type causes all
- other publications replicated from the source server to be dropped as
- well.
+ The <literal>FOR ALL TABLES, ALL SEQUENCES</literal> publications
+ established for this subscriber are always dropped; specifying this
+ object type causes all other publications replicated from the source
+ server to be dropped as well.
</para>
</listitem>
</itemizedlist>
@@ -323,7 +324,8 @@ PostgreSQL documentation
If a specified publication already exists on the publisher, it is reused.
It is useful to partially replicate the database if the specified
publication includes a list of tables. If the publication does not exist,
- it is automatically created with <literal>FOR ALL TABLES</literal>. Use
+ it is automatically created with
+ <literal>FOR ALL TABLES, ALL SEQUENCES</literal>. Use
<option>--dry-run</option> option to preview which publications will be
reused and which will be created.
</para>
@@ -534,7 +536,9 @@ PostgreSQL documentation
Create a publication and replication slot for each specified database on
the source server. Each publication is created using <link
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:
<quote><literal>pg_createsubscriber_%u_%x</literal></quote> (parameter:
database <parameter>oid</parameter>, random <parameter>int</parameter>).
@@ -617,7 +621,12 @@ PostgreSQL documentation
<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>
</step>
diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c
index 4d705778454..22319fd06ff 100644
--- a/src/bin/pg_basebackup/pg_createsubscriber.c
+++ b/src/bin/pg_basebackup/pg_createsubscriber.c
@@ -1840,7 +1840,7 @@ create_publication(PGconn *conn, struct LogicalRepInfo *dbinfo)
pg_log_info("creating publication \"%s\" in database \"%s\"",
dbinfo->pubname, dbinfo->dbname);
- appendPQExpBuffer(str, "CREATE PUBLICATION %s FOR ALL TABLES",
+ appendPQExpBuffer(str, "CREATE PUBLICATION %s FOR ALL TABLES, ALL SEQUENCES",
ipubname_esc);
pg_log_debug("command is: %s", str->data);
diff --git a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
index 9252d1c3c5c..044f202cb35 100644
--- a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
+++ b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
@@ -503,6 +503,14 @@ is($result, qq(0),
'the physical replication slot used as primary_slot_name has been removed'
);
+# Verify that the publication is configured to publish all tables and
+# all sequences.
+$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"
+);
+
# Insert rows on P
$node_p->safe_psql($db1, "INSERT INTO tbl1 VALUES('third row')");
$node_p->safe_psql($db2, "INSERT INTO tbl2 VALUES('row 1')");
--
2.53.0
^ permalink raw reply [nested|flat] 23+ messages in thread
* RE: Include sequences in publications created by pg_createsubscriber
@ 2026-06-22 12:39 Hayato Kuroda (Fujitsu) <[email protected]>
parent: vignesh C <[email protected]>
1 sibling, 1 reply; 23+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2026-06-22 12:39 UTC (permalink / raw)
To: 'vignesh C' <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
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
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Include sequences in publications created by pg_createsubscriber
@ 2026-06-23 01:03 Peter Smith <[email protected]>
parent: vignesh C <[email protected]>
1 sibling, 1 reply; 23+ messages in thread
From: Peter Smith @ 2026-06-23 01:03 UTC (permalink / raw)
To: vignesh C <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Sat, Jun 20, 2026 at 1:59 AM vignesh C <[email protected]> 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
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Include sequences in publications created by pg_createsubscriber
@ 2026-06-23 10:19 vignesh C <[email protected]>
parent: Hayato Kuroda (Fujitsu) <[email protected]>
0 siblings, 0 replies; 23+ messages in thread
From: vignesh C @ 2026-06-23 10:19 UTC (permalink / raw)
To: Hayato Kuroda (Fujitsu) <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
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
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Include sequences in publications created by pg_createsubscriber
@ 2026-06-24 07:30 vignesh C <[email protected]>
parent: Peter Smith <[email protected]>
0 siblings, 1 reply; 23+ messages in thread
From: vignesh C @ 2026-06-24 07:30 UTC (permalink / raw)
To: Peter Smith <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Tue, 23 Jun 2026 at 06:33, Peter Smith <[email protected]> wrote:
>
> On Sat, Jun 20, 2026 at 1:59 AM vignesh C <[email protected]> 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
Attachments:
[application/octet-stream] v2-0001-Include-sequences-in-publications-created-by-pg_c.patch (8.0K, ../../CALDaNm05u0UvteGQf_9wKbUFujO0JvJk=GnTeZyxsdB0bmPtcg@mail.gmail.com/2-v2-0001-Include-sequences-in-publications-created-by-pg_c.patch)
download | inline diff:
From 463272e0c2ae53ff3b39e3e1e1e508697732c9df Mon Sep 17 00:00:00 2001
From: Vignesh C <[email protected]>
Date: Fri, 19 Jun 2026 19:14:27 +0530
Subject: [PATCH v2] Include sequences in publications created by
pg_createsubscriber
pg_createsubscriber creates a publication on the publisher to establish
logical replication between the original primary and the converted
subscriber. Currently, the publication is created with FOR ALL TABLES,
which ensures that table data changes are replicated during the upgrade
process.
However, sequence state is not replicated as part of this workflow.
Recent additions to logical replication introduced support for publishing
all sequences and synchronizing sequence state through subscription
refresh. By creating publications with FOR ALL TABLES, ALL SEQUENCES,
pg_createsubscriber can take advantage of these capabilities and allow
sequence state to be synchronized before the upgraded node is promoted.
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.
---
doc/src/sgml/ref/pg_createsubscriber.sgml | 41 ++++++++++++-------
src/bin/pg_basebackup/pg_createsubscriber.c | 4 +-
.../t/040_pg_createsubscriber.pl | 8 ++++
3 files changed, 37 insertions(+), 16 deletions(-)
diff --git a/doc/src/sgml/ref/pg_createsubscriber.sgml b/doc/src/sgml/ref/pg_createsubscriber.sgml
index 3b3038d1891..4c7f5fee574 100644
--- a/doc/src/sgml/ref/pg_createsubscriber.sgml
+++ b/doc/src/sgml/ref/pg_createsubscriber.sgml
@@ -48,11 +48,11 @@ PostgreSQL documentation
<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.
+ replica from a physical standby server. All tables and sequences 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.
</para>
<para>
@@ -60,8 +60,9 @@ PostgreSQL documentation
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.
</para>
<para>
@@ -75,7 +76,16 @@ PostgreSQL documentation
synchronization. For details, see the <command>CREATE SUBSCRIPTION</command>
<link linkend="sql-createsubscription-params-with-copy-data">
<literal>copy_data</literal></link> option.
+ </para>
+ <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>
</refsect1>
@@ -275,10 +285,10 @@ PostgreSQL documentation
<listitem>
<para>
<literal>publications</literal>:
- The <literal>FOR ALL TABLES</literal> publications established for this
- subscriber are always dropped; specifying this object type causes all
- other publications replicated from the source server to be dropped as
- well.
+ The <literal>FOR ALL TABLES, ALL SEQUENCES</literal> publications
+ established for this subscriber are always dropped; specifying this
+ object type causes all other publications replicated from the source
+ server to be dropped as well.
</para>
</listitem>
</itemizedlist>
@@ -323,7 +333,8 @@ PostgreSQL documentation
If a specified publication already exists on the publisher, it is reused.
It is useful to partially replicate the database if the specified
publication includes a list of tables. If the publication does not exist,
- it is automatically created with <literal>FOR ALL TABLES</literal>. Use
+ it is automatically created with
+ <literal>FOR ALL TABLES, ALL SEQUENCES</literal>. Use
<option>--dry-run</option> option to preview which publications will be
reused and which will be created.
</para>
@@ -534,7 +545,9 @@ PostgreSQL documentation
Create a publication and replication slot for each specified database on
the source server. Each publication is created using <link
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>ALL
+ SEQUENCES</literal></link>. If the <option>--publication</option> option
is not specified, the publication has the following name pattern:
<quote><literal>pg_createsubscriber_%u_%x</literal></quote> (parameter:
database <parameter>oid</parameter>, random <parameter>int</parameter>).
@@ -617,7 +630,7 @@ PostgreSQL documentation
<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>
</step>
diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c
index 4d705778454..ec18faf50eb 100644
--- a/src/bin/pg_basebackup/pg_createsubscriber.c
+++ b/src/bin/pg_basebackup/pg_createsubscriber.c
@@ -1788,7 +1788,7 @@ wait_for_end_recovery(const char *conninfo, const struct CreateSubscriberOptions
}
/*
- * Create a publication that includes all tables in the database.
+ * Create a publication that includes all tables and sequences in the database.
*/
static void
create_publication(PGconn *conn, struct LogicalRepInfo *dbinfo)
@@ -1840,7 +1840,7 @@ create_publication(PGconn *conn, struct LogicalRepInfo *dbinfo)
pg_log_info("creating publication \"%s\" in database \"%s\"",
dbinfo->pubname, dbinfo->dbname);
- appendPQExpBuffer(str, "CREATE PUBLICATION %s FOR ALL TABLES",
+ appendPQExpBuffer(str, "CREATE PUBLICATION %s FOR ALL TABLES, ALL SEQUENCES",
ipubname_esc);
pg_log_debug("command is: %s", str->data);
diff --git a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
index 9252d1c3c5c..044f202cb35 100644
--- a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
+++ b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
@@ -503,6 +503,14 @@ is($result, qq(0),
'the physical replication slot used as primary_slot_name has been removed'
);
+# Verify that the publication is configured to publish all tables and
+# all sequences.
+$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"
+);
+
# Insert rows on P
$node_p->safe_psql($db1, "INSERT INTO tbl1 VALUES('third row')");
$node_p->safe_psql($db2, "INSERT INTO tbl2 VALUES('row 1')");
--
2.53.0
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Include sequences in publications created by pg_createsubscriber
@ 2026-06-25 00:58 Peter Smith <[email protected]>
parent: vignesh C <[email protected]>
0 siblings, 2 replies; 23+ messages in thread
From: Peter Smith @ 2026-06-25 00:58 UTC (permalink / raw)
To: vignesh C <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
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%40ma...
Kind Regards,
Peter Smith.
Fujitsu Australia
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Include sequences in publications created by pg_createsubscriber
@ 2026-06-25 05:35 Ashutosh Sharma <[email protected]>
parent: Peter Smith <[email protected]>
1 sibling, 1 reply; 23+ messages in thread
From: Ashutosh Sharma @ 2026-06-25 05:35 UTC (permalink / raw)
To: Peter Smith <[email protected]>; +Cc: vignesh C <[email protected]>; PostgreSQL Hackers <[email protected]>
Hi,
On Thu, Jun 25, 2026 at 6:29 AM Peter Smith <[email protected]> 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.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Include sequences in publications created by pg_createsubscriber
@ 2026-06-25 05:57 Peter Smith <[email protected]>
parent: Ashutosh Sharma <[email protected]>
0 siblings, 0 replies; 23+ messages in thread
From: Peter Smith @ 2026-06-25 05:57 UTC (permalink / raw)
To: Ashutosh Sharma <[email protected]>; +Cc: vignesh C <[email protected]>; PostgreSQL Hackers <[email protected]>
On Thu, Jun 25, 2026 at 5:35 PM Ashutosh Sharma <[email protected]> wrote:
>
> Hi,
>
> On Thu, Jun 25, 2026 at 6:29 AM Peter Smith <[email protected]> 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.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Include sequences in publications created by pg_createsubscriber
@ 2026-06-25 10:00 vignesh C <[email protected]>
parent: Peter Smith <[email protected]>
1 sibling, 1 reply; 23+ messages in thread
From: vignesh C @ 2026-06-25 10:00 UTC (permalink / raw)
To: Peter Smith <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Thu, 25 Jun 2026 at 06:29, Peter Smith <[email protected]> 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...
Regards,
Vignesh
Attachments:
[application/octet-stream] v3-0001-Include-sequences-in-publications-created-by-pg_c.patch (7.9K, ../../CALDaNm39kG3iweFqyS0=3UNOSzZn_13Cq5iEKqcyG7apXGPJbw@mail.gmail.com/2-v3-0001-Include-sequences-in-publications-created-by-pg_c.patch)
download | inline diff:
From 7f939667057e3d62ee98e08480493465691b51e1 Mon Sep 17 00:00:00 2001
From: Vignesh C <[email protected]>
Date: Fri, 19 Jun 2026 19:14:27 +0530
Subject: [PATCH v3] Include sequences in publications created by
pg_createsubscriber
pg_createsubscriber creates a publication on the publisher to establish
logical replication between the original primary and the converted
subscriber. Currently, the publication is created with FOR ALL TABLES,
which ensures that table data changes are replicated during the upgrade
process.
However, sequence state is not replicated as part of this workflow.
Recent additions to logical replication introduced support for publishing
all sequences and synchronizing sequence state through subscription
refresh. By creating publications with FOR ALL TABLES, ALL SEQUENCES,
pg_createsubscriber can take advantage of these capabilities and allow
sequence state to be synchronized before the upgraded node is promoted.
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.
---
doc/src/sgml/ref/pg_createsubscriber.sgml | 43 +++++++++++++------
src/bin/pg_basebackup/pg_createsubscriber.c | 4 +-
.../t/040_pg_createsubscriber.pl | 8 ++++
3 files changed, 39 insertions(+), 16 deletions(-)
diff --git a/doc/src/sgml/ref/pg_createsubscriber.sgml b/doc/src/sgml/ref/pg_createsubscriber.sgml
index 3b3038d1891..eddac27af9f 100644
--- a/doc/src/sgml/ref/pg_createsubscriber.sgml
+++ b/doc/src/sgml/ref/pg_createsubscriber.sgml
@@ -49,10 +49,12 @@ PostgreSQL documentation
<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>
<para>
@@ -61,7 +63,9 @@ PostgreSQL documentation
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.
</para>
<para>
@@ -75,7 +79,12 @@ PostgreSQL documentation
synchronization. For details, see the <command>CREATE SUBSCRIPTION</command>
<link linkend="sql-createsubscription-params-with-copy-data">
<literal>copy_data</literal></link> option.
+ </para>
+ <para>
+ To synchronize sequence values, run
+ <link linkend="sql-altersubscription-params-refresh-sequences">
+ <command>ALTER SUBSCRIPTION ... REFRESH SEQUENCES</command></link>.
</para>
</refsect1>
@@ -275,10 +284,10 @@ PostgreSQL documentation
<listitem>
<para>
<literal>publications</literal>:
- The <literal>FOR ALL TABLES</literal> publications established for this
- subscriber are always dropped; specifying this object type causes all
- other publications replicated from the source server to be dropped as
- well.
+ The <literal>FOR ALL TABLES, ALL SEQUENCES</literal> publications
+ established for this subscriber are always dropped; specifying this
+ object type causes all other publications replicated from the source
+ server to be dropped as well.
</para>
</listitem>
</itemizedlist>
@@ -323,7 +332,8 @@ PostgreSQL documentation
If a specified publication already exists on the publisher, it is reused.
It is useful to partially replicate the database if the specified
publication includes a list of tables. If the publication does not exist,
- it is automatically created with <literal>FOR ALL TABLES</literal>. Use
+ it is automatically created with
+ <literal>FOR ALL TABLES, ALL SEQUENCES</literal>. Use
<option>--dry-run</option> option to preview which publications will be
reused and which will be created.
</para>
@@ -532,10 +542,15 @@ PostgreSQL documentation
<step>
<para>
Create a publication and replication slot for each specified database on
- the source server. Each publication is created using <link
- linkend="sql-createpublication-params-for-all-tables"><literal>FOR ALL
- TABLES</literal></link>. If the <option>--publication</option> option
- is not specified, the publication has the following name pattern:
+ 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">
+ <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:
<quote><literal>pg_createsubscriber_%u_%x</literal></quote> (parameter:
database <parameter>oid</parameter>, random <parameter>int</parameter>).
If the <option>--replication-slot</option> option is not specified, the
diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c
index 4d705778454..ec18faf50eb 100644
--- a/src/bin/pg_basebackup/pg_createsubscriber.c
+++ b/src/bin/pg_basebackup/pg_createsubscriber.c
@@ -1788,7 +1788,7 @@ wait_for_end_recovery(const char *conninfo, const struct CreateSubscriberOptions
}
/*
- * Create a publication that includes all tables in the database.
+ * Create a publication that includes all tables and sequences in the database.
*/
static void
create_publication(PGconn *conn, struct LogicalRepInfo *dbinfo)
@@ -1840,7 +1840,7 @@ create_publication(PGconn *conn, struct LogicalRepInfo *dbinfo)
pg_log_info("creating publication \"%s\" in database \"%s\"",
dbinfo->pubname, dbinfo->dbname);
- appendPQExpBuffer(str, "CREATE PUBLICATION %s FOR ALL TABLES",
+ appendPQExpBuffer(str, "CREATE PUBLICATION %s FOR ALL TABLES, ALL SEQUENCES",
ipubname_esc);
pg_log_debug("command is: %s", str->data);
diff --git a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
index 9252d1c3c5c..044f202cb35 100644
--- a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
+++ b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
@@ -503,6 +503,14 @@ is($result, qq(0),
'the physical replication slot used as primary_slot_name has been removed'
);
+# Verify that the publication is configured to publish all tables and
+# all sequences.
+$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"
+);
+
# Insert rows on P
$node_p->safe_psql($db1, "INSERT INTO tbl1 VALUES('third row')");
$node_p->safe_psql($db2, "INSERT INTO tbl2 VALUES('row 1')");
--
2.53.0
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Include sequences in publications created by pg_createsubscriber
@ 2026-06-25 22:19 Peter Smith <[email protected]>
parent: vignesh C <[email protected]>
0 siblings, 1 reply; 23+ messages in thread
From: Peter Smith @ 2026-06-25 22:19 UTC (permalink / raw)
To: vignesh C <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
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
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Include sequences in publications created by pg_createsubscriber
@ 2026-06-29 08:49 vignesh C <[email protected]>
parent: Peter Smith <[email protected]>
0 siblings, 1 reply; 23+ messages in thread
From: vignesh C @ 2026-06-29 08:49 UTC (permalink / raw)
To: Peter Smith <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Fri, 26 Jun 2026 at 03:50, Peter Smith <[email protected]> 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
Attachments:
[application/octet-stream] v4-0001-Include-sequences-in-publications-created-by-pg_c.patch (8.3K, ../../CALDaNm3Kh83ufGbgJqc8Frn=UvqC3LAd43eKS=FRuEWaRxbp4w@mail.gmail.com/2-v4-0001-Include-sequences-in-publications-created-by-pg_c.patch)
download | inline diff:
From f62dc917b8a655389aa0a6f385c5ca24fcfc26e0 Mon Sep 17 00:00:00 2001
From: Vignesh C <[email protected]>
Date: Fri, 19 Jun 2026 19:14:27 +0530
Subject: [PATCH v4] Include sequences in publications created by
pg_createsubscriber
pg_createsubscriber creates a publication on the publisher to establish
logical replication between the original primary and the converted
subscriber. Currently, the publication is created with FOR ALL TABLES,
which ensures that table data changes are replicated during the upgrade
process.
However, sequence state is not replicated as part of this workflow.
Recent additions to logical replication introduced support for publishing
all sequences and synchronizing sequence state through subscription
refresh. By creating publications with FOR ALL TABLES, ALL SEQUENCES,
pg_createsubscriber can take advantage of these capabilities and allow
sequence state to be synchronized before the upgraded node is promoted.
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.
---
doc/src/sgml/ref/pg_createsubscriber.sgml | 50 +++++++++++++------
src/bin/pg_basebackup/pg_createsubscriber.c | 4 +-
.../t/040_pg_createsubscriber.pl | 8 +++
3 files changed, 45 insertions(+), 17 deletions(-)
diff --git a/doc/src/sgml/ref/pg_createsubscriber.sgml b/doc/src/sgml/ref/pg_createsubscriber.sgml
index 3b3038d1891..dfc2b1d34bb 100644
--- a/doc/src/sgml/ref/pg_createsubscriber.sgml
+++ b/doc/src/sgml/ref/pg_createsubscriber.sgml
@@ -48,11 +48,14 @@ PostgreSQL documentation
<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.
+ 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.
</para>
<para>
@@ -61,7 +64,13 @@ PostgreSQL documentation
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. When using
+ internally generated publications, they automatically include all sequences
+ in the database; however, their values are not automatically synchronized.
+ For user-nominated 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.
</para>
<para>
@@ -75,7 +84,12 @@ PostgreSQL documentation
synchronization. For details, see the <command>CREATE SUBSCRIPTION</command>
<link linkend="sql-createsubscription-params-with-copy-data">
<literal>copy_data</literal></link> option.
+ </para>
+ <para>
+ To synchronize sequence values, run
+ <link linkend="sql-altersubscription-params-refresh-sequences">
+ <command>ALTER SUBSCRIPTION ... REFRESH SEQUENCES</command></link>.
</para>
</refsect1>
@@ -275,10 +289,10 @@ PostgreSQL documentation
<listitem>
<para>
<literal>publications</literal>:
- The <literal>FOR ALL TABLES</literal> publications established for this
- subscriber are always dropped; specifying this object type causes all
- other publications replicated from the source server to be dropped as
- well.
+ The <literal>FOR ALL TABLES, ALL SEQUENCES</literal> publications
+ established for this subscriber are always dropped; specifying this
+ object type causes all other publications replicated from the source
+ server to be dropped as well.
</para>
</listitem>
</itemizedlist>
@@ -323,7 +337,8 @@ PostgreSQL documentation
If a specified publication already exists on the publisher, it is reused.
It is useful to partially replicate the database if the specified
publication includes a list of tables. If the publication does not exist,
- it is automatically created with <literal>FOR ALL TABLES</literal>. Use
+ it is automatically created with
+ <literal>FOR ALL TABLES, ALL SEQUENCES</literal>. Use
<option>--dry-run</option> option to preview which publications will be
reused and which will be created.
</para>
@@ -532,10 +547,15 @@ PostgreSQL documentation
<step>
<para>
Create a publication and replication slot for each specified database on
- the source server. Each publication is created using <link
- linkend="sql-createpublication-params-for-all-tables"><literal>FOR ALL
- TABLES</literal></link>. If the <option>--publication</option> option
- is not specified, the publication has the following name pattern:
+ 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">
+ <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:
<quote><literal>pg_createsubscriber_%u_%x</literal></quote> (parameter:
database <parameter>oid</parameter>, random <parameter>int</parameter>).
If the <option>--replication-slot</option> option is not specified, the
diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c
index 4d705778454..ec18faf50eb 100644
--- a/src/bin/pg_basebackup/pg_createsubscriber.c
+++ b/src/bin/pg_basebackup/pg_createsubscriber.c
@@ -1788,7 +1788,7 @@ wait_for_end_recovery(const char *conninfo, const struct CreateSubscriberOptions
}
/*
- * Create a publication that includes all tables in the database.
+ * Create a publication that includes all tables and sequences in the database.
*/
static void
create_publication(PGconn *conn, struct LogicalRepInfo *dbinfo)
@@ -1840,7 +1840,7 @@ create_publication(PGconn *conn, struct LogicalRepInfo *dbinfo)
pg_log_info("creating publication \"%s\" in database \"%s\"",
dbinfo->pubname, dbinfo->dbname);
- appendPQExpBuffer(str, "CREATE PUBLICATION %s FOR ALL TABLES",
+ appendPQExpBuffer(str, "CREATE PUBLICATION %s FOR ALL TABLES, ALL SEQUENCES",
ipubname_esc);
pg_log_debug("command is: %s", str->data);
diff --git a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
index 9252d1c3c5c..044f202cb35 100644
--- a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
+++ b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
@@ -503,6 +503,14 @@ is($result, qq(0),
'the physical replication slot used as primary_slot_name has been removed'
);
+# Verify that the publication is configured to publish all tables and
+# all sequences.
+$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"
+);
+
# Insert rows on P
$node_p->safe_psql($db1, "INSERT INTO tbl1 VALUES('third row')");
$node_p->safe_psql($db2, "INSERT INTO tbl2 VALUES('row 1')");
--
2.53.0
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Include sequences in publications created by pg_createsubscriber
@ 2026-06-29 14:36 Shlok Kyal <[email protected]>
parent: vignesh C <[email protected]>
0 siblings, 1 reply; 23+ messages in thread
From: Shlok Kyal @ 2026-06-29 14:36 UTC (permalink / raw)
To: vignesh C <[email protected]>; +Cc: Peter Smith <[email protected]>; PostgreSQL Hackers <[email protected]>
>
> 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
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Include sequences in publications created by pg_createsubscriber
@ 2026-06-30 06:47 vignesh C <[email protected]>
parent: Shlok Kyal <[email protected]>
0 siblings, 1 reply; 23+ messages in thread
From: vignesh C @ 2026-06-30 06:47 UTC (permalink / raw)
To: Shlok Kyal <[email protected]>; +Cc: Peter Smith <[email protected]>; PostgreSQL Hackers <[email protected]>
On Mon, 29 Jun 2026 at 20:07, Shlok Kyal <[email protected]> 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
Attachments:
[application/octet-stream] v5-0001-Include-sequences-in-publications-created-by-pg_c.patch (8.4K, ../../CALDaNm0G=RXa+mXwrqSLYATdT4NvhZ+bv6yrH8pB+xWzBDTSxQ@mail.gmail.com/2-v5-0001-Include-sequences-in-publications-created-by-pg_c.patch)
download | inline diff:
From 01b5da516391b20cd186e21d943400c9f2ade152 Mon Sep 17 00:00:00 2001
From: Vignesh C <[email protected]>
Date: Fri, 19 Jun 2026 19:14:27 +0530
Subject: [PATCH v5] Include sequences in publications created by
pg_createsubscriber
pg_createsubscriber creates a publication on the publisher to establish
logical replication between the original primary and the converted
subscriber. Currently, the publication is created with FOR ALL TABLES,
which ensures that table data changes are replicated during the upgrade
process.
However, sequence state is not replicated as part of this workflow.
Recent additions to logical replication introduced support for publishing
all sequences and synchronizing sequence state through subscription
refresh. By creating publications with FOR ALL TABLES, ALL SEQUENCES,
pg_createsubscriber can take advantage of these capabilities and allow
sequence state to be synchronized before the upgraded node is promoted.
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.
---
doc/src/sgml/ref/pg_createsubscriber.sgml | 51 +++++++++++++------
src/bin/pg_basebackup/pg_createsubscriber.c | 4 +-
.../t/040_pg_createsubscriber.pl | 8 +++
3 files changed, 46 insertions(+), 17 deletions(-)
diff --git a/doc/src/sgml/ref/pg_createsubscriber.sgml b/doc/src/sgml/ref/pg_createsubscriber.sgml
index 3b3038d1891..81435bb6d24 100644
--- a/doc/src/sgml/ref/pg_createsubscriber.sgml
+++ b/doc/src/sgml/ref/pg_createsubscriber.sgml
@@ -48,11 +48,15 @@ PostgreSQL documentation
<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.
+ 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.
</para>
<para>
@@ -61,7 +65,13 @@ PostgreSQL documentation
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. When using
+ internally generated publications, they automatically include all sequences
+ in the database; however, their values are not automatically synchronized.
+ 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.
</para>
<para>
@@ -75,7 +85,12 @@ PostgreSQL documentation
synchronization. For details, see the <command>CREATE SUBSCRIPTION</command>
<link linkend="sql-createsubscription-params-with-copy-data">
<literal>copy_data</literal></link> option.
+ </para>
+ <para>
+ To synchronize sequence values, run
+ <link linkend="sql-altersubscription-params-refresh-sequences">
+ <command>ALTER SUBSCRIPTION ... REFRESH SEQUENCES</command></link>.
</para>
</refsect1>
@@ -275,10 +290,10 @@ PostgreSQL documentation
<listitem>
<para>
<literal>publications</literal>:
- The <literal>FOR ALL TABLES</literal> publications established for this
- subscriber are always dropped; specifying this object type causes all
- other publications replicated from the source server to be dropped as
- well.
+ The <literal>FOR ALL TABLES, ALL SEQUENCES</literal> publications
+ established for this subscriber are always dropped; specifying this
+ object type causes all other publications replicated from the source
+ server to be dropped as well.
</para>
</listitem>
</itemizedlist>
@@ -323,7 +338,8 @@ PostgreSQL documentation
If a specified publication already exists on the publisher, it is reused.
It is useful to partially replicate the database if the specified
publication includes a list of tables. If the publication does not exist,
- it is automatically created with <literal>FOR ALL TABLES</literal>. Use
+ it is automatically created with
+ <literal>FOR ALL TABLES, ALL SEQUENCES</literal>. Use
<option>--dry-run</option> option to preview which publications will be
reused and which will be created.
</para>
@@ -532,10 +548,15 @@ PostgreSQL documentation
<step>
<para>
Create a publication and replication slot for each specified database on
- the source server. Each publication is created using <link
- linkend="sql-createpublication-params-for-all-tables"><literal>FOR ALL
- TABLES</literal></link>. If the <option>--publication</option> option
- is not specified, the publication has the following name pattern:
+ the source server. If the <option>--publication</option> option is not
+ 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:
<quote><literal>pg_createsubscriber_%u_%x</literal></quote> (parameter:
database <parameter>oid</parameter>, random <parameter>int</parameter>).
If the <option>--replication-slot</option> option is not specified, the
diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c
index 4d705778454..ec18faf50eb 100644
--- a/src/bin/pg_basebackup/pg_createsubscriber.c
+++ b/src/bin/pg_basebackup/pg_createsubscriber.c
@@ -1788,7 +1788,7 @@ wait_for_end_recovery(const char *conninfo, const struct CreateSubscriberOptions
}
/*
- * Create a publication that includes all tables in the database.
+ * Create a publication that includes all tables and sequences in the database.
*/
static void
create_publication(PGconn *conn, struct LogicalRepInfo *dbinfo)
@@ -1840,7 +1840,7 @@ create_publication(PGconn *conn, struct LogicalRepInfo *dbinfo)
pg_log_info("creating publication \"%s\" in database \"%s\"",
dbinfo->pubname, dbinfo->dbname);
- appendPQExpBuffer(str, "CREATE PUBLICATION %s FOR ALL TABLES",
+ appendPQExpBuffer(str, "CREATE PUBLICATION %s FOR ALL TABLES, ALL SEQUENCES",
ipubname_esc);
pg_log_debug("command is: %s", str->data);
diff --git a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
index 9252d1c3c5c..e11de069347 100644
--- a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
+++ b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
@@ -503,6 +503,14 @@ is($result, qq(0),
'the physical replication slot used as primary_slot_name has been removed'
);
+# Verify that the publication is configured to publish all tables and
+# all sequences.
+$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"
+);
+
# Insert rows on P
$node_p->safe_psql($db1, "INSERT INTO tbl1 VALUES('third row')");
$node_p->safe_psql($db2, "INSERT INTO tbl2 VALUES('row 1')");
--
2.53.0
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Include sequences in publications created by pg_createsubscriber
@ 2026-07-01 02:46 Peter Smith <[email protected]>
parent: vignesh C <[email protected]>
0 siblings, 1 reply; 23+ messages in thread
From: Peter Smith @ 2026-07-01 02:46 UTC (permalink / raw)
To: vignesh C <[email protected]>; +Cc: Shlok Kyal <[email protected]>; PostgreSQL Hackers <[email protected]>
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
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Include sequences in publications created by pg_createsubscriber
@ 2026-07-01 07:21 vignesh C <[email protected]>
parent: Peter Smith <[email protected]>
0 siblings, 2 replies; 23+ messages in thread
From: vignesh C @ 2026-07-01 07:21 UTC (permalink / raw)
To: Peter Smith <[email protected]>; +Cc: Shlok Kyal <[email protected]>; PostgreSQL Hackers <[email protected]>
On Wed, 1 Jul 2026 at 08:17, Peter Smith <[email protected]> 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
Attachments:
[application/octet-stream] v6-0001-Include-sequences-in-publications-created-by-pg_c.patch (8.4K, ../../CALDaNm0F9oX18eH5MbNRWS5uux2KneDc2RwX__+7vm7nvgHsXg@mail.gmail.com/2-v6-0001-Include-sequences-in-publications-created-by-pg_c.patch)
download | inline diff:
From b01331090626349eef960d4ecfed7a94dad57f0f Mon Sep 17 00:00:00 2001
From: Vignesh C <[email protected]>
Date: Fri, 19 Jun 2026 19:14:27 +0530
Subject: [PATCH v6] Include sequences in publications created by
pg_createsubscriber
pg_createsubscriber creates a publication on the publisher to establish
logical replication between the original primary and the converted
subscriber. Currently, the publication is created with FOR ALL TABLES,
which ensures that table data changes are replicated during the upgrade
process.
However, sequence state is not replicated as part of this workflow.
Recent additions to logical replication introduced support for publishing
all sequences and synchronizing sequence state through subscription
refresh. By creating publications with FOR ALL TABLES, ALL SEQUENCES,
pg_createsubscriber can take advantage of these capabilities and allow
sequence state to be synchronized before the upgraded node is promoted.
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.
---
doc/src/sgml/ref/pg_createsubscriber.sgml | 52 +++++++++++++------
src/bin/pg_basebackup/pg_createsubscriber.c | 4 +-
.../t/040_pg_createsubscriber.pl | 8 +++
3 files changed, 47 insertions(+), 17 deletions(-)
diff --git a/doc/src/sgml/ref/pg_createsubscriber.sgml b/doc/src/sgml/ref/pg_createsubscriber.sgml
index 3b3038d1891..f9b7102d4b3 100644
--- a/doc/src/sgml/ref/pg_createsubscriber.sgml
+++ b/doc/src/sgml/ref/pg_createsubscriber.sgml
@@ -48,11 +48,16 @@ PostgreSQL documentation
<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.
+ 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.
+ <application>pg_createsubscriber</application> must be run at the target
+ server.
</para>
<para>
@@ -61,7 +66,13 @@ PostgreSQL documentation
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. When using
+ internally generated publications, they automatically include all sequences
+ in the database; however, their values are not automatically synchronized.
+ 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.
</para>
<para>
@@ -75,7 +86,12 @@ PostgreSQL documentation
synchronization. For details, see the <command>CREATE SUBSCRIPTION</command>
<link linkend="sql-createsubscription-params-with-copy-data">
<literal>copy_data</literal></link> option.
+ </para>
+ <para>
+ To synchronize sequence values, run
+ <link linkend="sql-altersubscription-params-refresh-sequences">
+ <command>ALTER SUBSCRIPTION ... REFRESH SEQUENCES</command></link>.
</para>
</refsect1>
@@ -275,10 +291,10 @@ PostgreSQL documentation
<listitem>
<para>
<literal>publications</literal>:
- The <literal>FOR ALL TABLES</literal> publications established for this
- subscriber are always dropped; specifying this object type causes all
- other publications replicated from the source server to be dropped as
- well.
+ The <literal>FOR ALL TABLES, ALL SEQUENCES</literal> publications
+ established for this subscriber are always dropped; specifying this
+ object type causes all other publications replicated from the source
+ server to be dropped as well.
</para>
</listitem>
</itemizedlist>
@@ -323,7 +339,8 @@ PostgreSQL documentation
If a specified publication already exists on the publisher, it is reused.
It is useful to partially replicate the database if the specified
publication includes a list of tables. If the publication does not exist,
- it is automatically created with <literal>FOR ALL TABLES</literal>. Use
+ it is automatically created with
+ <literal>FOR ALL TABLES, ALL SEQUENCES</literal>. Use
<option>--dry-run</option> option to preview which publications will be
reused and which will be created.
</para>
@@ -532,10 +549,15 @@ PostgreSQL documentation
<step>
<para>
Create a publication and replication slot for each specified database on
- the source server. Each publication is created using <link
- linkend="sql-createpublication-params-for-all-tables"><literal>FOR ALL
- TABLES</literal></link>. If the <option>--publication</option> option
- is not specified, the publication has the following name pattern:
+ the source server. If the <option>--publication</option> option is not
+ 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:
<quote><literal>pg_createsubscriber_%u_%x</literal></quote> (parameter:
database <parameter>oid</parameter>, random <parameter>int</parameter>).
If the <option>--replication-slot</option> option is not specified, the
diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c
index 4d705778454..ec18faf50eb 100644
--- a/src/bin/pg_basebackup/pg_createsubscriber.c
+++ b/src/bin/pg_basebackup/pg_createsubscriber.c
@@ -1788,7 +1788,7 @@ wait_for_end_recovery(const char *conninfo, const struct CreateSubscriberOptions
}
/*
- * Create a publication that includes all tables in the database.
+ * Create a publication that includes all tables and sequences in the database.
*/
static void
create_publication(PGconn *conn, struct LogicalRepInfo *dbinfo)
@@ -1840,7 +1840,7 @@ create_publication(PGconn *conn, struct LogicalRepInfo *dbinfo)
pg_log_info("creating publication \"%s\" in database \"%s\"",
dbinfo->pubname, dbinfo->dbname);
- appendPQExpBuffer(str, "CREATE PUBLICATION %s FOR ALL TABLES",
+ appendPQExpBuffer(str, "CREATE PUBLICATION %s FOR ALL TABLES, ALL SEQUENCES",
ipubname_esc);
pg_log_debug("command is: %s", str->data);
diff --git a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
index 9252d1c3c5c..e11de069347 100644
--- a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
+++ b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
@@ -503,6 +503,14 @@ is($result, qq(0),
'the physical replication slot used as primary_slot_name has been removed'
);
+# Verify that the publication is configured to publish all tables and
+# all sequences.
+$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"
+);
+
# Insert rows on P
$node_p->safe_psql($db1, "INSERT INTO tbl1 VALUES('third row')");
$node_p->safe_psql($db2, "INSERT INTO tbl2 VALUES('row 1')");
--
2.53.0
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Include sequences in publications created by pg_createsubscriber
@ 2026-07-01 21:25 Peter Smith <[email protected]>
parent: vignesh C <[email protected]>
1 sibling, 0 replies; 23+ messages in thread
From: Peter Smith @ 2026-07-01 21:25 UTC (permalink / raw)
To: vignesh C <[email protected]>; +Cc: Shlok Kyal <[email protected]>; PostgreSQL Hackers <[email protected]>
Patch v6 LGTM.
======
Kind Regards,
Peter Smith.
Fujitsu Australia
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Include sequences in publications created by pg_createsubscriber
@ 2026-07-02 06:29 Shlok Kyal <[email protected]>
parent: vignesh C <[email protected]>
1 sibling, 1 reply; 23+ messages in thread
From: Shlok Kyal @ 2026-07-02 06:29 UTC (permalink / raw)
To: vignesh C <[email protected]>; +Cc: Peter Smith <[email protected]>; PostgreSQL Hackers <[email protected]>
>
> 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
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Include sequences in publications created by pg_createsubscriber
@ 2026-07-06 08:00 Shlok Kyal <[email protected]>
parent: Shlok Kyal <[email protected]>
0 siblings, 2 replies; 23+ messages in thread
From: Shlok Kyal @ 2026-07-06 08:00 UTC (permalink / raw)
To: vignesh C <[email protected]>; +Cc: Peter Smith <[email protected]>; PostgreSQL Hackers <[email protected]>
On Thu, 2 Jul 2026 at 11:59, Shlok Kyal <[email protected]> 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
Attachments:
[application/octet-stream] v7-0001-Include-sequences-in-publications-created-by-pg_c.patch (8.4K, ../../CANhcyEV-B0FzrZThZ3XAd0x5ZTj5BFfHQQ3OrHAJSTzeEJQzSQ@mail.gmail.com/2-v7-0001-Include-sequences-in-publications-created-by-pg_c.patch)
download | inline diff:
From 81a265c58048abb43397fe9494823981b2e062c5 Mon Sep 17 00:00:00 2001
From: Vignesh C <[email protected]>
Date: Fri, 19 Jun 2026 19:14:27 +0530
Subject: [PATCH v7] Include sequences in publications created by
pg_createsubscriber
pg_createsubscriber creates a publication on the publisher to establish
logical replication between the original primary and the converted
subscriber. Currently, the publication is created with FOR ALL TABLES,
which ensures that table data changes are replicated during the upgrade
process.
However, sequence state is not replicated as part of this workflow.
Recent additions to logical replication introduced support for publishing
all sequences and synchronizing sequence state through subscription
refresh. By creating publications with FOR ALL TABLES, ALL SEQUENCES,
pg_createsubscriber can take advantage of these capabilities and allow
sequence state to be synchronized before the upgraded node is promoted.
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.
---
doc/src/sgml/ref/pg_createsubscriber.sgml | 52 +++++++++++++------
src/bin/pg_basebackup/pg_createsubscriber.c | 4 +-
.../t/040_pg_createsubscriber.pl | 7 +++
3 files changed, 46 insertions(+), 17 deletions(-)
diff --git a/doc/src/sgml/ref/pg_createsubscriber.sgml b/doc/src/sgml/ref/pg_createsubscriber.sgml
index 3b3038d1891..f9b7102d4b3 100644
--- a/doc/src/sgml/ref/pg_createsubscriber.sgml
+++ b/doc/src/sgml/ref/pg_createsubscriber.sgml
@@ -48,11 +48,16 @@ PostgreSQL documentation
<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.
+ 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.
+ <application>pg_createsubscriber</application> must be run at the target
+ server.
</para>
<para>
@@ -61,7 +66,13 @@ PostgreSQL documentation
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. When using
+ internally generated publications, they automatically include all sequences
+ in the database; however, their values are not automatically synchronized.
+ 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.
</para>
<para>
@@ -75,7 +86,12 @@ PostgreSQL documentation
synchronization. For details, see the <command>CREATE SUBSCRIPTION</command>
<link linkend="sql-createsubscription-params-with-copy-data">
<literal>copy_data</literal></link> option.
+ </para>
+ <para>
+ To synchronize sequence values, run
+ <link linkend="sql-altersubscription-params-refresh-sequences">
+ <command>ALTER SUBSCRIPTION ... REFRESH SEQUENCES</command></link>.
</para>
</refsect1>
@@ -275,10 +291,10 @@ PostgreSQL documentation
<listitem>
<para>
<literal>publications</literal>:
- The <literal>FOR ALL TABLES</literal> publications established for this
- subscriber are always dropped; specifying this object type causes all
- other publications replicated from the source server to be dropped as
- well.
+ The <literal>FOR ALL TABLES, ALL SEQUENCES</literal> publications
+ established for this subscriber are always dropped; specifying this
+ object type causes all other publications replicated from the source
+ server to be dropped as well.
</para>
</listitem>
</itemizedlist>
@@ -323,7 +339,8 @@ PostgreSQL documentation
If a specified publication already exists on the publisher, it is reused.
It is useful to partially replicate the database if the specified
publication includes a list of tables. If the publication does not exist,
- it is automatically created with <literal>FOR ALL TABLES</literal>. Use
+ it is automatically created with
+ <literal>FOR ALL TABLES, ALL SEQUENCES</literal>. Use
<option>--dry-run</option> option to preview which publications will be
reused and which will be created.
</para>
@@ -532,10 +549,15 @@ PostgreSQL documentation
<step>
<para>
Create a publication and replication slot for each specified database on
- the source server. Each publication is created using <link
- linkend="sql-createpublication-params-for-all-tables"><literal>FOR ALL
- TABLES</literal></link>. If the <option>--publication</option> option
- is not specified, the publication has the following name pattern:
+ the source server. If the <option>--publication</option> option is not
+ 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:
<quote><literal>pg_createsubscriber_%u_%x</literal></quote> (parameter:
database <parameter>oid</parameter>, random <parameter>int</parameter>).
If the <option>--replication-slot</option> option is not specified, the
diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c
index 20b354aed56..cdaa5ad434a 100644
--- a/src/bin/pg_basebackup/pg_createsubscriber.c
+++ b/src/bin/pg_basebackup/pg_createsubscriber.c
@@ -1788,7 +1788,7 @@ wait_for_end_recovery(const char *conninfo, const struct CreateSubscriberOptions
}
/*
- * Create a publication that includes all tables in the database.
+ * Create a publication that includes all tables and sequences in the database.
*/
static void
create_publication(PGconn *conn, struct LogicalRepInfo *dbinfo)
@@ -1840,7 +1840,7 @@ create_publication(PGconn *conn, struct LogicalRepInfo *dbinfo)
pg_log_info("creating publication \"%s\" in database \"%s\"",
dbinfo->pubname, dbinfo->dbname);
- appendPQExpBuffer(str, "CREATE PUBLICATION %s FOR ALL TABLES",
+ appendPQExpBuffer(str, "CREATE PUBLICATION %s FOR ALL TABLES, ALL SEQUENCES",
ipubname_esc);
pg_log_debug("command is: %s", str->data);
diff --git a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
index 9252d1c3c5c..d4d6bea6767 100644
--- a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
+++ b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
@@ -503,6 +503,13 @@ is($result, qq(0),
'the physical replication slot used as primary_slot_name has been removed'
);
+# Verify that the publication is configured to publish all tables and
+# all sequences.
+$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");
+
# Insert rows on P
$node_p->safe_psql($db1, "INSERT INTO tbl1 VALUES('third row')");
$node_p->safe_psql($db2, "INSERT INTO tbl2 VALUES('row 1')");
--
2.34.1
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Include sequences in publications created by pg_createsubscriber
@ 2026-07-06 10:57 Ashutosh Sharma <[email protected]>
parent: Shlok Kyal <[email protected]>
1 sibling, 0 replies; 23+ messages in thread
From: Ashutosh Sharma @ 2026-07-06 10:57 UTC (permalink / raw)
To: Shlok Kyal <[email protected]>; +Cc: vignesh C <[email protected]>; Peter Smith <[email protected]>; PostgreSQL Hackers <[email protected]>
Hi,
On Mon, Jul 6, 2026 at 1:31 PM Shlok Kyal <[email protected]> wrote:
>
> On Thu, 2 Jul 2026 at 11:59, Shlok Kyal <[email protected]> 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.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Include sequences in publications created by pg_createsubscriber
@ 2026-07-07 06:20 Peter Smith <[email protected]>
parent: Shlok Kyal <[email protected]>
1 sibling, 1 reply; 23+ messages in thread
From: Peter Smith @ 2026-07-07 06:20 UTC (permalink / raw)
To: Shlok Kyal <[email protected]>; +Cc: vignesh C <[email protected]>; PostgreSQL Hackers <[email protected]>
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
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Include sequences in publications created by pg_createsubscriber
@ 2026-07-08 07:24 vignesh C <[email protected]>
parent: Peter Smith <[email protected]>
0 siblings, 1 reply; 23+ messages in thread
From: vignesh C @ 2026-07-08 07:24 UTC (permalink / raw)
To: Peter Smith <[email protected]>; +Cc: Shlok Kyal <[email protected]>; PostgreSQL Hackers <[email protected]>
On Tue, 7 Jul 2026 at 11:50, Peter Smith <[email protected]> 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...
Regards,
Vignesh
Attachments:
[application/x-patch] v8-0001-Include-sequences-in-publications-created-by-pg_c.patch (10.1K, ../../CALDaNm3Xxj+hwXtYh-Xxwevh8nyCbBUEzjJBRXpxoRDTE234pw@mail.gmail.com/2-v8-0001-Include-sequences-in-publications-created-by-pg_c.patch)
download | inline diff:
From 33b278d3a3257deca88b74b55e14b822e3d10db0 Mon Sep 17 00:00:00 2001
From: Vignesh C <[email protected]>
Date: Fri, 19 Jun 2026 19:14:27 +0530
Subject: [PATCH v8] Include sequences in publications created by
pg_createsubscriber
pg_createsubscriber creates a publication on the publisher to establish
logical replication between the original primary and the converted
subscriber. Currently, the publication is created with FOR ALL TABLES,
which ensures that table data changes are replicated during the upgrade
process.
However, sequence state is not replicated as part of this workflow.
Recent additions to logical replication introduced support for publishing
all sequences and synchronizing sequence state through subscription
refresh. By creating publications with FOR ALL TABLES, ALL SEQUENCES,
pg_createsubscriber can take advantage of these capabilities and allow
sequence state to be synchronized before the upgraded node is promoted.
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.
---
doc/src/sgml/ref/pg_createsubscriber.sgml | 59 ++++++++++++-------
src/bin/pg_basebackup/pg_createsubscriber.c | 4 +-
.../t/040_pg_createsubscriber.pl | 22 ++++++-
3 files changed, 62 insertions(+), 23 deletions(-)
diff --git a/doc/src/sgml/ref/pg_createsubscriber.sgml b/doc/src/sgml/ref/pg_createsubscriber.sgml
index 3b3038d1891..3b7ebb01ebf 100644
--- a/doc/src/sgml/ref/pg_createsubscriber.sgml
+++ b/doc/src/sgml/ref/pg_createsubscriber.sgml
@@ -48,11 +48,16 @@ PostgreSQL documentation
<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.
+ 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. When
+ using internally generated publications, 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. <application>pg_createsubscriber</application> must be run at
+ the target server.
</para>
<para>
@@ -61,7 +66,13 @@ PostgreSQL documentation
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. When using
+ internally generated publications, the publications automatically include
+ all sequences in the database; however, sequence values are not
+ automatically synchronized. For user-specified publications, sequences are
+ only replicated if they were explicitly added to those publications prior to
+ running the tool, and as with the default mode, sequence values must be
+ manually synchronized when needed.
</para>
<para>
@@ -75,7 +86,13 @@ PostgreSQL documentation
synchronization. For details, see the <command>CREATE SUBSCRIPTION</command>
<link linkend="sql-createsubscription-params-with-copy-data">
<literal>copy_data</literal></link> option.
+ </para>
+ <para>
+ To synchronize sequence values, run
+ <link linkend="sql-altersubscription-params-refresh-sequences">
+ <command>ALTER SUBSCRIPTION ... REFRESH SEQUENCES</command></link> on each
+ subscription at the target server.
</para>
</refsect1>
@@ -275,10 +292,10 @@ PostgreSQL documentation
<listitem>
<para>
<literal>publications</literal>:
- The <literal>FOR ALL TABLES</literal> publications established for this
- subscriber are always dropped; specifying this object type causes all
- other publications replicated from the source server to be dropped as
- well.
+ The <literal>FOR ALL TABLES, ALL SEQUENCES</literal> publications
+ established for this subscriber are always dropped; specifying this
+ object type causes all other publications replicated from the source
+ server to be dropped as well.
</para>
</listitem>
</itemizedlist>
@@ -323,7 +340,8 @@ PostgreSQL documentation
If a specified publication already exists on the publisher, it is reused.
It is useful to partially replicate the database if the specified
publication includes a list of tables. If the publication does not exist,
- it is automatically created with <literal>FOR ALL TABLES</literal>. Use
+ it is automatically created with
+ <literal>FOR ALL TABLES, ALL SEQUENCES</literal>. Use
<option>--dry-run</option> option to preview which publications will be
reused and which will be created.
</para>
@@ -531,15 +549,16 @@ PostgreSQL documentation
<step>
<para>
- Create a publication and replication slot for each specified database on
- the source server. Each publication is created using <link
- linkend="sql-createpublication-params-for-all-tables"><literal>FOR ALL
- TABLES</literal></link>. If the <option>--publication</option> option
- is not specified, the publication has the following name pattern:
- <quote><literal>pg_createsubscriber_%u_%x</literal></quote> (parameter:
- database <parameter>oid</parameter>, random <parameter>int</parameter>).
- If the <option>--replication-slot</option> option is not specified, the
- replication slot has the following name pattern:
+ 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>. These generated publications has the
+ following name pattern:
<quote><literal>pg_createsubscriber_%u_%x</literal></quote> (parameters:
database <parameter>oid</parameter>, random <parameter>int</parameter>).
These replication slots will be used by the subscriptions in a future
diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c
index 20b354aed56..cdaa5ad434a 100644
--- a/src/bin/pg_basebackup/pg_createsubscriber.c
+++ b/src/bin/pg_basebackup/pg_createsubscriber.c
@@ -1788,7 +1788,7 @@ wait_for_end_recovery(const char *conninfo, const struct CreateSubscriberOptions
}
/*
- * Create a publication that includes all tables in the database.
+ * Create a publication that includes all tables and sequences in the database.
*/
static void
create_publication(PGconn *conn, struct LogicalRepInfo *dbinfo)
@@ -1840,7 +1840,7 @@ create_publication(PGconn *conn, struct LogicalRepInfo *dbinfo)
pg_log_info("creating publication \"%s\" in database \"%s\"",
dbinfo->pubname, dbinfo->dbname);
- appendPQExpBuffer(str, "CREATE PUBLICATION %s FOR ALL TABLES",
+ appendPQExpBuffer(str, "CREATE PUBLICATION %s FOR ALL TABLES, ALL SEQUENCES",
ipubname_esc);
pg_log_debug("command is: %s", str->data);
diff --git a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
index 9252d1c3c5c..94cf47ae683 100644
--- a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
+++ b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
@@ -434,11 +434,16 @@ command_fails_like(
qr/options --publication and -a\/--all cannot be used together/,
'fail if --publication is used with --all');
-# run pg_createsubscriber with '--all' option
+# Remove any existing internal log files so that only the output from the
+# following pg_createsubscriber invocation is verified.
+unlink $_ for @internal_log_files;
+
+# run pg_createsubscriber with '--all' with debug verbose logging enabled
my ($stdout, $stderr) = run_command(
[
'pg_createsubscriber',
'--verbose',
+ '--verbose',
'--dry-run',
'--recovery-timeout' => $PostgreSQL::Test::Utils::timeout_default,
'--pgdata' => $node_s->data_dir,
@@ -446,6 +451,7 @@ my ($stdout, $stderr) = run_command(
'--socketdir' => $node_s->host,
'--subscriber-port' => $node_s->port,
'--all',
+ '--logdir' => $logdir,
],
'run pg_createsubscriber with --all');
@@ -458,6 +464,20 @@ is(scalar(() = $stderr =~ /would create the replication slot/g),
is(scalar(() = $stderr =~ /would create subscription/g),
3, "verify subscriptions are created for all databases");
+# Read the internal log generated by the above invocation.
+@internal_log_files = glob "$logdir/*/pg_createsubscriber_internal.log";
+is(scalar(@internal_log_files),
+ 1, "pg_createsubscriber_internal.log file was recreated");
+
+$internal_log = slurp_file($internal_log_files[0]);
+
+# Verify that the generated CREATE PUBLICATION command includes both
+# FOR ALL TABLES and ALL SEQUENCES.
+like(
+ $internal_log,
+ qr/CREATE PUBLICATION "pg_createsubscriber_[^"]+" FOR ALL TABLES, ALL SEQUENCES/,
+ "log shows CREATE PUBLICATION with both all tables and all sequences");
+
# Create a user-defined publication, and a table that is not a member of that
# publication.
$node_p->safe_psql(
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Include sequences in publications created by pg_createsubscriber
@ 2026-07-09 01:36 Peter Smith <[email protected]>
parent: vignesh C <[email protected]>
0 siblings, 1 reply; 23+ messages in thread
From: Peter Smith @ 2026-07-09 01:36 UTC (permalink / raw)
To: vignesh C <[email protected]>; +Cc: Shlok Kyal <[email protected]>; PostgreSQL Hackers <[email protected]>
Some review comments for v8
======
doc/src/sgml/ref/pg_createsubscriber.sgml
1.
+ which ensures each table is brought up to a synchronized state. When using
+ internally generated publications, the publications automatically include
+ all sequences in the database; however, sequence values are not
+ automatically synchronized.
Maybe "automatically" 2x in the same sentence is a bit much. The rest
can also be simpler.
SUGGESTION
Internally generated publications include all sequences in the
database, but their values are not automatically synchronized.
~~~
2.
+ 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>. These generated publications has the
+ following name pattern:
2a.
The space before the last <literal> tag here is probably not deliberate.
~
2b.
typo: /has the/have the/
======
Kind Regards,
Peter Smith.
Fujitsu Australia
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Include sequences in publications created by pg_createsubscriber
@ 2026-07-09 08:41 vignesh C <[email protected]>
parent: Peter Smith <[email protected]>
0 siblings, 0 replies; 23+ messages in thread
From: vignesh C @ 2026-07-09 08:41 UTC (permalink / raw)
To: Peter Smith <[email protected]>; +Cc: Shlok Kyal <[email protected]>; PostgreSQL Hackers <[email protected]>
On Thu, 9 Jul 2026 at 07:07, Peter Smith <[email protected]> wrote:
>
> Some review comments for v8
>
> ======
> doc/src/sgml/ref/pg_createsubscriber.sgml
>
> 1.
> + which ensures each table is brought up to a synchronized state. When using
> + internally generated publications, the publications automatically include
> + all sequences in the database; however, sequence values are not
> + automatically synchronized.
>
> Maybe "automatically" 2x in the same sentence is a bit much. The rest
> can also be simpler.
>
> SUGGESTION
> Internally generated publications include all sequences in the
> database, but their values are not automatically synchronized.
>
> ~~~
>
> 2.
> + 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>. These generated publications has the
> + following name pattern:
>
> 2a.
> The space before the last <literal> tag here is probably not deliberate.
>
> ~
>
> 2b.
> typo: /has the/have the/
Thanks for the comments, the attached v9 version patch has the changes
for the same.
Regards,
Vignesh
Attachments:
[application/octet-stream] v9-0001-Include-sequences-in-publications-created-by-pg_c.patch (9.9K, ../../CALDaNm0wW8Y-0EixBcJZZMphD1vcWg_dWdMyOjodB_U-ScxJPg@mail.gmail.com/2-v9-0001-Include-sequences-in-publications-created-by-pg_c.patch)
download | inline diff:
From e63e32cf8fb8e30d0d656c64b4b54d479035c2c9 Mon Sep 17 00:00:00 2001
From: Vignesh C <[email protected]>
Date: Fri, 19 Jun 2026 19:14:27 +0530
Subject: [PATCH v9] Include sequences in publications created by
pg_createsubscriber
pg_createsubscriber creates a publication on the publisher to establish
logical replication between the original primary and the converted
subscriber. Currently, the publication is created with FOR ALL TABLES,
which ensures that table data changes are replicated during the upgrade
process.
However, sequence state is not replicated as part of this workflow.
Recent additions to logical replication introduced support for publishing
all sequences and synchronizing sequence state through subscription
refresh. By creating publications with FOR ALL TABLES, ALL SEQUENCES,
pg_createsubscriber can take advantage of these capabilities and allow
sequence state to be synchronized before the upgraded node is promoted.
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.
---
doc/src/sgml/ref/pg_createsubscriber.sgml | 56 +++++++++++++------
src/bin/pg_basebackup/pg_createsubscriber.c | 4 +-
.../t/040_pg_createsubscriber.pl | 22 +++++++-
3 files changed, 62 insertions(+), 20 deletions(-)
diff --git a/doc/src/sgml/ref/pg_createsubscriber.sgml b/doc/src/sgml/ref/pg_createsubscriber.sgml
index 3b3038d1891..26fe31fc8ae 100644
--- a/doc/src/sgml/ref/pg_createsubscriber.sgml
+++ b/doc/src/sgml/ref/pg_createsubscriber.sgml
@@ -48,11 +48,16 @@ PostgreSQL documentation
<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.
+ 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. When
+ using internally generated publications, 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. <application>pg_createsubscriber</application> must be run at
+ the target server.
</para>
<para>
@@ -61,7 +66,12 @@ PostgreSQL documentation
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. Internally
+ generated publications include all sequences in the database, but their
+ values are not automatically synchronized. For user-specified publications,
+ sequences are only replicated if they were explicitly added to those
+ publications prior to running the tool, and as with the default mode,
+ sequence values must be manually synchronized when needed.
</para>
<para>
@@ -75,7 +85,13 @@ PostgreSQL documentation
synchronization. For details, see the <command>CREATE SUBSCRIPTION</command>
<link linkend="sql-createsubscription-params-with-copy-data">
<literal>copy_data</literal></link> option.
+ </para>
+ <para>
+ To synchronize sequence values, run
+ <link linkend="sql-altersubscription-params-refresh-sequences">
+ <command>ALTER SUBSCRIPTION ... REFRESH SEQUENCES</command></link> on each
+ subscription at the target server.
</para>
</refsect1>
@@ -275,10 +291,10 @@ PostgreSQL documentation
<listitem>
<para>
<literal>publications</literal>:
- The <literal>FOR ALL TABLES</literal> publications established for this
- subscriber are always dropped; specifying this object type causes all
- other publications replicated from the source server to be dropped as
- well.
+ The <literal>FOR ALL TABLES, ALL SEQUENCES</literal> publications
+ established for this subscriber are always dropped; specifying this
+ object type causes all other publications replicated from the source
+ server to be dropped as well.
</para>
</listitem>
</itemizedlist>
@@ -323,7 +339,8 @@ PostgreSQL documentation
If a specified publication already exists on the publisher, it is reused.
It is useful to partially replicate the database if the specified
publication includes a list of tables. If the publication does not exist,
- it is automatically created with <literal>FOR ALL TABLES</literal>. Use
+ it is automatically created with
+ <literal>FOR ALL TABLES, ALL SEQUENCES</literal>. Use
<option>--dry-run</option> option to preview which publications will be
reused and which will be created.
</para>
@@ -531,12 +548,17 @@ PostgreSQL documentation
<step>
<para>
- Create a publication and replication slot for each specified database on
- the source server. Each publication is created using <link
- linkend="sql-createpublication-params-for-all-tables"><literal>FOR ALL
- TABLES</literal></link>. If the <option>--publication</option> option
- is not specified, the publication has the following name pattern:
- <quote><literal>pg_createsubscriber_%u_%x</literal></quote> (parameter:
+ 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>. These generated publications have the
+ following name pattern:
+ <quote><literal>pg_createsubscriber_%u_%x</literal></quote> (parameters:
database <parameter>oid</parameter>, random <parameter>int</parameter>).
If the <option>--replication-slot</option> option is not specified, the
replication slot has the following name pattern:
diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c
index 20b354aed56..cdaa5ad434a 100644
--- a/src/bin/pg_basebackup/pg_createsubscriber.c
+++ b/src/bin/pg_basebackup/pg_createsubscriber.c
@@ -1788,7 +1788,7 @@ wait_for_end_recovery(const char *conninfo, const struct CreateSubscriberOptions
}
/*
- * Create a publication that includes all tables in the database.
+ * Create a publication that includes all tables and sequences in the database.
*/
static void
create_publication(PGconn *conn, struct LogicalRepInfo *dbinfo)
@@ -1840,7 +1840,7 @@ create_publication(PGconn *conn, struct LogicalRepInfo *dbinfo)
pg_log_info("creating publication \"%s\" in database \"%s\"",
dbinfo->pubname, dbinfo->dbname);
- appendPQExpBuffer(str, "CREATE PUBLICATION %s FOR ALL TABLES",
+ appendPQExpBuffer(str, "CREATE PUBLICATION %s FOR ALL TABLES, ALL SEQUENCES",
ipubname_esc);
pg_log_debug("command is: %s", str->data);
diff --git a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
index 9252d1c3c5c..94cf47ae683 100644
--- a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
+++ b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
@@ -434,11 +434,16 @@ command_fails_like(
qr/options --publication and -a\/--all cannot be used together/,
'fail if --publication is used with --all');
-# run pg_createsubscriber with '--all' option
+# Remove any existing internal log files so that only the output from the
+# following pg_createsubscriber invocation is verified.
+unlink $_ for @internal_log_files;
+
+# run pg_createsubscriber with '--all' with debug verbose logging enabled
my ($stdout, $stderr) = run_command(
[
'pg_createsubscriber',
'--verbose',
+ '--verbose',
'--dry-run',
'--recovery-timeout' => $PostgreSQL::Test::Utils::timeout_default,
'--pgdata' => $node_s->data_dir,
@@ -446,6 +451,7 @@ my ($stdout, $stderr) = run_command(
'--socketdir' => $node_s->host,
'--subscriber-port' => $node_s->port,
'--all',
+ '--logdir' => $logdir,
],
'run pg_createsubscriber with --all');
@@ -458,6 +464,20 @@ is(scalar(() = $stderr =~ /would create the replication slot/g),
is(scalar(() = $stderr =~ /would create subscription/g),
3, "verify subscriptions are created for all databases");
+# Read the internal log generated by the above invocation.
+@internal_log_files = glob "$logdir/*/pg_createsubscriber_internal.log";
+is(scalar(@internal_log_files),
+ 1, "pg_createsubscriber_internal.log file was recreated");
+
+$internal_log = slurp_file($internal_log_files[0]);
+
+# Verify that the generated CREATE PUBLICATION command includes both
+# FOR ALL TABLES and ALL SEQUENCES.
+like(
+ $internal_log,
+ qr/CREATE PUBLICATION "pg_createsubscriber_[^"]+" FOR ALL TABLES, ALL SEQUENCES/,
+ "log shows CREATE PUBLICATION with both all tables and all sequences");
+
# Create a user-defined publication, and a table that is not a member of that
# publication.
$node_p->safe_psql(
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 23+ messages in thread
end of thread, other threads:[~2026-07-09 08:41 UTC | newest]
Thread overview: 23+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-19 13:59 Include sequences in publications created by pg_createsubscriber vignesh C <[email protected]>
2026-06-22 12:39 ` Hayato Kuroda (Fujitsu) <[email protected]>
2026-06-23 10:19 ` vignesh C <[email protected]>
2026-06-23 01:03 ` Peter Smith <[email protected]>
2026-06-24 07:30 ` vignesh C <[email protected]>
2026-06-25 00:58 ` Peter Smith <[email protected]>
2026-06-25 05:35 ` Ashutosh Sharma <[email protected]>
2026-06-25 05:57 ` Peter Smith <[email protected]>
2026-06-25 10:00 ` vignesh C <[email protected]>
2026-06-25 22:19 ` Peter Smith <[email protected]>
2026-06-29 08:49 ` vignesh C <[email protected]>
2026-06-29 14:36 ` Shlok Kyal <[email protected]>
2026-06-30 06:47 ` vignesh C <[email protected]>
2026-07-01 02:46 ` Peter Smith <[email protected]>
2026-07-01 07:21 ` vignesh C <[email protected]>
2026-07-01 21:25 ` Peter Smith <[email protected]>
2026-07-02 06:29 ` Shlok Kyal <[email protected]>
2026-07-06 08:00 ` Shlok Kyal <[email protected]>
2026-07-06 10:57 ` Ashutosh Sharma <[email protected]>
2026-07-07 06:20 ` Peter Smith <[email protected]>
2026-07-08 07:24 ` vignesh C <[email protected]>
2026-07-09 01:36 ` Peter Smith <[email protected]>
2026-07-09 08:41 ` vignesh C <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox