# Why does pg_upgrade --check fail on logical replication slots? > **Quick answer:** Unconsumed changes are the usual cause, but they are only one of six > conditions. The others concern the new cluster and the output plugin, so you > can drain every slot and still fail. Read > `invalid_logical_slots.txt`, which distinguishes a slot that can catch up from > one that cannot, and check the new cluster's settings before the window. ## The bench Everything measured on this page was run on 2026-09-20 in one Debian trixie container under Docker 29.1.3, on a host with 8 vCPUs and 62 GB of RAM running Linux 6.17.0-1022-azure. The container carries PostgreSQL 16.15 (Debian 16.15-1.pgdg13+2), 17.11 (Debian 17.11-1.pgdg13+2) and 18.6 (Debian 18.6-1.pgdg13+2), and every upgrade used the **18.6** `pg_upgrade` binary, as the documentation requires. > "Always run the pg_upgrade binary of the new server, not the old one." > ([PostgreSQL 18, `pg_upgrade`](https://www.postgresql.org/docs/18/pgupgrade.html), read on 2026-09-20) Storage is the container's own overlay filesystem on local disk. That affects the `pg_checksums` timings below, but nothing else on this page. ## What is the problem? A logical replication slot is a bookmark the server keeps for a consumer. It records the point in the write-ahead log that the consumer has confirmed and prevents the server from discarding later WAL. From an old cluster at PostgreSQL 17.0 or later, `pg_upgrade` can **attempt** to migrate logical slots on a publisher. It can separately migrate subscription dependencies and replication origins on a subscriber. These mechanisms have different conditions, and this page measures both. The write-ahead log itself is not migrated. The consumer therefore has to be caught up first: the new cluster has no WAL for it to replay. ## The six conditions, all measured PostgreSQL lists the publisher-side prerequisites in one place. > "The old cluster has replicated all the transactions and logical decoding > messages to subscribers." > ([PostgreSQL 18, Upgrading Logical Replication Clusters](https://www.postgresql.org/docs/18/logical-replication-upgrade.html), read on 2026-09-20) That is the condition most people know. The table shows what happens when each of the six conditions is violated, one at a time, on the same 17.11 to 18.6 pair. In rows 2 to 6 the slot was **fully drained** first, so the catch-up condition was satisfied and only the condition under test could fail: | what was wrong | the check line that failed | what it said | |---|---|---| | slot had unconsumed changes | `Checking for valid logical replication slots` | `invalid_logical_slots.txt`: `The slot "slot_behind" has not consumed the WAL yet` | | new cluster `wal_level = replica` | `Checking for new cluster logical replication slots` | `"wal_level" must be "logical" but is set to "replica"` | | new cluster `max_replication_slots = 0` | `Checking for new cluster logical replication slots` | `"max_replication_slots" (0) must be greater than or equal to the number of logical replication slots (1) on the old cluster` | | plugin not in new cluster's `output_plugin_libraries` | `Checking for new cluster logical replication slots` | `Your installation contains logical replication slots with plugins that are not allowed by the new cluster's output_plugin_libraries setting.` | | plugin shared object absent from the new installation | `Checking for presence of required libraries` | `loadable_libraries.txt`, and the slot check above had already passed `ok` | | new cluster already had a permanent logical slot | `Checking for new cluster logical replication slots` | `expected 0 logical replication slots but found 1` | Five of these six checks occur after the consumer has caught up, so **draining is necessary but not sufficient.** ### The plugin fails in two different places `output_plugin_libraries` is an allow-list. It does not indicate whether the plugin is installed. Measured on 2026-09-20 on that 17.11 to 18.6 bench, a drained `test_decoding` slot against a new cluster whose `output_plugin_libraries` was set to `pgoutput` alone failed at `Checking for new cluster logical replication slots`. The same slot against a new cluster with the default allow-list, but with `test_decoding.so` moved out of the 18.6 library directory, passed the slot check and failed later at `Checking for presence of required libraries`. If you use `wal2json` or another non-core plugin, installing the extension on the old cluster is not enough. The plugin's shared library has to be present in the **new** server's library directory and allowed by the new server's allow-list. > "The output plugins referenced by the slots in the old cluster must be > installed in the new PostgreSQL executable directory." > ([PostgreSQL 18, Upgrading Logical Replication Clusters](https://www.postgresql.org/docs/18/logical-replication-upgrade.html), read on 2026-09-20) Measured on 2026-09-20 in that Debian trixie container under Docker 29.1.3, `output_plugin_libraries` defaults to `pgoutput, test_decoding` on both 17.11 and 18.6. This is why users of ordinary logical replication do not encounter this check. ### "Has not consumed the WAL yet" and "is invalid" are different problems The same file reports both states, and they have opposite fixes. Measured on 2026-09-20 on that 17.11 to 18.6 pair in that Debian trixie container under Docker 29.1.3, a slot whose required WAL had been removed under `max_slot_wal_keep_size = 16MB` produced: ``` The slot "slot_dead" is invalid ``` That slot cannot be drained because its WAL is gone. The options are to drop it and rebuild the consumer or abandon the upgrade. A slot reported as `has not consumed the WAL yet` is healthy but behind, so waiting is the fix. ## The preflight query, and the column that decides it Run this on the old cluster while it is running. ```sql SELECT slot_name, slot_type, plugin, database, active, temporary, restart_lsn, confirmed_flush_lsn, wal_status, invalidation_reason, conflicting, CASE WHEN restart_lsn IS NULL THEN NULL ELSE pg_size_pretty( pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) END AS retained_wal FROM pg_replication_slots ORDER BY slot_name; ``` Measured on 2026-09-20 on a PostgreSQL 17.11 cluster in container `dbg-pgu` holding one logical slot and one never-connected physical slot, that returns every selected column, expanded here because the row is too wide to read otherwise: ``` -[ RECORD 1 ]-------+-------------- slot_name | logi slot_type | logical plugin | test_decoding database | postgres active | f temporary | f restart_lsn | 0/14EEDB8 confirmed_flush_lsn | 0/14EEDF0 wal_status | reserved invalidation_reason | conflicting | f retained_wal | 56 bytes -[ RECORD 2 ]-------+-------------- slot_name | phys slot_type | physical plugin | database | active | f temporary | f restart_lsn | confirmed_flush_lsn | wal_status | invalidation_reason | conflicting | retained_wal | ``` Three details affect how you read a slot inventory. **A physical slot has nothing in these columns.** `restart_lsn`, `confirmed_flush_lsn` and `wal_status` are all null on a physical slot that has never been connected to. An inventory query that computes retained WAL as `pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)` returns null for that row rather than a number, which is why the `CASE` is there. Physical slots are not part of the `pg_upgrade` slot check; they belong to streaming replication. **`invalidation_reason` is the column that answers the question, not `conflicting`.** The documentation names `conflicting` and nothing else. > "All slots on the old cluster must be usable, i.e., there are no slots whose > `pg_replication_slots`.`conflicting` is not `true`." > ([PostgreSQL 18, Upgrade, prerequisites for a publisher](https://www.postgresql.org/docs/18/logical-replication-upgrade.html), > read 2026-09-20) On this bench that column was **false** for the slot that blocked the upgrade. Measured on 2026-09-20 on that 17.11 cluster, the slot that produced `The slot "slot_dead" is invalid` read `active = f`, `wal_status = lost`, `invalidation_reason = wal_removed`, `conflicting = f`. A preflight that tests only `conflicting` passes a slot that then stops the upgrade. Test `invalidation_reason IS NOT NULL`, or test whether `wal_status` is `lost`. **`active = false` identifies candidates, not garbage.** It is normal between polls for a consumer that connects periodically and permanent for an abandoned one. Before dropping a slot, confirm that no change-data-capture pipeline, backup workflow or failover process depends on it. An inactive slot is not necessarily abandoned, and dropping it commits you to rebuilding whatever was reading it. Then check what the new cluster will need. These settings belong on the **new** cluster and are tested against counts taken from the old one: ```sql SELECT current_setting('wal_level') AS wal_level, current_setting('max_replication_slots') AS max_replication_slots, current_setting('output_plugin_libraries', true) AS output_plugin_libraries; ``` ## The subscriber is a separate upgrade with separate rules The page so far is about the publisher. A subscriber has its own conditions, check line and report file. > "All the subscription tables in the old subscriber should be in state `i` > (initialize) or `r` (ready)." > ([PostgreSQL 18, Upgrading Logical Replication Clusters](https://www.postgresql.org/docs/18/logical-replication-upgrade.html), read on 2026-09-20) Measured on 2026-09-20, with a 17.11 publisher and a 17.11 subscriber and one subscription that had finished its initial copy, `srsubstate` was `r`, a replication origin existed, and the 18.6 check reported `Checking for new cluster configuration for subscriptions ok` and `*Clusters are compatible*`. The same pair, with the subscriber stopped part-way through the initial copy of a four million row table, had `srsubstate` set to `d`: ``` Checking for subscription state fatal Your installation contains subscriptions without origin or having relations not in i (initialize) or r (ready) state. ``` and `subs_invalid.txt` named the table: ``` The table sync state "d" is not allowed for database:"postgres" subscription:"s" schema:"public" relation:"big" ``` The subscriber's capacity setting differs from the publisher's, so it is easy to set the wrong one. > "The new cluster must have `max_active_replication_origins` configured to a > value greater than or equal to the number of subscriptions present in the old > cluster." > ([PostgreSQL 18, Upgrading Logical Replication Clusters](https://www.postgresql.org/docs/18/logical-replication-upgrade.html), read on 2026-09-20) Measured on 2026-09-20 on that pair with `max_active_replication_origins = 0` on the 18.6 cluster and everything else correct: ``` Checking for new cluster configuration for subscriptions "max_active_replication_origins" (0) must be greater than or equal to the number of subscriptions (1) on the old cluster Failure, exiting ``` ## The version boundary is the dangerous part Below PostgreSQL 17.0 there is no check because there is no migration. > "Logical slots on clusters before version 17.0 will silently be ignored." > ([PostgreSQL 18, Upgrading Logical Replication Clusters](https://www.postgresql.org/docs/18/logical-replication-upgrade.html), read on 2026-09-20) Measured on 2026-09-20, a PostgreSQL 16.15 cluster with a slot holding 5,000 unconsumed rows passed `--check` with `*Clusters are compatible*`, and the real upgrade to 18.6 then completed. On the new cluster: ``` slots_on_new -------------- 0 rows_on_new ------------- 5000 ``` The data arrived. The slot did not, and nothing reported this. A failed check would at least tell you to investigate. Plan the subscriber rebuild yourself before upgrading anything older than 17.0. ## Data checksums: a match requirement, not a direction PostgreSQL 18 enables data checksums by default at `initdb`; 17 did not. Measured on 2026-09-20 in that Debian trixie container under Docker 29.1.3, `pg_controldata` reports `Data page checksum version: 0` on a default 17.11 `initdb` and `1` on a default 18.6 one. `pg_upgrade` requires the two to agree, and all four combinations were run: | old cluster 17.11 | new cluster 18.6 | result | |---|---|---| | `initdb` default (off) | `initdb` default (on) | `old cluster does not use data checksums but the new one does`, `Failure, exiting` | | `initdb --data-checksums` | `initdb` default (on) | `*Clusters are compatible*` | | `initdb` default (off) | `initdb --no-data-checksums` | `*Clusters are compatible*` | | `initdb --data-checksums` | `initdb --no-data-checksums` | `old cluster uses data checksums but the new one does not`, `Failure, exiting` | This is the first failure for **many** 17 to 18 upgrades rather than for almost everybody, and the fourth row is the trap in the usual advice. A 17 cluster created with `--data-checksums`, which is common, fails against a new cluster initialised with `--no-data-checksums` in the other direction. Read the source cluster's `pg_controldata` before choosing the target's `initdb` flags. ### If you want to add checksums instead `pg_checksums --enable` needs the cluster stopped, and it says so: ``` pg_checksums: error: cluster must be shut down ``` > "The server must be shut down cleanly before running pg_checksums." > ([PostgreSQL 18, `pg_checksums`](https://www.postgresql.org/docs/18/app-pgchecksums.html), read on 2026-09-20) It does not rewrite every block. It rewrites the blocks whose checksum has to change. On a fresh enable that is nearly all of them. Measured on 2026-09-20 on 17.11 in that container, two runs: | data directory | blocks scanned | blocks written | elapsed | per block | |---|---|---|---|---| | 701 MB | 46,589 | 46,587 | 4.97 s | 107 µs | | 2,513 MB | 190,375 | 190,373 | 13.39 s | 70 µs | The written count is two short of the scanned count in both runs. The distinction between "scans everything" and "rewrites what must change" is real, but it makes little practical difference when enabling checksums from scratch. **Do not scale the duration by the size of your data directory.** Between those two 2026-09-20 runs on 17.11 in that Debian trixie container under Docker 29.1.3, the block count grew 4.1 times and the elapsed time grew 2.7 times on the same host, storage and version. The fixed sync cost is a larger share of the smaller run. Extrapolating linearly from a small rehearsal over-predicted by about 50 percent here, before storage throughput, relation layout, tablespaces, cache state and concurrent activity enter the calculation. Rehearse on production-like storage and time it. Whether to add checksums is separate from this upgrade. They improve detection of some page corruption, and changing them in a replicated topology has its own rules about doing it consistently across nodes or rebuilding standbys afterwards. Decide it separately and in its own window. ## The failure that shows you the wrong error If the old cluster has `wal_level` below `logical` when `pg_upgrade` starts it, the visible failure is a connection error: ``` connection to server on socket "/work/.s.PGSQL.50432" failed: No such file or directory could not connect to source postmaster started with the command: "/usr/lib/postgresql/17/bin/pg_ctl" -w -D "/work/old" -o "-p 50432 -b ..." start Failure, exiting ``` `pg_upgrade` starts the old cluster itself to inspect it. When that start fails, you see the failed connection rather than the reason. The reason was in the log the message points at, one line above the part everybody quotes: ``` FATAL: logical replication slot "slot_behind" exists, but "wal_level" < "logical" HINT: Change "wal_level" to be "logical" or higher. ``` **When the temporary postmaster fails to start, read its log before diagnosing the connection error.** A connection failure here can also be genuine: socket directory permissions, a socket path too long for the current working directory, a port collision, ownership or authentication. PostgreSQL provides `-s` for the path-length case specifically. > "In some situations the path name for the current directory might be too long > to be a valid socket name. In that case you can use the `-s` option to put the > socket files in some directory with a shorter path name." > ([PostgreSQL 18, `pg_upgrade`](https://www.postgresql.org/docs/18/pgupgrade.html), read on 2026-09-20) ### The general shape of this, and the fix The temporary postmaster must start with settings that permit the slot check. A `wal_level` supplied on the command line by a container supervisor is a common way to lose the setting because `pg_upgrade` supplies its own arguments. The same failure can come from an unexpected config directory, an include file that is not read or a wrapper that sets environment variables. The remedy is not limited to putting the setting in `postgresql.conf`. `pg_upgrade` passes options through. Measured on 2026-09-20 on that 17.11 to 18.6 pair in that Debian trixie container under Docker 29.1.3, with `wal_level` set only on the old server's command line and a slot present, the plain check failed with the connection error above, and this succeeded: ``` pg_upgrade -b /usr/lib/postgresql/17/bin -B /usr/lib/postgresql/18/bin \ -d /work/old -D /work/new -s /work \ -o '-c wal_level=logical' --check ... Checking for new cluster logical replication slots ok *Clusters are compatible* ``` `-o` / `--old-options` and `-O` / `--new-options` go to the temporary old and new servers respectively. ## Where the report files are `pg_upgrade_output.d` is created in the directory of the new cluster, not where you ran the command, and it is removed when the run succeeds. > "pg_upgrade creates various working files, such as schema dumps, stored within > `pg_upgrade_output.d` in the directory of the new cluster." > ([PostgreSQL 18, `pg_upgrade`](https://www.postgresql.org/docs/18/pgupgrade.html), read on 2026-09-20) The cleanup explains why a successful rehearsal leaves nothing to compare against: > "`pg_upgrade_output.d` and its contained files will be removed automatically if > pg_upgrade completes successfully; but in the event of trouble, the files there > may provide useful debugging information." > ([PostgreSQL 18, `pg_upgrade`](https://www.postgresql.org/docs/18/pgupgrade.html), read on 2026-09-20) Pass `-r` / `--retain` if you want the artefacts from a successful run. Three report files appeared on this bench: `invalid_logical_slots.txt`, `loadable_libraries.txt` and `subs_invalid.txt`. ## Draining a slot by hand For a slot with a live consumer, start the consumer and let `confirmed_flush_lsn` stop moving, then stop it and shut the cluster down cleanly. For a slot whose consumer you intend to rebuild anyway, you can consume the pending changes yourself: ```sql SELECT count(*) FROM pg_logical_slot_get_binary_changes( 'slot_behind', NULL, NULL, 'proto_version', '1', 'publication_names', 'p'); ``` That changes the consumer's position: those changes are gone from the slot and whatever was meant to receive them will not get them. Measured on 2026-09-20 on that 17.11 cluster it returned 5,003 changes for 5,000 inserted rows. Decoding the first byte of each message gives 5,000 `I` for the inserts, one `B` for begin, one `C` for commit and one `R`, a relation message describing the table. The relation message is sent before the first change for each table in a decoding session, so the subscriber knows the column layout before the first row arrives. **A change count from a slot is not a row count**, and on a transaction touching several tables the gap grows with the number of tables rather than staying at three. To drop a slot you have decided is finished: ```sql SELECT pg_drop_replication_slot('slot_behind'); ``` One more detail: `confirmed_flush_lsn` does not have to equal `pg_current_wal_lsn()` for the check to pass. Measured on 2026-09-20 on that PostgreSQL 17.11 cluster after draining, the slot sat at `0/1637E98` while the shutdown checkpoint written afterwards was at `0/1637F10`, 120 bytes further on, and the check passed. `pg_upgrade` treats a slot as caught up when no decodable changes remain after it. A shutdown checkpoint is not a decodable change, so making those two numbers equal on a live cluster is chasing a moving target. ## The order to do this in 1. Read `pg_controldata` on the old cluster for `Data page checksum version`, and choose the new cluster's `initdb` flags to match it. 2. Run `pg_upgrade --check` days before the window, with the transfer mode you intend to use in production, because mode-specific checks exist. `--link`, `--clone` and `--swap` are all in 18.6. 3. Inventory the slots with the query above. Sort them into catch up, repair or rebuild the consumer, retire and drop, and leave alone. 4. Set `wal_level`, `max_replication_slots` and, for a subscriber, `max_active_replication_origins` on the **new** cluster. 5. Confirm the output plugin's shared library is installed for the new version and allowed by `output_plugin_libraries`. 6. Confirm the new cluster has no permanent logical slots of its own. 7. For a publisher and subscriber pair: disable the subscriptions, upgrade and start the publisher, then upgrade the subscriber, recreate on the subscriber any publisher tables added in the interval, then enable the subscriptions and refresh the publications. Writes at the publisher during the subscriber's upgrade are replicated once it is back. 8. Drain the live consumers, shut down cleanly, check again and only then upgrade. 9. After the upgrade, run the two commands `pg_upgrade` prints. Measured on 2026-09-20 on 18.6 in that Debian trixie container under Docker 29.1.3, its closing output was `vacuumdb --all --analyze-in-stages --missing-stats-only` followed by `vacuumdb --all --analyze-only`. Two of those steps change what you can undo. `--link` leaves the old cluster unsafe to restart once the new one has started, and `--swap` does so once its file transfer begins. The rollback plan is therefore a decision made at step 2, not at step 8. Step 2 is the one people skip, and it is free. ## Why does AI-generated code cause this? The first problem is the generated upgrade runbook. It is the standard `pg_upgrade` procedure, correct in general, but it has no step for slots because slots are not part of the general procedure. Nothing in "upgrade PostgreSQL from 17 to 18" mentions logical replication. The second problem is the response to the error. The fastest way to make this check pass is to drop every slot, a suggestion that appears readily and silently commits you to rebuilding every subscriber from a full copy. That may be the right call, but it should be a decision rather than a way to get the check green. The third problem is the connection error. Given "could not connect to source postmaster", the natural diagnosis is permissions, sockets or ports, while the real cause is in a log file the message points at and nobody opens. ## How do I stop it coming back? **Alert on retained write-ahead log per slot and on `invalidation_reason`.** The first is the same metric that prevents a full disk. The second tells you that a consumer has passed the point where catching up is possible. **Put `wal_level` in the configuration file**, and know that `-o` exists when something else starts the server for you. **Run `pg_upgrade --check` on a restored copy regularly**, not once at the start of the window. It is read-only against the old cluster and finds every one of these in advance, including the five that draining does not fix. **Keep a written inventory of slots and who owns each one.** The difficult part of this failure is finding out whether anyone still needs the slot. The fix is usually straightforward. ## How DBGorilla helps The slot that fails your upgrade has usually been retaining write-ahead log for weeks, and the reason nobody noticed is that nothing was watching it. DBGorilla connects read-only and gives your AI coding agent metrics as time series with rates computed across them, the topology and how it has changed over time, and the workload and activity behind it. That is what turns a slot nobody owns into something visible before it is blocking a maintenance window. It surfaces and explains; it does not write to your database. [Get started free →](https://app.dbgorilla.com/signup) ## Related - [How to handle database migrations safely](https://www.dbgorilla.com/learn/postgres/how-to-handle-database-migrations-safely/) - [What is blocking my migration? How to read pg_locks](https://www.dbgorilla.com/learn/postgres/what-is-blocking-my-migration-how-to-read-pg-locks/)