PG 19 逻辑订阅端支持 REFRESH SEQUENCES, 有啥用?

PG 19 逻辑订阅端支持 REFRESH SEQUENCES, 有啥用?

一直以来通过逻辑复制来进行数据迁移、大版本升级等时, 割接时都需要小心处理序列, 否则割接后容易导致使用序列生成的字段值重复、或违反唯一约束等问题.

所以割接时需要对序列进行reset, 得先停业务(确保源端序列不再被消费), 然后读出源端所有序列的next value, 然后到订阅端执行set value的操作.

挺繁琐的.

前段时间PG 19出了个发布所有序列的补丁. 《PostgreSQL 19 preview - 发布端支持FOR ALL SEQUENCES, 通过逻辑复制进行大版本升级丝滑了亿点点》

今天这个补丁又让DBA更爽一点了, 支持在订阅端刷新序列值了.

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

Introduce "REFRESH SEQUENCES"for subscriptions.  
author  Amit Kapila <[email protected]>      
Thu, 23 Oct 2025 08:30:27 +0000 (08:30 +0000)  
committer   Amit Kapila <[email protected]>      
Thu, 23 Oct 2025 08:30:27 +0000 (08:30 +0000)  
commit  f0b3573c3aac6c0ea4cbc278f98178516579d370  
tree    af2caa24d8562050b79ccb302d97437596b4cc55    tree  
parent  6ae08d9583e9a5e951286948bdd9fcd58e67718a    commit | diff  
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 <[email protected]>  
Reviewed-by: Amit Kapila <[email protected]>  
Reviewed-by: shveta malik <[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: Chao Li <[email protected]>  
Reviewed-by: Hou Zhijie <[email protected]>  
Discussion: https://postgr.es/m/CAA4eK1LC+KJiAkSrpE_NwvNdidw9F2os7GERUeSxSKv71gXysQ@mail.gmail.com