pg_upgrade carries logical slots only from 17

Which end of the upgrade the feature is on

PostgreSQL 17 taught the upgrade tool to carry logical replication slots and subscription state across a major version boundary, so that a publisher and its subscribers can resume where they left off instead of resynchronising from a fresh copy. It is a genuinely large improvement and it is described, correctly, as landing in 17.

It is also constantly misread, in one specific direction. The feature lives in the new cluster’s tooling and it applies to the old cluster’s version, and those are not the same sentence. The release notes say it plainly, under Logical Replication in the PostgreSQL 17 release notes: this only works for old clusters that are version 17 or later. The tool’s own documentation is blunter still, and worth quoting because it names the failure mode: logical slots on clusters before version 17.0 will silently be ignored.

So, to be completely unambiguous about the cases people actually have:

Upgrading 16 to 17 does not preserve logical slots. Upgrading 15 to 17 does not. Upgrading 14 to 18 does not. Upgrading 16 to 18 does not. The first upgrade that preserves them is one whose source cluster is 17 or later, which for most fleets means 17 to 18, and which for a fleet still on 14, 15 or 16 means the slots have to be recreated by hand exactly as they always did. Version 17 is the first release that can be upgraded away from with slots intact, not the first that can be upgraded into.

The word in the documentation to take seriously is “silently”. There is no error, no warning in the upgrade output and no check that fails. The upgrade succeeds, the new cluster starts, and the slots are not there.

The same slot on a 16 source and a 17 source

Both servers run with logical decoding enabled and hold one slot for a downstream consumer.

CREATE TABLE subscription_event (event_id bigint PRIMARY KEY, payload text);
INSERT INTO subscription_event SELECT g, 'body-' || g FROM generate_series(1, 5000) AS g;
SELECT slot_name FROM pg_create_logical_replication_slot('analytics_feed', 'pgoutput');

CREATE TABLE
INSERT 0 5000
   slot_name    
----------------
 analytics_feed
(1 row)

On a 16 source, this is a slot that an upgrade will discard without mentioning it.

SELECT slot_name, plugin, temporary, conflicting
FROM pg_replication_slots
WHERE slot_type = 'logical';

   slot_name    |  plugin  | temporary | conflicting 
----------------+----------+-----------+-------------
 analytics_feed | pgoutput | f         | f
(1 row)

Nothing in that row says the slot is at risk, because nothing about the slot is wrong. What determines its fate is the version of the server it is sitting on, and the row cannot tell you that.

On a 17 source the same slot is a candidate for migration, and the columns that decide whether it qualifies can be read in advance.

SELECT slot_name,
       plugin,
       temporary,
       conflicting,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS undelivered
FROM pg_replication_slots
WHERE slot_type = 'logical';

   slot_name    |  plugin  | temporary | conflicting | undelivered 
----------------+----------+-----------+-------------+-------------
 analytics_feed | pgoutput | f         | f           | 0 bytes
(1 row)

undelivered is the one to watch in the week before the upgrade. The tool requires that the old cluster has replicated everything to its subscribers, and a slot with a backlog at shutdown is a slot the upgrade will refuse to carry. A consumer that has been down for an afternoon turns a preserved slot into an unpreserved one.

The destination has requirements of its own, and they are settings rather than slot state.

SELECT name, setting
FROM pg_settings
WHERE name IN ('wal_level', 'max_replication_slots')
ORDER BY name;

         name          | setting 
-----------------------+---------
 max_replication_slots | 10
 wal_level             | logical
(2 rows)

Both servers above were started with logical decoding switched on, which is why the first value reads that way; on a freshly initialised cluster it does not. The new cluster must have wal_level at logical, and max_replication_slots at least as large as the number of slots the old cluster holds. Neither is the default on a freshly initialised cluster, and both are start-time settings, so getting them wrong means a restart in the middle of a maintenance window.

What to check on the source before the day

For a source at 17 or later, the pre-flight list is short and every item is a query you can run a week early.

• No logical slot has conflicting true. An invalidated slot is not carried, and the reason it was invalidated is a column on 17.
• Every logical slot has consumed the log it needs, which is the undelivered figure above at or near zero with the consumers running.
• The destination cluster holds no permanent logical slots of its own before the upgrade runs, and its two settings above are already correct.
• Every output plugin the slots name is installed in the new cluster’s binary directory.

For a source at 14, 15 or 16, there is no pre-flight list, because there is nothing to preserve. Write down each slot with its plugin and the subscription that consumes it, upgrade, recreate the slots, and resynchronise. Doing that from an inventory captured beforehand is the difference between an hour and an afternoon.

Planning around it rather than being surprised by it

The practical consequence for a fleet on an older major is a choice between two upgrade shapes, and it deserves to be made deliberately rather than discovered.

One option is the double hop: upgrade to 17 first, accepting that the slots are lost at that step, and get the benefit at the next boundary. The second is a single jump straight to the newest supported major, losing the slots once rather than once now and never again. The second is fewer maintenance windows and fewer compatibility surfaces to test; the first is the only one that leaves you able to upgrade again without touching logical replication.

Neither is obviously right, and the deciding factor is usually how painful the resynchronisation is, which is a function of data volume rather than of anything in the release notes. What is clearly wrong is planning either of them on the assumption that slots will come across, and finding out afterwards from a subscriber that has silently stopped receiving anything.

What this costs to verify

All of the checks above are ordinary catalog reads against the source cluster, and they need no setting and no restart. Running them a week early costs a round trip and answers the only question that matters on the night.

The expensive part is the one this feature exists to remove: recreating a slot means the subscriber starts from an empty table and copies the publication’s data again, at whatever rate the link supports. That is the cost a 17-or-later source avoids, and the cost every earlier source still pays.