pg_upgrade --check runs the compatibility checks without changing anything, and you can run it while the old server is still up. It stops at the first failed check, so a cluster with three problems shows them one at a time. The exception is the data-type checks, which from PostgreSQL 17 run as one step and list every failing check together. This page shows the messages from a real PostgreSQL 14.24 → 18.6 run, what each means and how to clear it, then the checks it does not cover.

The lab used Docker and generated data. The script and full output are public. Run the check with the same transfer mode you will use for the real upgrade (--link, --clone, --copy-file-range or --swap), because some checks are mode-specific.

"old cluster does not use data checksums but the new one does"

Checking cluster versions                                     ok
old cluster does not use data checksums but the new one does
Failure, exiting

Cause: from PostgreSQL 18, initdb enables data checksums by default. PostgreSQL 14's initdb did not, so most PG14 clusters have none. pg_upgrade requires both clusters to match.

Fix: recreate the new cluster without checksums, which the PostgreSQL 18 release notes recommend for this case:

/usr/lib/postgresql/18/bin/initdb -D /var/lib/postgresql/18/main --no-data-checksums

If you want checksums, enable them on the old cluster first with pg_checksums --enable while it is shut down. That rewrites every data file, so time it on a copy. Check either cluster with pg_controldata <datadir> | grep checksum. Packaging scripts such as Debian's pg_createcluster pass their own initdb options, so check what yours does.

This one does not apply to RDS or Aurora, where AWS creates the new cluster.

"The source cluster contains prepared transactions"

Checking for prepared transactions
The source cluster contains prepared transactions
Failure, exiting

Cause: a two-phase commit transaction was prepared and never committed or rolled back. It usually comes from an XA transaction manager (some Java application servers and message brokers) that lost track of it. Besides blocking the upgrade, an orphaned prepared transaction holds locks, stops vacuum from removing dead rows and, left long enough, stops the database accepting writes.

Fix: find it, confirm with the owning application which outcome is correct, then resolve it:

SELECT gid, prepared, owner, database FROM pg_prepared_xacts;

COMMIT PREPARED 'stuck_2pc';   -- or
ROLLBACK PREPARED 'stuck_2pc';

Do not guess between commit and rollback on production data. If nothing should be using two-phase commit, set max_prepared_transactions = 0 on the new cluster so it cannot happen again.

"Checking for reg* data types in user tables"

Checking data type usage                                      failed check: Checking for reg* data types in user tables
Data type checks failed:
Your installation contains one of the reg* data types in user tables.
These data types reference system OIDs that are not preserved by
pg_upgrade, so this cluster cannot currently be upgraded.  You can
drop the problem columns and restart the upgrade.
A list of the problem columns is in the file:
    /work/new18/pg_upgrade_output.d/20261021T034907.196/tables_using_reg.txt

The file names each column:

In database: postgres
  public.job_hooks.handler

Cause: a user table stores regproc, regprocedure, regoper, regoperator, regconfig, regdictionary, regnamespace or regcollation values. They are stored as OIDs, which change in the new cluster. regclass, regrole and regtype are allowed.

Fix: convert the column to text, which stores the name instead of the OID, and cast it back where the application needs it:

ALTER TABLE job_hooks ALTER COLUMN handler TYPE text;

That rewrites the table and takes an ACCESS EXCLUSIVE lock, so plan it for a large table. Look in pg_upgrade_output.d after any failure; most data-type checks write a file like this one.

What passed but still matters

After those three fixes, the check printed *Clusters are compatible*. The PG14 cluster still had a logical replication slot used for CDC, and after the real upgrade the slot was gone. pg_upgrade only migrates logical slots from PostgreSQL 17 or later. See pg_upgrade drops logical replication slots from PostgreSQL 14.

The upgrade also ended with this, because PostgreSQL 18 now transfers most optimizer statistics but not all:

Some statistics are not transferred by pg_upgrade.
Once you start the new server, consider running these two commands:
    /usr/lib/postgresql/18/bin/vacuumdb -U postgres --all --analyze-in-stages --missing-stats-only
    /usr/lib/postgresql/18/bin/vacuumdb -U postgres --all --analyze-only

In the lab, column statistics for orders survived, but the extended statistics object had no data until vacuumdb --missing-stats-only ran, and pg_stat_user_tables started from zero. On a PG14 → 17 upgrade nothing survives; that is the usual cause of a slow morning after.

Other failures from 14 that the check reports

Not reproduced in this lab, but checked by pg_upgrade or documented by AWS:

  • Missing libraries: "Checking for presence of required libraries" fails when an extension's shared library is not installed for the new version. Install the extension package for 18 first. loadable_libraries.txt names the library and the databases that use it.
  • Install user: the user you connect as must be the bootstrap superuser of both clusters. Pass it with -U.
  • New cluster not empty: the target must be freshly created by initdb, with no user objects.
  • Invalid databases: a database left by an interrupted DROP DATABASE has datconnlimit = -2. Drop it.
  • Locale and encoding: template databases must be compatible. Create the new cluster with the same locale settings as the old one.

The RDS and Aurora equivalent

On RDS and Aurora you do not run pg_upgrade yourself. The managed upgrade runs a precheck first and writes the result to pg_upgrade_precheck.log in the instance logs. AWS documents the same classes of blocker: prepared transactions, reg* columns, invalid databases, logical replication slots, extensions not supported on the target, and very large numbers of large objects. The safe way to see the list is to restore a snapshot (or clone an Aurora cluster), start the upgrade on the copy and read that log. See the RDS major upgrade process.

Run all the checks before the window

-- Prepared transactions
SELECT gid, prepared, owner, database FROM pg_prepared_xacts;

-- reg* columns that block pg_upgrade (run in each database)
SELECT n.nspname, c.relname, a.attname, a.atttypid::regtype
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT a.attisdropped AND a.attnum > 0
  AND a.atttypid IN ('regproc'::regtype, 'regprocedure'::regtype, 'regoper'::regtype,
                     'regoperator'::regtype, 'regconfig'::regtype, 'regdictionary'::regtype,
                     'regnamespace'::regtype, 'regcollation'::regtype)
  AND n.nspname NOT IN ('pg_catalog', 'information_schema');

-- Invalid databases
SELECT datname FROM pg_database WHERE datconnlimit = -2;

-- Logical slots that will not survive an upgrade from 14
SELECT slot_name, plugin, active FROM pg_replication_slots WHERE slot_type = 'logical';

The full sequence, from inventory to rollback criteria, is in the PostgreSQL 14 upgrade checklist.