< Back to blog home Fujitsu PostgreSQL blog

Closing a critical gap in PostgreSQL upgrade workflows with sequence synchronization

Vigneshwaran C | June 30, 2026

In this post, we'll walk through a real-world upgrade scenario, show exactly where things can go wrong, and explain how the new PostgreSQL 19 sequence synchronization feature makes the entire process production-safe.

Discover how PostgreSQL 19 enhances upgrade workflows by introducing sequence synchronization, ensuring safe and seamless transitions during database upgrades

Background

In PostgreSQL logical replication, tables and their data can be replicated from a publisher to one or more subscribers. However, up to PostgreSQL 18, sequences were not replicated. As a result, after an upgrade, a subscriber promoted to primary could generate duplicate key values because its sequences had not advanced to match the previous publisher’s state.

This limitation is addressed in PostgreSQL 19.

The upgrade scenario

Consider a standard physical replication setup:

We want to upgrade both nodes with minimal disruption. Using pg_createsubscriber and pg_upgrade, we can perform a rolling upgrade as follows:

1. Run pg_createsubscriber to convert physical replication cluster to logical replication cluster:
   
2. Run pg_upgrade to upgrade Node-B to new PostgreSQL 19 version:
   
3. Stop connections/data changes in Node-A.
4. Synchronize the incremental data changes made on Node-A during the upgrade process to Node-B.
5. Redirect Writes to Node-B after the catchup.
6. Decommission Node-A.
   
7. Create standby node using pg_basebackup, Node-C from Node-B.

At the end of this process, we have:

Node-B (new version) → Node-C (new version)

This approach requires only a brief write outage during steps 3 and 4, allowing the remainder of the upgrade process to proceed with minimal disruption.

The hidden problem: Sequences are left behind

This approach relies on logical replication during the upgrade window. In the step 1, the original physical standby is converted into a logical subscriber using pg_createsubscriber, allowing replication to continue even while the two nodes run different PostgreSQL versions. From that point onward, logical replication is used throughout steps 2, 3, and 4 of the upgrade process while the upgraded node is prepared for production use.

During these steps, the original primary continues to accept application writes, while logical replication keeps the upgraded node synchronized. Table changes, including INSERT, UPDATE, and DELETE operations, are continuously replicated to the subscriber, allowing it to catch up with ongoing workload. Only after the upgraded node has been fully synchronized and is ready to take over are writes redirected to it during the switchover.

There is, however, one critical blind spot.

Sequence values are not replicated. While row data flows from Node-A to Node-B continuously, the sequences backing those tables remain at whatever value they held when pg_createsubscriber ran.

Table data is current. Sequences are stale. The database looks consistent — until writes switch over.

What goes wrong after upgrade

The problem surfaces at step 6, when writes are redirected to Node-B. At that moment, tables on Node-B contain up-to-date rows, but sequences are still at old or initial values. This misalignment has three serious consequences.

After upgrade, write goes to Node-B

• Duplicate key violations

  A sequence on Node-B regenerates the same ID that Node-A already wrote to the table:

  ERROR: duplicate key value violates unique constraint "users_pkey"
• Data integrity risk

  Applications that rely on sequences to generate unique identifiers can silently reuse previously assigned IDs when sequence state is stale. If the original rows have already been deleted, the reused values may not trigger constraint violations, making the problem difficult to detect and potentially causing incorrect application behaviour or broken references.

• Unsafe failover

  Even if replication looks healthy, the system is not actually consistent. The problem may not surface until writes start flowing — at exactly the worst moment.

Why manual workarounds fall short

The standard mitigation has been to manually advance sequences or run custom synchronization scripts before switchover. In practice this approach has real problems:

• Error-prone: Easy to miss a sequence, especially in large schemas
• Hard to automate: The right safe value depends on live write traffic that keeps advancing
• Risky in high-write systems: If sequences advance quickly, it can still miss values

Sequence synchronization: How it works

