From f0b3573c3aac6c0ea4cbc278f98178516579d370 Mon Sep 17 00:00:00 2001 From: Amit Kapila Date: Thu, 23 Oct 2025 08:30:27 +0000 Subject: Introduce "REFRESH SEQUENCES" for subscriptions. This patch adds support for a new SQL command: ALTER SUBSCRIPTION ... REFRESH SEQUENCES This command updates the sequence entries present in the pg_subscription_rel catalog table with the INIT state to trigger resynchronization. In addition to the new command, the following subscription commands have been enhanced to automatically refresh sequence mappings: ALTER SUBSCRIPTION ... REFRESH PUBLICATION ALTER SUBSCRIPTION ... ADD PUBLICATION ALTER SUBSCRIPTION ... DROP PUBLICATION ALTER SUBSCRIPTION ... SET PUBLICATION These commands will perform the following actions: Add newly published sequences that are not yet part of the subscription. Remove sequences that are no longer included in the publication. This ensures that sequence replication remains aligned with the current state of the publication on the publisher side. Note that the actual synchronization of sequence data/values will be handled in a subsequent patch that introduces a dedicated sequence sync worker. Author: Vignesh C Reviewed-by: Amit Kapila Reviewed-by: shveta malik Reviewed-by: Masahiko Sawada Reviewed-by: Hayato Kuroda Reviewed-by: Dilip Kumar Reviewed-by: Peter Smith Reviewed-by: Nisha Moond Reviewed-by: Shlok Kyal Reviewed-by: Chao Li Reviewed-by: Hou Zhijie Discussion: https://postgr.es/m/CAA4eK1LC+KJiAkSrpE_NwvNdidw9F2os7GERUeSxSKv71gXysQ@mail.gmail.com --- doc/src/sgml/catalogs.sgml | 29 +++++++++++++++------- doc/src/sgml/ref/alter_subscription.sgml | 42 ++++++++++++++++++++++++++------ 2 files changed, 55 insertions(+), 16 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 9b3aae8603b..6c8a0f173c9 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -8199,16 +8199,19 @@ SCRAM-SHA-256$<iteration count>:&l - The catalog pg_subscription_rel contains the - state for each replicated relation in each subscription. This is a - many-to-many mapping. + The catalog pg_subscription_rel stores the + state of each replicated table and sequence for each subscription. This + is a many-to-many mapping. - This catalog only contains tables known to the subscription after running - either CREATE SUBSCRIPTION or - ALTER SUBSCRIPTION ... REFRESH - PUBLICATION. + This catalog contains tables and sequences known to the subscription + after running: + CREATE SUBSCRIPTION, + + ALTER SUBSCRIPTION ... REFRESH PUBLICATION, or + + ALTER SUBSCRIPTION ... REFRESH SEQUENCES. @@ -8242,7 +8245,7 @@ SCRAM-SHA-256$<iteration count>:&l (references pg_class.oid) - Reference to relation + Reference to table or sequence @@ -8251,12 +8254,20 @@ SCRAM-SHA-256$<iteration count>:&l srsubstate char - State code: + State code for the table or sequence. + + + State codes for tables: i = initialize, d = data is being copied, f = finished table copy, s = synchronized, r = ready (normal replication) + + + State codes for sequences: + i = initialize, + r = ready diff --git a/doc/src/sgml/ref/alter_subscription.sgml b/doc/src/sgml/ref/alter_subscription.sgml index 12f72ba3167..8ab3b7fbd37 100644 --- a/doc/src/sgml/ref/alter_subscription.sgml +++ b/doc/src/sgml/ref/alter_subscription.sgml @@ -26,6 +26,7 @@ ALTER SUBSCRIPTION name SET PUBLICA ALTER SUBSCRIPTION name ADD PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ] ALTER SUBSCRIPTION name DROP PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ] ALTER SUBSCRIPTION name REFRESH PUBLICATION [ WITH ( refresh_option [= value] [, ... ] ) ] +ALTER SUBSCRIPTION name REFRESH SEQUENCES ALTER SUBSCRIPTION name ENABLE ALTER SUBSCRIPTION name DISABLE ALTER SUBSCRIPTION name SET ( subscription_parameter [= value] [, ... ] ) @@ -139,9 +140,10 @@ ALTER SUBSCRIPTION name RENAME TO < refresh (boolean) - When false, the command will not try to refresh table information. - REFRESH PUBLICATION should then be executed separately. - The default is true. + When false, the command will not try to refresh + table and sequence information. REFRESH PUBLICATION + should then be executed separately. The default is + true. @@ -158,13 +160,19 @@ ALTER SUBSCRIPTION name RENAME TO < REFRESH PUBLICATION - Fetch missing table information from publisher. This will start + Fetch missing table and sequence information from the publisher. This will start replication of tables that were added to the subscribed-to publications since CREATE SUBSCRIPTION or the last invocation of REFRESH PUBLICATION. + + The system catalog pg_subscription_rel + is updated to record all tables and sequences known to the subscription, + that are still part of the publication. + + refresh_option specifies additional options for the refresh operation. The supported options are: @@ -174,14 +182,19 @@ ALTER SUBSCRIPTION name RENAME TO < copy_data (boolean) - Specifies whether to copy pre-existing data in the publications - that are being subscribed to when the replication starts. - The default is true. + 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 true. Previously subscribed tables are not copied, even if a table's row filter WHERE clause has since been modified. + + Previously subscribed sequences are not re-synchronized. To do that, + use + ALTER SUBSCRIPTION ... REFRESH SEQUENCES. + See for details of how copy_data = true can interact with the @@ -200,6 +213,21 @@ ALTER SUBSCRIPTION name RENAME TO < + + REFRESH SEQUENCES + + + Re-synchronize sequence data with the publisher. Unlike + + ALTER SUBSCRIPTION ... REFRESH PUBLICATION which + only has the ability to synchronize newly added sequences, + REFRESH SEQUENCES will re-synchronize the sequence + data for all currently subscribed sequences. It does not add or remove + sequences from the subscription to match the publication. + + + + ENABLE -- cgit v1.2.3