First, on 9th of October 2025, Amit Kapila committed patch:
Add "ALL SEQUENCES" support to publications. This patch adds support for the ALL SEQUENCES clause in publications, enabling synchronization/replication of all sequences that is useful for upgrades. Publications can now include all sequences via FOR ALL SEQUENCES. psql enhancements: \d shows publications for a given sequence. \dRp indicates if a publication includes all sequences. ALL SEQUENCES can be combined with ALL TABLES, but not with other options like TABLE or TABLES IN SCHEMA. We can extend support for more granular clauses in future. The view pg_publication_sequences provides information about the mapping between publications and sequences. This patch enables publishing of sequences; subscriber-side support will be added in upcoming patches. Author: vignesh C <vignesh21@gmail.com> Author: Tomas Vondra <tomas@vondra.me> Reviewed-by: shveta malik <shveta.malik@gmail.com> Reviewed-by: Dilip Kumar <dilipbalaut@gmail.com> Reviewed-by: Peter Smith <smithpb2250@gmail.com> Reviewed-by: Hayato Kuroda <kuroda.hayato@fujitsu.com> Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com> Reviewed-by: Nisha Moond <nisha.moond412@gmail.com> Reviewed-by: Shlok Kyal <shlok.kyal.oss@gmail.com> Reviewed-by: Amit Kapila <amit.kapila16@gmail.com> Discussion: https://postgr.es/m/CAA4eK1LC+KJiAkSrpE_NwvNdidw9F2os7GERUeSxSKv71gXysQ@mail.gmail.com
And then, on 5th of November 2025, he also committed patch:
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 done in this
case.
Author: Vignesh C <vignesh21@gmail.com>
Reviewed-by: shveta malik <shveta.malik@gmail.com>
Reviewed-by: Hou Zhijie <houzj.fnst@fujitsu.com>
Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com>
Reviewed-by: Hayato Kuroda <kuroda.hayato@fujitsu.com>
Reviewed-by: Dilip Kumar <dilipbalaut@gmail.com>
Reviewed-by: Peter Smith <smithpb2250@gmail.com>
Reviewed-by: Nisha Moond <nisha.moond412@gmail.com>
Reviewed-by: Shlok Kyal <shlok.kyal.oss@gmail.com>
Reviewed-by: Amit Kapila <amit.kapila16@gmail.com>
Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Discussion: https://postgr.es/m/CAA4eK1LC+KJiAkSrpE_NwvNdidw9F2os7GERUeSxSKv71gXysQ@mail.gmail.com
I tried to describe what was added after first commit, but this was basically just a scaffolding. Now, though. I can show how it works.
For this to work I will need two databases, so let's make them:
=$ psql -p 5430 -c "CREATE DATABASE seq_test_src" =$ psql -p 5431 -c "CREATE DATABASE seq_test_dest"
Now, in both of them I will run:
=$ create table test_table_serial ( id serial primary key, payload text ); =$ create table test_table_identity ( id int8 generated always as identity primary key, payload text ); =$ create sequence standalone_seq;
Great. Now, let's setup the replication. On source side it's enough to:
=$ psql -X -p 5430 -d seq_test_src -c "create publication test_pub FOR ALL TABLES, ALL SEQUENCES" CREATE PUBLICATION
and on destination:
=$ psql -X -p 5431 -d seq_test_dest -c "create subscription test_sub connection 'host=127.0.0.1 port=5430 dbname=seq_test_src user=replicator' publication test_pub" NOTICE: created replication slot "test_sub" on publisher CREATE SUBSCRIPTION
And that should be it. Let's see how it works. First, let's insert some rows on source, and also make sure that we also touch the sequence that isn't bound to any table:
=$ psql -X -p 5430 -d seq_test_src <<_END_OF_SQL_ insert into test_table_serial (payload) values ('a'), ('b'); insert into test_table_identity (payload) values ('c'), ('d'), ('e'); with vals as ( select nextval('standalone_seq') as v from generate_series(1,10) i ) select count(*), min(v), max(v) from vals; _END_OF_SQL_
Which worked, and the final select showed:
count │ min │ max ───────┼─────┼───── 10 │ 1 │ 10 (1 row)
With this in place, let's see state of sequences, on source:
=$ psql -X -p 5430 -d seq_test_src <<_END_OF_SQL_ select * from standalone_seq; select * from test_table_identity_id_seq; select * from test_table_serial_id_seq; _END_OF_SQL_ last_value | log_cnt | is_called ------------+---------+----------- 10 | 23 | t (1 row) last_value | log_cnt | is_called ------------+---------+----------- 3 | 30 | t (1 row) last_value | log_cnt | is_called ------------+---------+----------- 2 | 31 | t (1 row)
and on destination:
=$ psql -X -p 5431 -d seq_test_dest <<_END_OF_SQL_ select * from standalone_seq; select * from test_table_identity_id_seq; select * from test_table_serial_id_seq; _END_OF_SQL_ last_value | log_cnt | is_called ------------+---------+----------- 1 | 0 | f (1 row) last_value | log_cnt | is_called ------------+---------+----------- 1 | 0 | f (1 row) last_value | log_cnt | is_called ------------+---------+----------- 1 | 0 | f (1 row)
Whoa. This is unexpected. Why is last_value 1?
Let's see one thing, in pg_subscription_rel, what are states of subscriptions?
=$ SELECT c.relname, r.srsubstate FROM pg_subscription AS s JOIN pg_subscription_rel AS r ON s.oid = r.srsubid JOIN pg_class AS c ON r.srrelid = c.oid WHERE s.subname = 'test_sub' AND c.relkind = 'S'; relname │ srsubstate ────────────────────────────┼──────────── test_table_serial_id_seq │ r test_table_identity_id_seq │ r standalone_seq │ r (3 rows)
Re-reading now the commit message it seems that subscriptions are synchronized when state is init.
Luckily there is simple query that I can run:
=$ psql -X -p 5431 -d seq_test_dest -c "ALTER subscription test_sub REFRESH SEQUENCES" ALTER SUBSCRIPTION
Afterwards, I get expected results:
=$ psql -X -p 5431 -d seq_test_dest <<_END_OF_SQL_ select * from standalone_seq; select * from test_table_identity_id_seq; select * from test_table_serial_id_seq; _END_OF_SQL_ last_value | log_cnt | is_called ------------+---------+----------- 14 | 0 | t (1 row) last_value | log_cnt | is_called ------------+---------+----------- 3 | 0 | t (1 row) last_value | log_cnt | is_called ------------+---------+----------- 2 | 0 | t (1 row)
Values are different than previously shown, as I way testing nextval() in the mean time 🙂
So, it looks like it doesn't happen on its own, and has to be manually invoked, but it's fast, and removed a lot of work related to figuring out which table to get id from to update which sequence.
One last thing that might be interesting – how fast is it?
Let's make some sequences in both source and destination:
=$ select format('create sequence test_seq_%s;', i) from generate_series(1,10000) i \gexec
Afterwards, I need to refresh subscription:
=$ psql -X -p 5431 -d seq_test_dest -c 'alter subscription test_sub refresh publication'
and then, on source only, let's run some random nextval counts on each of these sequences.
=$ select 'select 0' || repeat( format('+nextval(''test_seq_%s;'')', i), floor( 1 + random() * 3)::int4) from generate_series(1,10000) i -$ \gexec
This, somewhat complex query, ran nextval() on each of these 10k sequences, on each of them 1, 2 or 3 times.
Afterwards, I ran refresh with \timing on and got time 99.350ms. But I also logged how many rows are there in pg_subscription_rel with state i. I ran query cheking it every 100ms. Log:
2025-11-11 13:21:52.708457 : r:10005 2025-11-11 13:21:52.808429 : i:10003 ; r:2 2025-11-11 13:21:52.908437 : i:10003 ; r:2 2025-11-11 13:21:53.008439 : i:8803 ; r:1202 2025-11-11 13:21:53.108463 : i:7503 ; r:2502 2025-11-11 13:21:53.20844 : i:6103 ; r:3902 2025-11-11 13:21:53.308446 : i:4903 ; r:5102 2025-11-11 13:21:53.408442 : i:3803 ; r:6202 2025-11-11 13:21:53.508451 : i:2803 ; r:7202 2025-11-11 13:21:53.608447 : i:1803 ; r:8202 2025-11-11 13:21:53.708441 : i:803 ; r:9202 2025-11-11 13:21:53.808452 : r:10005
So, in total, we can say that the whole resync of 10000 sequences took around one second. Not bad. Not bad at all 🙂
All in all – it's a great addition, that will definitely greatly simplify using logical replication for upgrades.
Thanks a lot to everyone involved – great work!
Your work has such a positive impact on everyone 🔥 who takes time to read