PostgreSQL 19 introduces native sequence synchronization as part of logical replication. It works in two parts.

1. Publishing sequences

Sequences can now be explicitly included in a publication:

2. Synchronizing on the subscriber

Sequence values can be synchronized on the subscriber at different stages of the logical replication lifecycle:

1. During initial subscription creation

   When a subscription is created, all sequences that are part of the publication are automatically synchronized to the subscriber.

   -- On the subscriber (Node-B, PostgreSQL 19)
   CREATE SUBSCRIPTION sub … PUBLICATION pub;
2. When new sequences are added to a publication

   If additional sequences are later added to the publication using ALTER PUBLICATION, the corresponding sequence objects must first be created on the subscriber. Once the sequences exist on both the publisher and subscriber, they can be synchronized by refreshing the publication:

   -- On the subscriber (Node-B, PostgreSQL 19)
   ALTER SUBSCRIPTION sub REFRESH PUBLICATION;

   This command discovers newly published sequences and initiates their synchronization on the subscriber.

3. Refreshing Existing Sequences

   PostgreSQL 19 introduces a new command to resynchronize all sequences that are part of the subscription:

   -- On the subscriber (Node-B, PostgreSQL 19)
   ALTER SUBSCRIPTION sub REFRESH SEQUENCES;

   This command retrieves the latest sequence state from the publisher and advances the corresponding subscriber sequences to a safe value. It is particularly useful when sequence values may have diverged, such as during upgrade workflows, failover procedures, or after prolonged periods of logical replication activity.

When any of the above commands are executed, PostgreSQL starts a dedicated sequence synchronization worker.

Here is how the sequence synchronization works:

• Mark the sequences to INIT state
• Start Sequence synchronization worker
• Sequence synchronization worker does the following:
  • Prepare a batch of 100 sequences to be synchronized
  • Connects to the publisher and retrieves current sequence state for sequences in current batch.
  • Detects mismatches between the publisher and subscriber; however, it does not raise errors. Instead, it continues synchronizing the remaining sequences and periodically retries the mismatched entries until they are successfully synchronized.
  • Advances subscriber sequences to match the publisher's values and marks sequence to READY state.
  • Records the publisher's page LSN corresponding to the copied state
  • Repeat the above steps till there are no sequences in INIT state
• Sequence synchronization worker exits

Here is how the sequence synchronization worker synchronizes one sequence:

Unlike table synchronization, this operation does not replay sequence changes through the logical replication stream, it is a direct state copy.

Tracking synchronization state

To track sequence synchronization, the subscriber stores the publisher's sequence page LSN alongside each replicated sequence. This information allows PostgreSQL to determine whether a subscriber sequence remains synchronized with the publisher or requires a refresh.

The stored publisher page LSNs can be viewed on the subscriber using pg_subscription_rel:

The current sequence state and page LSN on the publisher can be obtained using pg_get_sequence_data():

By comparing the publisher's current sequence page LSN with the page LSN stored on the subscriber, administrators can determine whether a sequence has advanced since it was last synchronized.

In this example, the page LSN of s1 on the publisher is 0/0178F9E0, which matches the LSN stored on the subscriber during the most recent sequence synchronization. This indicates that s1 has not advanced since it was last synchronized.

For s2, however, the publisher's current page LSN is 0/01836448, which is ahead of the LSN stored on the subscriber (0/0178FAB0). This indicates that the sequence has advanced on the publisher and may require resynchronization.

To bring s2 back into sync, the subscriber can execute ALTER SUBSCRIPTION ... REFRESH SEQUENCES that refreshes all subscribed sequences, including s2, by retrieving their latest state from the publisher. This ALTER SUBSCRIPTION ... REFRESH SEQUENCES command launches a sequence synchronization worker that retrieves the latest sequence values from the publisher. The operation is lightweight, non-disruptive, and can be performed at any point while logical replication is active.

The new steps to upgrade

