PS_NITPICKS_202400809_seq_0004.txt
text/plain
Filename: PS_NITPICKS_202400809_seq_0004.txt
Type: text/plain
Part: 0
Message:
Re: Logical Replication of sequences
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index d0fdcde..bc2aacc 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -1571,49 +1571,88 @@ test_sub=# SELECT * FROM t1 ORDER BY id;
</sect1>
<sect1 id="logical-replication-sequences">
- <title>Sequences</title>
+ <title>Replicating Sequences</title>
<para>
- Sequences can be synchronized between a publisher and a subscriber using
- <link linkend="sql-createsubscription"><command>CREATE SUBSCRIPTION</command></link>
- to initially synchronize sequences, <link linkend="sql-altersubscription-params-refresh-publication">
- <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link> to
- synchronize any newly added sequences and <link linkend="sql-altersubscription-params-refresh-publication-sequences">
- <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION SEQUENCES</command></link>
- to re-synchronize all sequences. A new sequence synchronization worker will
- be started to synchronize the sequences after executing the above commands
- and will exit once the sequences are synchronized.
+ To replicate sequences from a publisher to a subscriber, first publish the
+ sequence using <link linkend="sql-createpublication-params-for-all-sequences">
+ <command>CREATE PUBLICATION ... FOR ALL SEQUENCES</command></link>.
</para>
<para>
- Sequence synchronization worker will be used from
- <link linkend="guc-max-sync-workers-per-subscription">
+ 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 any 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 sequence synchronization worker will be started to synchronize the
+ sequences after executing any of the above subscriber commands, and will
+ will exit once the sequences are synchronized.
+ </para>
+ <para>
+ The ability to launch a sequence synchronization worker will be limited by
+ the <link linkend="guc-max-sync-workers-per-subscription">
<varname>max_sync_workers_per_subscription</varname></link>
configuration.
</para>
- <sect2 id="sequences-definition-differ-publisher-subscriber">
- <title>Differences in Sequence Definitions Between Publisher and Subscriber</title>
+ <sect2 id="sequence-definition-mismatches">
+ <title>Sequence Definition Mismatches</title>
+ <warning>
+ <para>
+ If there are differences in sequence definitions between the publisher and
+ subscriber, a WARNING is logged.
+ </para>
+ </warning>
<para>
- If there are differences in sequence definitions between the publisher and
- subscriber, a WARNING is logged. To resolve this, use
+ 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.
Subsequently, execute <link linkend="sql-altersubscription-params-refresh-publication-sequences">
<command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION SEQUENCES</command></link>.
- It is advisable not to change sequence definitions on either the publisher
- or the subscriber until synchronization is complete and the
+ </para>
+ <para>
+ Changes to sequence definitions during the execution of
+ <link linkend="sql-altersubscription-params-refresh-publication-sequences">
+ <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION SEQUENCES</command></link>
+ may not be detected, potentially leading to inconsistent values. To avoid
+ this, refrain from modifying sequence definitions on both publisher and
+ subscriber until synchronization is complete and the
<link linkend="catalog-pg-subscription-rel">pg_subscription_rel</link>.<structfield>srsubstate</structfield>
reaches <literal>r</literal> (ready) state.
- </para>
+ </para>
</sect2>
<sect2 id="sequences-out-of-sync">
- <title>Handling Sequences Out of Sync Between Publisher and Subscriber</title>
+ <title>Refreshing Stale Sequences</title>
<para>
- Sequence values may frequently become out of sync due to updates on the
- publisher. To verify this, compare the sequences values between the
- publisher and subscriber and execute
+ Subscriber side sequence values may frequently become out of sync due to
+ updates on the publisher.
+ </para>
+ <para>
+ To verify this, compare the sequences values between the publisher and
+ subscriber and execute
<link linkend="sql-altersubscription-params-refresh-publication-sequences">
<command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION SEQUENCES</command></link>
if required.
@@ -1621,7 +1660,7 @@ test_sub=# SELECT * FROM t1 ORDER BY id;
</sect2>
<sect2 id="logical-replication-sequences-examples">
- <title>Examples: Synchronizing Sequences Between Publisher and Subscriber</title>
+ <title>Examples</title>
<para>
Create some test sequences on the publisher.
@@ -1642,7 +1681,7 @@ CREATE SEQUENCE
</programlisting></para>
<para>
- Update the sequences at the publisher side few times.
+ Update the sequences at the publisher side few times.
<programlisting>
test_pub=# SELECT nextval('s1');
nextval
@@ -1667,14 +1706,14 @@ test_pub=# SELECT nextval('s2');
</programlisting></para>
<para>
- Create publications for the sequences.
+ Create a publication for the sequences.
<programlisting>
test_pub=# CREATE PUBLICATION pub1 FOR ALL SEQUENCES;
CREATE PUBLICATION
</programlisting></para>
<para>
- Create subscriptions for the publications.
+ Subscribe to the publication.
<programlisting>
test_sub=# CREATE SUBSCRIPTION sub1
test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=sub1'
@@ -1683,7 +1722,7 @@ CREATE SUBSCRIPTION
</programlisting></para>
<para>
- Observe that initial sequence value is synchronized.
+ Observe that initial sequence values are synchronized.
<programlisting>
test_sub=# SELECT * FROM s1;
last_value | log_cnt | is_called
@@ -1699,7 +1738,7 @@ test_sub=# SELECT * FROM s2;
</programlisting></para>
<para>
- Update the sequneces at the publisher side.
+ Update the sequences at the publisher side.
<programlisting>
test_pub=# SELECT nextval('s1');
nextval
@@ -1714,7 +1753,9 @@ test_pub=# SELECT nextval('s2');
</programlisting></para>
<para>
- Re-synchronize all the sequences at the subscriber using ALTER SUBSCRIPTION REFRESH PUBLCIATIN SEQUENCES:
+ 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
@@ -1733,35 +1774,6 @@ test_sub=# SELECT * FROM s2
</programlisting></para>
</sect2>
- <sect2 id="sequence-synchronization-caveats">
- <title>Caveats</title>
-
- <para>
- At this writing, there are couple of limitations of the sequence
- replication. These will probably be fixed in future releases:
-
- <itemizedlist>
- <listitem>
- <para>
- Changes to sequence definitions during the execution of
- <link linkend="sql-altersubscription-params-refresh-publication-sequences">
- <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION SEQUENCES</command></link>
- may not be detected, potentially leading to inconsistent values. To avoid
- this, refrain from modifying sequence definitions on both publisher and
- subscriber until synchronization is complete and the
- <link linkend="catalog-pg-subscription-rel">pg_subscription_rel</link>.<structfield>srsubstate</structfield>
- reaches <literal>r</literal> (ready) state.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Incremental synchronization of sequences is not supported.
- </para>
- </listitem>
- </itemizedlist>
- </para>
- </sect2>
</sect1>
<sect1 id="logical-replication-conflicts">
@@ -1871,7 +1883,7 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER
<listitem>
<para>
- Incremental sequence changes is not replicated. The data in serial or
+ Incremental sequence changes are 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
diff --git a/doc/src/sgml/ref/alter_subscription.sgml b/doc/src/sgml/ref/alter_subscription.sgml
index 7d1399b..457a614 100644
--- a/doc/src/sgml/ref/alter_subscription.sgml
+++ b/doc/src/sgml/ref/alter_subscription.sgml
@@ -200,7 +200,7 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO <
<command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION SEQUENCES</command></link>
</para>
<para>
- See <xref linkend="sequences-definition-differ-publisher-subscriber"/> for recommendations on how
+ See <xref linkend="sequence-definition-mismatches"/> for recommendations on how
to handle any warnings about differences in the sequence definition
between the publisher and the subscriber, which might occur when
<literal>copy_data = true</literal>.
@@ -234,14 +234,14 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO <
will re-synchronize the sequence data for all subscribed sequences.
</para>
<para>
- See <xref linkend="sequences-out-of-sync"/> for recommendations on how
- to identify sequences and handle out of sync sequences.
- </para>
- <para>
- See <xref linkend="sequences-definition-differ-publisher-subscriber"/> for
+ See <xref linkend="sequence-definition-mismatches"/> for
recommendations on how to handle any warnings about differences in the
sequence definition between the publisher and the subscriber.
</para>
+ <para>
+ See <xref linkend="sequences-out-of-sync"/> for recommendations on how
+ to identify sequences and handle out-of-sync sequences.
+ </para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml
index d39976c..1b1c999 100644
--- a/doc/src/sgml/ref/create_subscription.sgml
+++ b/doc/src/sgml/ref/create_subscription.sgml
@@ -264,7 +264,7 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
<literal>origin</literal> parameter.
</para>
<para>
- See <xref linkend="sequences-definition-differ-publisher-subscriber"/>
+ See <xref linkend="sequence-definition-mismatches"/>
for recommendations on how to handle any warnings about differences in
the sequence definition between the publisher and the subscriber,
which might occur when <literal>copy_data = true</literal>.