v20250610-0006-Documentation-for-sequence-synchronization.patch
application/x-patch
Filename: v20250610-0006-Documentation-for-sequence-synchronization.patch
Type: application/x-patch
Part: 5
Message:
Re: Logical Replication of sequences
From d31ccc1ac2c659347a735a4dd7d5bfb33abf81be Mon Sep 17 00:00:00 2001
From: Vignesh <vignesh21@gmail.com>
Date: Thu, 22 May 2025 20:09:11 +0530
Subject: [PATCH v20250610 6/6] Documentation for sequence synchronization
feature.
Documentation for sequence synchronization feature.
---
doc/src/sgml/catalogs.sgml | 29 ++-
doc/src/sgml/config.sgml | 16 +-
doc/src/sgml/logical-replication.sgml | 244 ++++++++++++++++++++--
doc/src/sgml/monitoring.sgml | 14 +-
doc/src/sgml/ref/alter_subscription.sgml | 55 ++++-
doc/src/sgml/ref/create_publication.sgml | 84 ++++++--
doc/src/sgml/ref/create_subscription.sgml | 6 +
doc/src/sgml/system-views.sgml | 67 ++++++
8 files changed, 455 insertions(+), 60 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index fa86c569dc4..7d7571a995c 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -8149,16 +8149,19 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
</indexterm>
<para>
- The catalog <structname>pg_subscription_rel</structname> contains the
- state for each replicated relation in each subscription. This is a
- many-to-many mapping.
+ The catalog <structname>pg_subscription_rel</structname> stores the
+ state of each replicated table and sequence for each subscription. This
+ is a many-to-many mapping.
</para>
<para>
- This catalog only contains tables known to the subscription after running
- either <link linkend="sql-createsubscription"><command>CREATE SUBSCRIPTION</command></link> or
- <link linkend="sql-altersubscription"><command>ALTER SUBSCRIPTION ... REFRESH
- PUBLICATION</command></link>.
+ This catalog only contains tables and sequences known to the subscription
+ after running
+ <link linkend="sql-createsubscription"><command>CREATE SUBSCRIPTION</command></link> or
+ <link linkend="sql-altersubscription-params-refresh-publication">
+ <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link> or
+ <link linkend="sql-altersubscription-params-refresh-publication-sequences">
+ <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION SEQUENCES</command></link>.
</para>
<table>
@@ -8192,7 +8195,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>
@@ -8201,12 +8204,20 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
<structfield>srsubstate</structfield> <type>char</type>
</para>
<para>
- State code:
+ State code for the table or sequence.
+ </para>
+ <para>
+ State codes for tables:
<literal>i</literal> = initialize,
<literal>d</literal> = data is being copied,
<literal>f</literal> = finished table copy,
<literal>s</literal> = synchronized,
<literal>r</literal> = ready (normal replication)
+ </para>
+ <para>
+ State codes for sequences:
+ <literal>i</literal> = initialize,
+ <literal>r</literal> = ready
</para></entry>
</row>
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 021153b2a5f..751edee00f5 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5184,9 +5184,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 or table synchronization worker or
+ sequence synchronization worker will be respawned.
</para>
</listitem>
</varlistentry>
@@ -5327,8 +5327,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
@@ -5351,10 +5351,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/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 686dd441d02..cc5cd986d92 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -102,16 +102,20 @@
A <firstterm>publication</firstterm> can be defined on any physical
replication primary. The node where a publication is defined is referred to
as <firstterm>publisher</firstterm>. A publication is a set of changes
- generated from a table or a group of tables, and might also be described as
- a change set or replication set. Each publication exists in only one database.
+ generated from a table or a group of tables or the current state of all
+ sequences, and might also be described as a change set or replication set.
+ Each publication exists in only one database.
</para>
<para>
Publications are different from schemas and do not affect how the table is
accessed. Each table can be added to multiple publications if needed.
- Publications may currently only contain tables and all tables in schema.
- Objects must be added explicitly, except when a publication is created for
- <literal>ALL TABLES</literal>.
+ 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>, or <literal>FOR ALL TABLES</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>
@@ -1711,6 +1715,204 @@ Publications:
</note>
</sect1>
+ <sect1 id="logical-replication-sequences">
+ <title>Replicating Sequences</title>
+
+ <para>
+ To replicate 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>.
+ </para>
+
+ <para>
+ At the subscriber side:
+ <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-publication-sequences">
+ <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION 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>
+ <warning>
+ <para>
+ During sequence synchronization, the sequence definitions of the publisher
+ and the subscriber are compared. A WARNING is logged listing all differing
+ sequences before the process exits. The apply worker detects the 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>
+ </warning>
+ <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.
+ Then, execute <link linkend="sql-altersubscription-params-refresh-publication-sequences">
+ <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION SEQUENCES</command></link>.
+ </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-publication-sequences">
+ <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION 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 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-publication-sequences">
+ <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION SEQUENCES</command></link>.
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION 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>
@@ -2040,16 +2242,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-publication-sequences">
+ <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION 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>
@@ -2367,8 +2572,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>
@@ -2381,8 +2586,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 4265a22d4de..8fa27144da8 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -2030,8 +2030,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>
@@ -2177,6 +2178,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/alter_subscription.sgml b/doc/src/sgml/ref/alter_subscription.sgml
index fdc648d007f..0ecc91b6fc1 100644
--- a/doc/src/sgml/ref/alter_subscription.sgml
+++ b/doc/src/sgml/ref/alter_subscription.sgml
@@ -26,6 +26,7 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> SET PUBLICA
ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> ADD PUBLICATION <replaceable class="parameter">publication_name</replaceable> [, ...] [ WITH ( <replaceable class="parameter">publication_option</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> DROP PUBLICATION <replaceable class="parameter">publication_name</replaceable> [, ...] [ WITH ( <replaceable class="parameter">publication_option</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> REFRESH PUBLICATION [ WITH ( <replaceable class="parameter">refresh_option</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
+ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> REFRESH PUBLICATION SEQUENCES
ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> ENABLE
ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> DISABLE
ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">subscription_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )
@@ -67,6 +68,7 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO <
<para>
Commands <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command>,
+ <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION SEQUENCES</command>,
<command>ALTER SUBSCRIPTION ... {SET|ADD|DROP} PUBLICATION ...</command>
with <literal>refresh</literal> option as <literal>true</literal>,
<command>ALTER SUBSCRIPTION ... SET (failover = true|false)</command> and
@@ -158,30 +160,51 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO <
<term><literal>REFRESH PUBLICATION</literal></term>
<listitem>
<para>
- Fetch missing table information from publisher. This will start
+ Fetch missing table information from the publisher. This will start
replication of tables that were added to the subscribed-to publications
since <link linkend="sql-createsubscription">
<command>CREATE SUBSCRIPTION</command></link> or
the last invocation of <command>REFRESH PUBLICATION</command>.
</para>
+ <para>
+ Also, fetch missing sequence information from the publisher.
+ </para>
+
+ <para>
+ The system catalog <link linkend="catalog-pg-subscription-rel">pg_subscription_rel</link>
+ is updated to record all tables and sequences known to the subscription,
+ that are still part of the publication.
+ </para>
+
<para>
<replaceable>refresh_option</replaceable> specifies additional options for the
- refresh operation. The supported options are:
+ refresh operation. The only supported option is:
<variablelist>
<varlistentry>
<term><literal>copy_data</literal> (<type>boolean</type>)</term>
<listitem>
<para>
- Specifies whether to copy pre-existing data in the publications
- that are being subscribed to when the replication starts.
- The default is <literal>true</literal>.
+ Specifies whether to copy pre-existing data for tables and synchronize
+ sequences in the publications that are being subscribed to when the replication
+ starts. The default is <literal>true</literal>.
</para>
<para>
Previously subscribed tables are not copied, even if a table's row
filter <literal>WHERE</literal> clause has since been modified.
</para>
+ <para>
+ Previously subscribed sequences are not re-synchronized. To do that,
+ see <link linkend="sql-altersubscription-params-refresh-publication-sequences">
+ <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION SEQUENCES</command></link>.
+ </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>
<para>
See <xref linkend="sql-createsubscription-notes"/> for details of
how <literal>copy_data = true</literal> can interact with the
@@ -200,6 +223,28 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO <
</listitem>
</varlistentry>
+ <varlistentry id="sql-altersubscription-params-refresh-publication-sequences">
+ <term><literal>REFRESH PUBLICATION SEQUENCES</literal></term>
+ <listitem>
+ <para>
+ Fetch missing sequence information from the publisher, then re-synchronize
+ sequence data with the publisher. Unlike <link linkend="sql-altersubscription-params-refresh-publication">
+ <literal>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</literal></link> which
+ only synchronizes newly added sequences, <literal>REFRESH PUBLICATION SEQUENCES</literal>
+ will re-synchronize the sequence data for all subscribed sequences.
+ </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.
+ </para>
+ <para>
+ See <xref linkend="sequences-out-of-sync"/> for recommendations on how to
+ identify and handle out-of-sync sequences.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-altersubscription-params-enable">
<term><literal>ENABLE</literal></term>
<listitem>
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index 802630f2df1..8c794d9b8d0 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -22,14 +22,20 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
- [ FOR ALL TABLES
- | FOR <replaceable class="parameter">publication_object</replaceable> [, ... ] ]
+ [ FOR { <replaceable class="parameter">publication_object</replaceable> [, ... ] | ALL <replaceable class="parameter">all_publication_object</replaceable> [, ... ] } ]
[ WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
<phrase>where <replaceable class="parameter">publication_object</replaceable> is one of:</phrase>
TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] [ WHERE ( <replaceable class="parameter">expression</replaceable> ) ] [, ... ]
TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ]
+ ALL TABLES
+ ALL SEQUENCES
+
+<phrase>where <replaceable class="parameter">all_publication_object</replaceable> is one of:</phrase>
+
+ TABLES
+ SEQUENCES
</synopsis>
</refsynopsisdiv>
@@ -120,16 +126,6 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
</listitem>
</varlistentry>
- <varlistentry id="sql-createpublication-params-for-all-tables">
- <term><literal>FOR ALL TABLES</literal></term>
- <listitem>
- <para>
- Marks the publication as one that replicates changes for all tables in
- the database, including tables created in the future.
- </para>
- </listitem>
- </varlistentry>
-
<varlistentry id="sql-createpublication-params-for-tables-in-schema">
<term><literal>FOR TABLES IN SCHEMA</literal></term>
<listitem>
@@ -161,6 +157,31 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
</listitem>
</varlistentry>
+ <varlistentry id="sql-createpublication-params-for-all-tables">
+ <term><literal>FOR ALL TABLES</literal></term>
+ <listitem>
+ <para>
+ Marks the publication as one that replicates changes for all tables in
+ the database, including tables created in the future.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="sql-createpublication-params-for-all-sequences">
+ <term><literal>FOR ALL SEQUENCES</literal></term>
+ <listitem>
+ <para>
+ Marks the publication as one that synchronizes changes for all sequences
+ in the database, including sequences created in the future.
+ </para>
+
+ <para>
+ Only persistent sequences are included in the publication. Temporary
+ sequences and unlogged sequences are excluded from the publication.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-createpublication-params-with">
<term><literal>WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
<listitem>
@@ -188,6 +209,9 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
for logical replication does not take this parameter into account when
copying existing table data.
</para>
+ <para>
+ This parameter is not applicable for sequences.
+ </para>
</listitem>
</varlistentry>
@@ -224,6 +248,10 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
See <xref linkend="logical-replication-gencols"/> for more details about
logical replication of generated columns.
</para>
+
+ <para>
+ This parameter is not applicable for sequences.
+ </para>
</listitem>
</varlistentry>
@@ -259,6 +287,10 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
If this is enabled, <literal>TRUNCATE</literal> operations performed
directly on partitions are not replicated.
</para>
+
+ <para>
+ This parameter is not applicable for sequences.
+ </para>
</listitem>
</varlistentry>
</variablelist></para>
@@ -279,10 +311,10 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<title>Notes</title>
<para>
- If <literal>FOR TABLE</literal>, <literal>FOR ALL TABLES</literal> or
- <literal>FOR TABLES IN SCHEMA</literal> are not specified, then the
- publication starts out with an empty set of tables. That is useful if
- tables or schemas are to be added later.
+ If <literal>FOR TABLE</literal>, <literal>FOR TABLES IN SCHEMA</literal>,
+ <literal>FOR ALL TABLES</literal> or <literal>FOR ALL SEQUENCES</literal>
+ are not specified, then the publication starts out with an empty set of
+ tables. That is useful if tables or schemas are to be added later.
</para>
<para>
@@ -298,8 +330,9 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<para>
To add a table to a publication, the invoking user must have ownership
- rights on the table. The <command>FOR ALL TABLES</command> and
- <command>FOR TABLES IN SCHEMA</command> clauses require the invoking
+ rights on the table. The <literal>FOR TABLES IN SCHEMA</literal>,
+ <literal>FOR ALL TABLES</literal> and
+ <literal>FOR ALL SEQUENCES</literal> clauses require the invoking
user to be a superuser.
</para>
@@ -449,6 +482,21 @@ CREATE PUBLICATION sales_publication FOR TABLES IN SCHEMA marketing, sales;
<programlisting>
CREATE PUBLICATION users_filtered FOR TABLE users (user_id, firstname);
</programlisting></para>
+
+ <para>
+ Create a publication that publishes all sequences for synchronization:
+<programlisting>
+CREATE PUBLICATION all_sequences FOR ALL SEQUENCES;
+</programlisting>
+ </para>
+
+ <para>
+ Create a publication that publishes all changes in all tables, and
+ all sequences for synchronization:
+<programlisting>
+CREATE PUBLICATION all_tables_sequences FOR ALL TABLES, ALL SEQUENCES;
+</programlisting>
+ </para>
</refsect1>
<refsect1>
diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml
index 57dec28a5df..44308515bbb 100644
--- a/doc/src/sgml/ref/create_subscription.sgml
+++ b/doc/src/sgml/ref/create_subscription.sgml
@@ -263,6 +263,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>
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 986ae1f543d..e02cc1e7c5a 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -131,6 +131,11 @@
<entry>prepared transactions</entry>
</row>
+ <row>
+ <entry><link linkend="view-pg-publication-sequences"><structname>pg_publication_sequences</structname></link></entry>
+ <entry>publications and information of their associated sequences</entry>
+ </row>
+
<row>
<entry><link linkend="view-pg-publication-tables"><structname>pg_publication_tables</structname></link></entry>
<entry>publications and information of their associated tables</entry>
@@ -2475,6 +2480,68 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
</sect1>
+ <sect1 id="view-pg-publication-sequences">
+ <title><structname>pg_publication_sequences</structname></title>
+
+ <indexterm zone="view-pg-publication-sequences">
+ <primary>pg_publication_sequences</primary>
+ </indexterm>
+
+ <para>
+ The view <structname>pg_publication_sequences</structname> provides
+ information about the mapping between publications and information of
+ sequences they contain.
+ </para>
+
+ <table>
+ <title><structname>pg_publication_sequences</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>pubname</structfield> <type>name</type>
+ (references <link linkend="catalog-pg-publication"><structname>pg_publication</structname></link>.<structfield>pubname</structfield>)
+ </para>
+ <para>
+ Name of publication
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>schemaname</structfield> <type>name</type>
+ (references <link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.<structfield>nspname</structfield>)
+ </para>
+ <para>
+ Name of schema containing sequence
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>sequencename</structfield> <type>name</type>
+ (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>relname</structfield>)
+ </para>
+ <para>
+ Name of sequence
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
<sect1 id="view-pg-publication-tables">
<title><structname>pg_publication_tables</structname></title>
--
2.34.1