v20251020_2-0003-Documentation-for-sequence-synchronizati.patch
application/octet-stream
Filename: v20251020_2-0003-Documentation-for-sequence-synchronizati.patch
Type: application/octet-stream
Part: 2
Message:
RE: Logical Replication of sequences
From a3e7f34281ea5f7c00995cafd9150daef27b8b8f Mon Sep 17 00:00:00 2001
From: Zhijie Hou <houzj.fnst@fujitsu.com>
Date: Mon, 20 Oct 2025 15:41:14 +0800
Subject: [PATCH v20251020_2 3/3] Documentation for sequence synchronization
feature.
Documentation for sequence synchronization feature.
Author: Vignesh C
Reviewer: Amit Kapila, Shveta Malik, Dilip Kumar, Peter Smith, Nisha Moond
Discussion: https://www.postgresql.org/message-id/CAA4eK1LC+KJiAkSrpE_NwvNdidw9F2os7GERUeSxSKv71gXysQ@mail.gmail.com
---
doc/src/sgml/catalogs.sgml | 2 +-
doc/src/sgml/config.sgml | 16 +-
doc/src/sgml/func/func-sequence.sgml | 24 +++
doc/src/sgml/logical-replication.sgml | 239 ++++++++++++++++++++--
doc/src/sgml/monitoring.sgml | 14 +-
doc/src/sgml/ref/create_subscription.sgml | 19 +-
6 files changed, 285 insertions(+), 29 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 6c8a0f173c9..2fc63442980 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -6568,7 +6568,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
(references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>)
</para>
<para>
- Reference to relation
+ Reference to table or sequence
</para></entry>
</row>
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 0a2a8b49fdb..9d54f8b26ed 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5191,9 +5191,9 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
is taken into account.
</para>
<para>
- In logical replication, this parameter also limits how often a failing
- replication apply worker or table synchronization worker will be
- respawned.
+ In logical replication, this parameter also limits how quickly a
+ failing replication apply worker, table synchronization worker, or
+ sequence synchronization worker will be respawned.
</para>
</listitem>
</varlistentry>
@@ -5334,8 +5334,8 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
<listitem>
<para>
Specifies maximum number of logical replication workers. This includes
- leader apply workers, parallel apply workers, and table synchronization
- workers.
+ leader apply workers, parallel apply workers, table synchronization
+ workers and a sequence synchronization worker.
</para>
<para>
Logical replication workers are taken from the pool defined by
@@ -5358,10 +5358,12 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
<para>
Maximum number of synchronization workers per subscription. This
parameter controls the amount of parallelism of the initial data copy
- during the subscription initialization or when new tables are added.
+ during the subscription initialization or when new tables or sequences
+ are added.
</para>
<para>
- Currently, there can be only one synchronization worker per table.
+ Currently, there can be only one table synchronization worker per table
+ and one sequence synchronization worker to synchronize all sequences.
</para>
<para>
The synchronization workers are taken from the pool defined by
diff --git a/doc/src/sgml/func/func-sequence.sgml b/doc/src/sgml/func/func-sequence.sgml
index e9f5b4e8e6b..80e51e9e365 100644
--- a/doc/src/sgml/func/func-sequence.sgml
+++ b/doc/src/sgml/func/func-sequence.sgml
@@ -143,6 +143,30 @@ SELECT setval('myseq', 42, false); <lineannotation>Next <function>nextval</fu
or <literal>SELECT</literal> privilege on the last used sequence.
</para></entry>
</row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_get_sequence_data</primary>
+ </indexterm>
+ <function>pg_get_sequence_data</function> ( <type>regclass</type> )
+ <returnvalue>record</returnvalue>
+ ( <parameter>last_value</parameter> <type>bigint</type>,
+ <parameter>is_called</parameter> <type>bool</type>,
+ <parameter>page_lsn</parameter> <type>pg_lsn</type> )
+ </para>
+ <para>
+ Returns information about the sequence. <literal>last_value</literal>
+ indicates last sequence value set in sequence by nextval or setval,
+ <literal>is_called</literal> indicates whether the sequence has been
+ used, and <literal>page_lsn</literal> is the LSN corresponding to the
+ most recent WAL record that modified this sequence relation.
+ </para>
+ <para>
+ This function requires <literal>USAGE</literal>
+ or <literal>SELECT</literal> privilege on the sequence.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index b01f5e998b2..548aab31960 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -113,7 +113,9 @@
Publications may currently only contain tables or sequences. Objects must be
added explicitly, except when a publication is created using
<literal>FOR TABLES IN SCHEMA</literal>, <literal>FOR ALL TABLES</literal>,
- or <literal>FOR ALL SEQUENCES</literal>.
+ or <literal>FOR ALL SEQUENCES</literal>. Unlike tables, the current state of
+ sequences may be synchronized at any time. For more information, refer to
+ <xref linkend="logical-replication-sequences"/>.
</para>
<para>
@@ -1745,6 +1747,209 @@ Publications:
</note>
</sect1>
+ <sect1 id="logical-replication-sequences">
+ <title>Replicating Sequences</title>
+
+ <para>
+ To synchronize sequences from a publisher to a subscriber, first publish
+ them using <link linkend="sql-createpublication-params-for-all-sequences">
+ <command>CREATE PUBLICATION ... FOR ALL SEQUENCES</command></link> and then
+ at the subscriber side:
+ </para>
+
+ <para>
+ <itemizedlist>
+ <listitem>
+ <para>
+ use <link linkend="sql-createsubscription"><command>CREATE SUBSCRIPTION</command></link>
+ to initially synchronize the published sequences.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ use <link linkend="sql-altersubscription-params-refresh-publication">
+ <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>
+ to synchronize only newly added sequences.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ use <link linkend="sql-altersubscription-params-refresh-sequences">
+ <command>ALTER SUBSCRIPTION ... REFRESH SEQUENCES</command></link>
+ to re-synchronize all sequences.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ A new <firstterm>sequence synchronization worker</firstterm> will be started
+ after executing any of the above subscriber commands, and will exit once the
+ sequences are synchronized.
+ </para>
+ <para>
+ The ability to launch a sequence synchronization worker is limited by the
+ <link linkend="guc-max-sync-workers-per-subscription">
+ <varname>max_sync_workers_per_subscription</varname></link>
+ configuration.
+ </para>
+
+ <sect2 id="sequence-definition-mismatches">
+ <title>Sequence Definition Mismatches</title>
+ <para>
+ During sequence synchronization, the sequence definitions of the publisher
+ and the subscriber are compared. An ERROR is logged listing all differing
+ sequences before the process exits. The apply worker detects this failure
+ and repeatedly respawns the sequence synchronization worker to continue
+ the synchronization process until all differences are resolved. See also
+ <link linkend="guc-wal-retrieve-retry-interval"><varname>wal_retrieve_retry_interval</varname></link>.
+ </para>
+ <para>
+ To resolve this, use
+ <link linkend="sql-altersequence"><command>ALTER SEQUENCE</command></link>
+ to align the subscriber's sequence parameters with those of the publisher.
+ </para>
+ </sect2>
+
+ <sect2 id="missing-sequences">
+ <title>Missing Sequences</title>
+ <para>
+ During sequence synchronization, if a sequence is dropped on the
+ publisher, the sequence synchronization worker will identify this and
+ remove it from sequence synchronization on the subscriber.
+ </para>
+ </sect2>
+
+ <sect2 id="sequences-out-of-sync">
+ <title>Refreshing Stale Sequences</title>
+ <para>
+ Subscriber side sequence values may frequently become out of sync due to
+ updates on the publisher.
+ </para>
+ <para>
+ To verify, compare the sequence values between the publisher and
+ subscriber, and if necessary, execute
+ <link linkend="sql-altersubscription-params-refresh-sequences">
+ <command>ALTER SUBSCRIPTION ... REFRESH SEQUENCES</command></link>.
+ </para>
+ </sect2>
+
+ <sect2 id="logical-replication-sequences-examples">
+ <title>Examples</title>
+
+ <para>
+ Create some sequences on the publisher.
+<programlisting>
+test_pub=# CREATE SEQUENCE s1 START WITH 10 INCREMENT BY 1;
+CREATE SEQUENCE
+test_pub=# CREATE SEQUENCE s2 START WITH 100 INCREMENT BY 10;
+CREATE SEQUENCE
+</programlisting></para>
+
+ <para>
+ Create the same sequences on the subscriber.
+<programlisting>
+test_sub=# CREATE SEQUENCE s1 START WITH 10 INCREMENT BY 1
+CREATE SEQUENCE
+test_sub=# CREATE SEQUENCE s2 START WITH 100 INCREMENT BY 10;
+CREATE SEQUENCE
+</programlisting></para>
+
+ <para>
+ Update the sequences at the publisher side a few times.
+<programlisting>
+test_pub=# SELECT nextval('s1');
+ nextval
+---------
+ 10
+(1 row)
+test_pub=# SELECT nextval('s1');
+ nextval
+---------
+ 11
+(1 row)
+test_pub=# SELECT nextval('s2');
+ nextval
+---------
+ 100
+(1 row)
+test_pub=# SELECT nextval('s2');
+ nextval
+---------
+ 110
+(1 row)
+</programlisting></para>
+
+ <para>
+ Create a publication for the sequences.
+<programlisting>
+test_pub=# CREATE PUBLICATION pub1 FOR ALL SEQUENCES;
+CREATE PUBLICATION
+</programlisting></para>
+
+ <para>
+ Subscribe to the publication.
+<programlisting>
+test_sub=# CREATE SUBSCRIPTION sub1
+test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=sub1'
+test_sub-# PUBLICATION pub1;
+CREATE SUBSCRIPTION
+</programlisting></para>
+
+ <para>
+ Observe that initial sequence values are synchronized.
+<programlisting>
+test_sub=# SELECT * FROM s1;
+ last_value | log_cnt | is_called
+------------+---------+-----------
+ 11 | 31 | t
+(1 row)
+
+test_sub=# SELECT * FROM s2;
+ last_value | log_cnt | is_called
+------------+---------+-----------
+ 110 | 31 | t
+(1 row)
+</programlisting></para>
+
+ <para>
+ Update the sequences at the publisher side.
+<programlisting>
+test_pub=# SELECT nextval('s1');
+ nextval
+---------
+ 12
+(1 row)
+test_pub=# SELECT nextval('s2');
+ nextval
+---------
+ 120
+(1 row)
+</programlisting></para>
+
+ <para>
+ Re-synchronize all the sequences at the subscriber side using
+ <link linkend="sql-altersubscription-params-refresh-sequences">
+ <command>ALTER SUBSCRIPTION ... REFRESH SEQUENCES</command></link>.
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION sub1 REFRESH SEQUENCES;
+ALTER SUBSCRIPTION
+
+test_sub=# SELECT * FROM s1;
+ last_value | log_cnt | is_called
+------------+---------+-----------
+ 12 | 30 | t
+(1 row)
+
+test_sub=# SELECT * FROM s2
+ last_value | log_cnt | is_called
+------------+---------+-----------
+ 120 | 30 | t
+(1 row)
+</programlisting></para>
+ </sect2>
+ </sect1>
+
<sect1 id="logical-replication-conflicts">
<title>Conflicts</title>
@@ -2090,16 +2295,19 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER
<listitem>
<para>
- Sequence data is not replicated. The data in serial or identity columns
- backed by sequences will of course be replicated as part of the table,
- but the sequence itself would still show the start value on the
- subscriber. If the subscriber is used as a read-only database, then this
- should typically not be a problem. If, however, some kind of switchover
- or failover to the subscriber database is intended, then the sequences
- would need to be updated to the latest values, either by copying the
- current data from the publisher (perhaps
- using <command>pg_dump</command>) or by determining a sufficiently high
- value from the tables themselves.
+ Incremental sequence changes are not replicated. Although the data in
+ serial or identity columns backed by sequences will be replicated as part
+ of the table, the sequences themselves do not replicate ongoing changes.
+ On the subscriber, a sequence will retain the last value it synchronized
+ from the publisher. If the subscriber is used as a read-only database,
+ then this should typically not be a problem. If, however, some kind of
+ switchover or failover to the subscriber database is intended, then the
+ sequences would need to be updated to the latest values, either by
+ executing <link linkend="sql-altersubscription-params-refresh-sequences">
+ <command>ALTER SUBSCRIPTION ... REFRESH SEQUENCES</command></link>
+ or by copying the current data from the publisher (perhaps using
+ <command>pg_dump</command>) or by determining a sufficiently high value
+ from the tables themselves.
</para>
</listitem>
@@ -2423,8 +2631,8 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER
<para>
<link linkend="guc-max-logical-replication-workers"><varname>max_logical_replication_workers</varname></link>
must be set to at least the number of subscriptions (for leader apply
- workers), plus some reserve for the table synchronization workers and
- parallel apply workers.
+ workers), plus some reserve for the parallel apply workers, table synchronization workers, and a sequence
+ synchronization worker.
</para>
<para>
@@ -2437,8 +2645,9 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER
<para>
<link linkend="guc-max-sync-workers-per-subscription"><varname>max_sync_workers_per_subscription</varname></link>
- controls the amount of parallelism of the initial data copy during the
- subscription initialization or when new tables are added.
+ controls how many tables can be synchronized in parallel during
+ subscription initialization or when new tables are added. One additional
+ worker is also needed for sequence synchronization.
</para>
<para>
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index d5f0fb7ba7c..0b2402b6ea6 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -2045,8 +2045,9 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para>
<para>
Type of the subscription worker process. Possible types are
- <literal>apply</literal>, <literal>parallel apply</literal>, and
- <literal>table synchronization</literal>.
+ <literal>apply</literal>, <literal>parallel apply</literal>,
+ <literal>table synchronization</literal>, and
+ <literal>sequence synchronization</literal>.
</para></entry>
</row>
@@ -2192,6 +2193,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>sequence_sync_error_count</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of times an error occurred during the sequence synchronization
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>sync_error_count</structfield> <type>bigint</type>
diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml
index ed82cf1809e..05bf2f2f49f 100644
--- a/doc/src/sgml/ref/create_subscription.sgml
+++ b/doc/src/sgml/ref/create_subscription.sgml
@@ -228,7 +228,7 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
the initial synchronization requires all data types to have binary
send and receive functions, otherwise the synchronization will fail
(see <xref linkend="sql-createtype"/> for more about send/receive
- functions).
+ functions). This parameter is not applicable for sequences.
</para>
<para>
@@ -265,6 +265,12 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
<literal>copy_data = true</literal> can interact with the
<literal>origin</literal> parameter.
</para>
+ <para>
+ See <xref linkend="sequence-definition-mismatches"/>
+ for recommendations on how to handle any warnings about sequence
+ definition differences between the publisher and the subscriber,
+ which might occur when <literal>copy_data = true</literal>.
+ </para>
</listitem>
</varlistentry>
@@ -280,6 +286,7 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
temporary files and applied after the transaction is committed. Note
that if an error happens in a parallel apply worker, the finish LSN
of the remote transaction might not be reported in the server log.
+ This parameter is not applicable for sequences.
</para>
<caution>
@@ -310,7 +317,8 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
The value of this parameter overrides the
<xref linkend="guc-synchronous-commit"/> setting within this
subscription's apply worker processes. The default value
- is <literal>off</literal>.
+ is <literal>off</literal>. This parameter is not applicable for
+ sequences.
</para>
<para>
@@ -340,7 +348,8 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
<listitem>
<para>
Specifies whether two-phase commit is enabled for this subscription.
- The default is <literal>false</literal>.
+ The default is <literal>false</literal>. This parameter is not
+ applicable for sequences.
</para>
<para>
@@ -417,6 +426,7 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
changes that don't have an origin. Setting <literal>origin</literal>
to <literal>any</literal> means that the publisher sends changes
regardless of their origin. The default is <literal>any</literal>.
+ This parameter is not applicable for sequences.
</para>
<para>
See <xref linkend="sql-createsubscription-notes"/> for details of how
@@ -449,7 +459,8 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
<xref linkend="conflict-update-deleted"/> is enabled, and a physical
replication slot named <quote><literal>pg_conflict_detection</literal></quote>
is created on the subscriber to prevent the information for detecting
- conflicts from being removed.
+ conflicts from being removed. This parameter is not applicable for
+ sequences.
</para>
<para>
--
2.47.3