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:
- 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.
- 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.
- 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):
INSERT INTO demo VALUES (1, 'initial-data');
CREATE PUBLICATION pub_demo FOR TABLE demo;
On the subscriber (e.g., PostgreSQL 17):
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.
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:
The subscriber will now also show this new row.
On the subscriber:
id | data
----+----------------
1 | initial-data
2 | before-upgrade
(2 rows)
Let's observe the subscription catalog state and replication origin before the upgrade:
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.
pg_ctl -D /path/to/old/subscriber stop
Example pg_upgrade command (replace paths with your own)
--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
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):
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:
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:
When we check the subscriber, the new row is already there, seamlessly replicated from the publisher:
On the subscriber (PostgreSQL 18):
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 |
