public inbox for [email protected]
help / color / mirror / Atom feedFrom: vignesh C <[email protected]>
To: Peter Smith <[email protected]>
Cc: Shlok Kyal <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Include sequences in publications created by pg_createsubscriber
Date: Wed, 8 Jul 2026 12:54:31 +0530
Message-ID: <CALDaNm3Xxj+hwXtYh-Xxwevh8nyCbBUEzjJBRXpxoRDTE234pw@mail.gmail.com> (raw)
In-Reply-To: <CAHut+Puu6AvPWG=dbcbEVunPJVsaUz19tUpqJhqg0yU2W97XWQ@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>
<CALDaNm3Kh83ufGbgJqc8Frn=UvqC3LAd43eKS=FRuEWaRxbp4w@mail.gmail.com>
<CANhcyEWFi-FkBERbKcK+TKVnBThF1_qUbZ2gC-Lo6P4z7_05EQ@mail.gmail.com>
<CALDaNm0G=RXa+mXwrqSLYATdT4NvhZ+bv6yrH8pB+xWzBDTSxQ@mail.gmail.com>
<CAHut+Pt4-P4N7m_SWVWvQzjh0hVd0JVgCqyxL5nU_eYKqMGxhA@mail.gmail.com>
<CALDaNm0F9oX18eH5MbNRWS5uux2KneDc2RwX__+7vm7nvgHsXg@mail.gmail.com>
<CANhcyEWKiBcV-VjNzCy3DZGmJ=_kso=yOnOVzNDLEgHBs42c0g@mail.gmail.com>
<CANhcyEV-B0FzrZThZ3XAd0x5ZTj5BFfHQQ3OrHAJSTzeEJQzSQ@mail.gmail.com>
<CAHut+Puu6AvPWG=dbcbEVunPJVsaUz19tUpqJhqg0yU2W97XWQ@mail.gmail.com>
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)
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], [email protected]
Subject: Re: Include sequences in publications created by pg_createsubscriber
In-Reply-To: <CALDaNm3Xxj+hwXtYh-Xxwevh8nyCbBUEzjJBRXpxoRDTE234pw@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