public inbox for [email protected]
help / color / mirror / Atom feedFrom: vignesh C <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: Include sequences in publications created by pg_createsubscriber
Date: Fri, 19 Jun 2026 19:29:03 +0530
Message-ID: <CALDaNm2RYQRLjexRJOpiZOFkspp8PRgoh6S7d2y003TJ_TSSZA@mail.gmail.com> (raw)
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
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]
Subject: Re: Include sequences in publications created by pg_createsubscriber
In-Reply-To: <CALDaNm2RYQRLjexRJOpiZOFkspp8PRgoh6S7d2y003TJ_TSSZA@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