C10 — pg_upgrade Readiness¶

Opt-in checks (they need a target PostgreSQL version, so they live only in the seeded-on-demand suite, never on a schedule — see The Catalog) that look for the specific things known to block or complicate a pg_upgrade run: legacy extensions, deprecated data types, and schema patterns that need attention before a major-version migration rather than being discovered mid-upgrade.

15 checks, PGHF10-001 through PGHF10-015. Every check here is PGHF-namespace and built-in — see Metadata Columns Explained for what each field below actually means, and Resetting to Defaults for why these definitions can't be hand-edited in place.

PGHF10-001 — Legacy extensions (tsearch2/plpython2u)¶

tsearch2 and plpython2u are removed outright in PostgreSQL 14 — pg_upgrade will fail if either is still installed, so this needs fixing before the upgrade attempt, not discovered during it.

How to fix

DROP EXTENSION tsearch2;

For plpython2u, there's no drop-in replacement — migrate each function's body to plpython3u first (Python 2→3 syntax differences apply), then:

DROP EXTENSION plpython2u;
CREATE EXTENSION plpython3u;

PGHF10-002 — PostGIS version check¶

PostGIS upgrades often require their own separate step after pg_upgrade completes — skipping it risks a version mismatch between the extension and the new server that can silently corrupt spatial data.

How to fix

After pg_upgrade completes, run PostGIS's own post-upgrade step before using spatial functions on the new cluster:

SELECT postgis_extensions_upgrade();

Check the PostGIS upgrade guide for the specific soft-upgrade vs. hard-upgrade path your version jump requires — some major PostGIS version changes need pg_dump/pg_restore instead of an in-place upgrade.

PGHF10-003 — abstime/reltime/tinterval columns¶

These column types were removed in PostgreSQL 12 — any table still using them must be converted to timestamp/interval before upgrading, or pg_upgrade fails outright.

How to fix

ALTER TABLE schema.the_table ALTER COLUMN the_column TYPE timestamp USING the_column::text::timestamp;

