skip to content

What does PostgreSQL's pg_upgrade --check verify, and why run it days before the window against the still-running old server?

level: middleimportance: should knowfreq 48%

answer

  1. check clusters only, change nothing
  2. live check needs a second port
  3. add the transfer-mode flag
  4. a file lists every offender
  5. cannot vouch for module binaries

basics

~20 s

pg_upgrade --check runs the upgrade's compatibility checks without changing any data: control-data settings, blocked data types, prepared transactions, missing extension libraries and more. It works against the live old server, so blockers surface days ahead instead of mid-outage.

solid answer

~50 s

`pg_upgrade --check` performs only the checks and changes no data. Run the **new** version's `pg_upgrade` against an `initdb`'d, empty new cluster; the old server may keep serving traffic as long as the old and new port numbers differ. Pass your transfer mode (`--link`, `--clone`, `--copy-file-range` or `--swap`) as well so the mode-specific checks, such as the same-file-system test, run. It compares `pg_controldata` settings (including the checksum setting), confirms the new cluster is empty, that every non-`template0` database accepts connections, that there are no prepared transactions, no columns of unsupported `reg*` types, no loadable libraries missing from the new installation, plus version-specific checks such as inconsistent NOT NULL constraints on inheritance children for pre-18 sources. Each failure names a file listing the offending objects. It cannot prove that third-party module binaries are compatible, nor time the run.

code

bash · 5 lines
bash
# old 14 server still serving the e-learning app on 5432; new 18 cluster initialised and stopped
/usr/lib/postgresql/18/bin/pg_upgrade --check --link \
  -b /usr/lib/postgresql/14/bin -B /usr/lib/postgresql/18/bin \
  -d /srv/pg/14/main -D /srv/pg/18/main \
  -p 5432 -P 5433

go deeper

for a junior

Know that pg_upgrade has a --check option that reports problems without changing data, and that you run it before the real upgrade.

for a middle

Explain how to run a live check, why the mode flag matters, and the common blockers with their fixes, from prepared transactions to blocked data types.

for a senior

Make --check a repeated step in the runbook, read its report files, and plan for what it cannot catch: module binary compatibility, duration and schema-restore failures.

for a principal

Treat the preflight as fleet tooling: identical schemas produce identical reports and post-steps, so checks can gate a standard upgrade pipeline.

## What --check is `pg_upgrade --check` (`-c`) is the upgrade **preflight**. Its option text is short: check clusters only, don't change any data. It performs the same compatibility checks a real run starts with and then stops, so the old cluster is unmodified and can keep running or be restarted. The manual adds that it will also outline any manual adjustments needed after the upgrade. The value is timing. Every blocker it finds during a real run is found while the application is already down; found days earlier, it is a ticket instead of an extended outage. ## Running it against the live server 1. Install the new version's binaries and **extension shared objects** (for example a contrib module's `.so`) next to the old installation. 2. `initdb` the new cluster with options compatible with the old one; leave it stopped and empty. 3. Make sure `pg_upgrade` can connect repeatedly to both clusters, for example with `peer` authentication or a `~/.pgpass` file. 4. Run the **new** version's `pg_upgrade` with `--check`, the old and new bin and data directories, and the transfer mode you intend to use. 5. If the old server is running, give its real port with `-p` and a different one with `-P`; a live check with equal ports is rejected. ## What it checks The checks depend on the source version; for a 14 to 18 upgrade they include: | Check | Fails when | Typical fix | |---|---|---| | Control data | Block size, compile options or the **data checksum** setting differ | Re-run `initdb` with matching options | | New cluster empty | The target already holds user relations | Re-create the target cluster | | Database connections | A non-`template0` database has `datallowconn` false | Allow connections or drop it | | Prepared transactions | `pg_prepared_xacts` is not empty | `COMMIT PREPARED` or `ROLLBACK PREPARED` | | Data types | User columns use `regproc`, `regprocedure`, `regoper`, `regoperator`, `regconfig`, `regdictionary`, `regnamespace` or `regcollation` | Change those columns' types | | Loadable libraries | A library the old databases reference is missing from the new installation | Install the new-version build of the module | | NOT NULL inheritance | A child column lacks the NOT NULL its parent has (sources before 18) | `ALTER TABLE ... ALTER column SET NOT NULL` | | Logical slots and subscriptions | Prerequisites unmet (only for sources 17 or later) | Adjust `wal_level`, slot and origin limits | | Transfer mode | Directories on different file systems for link or swap, or no reflink support for clone | Change the layout or the mode | On most failures, `pg_upgrade` prints the problem and the path of a file listing the offending tables, columns, libraries or databases, so the fix is mechanical. ## What it does not tell you - **Binary compatibility of external modules.** The manual is explicit that `pg_upgrade` cannot check this; it only finds missing libraries. - **How long the run will take.** Only a rehearsal on a copy tells you that; the manual suggests upgrading a schema-only copy with dummy data for deployment testing. - **Application incompatibilities.** Those are in the release notes' Migration sections and in your test suite. - **Everything that can break later.** A schema restore can still fail during the real run, for example on a contrib module's objects; `pg_upgrade` then exits and you revert to the old cluster, which is unmodified at that point. ## Reading a failure Each failed check prints `fatal`, a short explanation and, where objects are involved, the path of a report file under `pg_upgrade_output.d` in the new cluster's data directory. That directory is removed automatically only after a successful upgrade, so after a failed check the evidence stays there. Typical sequences: 1. A `reg*` column report names table and column; change the column to `text` or another type before the window. 2. A missing-library report names the library and the databases that reference it; install the module built for 18, or drop what uses it if it is unused. 3. A NOT NULL inheritance report names each child column; the message itself gives the `ALTER TABLE ... SET NOT NULL` fix. 4. A connection-status report names databases with `datallowconn` false; allow connections or drop them. ## Making it routine - Run it when you plan the upgrade, again the day before, and once more at the start of the window: new prepared transactions or new columns can appear in between. - Keep the report files with the change record. - Across many clusters with the same schema, the checks and the post-upgrade steps repeat identically, because they depend on the schema, not on user data.

  • pg_upgrade --check passed last week. Why can the real run still fail?
    The clusters change between check and run: someone leaves a prepared transaction open, adds a column of a blocked type, or installs a module the new side lacks. Some failures appear only while the schema is restored into the new cluster, such as a contrib module's objects. When that happens `pg_upgrade` exits, the old cluster is still unmodified, and you fix it and retry, so re-run `--check` at the start of the window.
  • Why must the old and new ports differ during a live check?
    In a live check the old postmaster is already listening on its port, and `pg_upgrade` must start the new cluster's temporary postmaster as well. Both cannot bind the same port, so `pg_upgrade` rejects equal ports for a live check. In a real run both clusters are stopped and started one at a time, so the same port is fine.

  • pg_upgrade --check needs the old server stopped, so it can only run inside the window.
  • The old version's pg_upgrade binary is the one to run for the check.
  • A clean --check proves every third-party extension's binaries are compatible.
  • A plain --check already validates link mode, so the mode flag is unnecessary.
  • Restarting the old server clears prepared transactions before the upgrade.