diff options
| -rw-r--r-- | doc/src/sgml/catalogs.sgml | 2 | ||||
| -rw-r--r-- | doc/src/sgml/config.sgml | 16 | ||||
| -rw-r--r-- | doc/src/sgml/func/func-sequence.sgml | 28 | ||||
| -rw-r--r-- | doc/src/sgml/logical-replication.sgml | 294 | ||||
| -rw-r--r-- | doc/src/sgml/monitoring.sgml | 5 | ||||
| -rw-r--r-- | doc/src/sgml/ref/alter_subscription.sgml | 15 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_subscription.sgml | 31 |
7 files changed, 350 insertions, 41 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 683f7c36f46..d7e48f61905 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -5199,8 +5199,8 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class=" </para> <para> In logical replication, this parameter also limits how often a failing - replication apply worker or table synchronization worker will be - respawned. + replication apply worker or table/sequence synchronization worker will + be respawned. </para> </listitem> </varlistentry> @@ -5341,8 +5341,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, and table/sequence + synchronization workers. </para> <para> Logical replication workers are taken from the pool defined by @@ -5365,10 +5365,14 @@ 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. + for tables during the subscription initialization or when new tables + are added. One additional worker is also needed for sequence + synchronization. </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 per + subscription. </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..e8d42b27c21 100644 --- a/doc/src/sgml/func/func-sequence.sgml +++ b/doc/src/sgml/func/func-sequence.sgml @@ -143,6 +143,34 @@ 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" id="func-pg-get-sequence-data"><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. + <structfield>last_value</structfield> is the last sequence value + written to disk. If caching is used, this value can be greater than the + last value handed out from the sequence. + <structfield>is_called</structfield> indicates whether the sequence has + been used. <structfield>page_lsn</structfield> is the LSN corresponding + to the most recent WAL record that modified this sequence relation. + </para> + <para> + This function is primarily intended for internal use by pg_dump and by + logical replication to synchronize sequences. It 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 d64ed9dc36b..79ecd09614f 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, sequences can be + synchronized at any time. For more information, see + <xref linkend="logical-replication-sequences"/>. </para> <para> @@ -1745,6 +1747,247 @@ 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 + on the subscriber: + </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 currently known to the subscription. + </para> + </listitem> + </itemizedlist> + </para> + + <para> + A <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> + The sequence synchronization worker validates that sequence definitions + match between publisher and subscriber. If mismatches exist, the worker + logs an error identifying them and exits. The apply worker continues + respawning the sequence synchronization worker until synchronization + succeeds. 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="sequences-out-of-sync"> + <title>Refreshing Out-of-Sync Sequences</title> + <para> + Subscriber sequence values will become out of sync as the publisher + advances them. + </para> + <para> + To detect this, compare the + <link linkend="catalog-pg-subscription-rel">pg_subscription_rel</link>.<structfield>srsublsn</structfield> + on the subscriber with the <structfield>page_lsn</structfield> obtained + from the <link linkend="func-pg-get-sequence-data"><function>pg_get_sequence_data</function></link> + function for the sequence on the publisher. Then run + <link linkend="sql-altersubscription-params-refresh-sequences"> + <command>ALTER SUBSCRIPTION ... REFRESH SEQUENCES</command></link> to + re-synchronize if necessary. + </para> + <warning> + <para> + Each sequence caches a block of values (typically 32) in memory before + generating a new WAL record, so its LSN advances only after the entire + cached batch has been consumed. As a result, sequence value drift cannot + be detected by LSN comparison when sequence increments fall within the + same cached block (typically 32 values). + </para> + </warning> + </sect2> + + <sect2 id="logical-replication-sequences-examples"> + <title>Examples</title> + + <para> + Create some sequences on the publisher. +<programlisting> +/* pub # */ CREATE SEQUENCE s1 START WITH 10 INCREMENT BY 1; +/* pub # */ CREATE SEQUENCE s2 START WITH 100 INCREMENT BY 10; +</programlisting></para> + + <para> + Create the same sequences on the subscriber. +<programlisting> +/* sub # */ CREATE SEQUENCE s1 START WITH 10 INCREMENT BY 1; +/* sub # */ CREATE SEQUENCE s2 START WITH 100 INCREMENT BY 10; +</programlisting></para> + + <para> + Advance the sequences on the publisher a few times. +<programlisting> +/* pub # */ SELECT nextval('s1'); + nextval +--------- + 10 +(1 row) +/* pub # */ SELECT nextval('s1'); + nextval +--------- + 11 +(1 row) +/* pub # */ SELECT nextval('s2'); + nextval +--------- + 100 +(1 row) +/* pub # */ SELECT nextval('s2'); + nextval +--------- + 110 +(1 row) +</programlisting></para> + + <para> + Check the sequence page LSNs on the publisher. +<programlisting> +/* pub # */ SELECT * FROM pg_get_sequence_data('s1'); + last_value | is_called | page_lsn +------------+-----------+------------ + 11 | t | 0/0178F9E0 +(1 row) +/* pub # */ SELECT * FROM pg_get_sequence_data('s2'); + last_value | is_called | page_lsn +------------+-----------+------------ + 110 | t | 0/0178FAB0 +(1 row) +</programlisting></para> + + <para> + Create a publication for the sequences. +<programlisting> +/* pub # */ CREATE PUBLICATION pub1 FOR ALL SEQUENCES; +</programlisting></para> + + <para> + Subscribe to the publication. +<programlisting> +/* sub # */ CREATE SUBSCRIPTION sub1 +/* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=sub1' +/* sub - */ PUBLICATION pub1; +</programlisting></para> + + <para> + Verify that the initial sequence values are synchronized. +<programlisting> +/* sub # */ SELECT last_value, is_called FROM s1; + last_value | is_called +------------+----------- + 11 | t +(1 row) + +/* sub # */ SELECT last_value, is_called FROM s2; + last_value | is_called +------------+----------- + 110 | t +(1 row) +</programlisting></para> + + <para> + Confirm that the sequence page LSNs on the publisher have been recorded + on the subscriber. +<programlisting> +/* sub # */ SELECT srrelid::regclass, srsublsn FROM pg_subscription_rel; + srrelid | srsublsn +---------+------------ + s1 | 0/0178F9E0 + s2 | 0/0178FAB0 +(2 rows) +</programlisting></para> + + <para> + Advance the sequences on the publisher 50 more times. +<programlisting> +/* pub # */ SELECT nextval('s1') FROM generate_series(1,50); +/* pub # */ SELECT nextval('s2') FROM generate_series(1,50); +</programlisting></para> + + <para> + Check the sequence page LSNs on the publisher. +<programlisting> +/* pub # */ SELECT * FROM pg_get_sequence_data('s1'); + last_value | is_called | page_lsn +------------+-----------+------------ + 61 | t | 0/017CED28 +(1 row) + +/* pub # */ SELECT * FROM pg_get_sequence_data('s2'); + last_value | is_called | page_lsn +------------+-----------+------------ + 610 | t | 0/017CEDF8 +(1 row) +</programlisting></para> + + <para> + The difference between the sequence page LSNs on the publisher and the + sequence page LSNs on the subscriber indicates that the sequences are out + of sync. Re-synchronize all sequences known to the subscriber using + <link linkend="sql-altersubscription-params-refresh-sequences"> + <command>ALTER SUBSCRIPTION ... REFRESH SEQUENCES</command></link>. +<programlisting> +/* sub # */ ALTER SUBSCRIPTION sub1 REFRESH SEQUENCES; +</programlisting></para> + + <para> + Recheck the sequences on the subscriber. +<programlisting> +/* sub # */ SELECT last_value, is_called FROM s1; + last_value | is_called +------------+----------- + 61 | t +(1 row) + +/* sub # */ SELECT last_value, is_called FROM s2; + last_value | is_called +------------+----------- + 610 | t +(1 row) +</programlisting></para> + </sect2> + </sect1> + <sect1 id="logical-replication-conflicts"> <title>Conflicts</title> @@ -2090,16 +2333,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> @@ -2290,9 +2536,9 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER </para> <para> - In order to be able to copy the initial table data, the role used for the - replication connection must have the <literal>SELECT</literal> privilege on - a published table (or be a superuser). + In order to be able to copy the initial table or sequence data, the role + used for the replication connection must have the <literal>SELECT</literal> + privilege on a published table or sequence (or be a superuser). </para> <para> @@ -2303,8 +2549,8 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER <para> To add tables to a publication, the user must have ownership rights on the table. To add all tables in schema to a publication, the user must be a - superuser. To create a publication that publishes all tables or all tables in - schema automatically, the user must be a superuser. + superuser. To create a publication that publishes all tables, all tables in + schema, or all sequences automatically, the user must be a superuser. </para> <para> @@ -2329,8 +2575,11 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER privileges of the subscription owner. However, when performing an insert, update, delete, or truncate operation on a particular table, it will switch roles to the table owner and perform the operation with the table owner's - privileges. This means that the subscription owner needs to be able to - <literal>SET ROLE</literal> to each role that owns a replicated table. + privileges. Similarly, when synchronizing sequence data, it will switch to + the sequence owner's role and perform the operation using the sequence + owner's privileges. This means that the subscription owner needs to be able + to <literal>SET ROLE</literal> to each role that owns a replicated table or + sequence. </para> <para> @@ -2423,8 +2672,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, and + table/sequence synchronization workers. </para> <para> @@ -2437,8 +2686,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 2741c138593..7b9fa20df9e 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> diff --git a/doc/src/sgml/ref/alter_subscription.sgml b/doc/src/sgml/ref/alter_subscription.sgml index 8ab3b7fbd37..27c06439f4f 100644 --- a/doc/src/sgml/ref/alter_subscription.sgml +++ b/doc/src/sgml/ref/alter_subscription.sgml @@ -196,6 +196,12 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO < <command>ALTER SUBSCRIPTION ... REFRESH 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 <link linkend="sql-createsubscription-params-with-origin"><literal>origin</literal></link> @@ -225,6 +231,15 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO < data for all currently subscribed sequences. It does not add or remove sequences from the subscription to match the publication. </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> diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml index ed82cf1809e..197be0c6f6b 100644 --- a/doc/src/sgml/ref/create_subscription.sgml +++ b/doc/src/sgml/ref/create_subscription.sgml @@ -127,10 +127,10 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl <para> Since no connection is made when this option is - <literal>false</literal>, no tables are subscribed. To initiate - replication, you must manually create the replication slot, enable - the failover if required, enable the subscription, and refresh the - subscription. See + <literal>false</literal>, no tables and sequences are subscribed. To + initiate replication, you must manually create the replication slot, + enable the failover if required, enable the subscription, and refresh + the subscription. See <xref linkend="logical-replication-subscription-examples-deferred-slot"/> for examples. </para> @@ -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 has no effect 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 has no effect 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 has no effect 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 has no effect + for sequences. </para> <para> @@ -398,8 +407,8 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl <para> If true, all replication actions are performed as the subscription owner. If false, replication workers will perform actions on each - table as the owner of that table. The latter configuration is - generally much more secure; for details, see + table or sequence as the owner of that relation. The latter + configuration is generally much more secure; for details, see <xref linkend="logical-replication-security" />. The default is <literal>false</literal>. </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 has no effect 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 has no effect for + sequences. </para> <para> |