Test the cast on a representative sample first — abstime/reltime/tinterval's text representations don't always map cleanly onto timestamp/interval for edge-case values (e.g. abstime's invalid/infinity).

PGHF10-004 — money type columns¶

money-type output formatting depends on lc_monetary — a locale mismatch between the source and target server can silently corrupt the displayed (and sometimes stored) values after upgrade, not just look different.

How to fix

Either confirm lc_monetary matches exactly between source and target server before upgrading, or convert away from money to numeric (generally the safer long-term choice, since numeric has no locale dependency):

ALTER TABLE schema.the_table ALTER COLUMN price TYPE numeric(19,4) USING price::numeric;

PGHF10-005 — SQL_ASCII databases¶

SQL_ASCII disables all encoding validation on the database — any accumulated invalid byte sequences become a real problem the moment an upgrade or dump/restore tries to actually interpret the data as text.

How to fix

There's no simple ALTER DATABASE for this — encoding is fixed at CREATE DATABASE time. Before converting:

Audit the actual byte content for invalid sequences under the target encoding (commonly UTF8) — a naive dump/restore into a UTF8 database will fail or silently mangle data if any invalid sequences exist.
Fix or quarantine the offending rows, then dump/restore into a freshly created UTF8 database:

CREATE DATABASE the_db_utf8 WITH ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';

This is genuinely risky if the data has accumulated invalid bytes for years — budget real validation time, not just a mechanical dump/restore.

PGHF10-006 — Collation version¶

A collation-library version mismatch after an OS or PostgreSQL upgrade can silently reorder index entries relative to their sort order — ALTER DATABASE ... REFRESH COLLATION VERSION plus a REINDEX is what prevents index corruption from an invisible sort-order drift.

How to fix

REINDEX DATABASE the_db;  -- rebuild every index against the new collation ordering first
ALTER DATABASE the_db REFRESH COLLATION VERSION;  -- then clear the version-mismatch flag

Order matters — refreshing the recorded version before reindexing just silences the warning without actually fixing indexes that may now be subtly out of sort order.

PGHF10-007 — Tables approaching 1600-column limit¶

PostgreSQL hard-limits every table to 1600 columns — a table already near that ceiling needs restructuring before an upgrade (or before it hits the limit during normal operation) rather than failing at the worst possible moment.

How to fix

No simple config change fixes a hard architectural limit — restructure the table:

Split it vertically into two or more related tables joined by the primary key (a common pattern for very wide "everything about X" tables).
Consider whether some columns genuinely belong in a jsonb column instead of individual columns, if they're sparse/rarely-queried attributes.
Also account for dropped columns — PostgreSQL doesn't reclaim column-number slots from a DROP COLUMN, so an already-wide table with a history of dropped columns may be closer to the limit than its current visible column count suggests.

PGHF10-008 — Pending prepared transactions¶

pg_upgrade refuses to run at all with pending prepared transactions on the cluster — the same rows PGHF05-013 flags for their vacuum-blocking effect also need resolving (COMMIT PREPARED or ROLLBACK PREPARED) before any upgrade attempt.

How to fix

See PGHF05-013's remediation — resolve every row from pg_prepared_xacts with COMMIT PREPARED/ROLLBACK PREPARED before attempting pg_upgrade; it will refuse to proceed otherwise.

PGHF10-009 — Logical slots on upgrade¶

pg_upgrade does not preserve logical replication slots on versions before PG17 — any existing slots vanish across the upgrade unless they're removed and re-created deliberately, silently breaking downstream subscribers.

How to fix

On PG17+ source and target, pg_upgrade preserves logical slots automatically — no action needed beyond confirming both ends are PG17+.

On an older source, record every slot's name/plugin/subscriber before upgrading, then after the upgrade completes, re-create each slot and have every downstream subscriber re-subscribe from scratch (a full initial sync, since the old slot's position can't carry over):

SELECT slot_name, plugin FROM pg_replication_slots WHERE slot_type = 'logical';  -- run BEFORE upgrading

PGHF10-010 — C-language functions¶

User-defined C-language functions are compiled against a specific PostgreSQL major version's internals — they need recompiling for the new version, or they'll fail to load (or worse, load with incompatible ABI assumptions) post-upgrade.

How to fix

Locate the source for each flagged function's shared library.
Rebuild it against the target major version's pg_config/development headers (PGXS-based builds pick this up automatically from pg_config --pgxs on the target install).
Install the rebuilt .so on the target server before pg_upgrade runs, at the path the function definition expects — a mismatch here fails at function-call time, not at upgrade time, so test it explicitly rather than assuming success.

PGHF10-011 — plpython2u functions¶

plpython2u was removed in PostgreSQL 14 — any function still written against it needs migrating to plpython3u before the upgrade, since there's no compatibility shim on the target version.

How to fix

See PGHF10-001's remediation — migrate each function body from Python 2 to Python 3 syntax/semantics (print statement → function, str/unicode handling, integer division, and similar), then:

CREATE EXTENSION IF NOT EXISTS plpython3u;
CREATE OR REPLACE FUNCTION ... LANGUAGE plpython3u AS '<function body>';

PGHF10-012 — Ghost extensions¶

An extension recorded as installed but whose control file is no longer available on disk (e.g. after an OS package removal) causes pg_upgrade --check to fail outright — needs either reinstalling the package or dropping the extension first.

How to fix

Either reinstall the OS package that provides the extension's control file (preferred if anything still depends on it):

apt install postgresql-<major>-<extension>   # or the equivalent for your distro

or, if it's genuinely unused, drop the catalog entry:

DROP EXTENSION the_ghost_extension;

PGHF10-013 — Large objects¶

A large number of large objects can significantly extend pg_upgrade's runtime — worth knowing the count ahead of time so the maintenance window is sized realistically, rather than discovering it mid-upgrade.

How to fix

No config fix — this is a maintenance-window sizing input, not a defect. Use pg_upgrade --link (hard-link mode) instead of the default copy mode where the filesystem supports it — it avoids physically copying large-object data and dramatically cuts upgrade time for large-object-heavy databases. If large objects are no longer needed, lo_unlink() unused ones before upgrading to shrink the count for real.

PGHF10-014 — Custom tablespace paths¶

Every custom tablespace path needs to exist and be writable on the target system before pg_upgrade runs — an overlooked tablespace mount is a common, entirely avoidable upgrade-day failure.

How to fix

For each path in this check's output, before upgrading, confirm on the target server:

ls -ld /the/tablespace/path

exists, is owned by the PostgreSQL OS user, and has the mount/disk space actually available. pg_upgrade --check catches this too, but confirming it manually ahead of time avoids finding out at the start of the actual maintenance window.

PGHF10-015 — datconnlimit=0 databases¶

pg_upgrade needs to connect to every database in the cluster to do its work — a database with datconnlimit=0 refuses all connections, including pg_upgrade's own, and will cause the upgrade to fail on that database specifically.

How to fix

Temporarily lift the limit before upgrading, then restore it afterward if it was intentional:

ALTER DATABASE the_db CONNECTION LIMIT -1;
-- ... run pg_upgrade ...
ALTER DATABASE the_db CONNECTION LIMIT 0;  -- restore afterward, if the restriction was deliberate

Continue to C11 — Security Posture.