With a new feature introduced in PostgreSQL 17, this changes. The upgrade process now natively preserves the full state of your logical subscriptions, making subscriber upgrades a seamless and reliable part of your maintenance routine.

Seamlessly upgrade PostgreSQL subscribers with PostgreSQL 17’s new feature that preserves logical replication states

The problem: A broken link after pg_upgrade

Before PostgreSQL 17, pg_upgrade did not fully support logical replication. While the top-level subscription metadata in the pg_subscription catalog was kept, crucial details were discarded, creating a broken link between the old and new clusters:

• Lost table states (pg_subscription_rel): The subscriber forgot which tables were fully synchronized (r) and which were still being initialized (i).
• Lost replication origin: The LSN (Log Sequence Number) that tracks replication progress was not preserved, leaving the new cluster with no reference point.

This meant that after an upgrade, the subscriber had no memory of its synchronization status. Upon restart, the user faced a risky choice: re-sync everything from scratch or try to resume blindly, risking missed or duplicated data.

The solution in PostgreSQL 17

The new feature addresses this by teaching pg_upgrade to handle the full replication state using three key mechanisms:

1. Preserving table synchronization states

pg_dump now restores the contents of pg_subscription_rel by internally calling a new helper function, binary_upgrade_add_sub_rel_state(). This ensures that after the upgrade, each table retains its correct state (i or r), allowing the subscriber to know exactly which tables are fully up-to-date and which require further initialization.

1. Preserving replication origins

To ensure replication resumes at the exact correct point, pg_upgrade now restores the replication origin’s remote LSN. This is handled by a new internal function, binary_upgrade_replorigin_advance(). By preserving this LSN, the subscriber resumes replication precisely where it left off, eliminating the risk of data loss or duplication.

1. Built-in safety checks

To prevent an upgrade from proceeding with a broken or inconsistent setup, pg_upgrade now performs a critical validation step. It will fail with a clear error message if any subscription relation is not in a safe state (i or r), which helps catch potential issues early.

A practical example: Upgrading a subscriber node

To see this feature in action, let’s walk through a simple pg_upgrade scenario for a subscriber node.

1 Initial setup

First, we establish a publisher and a subscriber, both running PostgreSQL 17 for this example.

On the publisher (e.g., PostgreSQL 17):

CREATE TABLE demo(id int primary key, data text);

INSERT INTO demo VALUES (1, ‘initial-data’);

CREATE PUBLICATION pub_demo FOR TABLE demo;

On the subscriber (e.g., PostgreSQL 17):

CREATE TABLE demo(id int primary key, data text);

CREATE SUBSCRIPTION sub_demo CONNECTION ‘host=publisher dbname=pubdb user=pub_user password=pubpass’ PUBLICATION pub_demo;

At this point, the subscriber’s demo table has one row, and replication is active.

SELECT * FROM demo; id | data ––+––––––– 1 | initial-data (1 row)

Now, let’s insert some data on the publisher that will be replicated before the upgrade.

On the publisher:

INSERT INTO demo VALUES (2, ‘before-upgrade’);

The subscriber will now also show this new row.

On the subscriber:

SELECT * FROM demo; id | data ––+–––––––– 1 | initial-data 2 | before-upgrade (2 rows)

Let’s observe the subscription catalog state and replication origin before the upgrade:

SELECT * FROM pg_subscription_rel; srsubid | srrelid | srsubstate | srsublsn ———+———+————+———— 16409 | 16401 | r | 0/017EDCE0 (1 row)

SELECT subname, remote_lsn FROM pg_replication_origin_status, pg_subscription; subname | remote_lsn –––––+———— sub_demo | 0/017FA890 (1 row)

2 The pg_upgrade process

Now, we stop the subscriber instance and perform the upgrade to PostgreSQL 18.

# Stop the old server first pg_ctl -D /path/to/old/subscriber stop

Example pg_upgrade command (replace paths with your own)

pg_upgrade
–old-datadir=/path/to/old/subscriber
–new-datadir=/path/to/new/subscriber
–old-bindir=/path/to/old/bin
–new-bindir=/path/to/new/bin

Start the upgraded server

pg_ctl -D /path/to/new/subscriber start

3 Testing replication after the upgrade

Because the upgrade preserved our subscription state, we can immediately resume replication without any manual intervention.

On the subscriber (PostgreSQL 18):

SELECT * from pg_subscription_rel; srsubid | srrelid | srsubstate | srsublsn ———+———+————+———— 16404 | 16401 | r | 0/017EDCE0 (1 row)

SELECT subname, remote_lsn FROM pg_replication_origin_status, pg_subscription; subname | remote_lsn –––––+———— sub_demo | 0/017FA890 (1 row)

Notice that remote_lsn and srsublsn are preserved from the pre-upgrade state. And our data is still there:

SELECT * FROM demo; id | data ––+–––––––– 1 | initial-data 2 | before-upgrade (2 rows)

Now, let’s insert a new row on the publisher to confirm continuous replication.

On the publisher:

INSERT INTO demo VALUES (3, ‘after-upgrade’);

When we check the subscriber, the new row is already there, seamlessly replicated from the publisher:

On the subscriber (PostgreSQL 18):

SELECT * FROM demo; id | data ––+–––––––– 1 | initial-data 2 | before-upgrade 3 | after-upgrade (3 rows)

The result confirms that the subscriber correctly resumed replication without needing a full re-sync. The first and second rows were preserved, and the third row was successfully replicated after the upgrade.

Why this matters

This enhancement is a major win for PostgreSQL administrators, making pg_upgrade a first-class tool for managing subscriber nodes.

• Reliable upgrades

Replication continues exactly where it left off, eliminating the risk of data loss or duplicates.

• Operational simplicity

The need for manual re-syncs and complex scripting is gone, making upgrades faster and more predictable.

• Enhanced data safety

The built-in validation ensures that an upgrade only proceeds if the replication state is safe.

Conclusion

By preserving subscription relation states and replication origins during pg_upgrade, PostgreSQL 17 resolves a long-standing gap in logical replication. This enhancement ensures smoother upgrades, strengthens reliability for subscriber nodes, and reinforces PostgreSQL’s role as a robust platform for data synchronization.

Here’s a quick summary of the transformation this change brings:

Aspect	Before PostgreSQL 17	PostgreSQL 17+
Table States	Lost → Re-sync	Preserved → Resumes with correct states
Replication Origin (LSN)	Lost → Re-sync	Preserved → Continues from the correct LSN
Upgrade Safety	No pre-checks → Potential issues	Validated → Fails if unsafe states are detected
Post-upgrade Replication	Risky, manual, complex	Seamless → Continues without interruption or data loss

Topics: PostgreSQL, Logical replication, High Availability, pg_upgrade, Database administration