Compared to the earlier upgrade workflow, the only additional requirement is Step 5, which synchronizes sequence state between the publisher and subscriber before traffic is switched to the upgraded node.

1. Run pg_createsubscriber to convert physical replication cluster to logical replication cluster:

   

2. Upgrade Node-B to new PostgreSQL 20 version.
   PostgreSQL 20 is not released at this stage. This refers to the future version once it becomes available.
3. Stop connections/data changes in Node-A.
4. Catch up the incremental data changes done during upgrade
5. Choose one of the following approaches to ensure that all required sequences are subscribed:
   • Option A: Existing publication already includes all sequences
     • Run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to discover any newly added publisher sequences.
     • Run ALTER SUBSCRIPTION ... REFRESH SEQUENCES to synchronize the state of all subscribed sequences.
   • Option B: Existing publication includes all tables but not sequences
     • Run ALTER PUBLICATION ... SET ALL TABLES, ALL SEQUENCES on the publisher to include sequences in the publication.
     • Run ALTER SUBSCRIPTION ... REFRESH PUBLICATION on the subscriber to discover the newly added sequences.
     • Run ALTER SUBSCRIPTION ... REFRESH SEQUENCES to synchronize the state of all subscribed sequences.
   • Option C: Create a dedicated publication/subscription for sequences
     • Create a publication for sequences and a corresponding subscription, for example:
       CREATE PUBLICATION seq_pub FOR ALL SEQUENCES;
       CREATE SUBSCRIPTION seq_sub ... PUBLICATION seq_pub;
     • Run ALTER SUBSCRIPTION ... REFRESH PUBLICATION if additional sequences were added after the subscription was created.
     • Run ALTER SUBSCRIPTION ... REFRESH SEQUENCES to synchronize the state of all subscribed sequences.

   Finally, wait for all sequence synchronization workers to complete (check log) and for all sequences to reach the READY state before switchover.

6. Redirect writes to Node-B after the catchup.
7. Decommission Node-A.
   
8. Create standby node, Node-C from Node-B.

At the end of this process, we have:

Node-B (new version) → Node-C (new version)

All of this can be accomplished with minimal disruption, as writes need to be stopped only briefly while Steps 3 through 5 are performed.

The result: A fully safe upgrade

With sequence synchronization in place, the upgrade workflow becomes safe end-to-end:

Conclusion

Sequence synchronization closes a long-standing weakness in PostgreSQL's logical replication story. For anyone running online upgrades with pg_createsubscriber, this feature changes the calculus entirely — what previously required careful manual steps and a healthy dose of luck is now handled automatically and correctly by the database itself.

For PostgreSQL clusters that rely on sequences heavily (and most do), this is one of the most practically impactful additions in the PostgreSQL 19 release. The ability to perform online upgrades with sequences kept in sync is a major milestone for PostgreSQL operational reliability.

Key takeaway

Include sequences via ALTER PUBLICATION … SET ALL SEQUENCES, ALL TABLES and run ALTER SUBSCRIPTION … REFRESH PUBLICATION before redirecting writes. This ensures sequences are safely synchronized in a production-ready way.

For the future

Looking ahead, there are plans to further automate sequence synchronization to simplify management and reduce operational effort.

The Fujitsu OSS team, together with the PostgreSQL community, remains committed to continuing the evolution of logical replication by delivering new capabilities and enhancements in future releases.

Where to find more information

• PostgreSQL documentation
  • ALTER SUBSCRIPTION … REFRESH SEQUENCES
  • pg_createsubscriber
  • pg_upgrade
• GitHub source commits
  • 09-Oct-2026.
    Support ALL SEQUENCES clause in publications
  • 23-Oct-2026.
    Add REFRESH SEQUENCES syntax
  • 05-Nov-2026.
    Support sequence synchronization
• psql-hackers mailing list:
  • Logical replication of sequences
• Future development discussions
  • Support automatic sequence replication
  • Support EXCEPT for ALL SEQUENCES publications