PostgreSQL 19 sequence replication advances only on explicit refresh, risking ID collisions at promotion

Sequence replication arrives in PostgreSQL 19

The release candidate of PostgreSQL 19 lets a publication declare ALL SEQUENCES, and a subscriber created from that publication imports the publisher's current sequence positions as part of its initial table synchronization. Two supporting pieces ship alongside it: an ALTER SUBSCRIPTION ... REFRESH SEQUENCES command that pulls fresh values on request, and a new pg_get_sequence_data() function for inspecting what a subscriber currently believes a sequence is worth. According to a hands-on write-up on dev.to, none of this exists in PostgreSQL 18, where the equivalent CREATE PUBLICATION statement simply fails with a syntax error.

The failover problem it targets

Historically, logical replication copied table rows while ignoring sequences entirely. A subscriber promoted to primary after a failure would therefore hand out IDs from a sequence still parked at its default starting position, and the first local inserts would collide with rows already present on disk. As the author notes, that behavior is a standard reason teams avoid logical replication for major-version upgrades or disaster recovery and use physical replication instead, even though physical replication cannot filter which tables come across.

A snapshot, not a stream

The testing ran two postgres:19rc1 containers on a shared Docker network, with wal_level set to logical on the publisher, a table with a bigint identity primary key, and 1,000 seeded rows. After the initial sync, both a PostgreSQL 18 and a PostgreSQL 19 subscriber held all 1,000 rows, but the sequences diverged: the 19 subscriber's orders_id_seq landed on 1,000 while the 18 subscriber's never moved from 1. An insert run against the 18 subscriber as if it had just been promoted failed immediately with a duplicate-key violation on id 1; the same insert on the fresh 19 subscriber returned id 1001 and succeeded.

The limitation surfaced when the author pushed 500 more rows through the publisher without touching the subscription. The subscriber's table data caught up completely, with both count(*) and max(id) reaching 1,500, but its sequence remained at 1,000. In other words, sequence synchronization in PostgreSQL 19 is a snapshot taken at discrete moments — CREATE SUBSCRIPTION, REFRESH PUBLICATION, or REFRESH SEQUENCES — rather than a continuous stream alongside row changes. Running the refresh explicitly brought the subscriber's value to 1,500 in roughly 118 milliseconds.

Reproducing the failure the feature exists to prevent

The author then added another 200 rows on the publisher, bringing the total to 1,700 fully replicated rows, and performed a simulated promotion: disabling the subscription, dropping it, and inserting locally without refreshing sequences first, since the previous refresh had happened before the new batch landed. The insert failed with a duplicate-key violation on id 1501. The takeaway is that refreshing sequences has to be the last thing an operator does before promotion, not something done at some earlier point in the subscription's life — a step the documentation mentions but, per the write-up, does not put front and centre.

New sequences must be pre-created

The author also tested the common case of sequences appearing after a subscription already exists, by creating 300 extra sequences on the publisher and asking the subscriber to refresh the publication. The command errored out over a missing relation, and zero of the 300 were created. The behavior is all-or-nothing and, in the author's testing, sequences needed to already exist as objects on the subscriber before the subscription would acknowledge them at all. The documented workaround is to create matching sequences manually on the subscriber; after doing so, both refresh commands completed in under 200 milliseconds and the subscriber's count of sequences went from one to 301.

Cost and observability

No meaningful cost was measurable: refreshing a single sequence or 301 of them each finished in under 200 milliseconds. A new sync_seq_error_count column on pg_stat_subscription_stats is intended for monitoring this path in production.

Why it matters

Sequence state has long been the quiet blocker that pushed teams toward physical replication for failover and version upgrades, at the cost of losing table-level filtering. PostgreSQL 19 closes much of that gap, and the initial-sync behavior genuinely prevents ID collisions for freshly promoted subscribers. But because sequence sync is event-driven rather than continuous, failover runbooks must treat a final REFRESH SEQUENCES as a mandatory step immediately before promotion, and schema drift still requires manually creating new sequences on the subscriber. One caveat: all of this was tested against RC1, with general availability planned for 19 November 2026, so details could still change before the final release.