public inbox for [email protected]
help / color / mirror / Atom feedFrom: vignesh C <[email protected]>
To: Peter Smith <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Include sequences in publications created by pg_createsubscriber
Date: Mon, 29 Jun 2026 14:19:06 +0530
Message-ID: <CALDaNm3Kh83ufGbgJqc8Frn=UvqC3LAd43eKS=FRuEWaRxbp4w@mail.gmail.com> (raw)
In-Reply-To: <CAHut+PuaM2BB34207YDkdr85neU1Xe2+QkYY3aDMUinFF=96gQ@mail.gmail.com>
References: <CALDaNm2RYQRLjexRJOpiZOFkspp8PRgoh6S7d2y003TJ_TSSZA@mail.gmail.com>
<CAHut+PtGOeXr69qQRgq_DW=BEEix_6bDsgmR8Vk4_qbNy5BvAA@mail.gmail.com>
<CALDaNm05u0UvteGQf_9wKbUFujO0JvJk=GnTeZyxsdB0bmPtcg@mail.gmail.com>
<CAHut+PsKCgwTP-6TxdwcNT5FT3dTyJiVfEKMPJR1K3df0EbOqQ@mail.gmail.com>
<CALDaNm39kG3iweFqyS0=3UNOSzZn_13Cq5iEKqcyG7apXGPJbw@mail.gmail.com>
<CAHut+PuaM2BB34207YDkdr85neU1Xe2+QkYY3aDMUinFF=96gQ@mail.gmail.com>
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
view thread (23+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Include sequences in publications created by pg_createsubscriber
In-Reply-To: <CALDaNm3Kh83ufGbgJqc8Frn=UvqC3LAd43eKS=FRuEWaRxbp4w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox