PostgreSQL 19 preview - 逻辑复制正式支持 Sequence 同步

本期播客

PostgreSQL 19 preview - 逻辑复制正式支持 Sequence 同步

PostgreSQL 19 逻辑复制正式支持 Sequence 同步, 从创建订阅、刷新发布端到刷新序列的阶段性支持, 订阅端由一个专门的sequencesync worker来负责监控sequence同步状态, 同步并更新订阅端序列状态.

不废话了, 看patch: 

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5509055d6956745532e65ab218e15b99d87d66ce

Add sequence synchronization for logical replication.  
author  Amit Kapila <[email protected]>    
Wed, 5 Nov 2025 05:54:25 +0000 (05:54 +0000)  
committer Amit Kapila <[email protected]>    
Wed, 5 Nov 2025 05:59:58 +0000 (05:59 +0000)  
commit  5509055d6956745532e65ab218e15b99d87d66ce  
tree  6d3776b6e028dbe5a465657395c5c624b0b21a75  tree  
parent  1fd981f05369340a8afa4d013a350b0b2ac6e33e  commit | diff  
Add sequence synchronization for logical replication.  

This patch introduces sequence synchronization. Sequences that are synced  
will have 2 states:  
   - INIT (needs [re]synchronizing)  
   - READY (is already synchronized)  

A new sequencesync worker is launched as needed to synchronize sequences.  
A single sequencesync worker is responsible for synchronizing all  
sequences. It begins by retrieving the list of sequences that are flagged  
for synchronization, i.e., those in the INIT state. These sequences are  
then processed in batches, allowing multiple entries to be synchronized  
within a single transaction. The worker fetches the current sequence  
values and page LSNs from the remote publisher, updates the corresponding  
sequences on the local subscriber, and finally marks each sequence as  
READY upon successful synchronization.  

Sequence synchronization occurs in 3 places:  
1) CREATE SUBSCRIPTION  
    - The command syntax remains unchanged.  
    - The subscriber retrieves sequences associated with publications.  
    - Published sequences are added to pg_subscription_rel with INIT  
      state.  
    - Initiate the sequencesync worker to synchronize all sequences.  

2) ALTER SUBSCRIPTION ... REFRESH PUBLICATION  
    - The command syntax remains unchanged.  
    - Dropped published sequences are removed from pg_subscription_rel.  
    - Newly published sequences are added to pg_subscription_rel with INIT  
      state.  
    - Initiate the sequencesync worker to synchronize only newly added  
      sequences.  

3) ALTER SUBSCRIPTION ... REFRESH SEQUENCES  
    - A new command introduced for PG19 by f0b3573c3a.  
    - All sequences in pg_subscription_rel are reset to INIT state.  
    - Initiate the sequencesync worker to synchronize all sequences.  
    - Unlike "ALTER SUBSCRIPTION ... REFRESH PUBLICATION"command,  
      addition and removal of missing sequences will not be donein this  
case.  

Author: Vignesh C <[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]>  
Reviewed-by: Amit Kapila <[email protected]>  
Reviewed-by: Chao Li <[email protected]>  
Discussion: https://postgr.es/m/CAA4eK1LC+KJiAkSrpE_NwvNdidw9F2os7GERUeSxSKv71gXysQ@mail.gmail.com