On Thu, 6 Nov 2025 at 16:07, Amit Kapila <[email protected]> wrote: > > On Thu, Nov 6, 2025 at 10:48 AM vignesh C <[email protected]> wrote: > > > > The patch also includes the change for buildfarm failure at [1]. > > [1] - > > https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=prion&dt=2025-11-05%2010%3A30%3A15 > > > > The attached v2 version patch has the changes for the same. > > > > Thanks for the patch, Pushed.
Thanks for pushing the patch, here is a rebased version of the remaining patches. This patch also addresses Peter's comments at [1 except the 4th comment (renaming existing column) for which I will post a patch separately. [1] - https://www.postgresql.org/message-id/CAHut%2BPtoLN0bRu7bNiSeF04dQQecoW-EXKMBX%3DHy0uqCvQa8MA%40mail.gmail.com Regards, Vignesh
From f2d8c32e84c35302ce2153d7baf19b2c4a4fc51f Mon Sep 17 00:00:00 2001 From: Vignesh C <[email protected]> Date: Mon, 3 Nov 2025 19:50:16 +0530 Subject: [PATCH v20251107] Add seq_sync_error_count to subscription statistics. This commit adds a new column, seq_sync_error_count, to the subscription statistics. This counter tracks the number of errors reported by the sequence synchronization worker. Since a single worker handles the synchronization of all sequences, this value may reflect errors from multiple sequences. --- doc/src/sgml/monitoring.sgml | 11 +++ src/backend/catalog/system_views.sql | 1 + .../replication/logical/sequencesync.c | 3 + src/backend/replication/logical/tablesync.c | 3 +- src/backend/replication/logical/worker.c | 18 ++-- .../utils/activity/pgstat_subscription.c | 27 ++++- src/backend/utils/adt/pgstatfuncs.c | 39 +++++--- src/include/catalog/pg_proc.dat | 6 +- src/include/pgstat.h | 6 +- src/test/regress/expected/rules.out | 3 +- src/test/subscription/t/026_stats.pl | 98 +++++++++++++------ 11 files changed, 151 insertions(+), 64 deletions(-) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index fc64df43e3f..2741c138593 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -2192,6 +2192,17 @@ 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>seq_sync_error_count</structfield> <type>bigint</type> + </para> + <para> + Number of times an error occurred in the sequence synchronization + worker. A single worker synchronizes all sequences, so one error + increment may represent failures across multiple sequences. + </para></entry> + </row> + <row> <entry role="catalog_table_entry"><para role="column_definition"> <structfield>sync_error_count</structfield> <type>bigint</type> diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index dec8df4f8ee..059e8778ca7 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1415,6 +1415,7 @@ CREATE VIEW pg_stat_subscription_stats AS ss.subid, s.subname, ss.apply_error_count, + ss.seq_sync_error_count, ss.sync_error_count, ss.confl_insert_exists, ss.confl_update_origin_differs, diff --git a/src/backend/replication/logical/sequencesync.c b/src/backend/replication/logical/sequencesync.c index a8a39bec508..e093e65e540 100644 --- a/src/backend/replication/logical/sequencesync.c +++ b/src/backend/replication/logical/sequencesync.c @@ -732,6 +732,9 @@ start_sequence_sync() * idle state. */ AbortOutOfAnyTransaction(); + pgstat_report_subscription_error(MySubscription->oid, + WORKERTYPE_SEQUENCESYNC); + PG_RE_THROW(); } } diff --git a/src/backend/replication/logical/tablesync.c b/src/backend/replication/logical/tablesync.c index e5a2856fd17..dcc6124cc73 100644 --- a/src/backend/replication/logical/tablesync.c +++ b/src/backend/replication/logical/tablesync.c @@ -1530,7 +1530,8 @@ start_table_sync(XLogRecPtr *origin_startpos, char **slotname) * idle state. */ AbortOutOfAnyTransaction(); - pgstat_report_subscription_error(MySubscription->oid, false); + pgstat_report_subscription_error(MySubscription->oid, + WORKERTYPE_TABLESYNC); PG_RE_THROW(); } diff --git a/src/backend/replication/logical/worker.c b/src/backend/replication/logical/worker.c index 28f61f96a1a..93970c6af29 100644 --- a/src/backend/replication/logical/worker.c +++ b/src/backend/replication/logical/worker.c @@ -5606,7 +5606,8 @@ start_apply(XLogRecPtr origin_startpos) * idle state. */ AbortOutOfAnyTransaction(); - pgstat_report_subscription_error(MySubscription->oid, !am_tablesync_worker()); + pgstat_report_subscription_error(MySubscription->oid, + MyLogicalRepWorker->type); PG_RE_THROW(); } @@ -5953,15 +5954,12 @@ DisableSubscriptionAndExit(void) RESUME_INTERRUPTS(); - if (am_leader_apply_worker() || am_tablesync_worker()) - { - /* - * Report the worker failed during either table synchronization or - * apply. - */ - pgstat_report_subscription_error(MyLogicalRepWorker->subid, - !am_tablesync_worker()); - } + /* + * Report the worker failed during sequence synchronization, table + * synchronization, or apply. + */ + pgstat_report_subscription_error(MyLogicalRepWorker->subid, + MyLogicalRepWorker->type); /* Disable the subscription */ StartTransactionCommand(); diff --git a/src/backend/utils/activity/pgstat_subscription.c b/src/backend/utils/activity/pgstat_subscription.c index f9a1c831a07..35916772b9d 100644 --- a/src/backend/utils/activity/pgstat_subscription.c +++ b/src/backend/utils/activity/pgstat_subscription.c @@ -17,6 +17,7 @@ #include "postgres.h" +#include "replication/worker_internal.h" #include "utils/pgstat_internal.h" @@ -24,7 +25,7 @@ * Report a subscription error. */ void -pgstat_report_subscription_error(Oid subid, bool is_apply_error) +pgstat_report_subscription_error(Oid subid, LogicalRepWorkerType wtype) { PgStat_EntryRef *entry_ref; PgStat_BackendSubEntry *pending; @@ -33,10 +34,25 @@ pgstat_report_subscription_error(Oid subid, bool is_apply_error) InvalidOid, subid, NULL); pending = entry_ref->pending; - if (is_apply_error) - pending->apply_error_count++; - else - pending->sync_error_count++; + switch (wtype) + { + case WORKERTYPE_APPLY: + pending->apply_error_count++; + break; + + case WORKERTYPE_SEQUENCESYNC: + pending->seq_sync_error_count++; + break; + + case WORKERTYPE_TABLESYNC: + pending->sync_error_count++; + break; + + default: + /* Should never happen. */ + Assert(0); + break; + } } /* @@ -115,6 +131,7 @@ pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait) #define SUB_ACC(fld) shsubent->stats.fld += localent->fld SUB_ACC(apply_error_count); + SUB_ACC(seq_sync_error_count); SUB_ACC(sync_error_count); for (int i = 0; i < CONFLICT_NUM_TYPES; i++) SUB_ACC(conflict_count[i]); diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index a710508979e..530d5e6a427 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -2203,44 +2203,49 @@ pg_stat_get_replication_slot(PG_FUNCTION_ARGS) Datum pg_stat_get_subscription_stats(PG_FUNCTION_ARGS) { -#define PG_STAT_GET_SUBSCRIPTION_STATS_COLS 12 +#define PG_STAT_GET_SUBSCRIPTION_STATS_COLS 13 Oid subid = PG_GETARG_OID(0); TupleDesc tupdesc; Datum values[PG_STAT_GET_SUBSCRIPTION_STATS_COLS] = {0}; bool nulls[PG_STAT_GET_SUBSCRIPTION_STATS_COLS] = {0}; PgStat_StatSubEntry *subentry; PgStat_StatSubEntry allzero; - int i = 0; + int i = 1; /* Get subscription stats */ subentry = pgstat_fetch_stat_subscription(subid); /* Initialise attributes information in the tuple descriptor */ tupdesc = CreateTemplateTupleDesc(PG_STAT_GET_SUBSCRIPTION_STATS_COLS); - TupleDescInitEntry(tupdesc, (AttrNumber) 1, "subid", + TupleDescInitEntry(tupdesc, (AttrNumber) i++, "subid", OIDOID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 2, "apply_error_count", + TupleDescInitEntry(tupdesc, (AttrNumber) i++, "apply_error_count", INT8OID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 3, "sync_error_count", + TupleDescInitEntry(tupdesc, (AttrNumber) i++, "seq_sync_error_count", INT8OID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 4, "confl_insert_exists", + TupleDescInitEntry(tupdesc, (AttrNumber) i++, "sync_error_count", INT8OID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 5, "confl_update_origin_differs", + TupleDescInitEntry(tupdesc, (AttrNumber) i++, "confl_insert_exists", INT8OID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 6, "confl_update_exists", + TupleDescInitEntry(tupdesc, (AttrNumber) i++, "confl_update_origin_differs", INT8OID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 7, "confl_update_deleted", + TupleDescInitEntry(tupdesc, (AttrNumber) i++, "confl_update_exists", INT8OID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 8, "confl_update_missing", + TupleDescInitEntry(tupdesc, (AttrNumber) i++, "confl_update_deleted", INT8OID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 9, "confl_delete_origin_differs", + TupleDescInitEntry(tupdesc, (AttrNumber) i++, "confl_update_missing", INT8OID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 10, "confl_delete_missing", + TupleDescInitEntry(tupdesc, (AttrNumber) i++, "confl_delete_origin_differs", INT8OID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 11, "confl_multiple_unique_conflicts", + TupleDescInitEntry(tupdesc, (AttrNumber) i++, "confl_delete_missing", INT8OID, -1, 0); - TupleDescInitEntry(tupdesc, (AttrNumber) 12, "stats_reset", + TupleDescInitEntry(tupdesc, (AttrNumber) i++, "confl_multiple_unique_conflicts", + INT8OID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) i, "stats_reset", TIMESTAMPTZOID, -1, 0); + + Assert(i == PG_STAT_GET_SUBSCRIPTION_STATS_COLS); + BlessTupleDesc(tupdesc); if (!subentry) @@ -2250,12 +2255,18 @@ pg_stat_get_subscription_stats(PG_FUNCTION_ARGS) subentry = &allzero; } + /* Reset index for building the result tuple values */ + i = 0; + /* subid */ values[i++] = ObjectIdGetDatum(subid); /* apply_error_count */ values[i++] = Int64GetDatum(subentry->apply_error_count); + /* seq_sync_error_count */ + values[i++] = Int64GetDatum(subentry->seq_sync_error_count); + /* sync_error_count */ values[i++] = Int64GetDatum(subentry->sync_error_count); diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 34b7fddb0e7..5cf9e12fcb9 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5704,9 +5704,9 @@ { oid => '6231', descr => 'statistics: information about subscription stats', proname => 'pg_stat_get_subscription_stats', provolatile => 's', proparallel => 'r', prorettype => 'record', proargtypes => 'oid', - proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,timestamptz}', - proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o}', - proargnames => '{subid,subid,apply_error_count,sync_error_count,confl_insert_exists,confl_update_origin_differs,confl_update_exists,confl_update_deleted,confl_update_missing,confl_delete_origin_differs,confl_delete_missing,confl_multiple_unique_conflicts,stats_reset}', + proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,timestamptz}', + proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o}', + proargnames => '{subid,subid,apply_error_count,seq_sync_error_count,sync_error_count,confl_insert_exists,confl_update_origin_differs,confl_update_exists,confl_update_deleted,confl_update_missing,confl_delete_origin_differs,confl_delete_missing,confl_multiple_unique_conflicts,stats_reset}', prosrc => 'pg_stat_get_subscription_stats' }, { oid => '6118', descr => 'statistics: information about subscription', proname => 'pg_stat_get_subscription', prorows => '10', proisstrict => 'f', diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 7ae503e71a2..a0610bb3e31 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -16,6 +16,7 @@ #include "portability/instr_time.h" #include "postmaster/pgarch.h" /* for MAX_XFN_CHARS */ #include "replication/conflict.h" +#include "replication/worker_internal.h" #include "utils/backend_progress.h" /* for backward compatibility */ /* IWYU pragma: export */ #include "utils/backend_status.h" /* for backward compatibility */ /* IWYU pragma: export */ #include "utils/pgstat_kind.h" @@ -108,6 +109,7 @@ typedef struct PgStat_FunctionCallUsage typedef struct PgStat_BackendSubEntry { PgStat_Counter apply_error_count; + PgStat_Counter seq_sync_error_count; PgStat_Counter sync_error_count; PgStat_Counter conflict_count[CONFLICT_NUM_TYPES]; } PgStat_BackendSubEntry; @@ -416,6 +418,7 @@ typedef struct PgStat_SLRUStats typedef struct PgStat_StatSubEntry { PgStat_Counter apply_error_count; + PgStat_Counter seq_sync_error_count; PgStat_Counter sync_error_count; PgStat_Counter conflict_count[CONFLICT_NUM_TYPES]; TimestampTz stat_reset_timestamp; @@ -769,7 +772,8 @@ extern PgStat_SLRUStats *pgstat_fetch_slru(void); * Functions in pgstat_subscription.c */ -extern void pgstat_report_subscription_error(Oid subid, bool is_apply_error); +extern void pgstat_report_subscription_error(Oid subid, + LogicalRepWorkerType wtype); extern void pgstat_report_subscription_conflict(Oid subid, ConflictType type); extern void pgstat_create_subscription(Oid subid); extern void pgstat_drop_subscription(Oid subid); diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 2bf968ae3d3..7c52181cbcb 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2191,6 +2191,7 @@ pg_stat_subscription| SELECT su.oid AS subid, pg_stat_subscription_stats| SELECT ss.subid, s.subname, ss.apply_error_count, + ss.seq_sync_error_count, ss.sync_error_count, ss.confl_insert_exists, ss.confl_update_origin_differs, @@ -2202,7 +2203,7 @@ pg_stat_subscription_stats| SELECT ss.subid, ss.confl_multiple_unique_conflicts, ss.stats_reset FROM pg_subscription s, - LATERAL pg_stat_get_subscription_stats(s.oid) ss(subid, apply_error_count, sync_error_count, confl_insert_exists, confl_update_origin_differs, confl_update_exists, confl_update_deleted, confl_update_missing, confl_delete_origin_differs, confl_delete_missing, confl_multiple_unique_conflicts, stats_reset); + LATERAL pg_stat_get_subscription_stats(s.oid) ss(subid, apply_error_count, seq_sync_error_count, sync_error_count, confl_insert_exists, confl_update_origin_differs, confl_update_exists, confl_update_deleted, confl_update_missing, confl_delete_origin_differs, confl_delete_missing, confl_multiple_unique_conflicts, stats_reset); pg_stat_sys_indexes| SELECT relid, indexrelid, schemaname, diff --git a/src/test/subscription/t/026_stats.pl b/src/test/subscription/t/026_stats.pl index 00a1c2fcd48..fc0bcee5187 100644 --- a/src/test/subscription/t/026_stats.pl +++ b/src/test/subscription/t/026_stats.pl @@ -21,10 +21,16 @@ $node_subscriber->start; sub create_sub_pub_w_errors { - my ($node_publisher, $node_subscriber, $db, $table_name) = @_; - # Initial table setup on both publisher and subscriber. On subscriber we - # create the same tables but with primary keys. Also, insert some data that - # will conflict with the data replicated from publisher later. + my ($node_publisher, $node_subscriber, $db, $table_name, $sequence_name) + = @_; + # Initial table and sequence setup on both publisher and subscriber. + # + # Tables: Created on both nodes, but the subscriber version includes + # primary keys and pre-populated data that will intentionally conflict with + # replicated data from the publisher. + # + # Sequences: Created on both nodes with different INCREMENT values to + # intentionally trigger replication conflicts. $node_publisher->safe_psql( $db, qq[ @@ -32,6 +38,7 @@ sub create_sub_pub_w_errors CREATE TABLE $table_name(a int); ALTER TABLE $table_name REPLICA IDENTITY FULL; INSERT INTO $table_name VALUES (1); + CREATE SEQUENCE $sequence_name; COMMIT; ]); $node_subscriber->safe_psql( @@ -40,35 +47,57 @@ sub create_sub_pub_w_errors BEGIN; CREATE TABLE $table_name(a int primary key); INSERT INTO $table_name VALUES (1); + CREATE SEQUENCE $sequence_name INCREMENT BY 10; COMMIT; ]); # Set up publication. my $pub_name = $table_name . '_pub'; + my $pub_seq_name = $sequence_name . '_pub'; my $publisher_connstr = $node_publisher->connstr . qq( dbname=$db); - $node_publisher->safe_psql($db, - qq(CREATE PUBLICATION $pub_name FOR TABLE $table_name)); + $node_publisher->safe_psql( + $db, + qq[ + CREATE PUBLICATION $pub_name FOR TABLE $table_name; + CREATE PUBLICATION $pub_seq_name FOR ALL SEQUENCES; + ]); # Create subscription. The tablesync for table on subscription will enter into - # infinite error loop due to violating the unique constraint. + # infinite error loop due to violating the unique constraint. The sequencesync + # will also fail due to different sequence increment values on publisher and + # subscriber. my $sub_name = $table_name . '_sub'; $node_subscriber->safe_psql($db, - qq(CREATE SUBSCRIPTION $sub_name CONNECTION '$publisher_connstr' PUBLICATION $pub_name) + qq(CREATE SUBSCRIPTION $sub_name CONNECTION '$publisher_connstr' PUBLICATION $pub_name, $pub_seq_name) ); $node_publisher->wait_for_catchup($sub_name); - # Wait for the tablesync error to be reported. + # Wait for the tablesync and sequencesync error to be reported. $node_subscriber->poll_query_until( $db, qq[ - SELECT sync_error_count > 0 - FROM pg_stat_subscription_stats - WHERE subname = '$sub_name' + SELECT count(1) = 1 FROM pg_stat_subscription_stats + WHERE subname = '$sub_name' AND seq_sync_error_count > 0 AND sync_error_count > 0 + ]) + or die + qq(Timed out while waiting for sequencesync errors and tablesync errors for subscription '$sub_name'); + + # Change the sequence INCREMENT value back to the default on the subscriber + # so it doesn't error out. + $node_subscriber->safe_psql($db, + qq(ALTER SEQUENCE $sequence_name INCREMENT 1)); + + # Wait for sequencesync to finish. + $node_subscriber->poll_query_until( + $db, + qq[ + SELECT count(1) = 1 FROM pg_subscription_rel + WHERE srrelid = '$sequence_name'::regclass AND srsubstate = 'r' ]) or die - qq(Timed out while waiting for tablesync errors for subscription '$sub_name'); + qq(Timed out while waiting for subscriber to synchronize data for sequence '$sequence_name'.); # Truncate test_tab1 so that tablesync worker can continue. $node_subscriber->safe_psql($db, qq(TRUNCATE $table_name)); @@ -136,14 +165,17 @@ is($result, qq(0), # Create the publication and subscription with sync and apply errors my $table1_name = 'test_tab1'; +my $sequence1_name = 'test_seq1'; my ($pub1_name, $sub1_name) = create_sub_pub_w_errors($node_publisher, $node_subscriber, $db, - $table1_name); + $table1_name, $sequence1_name); -# Apply errors, sync errors, and conflicts are > 0 and stats_reset timestamp is NULL +# Apply errors, sequencesync errors, tablesync errors, and conflicts are > 0 and stats_reset +# timestamp is NULL. is( $node_subscriber->safe_psql( $db, qq(SELECT apply_error_count > 0, + seq_sync_error_count > 0, sync_error_count > 0, confl_insert_exists > 0, confl_delete_missing > 0, @@ -151,8 +183,8 @@ is( $node_subscriber->safe_psql( FROM pg_stat_subscription_stats WHERE subname = '$sub1_name') ), - qq(t|t|t|t|t), - qq(Check that apply errors, sync errors, and conflicts are > 0 and stats_reset is NULL for subscription '$sub1_name'.) + qq(t|t|t|t|t|t), + qq(Check that apply errors, sequencesync errors, tablesync errors, and conflicts are > 0 and stats_reset is NULL for subscription '$sub1_name'.) ); # Reset a single subscription @@ -160,10 +192,12 @@ $node_subscriber->safe_psql($db, qq(SELECT pg_stat_reset_subscription_stats((SELECT subid FROM pg_stat_subscription_stats WHERE subname = '$sub1_name'))) ); -# Apply errors, sync errors, and conflicts are 0 and stats_reset timestamp is not NULL +# Apply errors, sequencesync errors, tablesync errors, and conflicts are 0 and +# stats_reset timestamp is not NULL. is( $node_subscriber->safe_psql( $db, qq(SELECT apply_error_count = 0, + seq_sync_error_count = 0, sync_error_count = 0, confl_insert_exists = 0, confl_delete_missing = 0, @@ -171,8 +205,8 @@ is( $node_subscriber->safe_psql( FROM pg_stat_subscription_stats WHERE subname = '$sub1_name') ), - qq(t|t|t|t|t), - qq(Confirm that apply errors, sync errors, and conflicts are 0 and stats_reset is not NULL after reset for subscription '$sub1_name'.) + qq(t|t|t|t|t|t), + qq(Confirm that apply errors, sequencesync errors, tablesync errors, and conflicts are 0 and stats_reset is not NULL after reset for subscription '$sub1_name'.) ); # Get reset timestamp @@ -198,14 +232,17 @@ is( $node_subscriber->safe_psql( # Make second subscription and publication my $table2_name = 'test_tab2'; +my $sequence2_name = 'test_seq2'; my ($pub2_name, $sub2_name) = create_sub_pub_w_errors($node_publisher, $node_subscriber, $db, - $table2_name); + $table2_name, $sequence2_name); -# Apply errors, sync errors, and conflicts are > 0 and stats_reset timestamp is NULL +# Apply errors, sequencesync errors, tablesync errors, and conflicts are > 0 +# and stats_reset timestamp is NULL is( $node_subscriber->safe_psql( $db, qq(SELECT apply_error_count > 0, + seq_sync_error_count > 0, sync_error_count > 0, confl_insert_exists > 0, confl_delete_missing > 0, @@ -213,18 +250,20 @@ is( $node_subscriber->safe_psql( FROM pg_stat_subscription_stats WHERE subname = '$sub2_name') ), - qq(t|t|t|t|t), - qq(Confirm that apply errors, sync errors, and conflicts are > 0 and stats_reset is NULL for sub '$sub2_name'.) + qq(t|t|t|t|t|t), + qq(Confirm that apply errors, sequencesync errors, tablesync errors, and conflicts are > 0 and stats_reset is NULL for sub '$sub2_name'.) ); # Reset all subscriptions $node_subscriber->safe_psql($db, qq(SELECT pg_stat_reset_subscription_stats(NULL))); -# Apply errors, sync errors, and conflicts are 0 and stats_reset timestamp is not NULL +# Apply errors, sequencesync errors, tablesync errors, and conflicts are 0 and +# stats_reset timestamp is not NULL. is( $node_subscriber->safe_psql( $db, qq(SELECT apply_error_count = 0, + seq_sync_error_count = 0, sync_error_count = 0, confl_insert_exists = 0, confl_delete_missing = 0, @@ -232,13 +271,14 @@ is( $node_subscriber->safe_psql( FROM pg_stat_subscription_stats WHERE subname = '$sub1_name') ), - qq(t|t|t|t|t), - qq(Confirm that apply errors, sync errors, and conflicts are 0 and stats_reset is not NULL for sub '$sub1_name' after reset.) + qq(t|t|t|t|t|t), + qq(Confirm that apply errors, sequencesync errors, tablesync errors, and conflicts are 0 and stats_reset is not NULL for sub '$sub1_name' after reset.) ); is( $node_subscriber->safe_psql( $db, qq(SELECT apply_error_count = 0, + seq_sync_error_count = 0, sync_error_count = 0, confl_insert_exists = 0, confl_delete_missing = 0, @@ -246,8 +286,8 @@ is( $node_subscriber->safe_psql( FROM pg_stat_subscription_stats WHERE subname = '$sub2_name') ), - qq(t|t|t|t|t), - qq(Confirm that apply errors, sync errors, and conflicts are 0 and stats_reset is not NULL for sub '$sub2_name' after reset.) + qq(t|t|t|t|t|t), + qq(Confirm that apply errors, sequencesync errors, tablesync errors, errors, and conflicts are 0 and stats_reset is not NULL for sub '$sub2_name' after reset.) ); $reset_time1 = $node_subscriber->safe_psql($db, -- 2.43.0
From 215b7c744bf9cad9376124bbcae47f61974392e0 Mon Sep 17 00:00:00 2001 From: Vignesh C <[email protected]> Date: Mon, 27 Oct 2025 09:18:07 +0530 Subject: [PATCH v20251107 2/2] Documentation for sequence synchronization feature. Documentation for sequence synchronization feature. Author: Vignesh C <[email protected]> Reviewed-by: Amit Kapila <[email protected]> Reviewed-by: shveta malik <[email protected]> Reviewed-by: Hou Zhijie <[email protected]> Reviewed-by: Masahiko Sawada <[email protected]> Reviewed-by: Hayato Kuroda <[email protected]> Reviewed-by: Dilip Kumar <[email protected]> Reviewed-by: Peter Smith <[email protected]> Reviewed-by: Nisha Moond <[email protected]> Reviewed-by: Shlok Kyal <[email protected]> Discussion: https://www.postgresql.org/message-id/caa4ek1lc+kjiaksrpe_nwvndidw9f2os7geruesxskv71gx...@mail.gmail.co --- doc/src/sgml/catalogs.sgml | 2 +- doc/src/sgml/config.sgml | 14 +- doc/src/sgml/func/func-sequence.sgml | 24 +++ doc/src/sgml/logical-replication.sgml | 235 ++++++++++++++++++++-- doc/src/sgml/monitoring.sgml | 5 +- doc/src/sgml/ref/alter_subscription.sgml | 15 ++ doc/src/sgml/ref/create_subscription.sgml | 31 ++- 7 files changed, 292 insertions(+), 34 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 d8a9f14b618..cf073f28f84 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, or table/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, and table/sequence + synchronization workers. </para> <para> Logical replication workers are taken from the pool defined by @@ -5359,9 +5359,11 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class=" 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. + 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 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 daab2cae989..d7bf465f948 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,205 @@ 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 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> + 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 Stale Sequences</title> + <para> + Subscriber side sequence values may frequently become out of sync due to + updates on the publisher. + </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 page_lsn obtained from the + <function>pg_get_sequence_data</function> for the sequence on the publisher. + Then run <link linkend="sql-altersubscription-params-refresh-sequences"> + <command>ALTER SUBSCRIPTION ... REFRESH SEQUENCES</command></link> to + resynchronize 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 comparing LSNs for sequence increments that fall within the + same cached block. + </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> + Update the sequences at the publisher side 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> + 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> + Observe that initial sequence values are synchronized. +<programlisting> +/* sub # */ SELECT * FROM s1; + last_value | log_cnt | is_called +------------+---------+----------- + 11 | 31 | t +(1 row) + +/* 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> +/* pub # */ SELECT nextval('s1'); + nextval +--------- + 12 +(1 row) +/* pub # */ SELECT nextval('s2'); + nextval +--------- + 120 +(1 row) +</programlisting></para> + + <para> + 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; + +/* sub # */ SELECT * FROM s1; + last_value | log_cnt | is_called +------------+---------+----------- + 12 | 30 | t +(1 row) + +/* 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 +2291,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 +2627,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 +2641,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 @@ -195,6 +195,12 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO < use <link linkend="sql-altersubscription-params-refresh-sequences"> <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 @@ -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> -- 2.43.0
