pg_upgrade and extensions

What the tool is doing

A major version upgrade changes the on-disk catalog format. pg_upgrade avoids a dump and restore by converting the catalog and leaving the user data files alone, which is why it finishes in minutes on a database that would take a day to reload. The user data is byte-compatible between majors; only the metadata around it is not.

That design decides everything else on this page. Because the data files are reused rather than rewritten, every piece of code that reads them has to exist on the new installation before the conversion runs, in a build made for the new major version. Core types and operators come with the server. Anything an extension contributed does not.

It also decides what the exercise is made of. A dump and restore is one long stream of bytes and its duration is a function of data volume. An upgrade in place is a short catalog conversion plus a file-transfer step whose duration is a function of how the files move, and that step is the only part of it you get to choose. Most of the decisions below are about the choice, or about the things it drags along behind it.

The audit, which is per database

Extensions are installed into a database, not into a cluster, so a cluster-level answer requires walking every database on it:

psql -Atc "SELECT datname FROM pg_database
           WHERE datallowconn AND NOT datistemplate ORDER BY 1" \
| while read -r db; do
    psql -d "$db" -Atc "
      SELECT current_database(), extname, extversion
      FROM pg_extension
      WHERE extname <> 'plpgsql'
      ORDER BY extname;"
  done

plpgsql is excluded because it ships with the server and is installed in template1 by default, so it is noise in every result. Everything else in that list is something a human chose, and every line of it is a question for the upgrade.

Within one database, the fuller picture pairs what is installed against what the installation can offer:

SELECT e.extname,
       e.extversion                       AS installed,
       a.default_version                  AS available_default,
       n.nspname                          AS schema
FROM pg_extension e
JOIN pg_namespace n           ON n.oid  = e.extnamespace
LEFT JOIN pg_available_extensions a ON a.name = e.extname
ORDER BY e.extname;

A null in available_default means the control file for that extension is not present on this server at all, which on the new installation is precisely the condition you are trying to discover before the upgrade rather than during it. Run this on a freshly built target server with all the extension packages installed, and any null is a package you still have to find.

Which of them anything actually uses

The awkward question is whether an extension can simply be dropped. The catalog answers part of it and cannot answer the rest, so it is worth being clear about which part is which.

It can tell you where an extension’s types are in use, which is the hardest case to unpick because the data itself depends on the extension:

SELECT e.extname,
       c.oid::regclass AS table_name,
       a.attname       AS column_name,
       t.typname       AS type_name
FROM pg_attribute a
JOIN pg_class     c ON c.oid = a.attrelid AND c.relkind IN ('r', 'p', 'm')
JOIN pg_type      t ON t.oid = a.atttypid
JOIN pg_depend    d ON d.classid    = 'pg_type'::regclass
                   AND d.objid      = t.oid
                   AND d.refclassid = 'pg_extension'::regclass
JOIN pg_extension e ON e.oid = d.refobjid
WHERE a.attnum > 0 AND NOT a.attisdropped
ORDER BY e.extname, table_name;

Anything returned there cannot be dropped without changing the schema and migrating the data, which makes it a hard dependency on the target version having a build.

What the catalog cannot tell you is whether anybody calls an extension’s functions. There is no dependency recorded between an application and a function it invokes. The closest approximations are pg_stat_user_functions, which only counts anything when track_functions is set to pl or all, and searching the statement text in pg_stat_statements. Both are evidence rather than proof, and an extension used once a quarter by a report will look unused in either.

The column types that stop the upgrade dead

There is a second schema audit, unrelated to extensions, and it is the one that becomes a bad surprise because nothing in ordinary operation makes it visible.

The reg* types are text-facing wrappers around object identifiers. A regproc column stores an OID and prints a function name; a regclass column stores an OID and prints a table name. What matters for an upgrade is whether the stored OID can be made to mean the same thing on the far side, and for most of the family it cannot. The documentation names them: a table column of type regcollation, regconfig, regdictionary, regnamespace, regoper, regoperator, regproc or regprocedure is not supported, while regclass, regrole and regtype are.

They turn up in the places where somebody was being clever: a job table holding the name of the function to call, a routing table holding a text-search configuration, a dispatch column holding a schema. One fixture shows both what the audit finds and what it correctly leaves alone.

CREATE TABLE public.job_definition (
  job_id     bigserial PRIMARY KEY,
  handler    regproc     NOT NULL,
  result_of  regtype     NOT NULL,
  owned_by   regrole     NOT NULL
);

SELECT c.oid::regclass AS table_name,
       a.attname       AS column_name,
       t.typname       AS type_name
FROM pg_attribute  a
JOIN pg_class      c ON c.oid = a.attrelid AND c.relkind IN ('r', 'p', 'm')
JOIN pg_type       t ON t.oid = a.atttypid
JOIN pg_namespace  n ON n.oid = c.relnamespace
WHERE a.attnum > 0 AND NOT a.attisdropped
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND t.typname IN ('regcollation', 'regconfig', 'regdictionary', 'regnamespace',
                    'regoper', 'regoperator', 'regproc', 'regprocedure')
ORDER BY table_name, column_name;

Three of that table’s columns carry reg* types and exactly one of them is a blocker, which is the point: the family looks homogeneous and is not. The fix is a schema change, usually to text with a cast at the point of use, and that needs an application release rather than a maintenance window. Finding it a week early and finding it on the night are two different projects.

Run the query in every database rather than in the interesting one. The audit at the top of this page already walks the list; this is a second statement to put inside the same loop.

Order of operations

The sequence that avoids the common failures is short and the order is load-bearing.

Build or install the new major version’s server packages, then install the matching shared object files for every extension in the audit. The documentation is explicit that this is a step you perform with operating system commands, and equally explicit about the thing not to do afterwards: do not run CREATE EXTENSION on the new cluster. The schema definitions arrive with the catalog conversion, and creating them by hand produces duplicates.

Then run the checks. pg_upgrade --check can run against a still-running old server, so there is no reason to defer it to the maintenance window. If you intend to use a particular transfer mode, pass its flag to the check as well, because each mode has checks of its own: --link, --clone, --copy-file-range or, new in PostgreSQL 18, --swap.

Be precise about what that check covers. It verifies that the two clusters are binary-compatible in the ways the core server can reason about, including compile-time settings and pointer width. The documentation states plainly that external modules must also be binary compatible and that this cannot be checked by pg_upgrade. A clean check on a cluster whose extensions have no build for the target is a clean check, and it means nothing about the extensions.

Two smaller things in this phase deserve to be decided rather than discovered. The tool starts short-lived servers in both data directories and connects to each of them several times, so authentication has to work with nobody typing anything: peer in pg_hba.conf or a password file, put back to what you actually want afterwards. And --jobs is worth setting, with a caveat that decides whether it does anything at all. It parallelises across databases and tablespaces, so a cluster with one database in one tablespace gains nothing from any value you give it, and a cluster carrying forty small databases gains most of the upgrade.

The documentation also offers the cheapest rehearsal available, and it is cheap because of a property of the tool that is easy to miss. The post-upgrade steps are derived from the database schemas rather than from user data, so a schema-only copy with dummy data in it goes through the same steps as the real thing. Where every cluster in a fleet runs the same application, one rehearsal answers for all of them, and what it answers is the part that actually varies: which rebuild and reindex scripts you are going to be handed at the end.

The transfer mode is a rollback decision

--copy, the default, duplicates every data file. It is the slowest, it needs the new cluster’s space alongside the old cluster’s for the duration, and it leaves the old cluster fully usable, which means rollback is stopping one server and starting the other.

--link creates hard links instead, so the upgrade is nearly instant regardless of database size. The cost is that both clusters now point at the same files, and once the new server has started and written anything, the documentation is unambiguous about where that leaves you: it is unsafe to use the old cluster, and the old cluster has to be restored from backup. Rollback stops being a switch and becomes a restore.

--clone uses filesystem copy-on-write where it is available, and its interesting property is not the speed. It is that starting the new cluster does not make the old one unusable, which the documentation says outright, so it is the one mode that buys link-mode speed without buying link-mode’s rollback story. --copy-file-range uses the kernel call of that name, on Linux and FreeBSD, and depending on the filesystem behaves either like clone or like a faster copy.

--swap, new in 18, moves the data directories rather than copying, linking or cloning them, and requires both directories to be on the same filesystem. It is documented as potentially the fastest of the set, especially on clusters with many relations, and it carries the harshest one-way property: the old cluster is unsafe from the moment the file transfer begins, not from the moment the new server starts.

“Many relations” is the phrase to notice, because it names what the transfer step is really costed in. A heap is stored in segments of a gigabyte by default, each its own file, and every relation carries a free space map and a visibility map beside it. A two-terabyte table is therefore about two thousand heap files before its indexes and its forks are counted, and a schema with ten thousand partitions is tens of thousands of files whatever they weigh. Copy mode pays for the bytes; link, clone and swap pay for the file count. That is why a large simple database links in seconds and a small heavily partitioned one does not.

If you want link-mode speed with the original untouched and clone mode is unavailable, the documentation describes how to have both: copy the old cluster and upgrade the copy in link mode. The copy is made with rsync while the server runs and then again with --checksum once it is shut down, and the reason for that second flag is worth carrying around. rsync compares modification times at a granularity of one second, so a file written twice inside the same second can be skipped; the checksum pass is what closes that window.

Choose on how you would recover, not on how long the upgrade takes. Ten extra minutes of copying is cheap insurance against the alternative being a restore from backup, and whether link mode is safe has a real answer rather than a preference.

Nothing upgrades the standbys

This is the step that most often turns a rehearsed upgrade into a long night, because the tool never mentions it and the primary comes up perfectly well without it.

pg_upgrade upgrades one data directory. A streaming replica’s data directory is a copy of the old primary’s, in the old format, and nothing in the procedure has touched it. There are two ways forward and they are not equivalent.

The obvious one is to rebuild each standby from the upgraded primary with pg_basebackup. It always works and it costs a full copy over the network. Two terabytes across a link that sustains a hundred and twenty-five megabytes a second is about sixteen thousand seconds, call it four and a half hours per standby, all of it spent without the redundancy the standby exists to provide.

The other is the procedure in the documentation, which is rsync run on the primary against each standby, and which is available only if you upgraded in link mode. It works because the old and new clusters on the primary share their data files through hard links: rsync with --hard-links and --size-only reproduces that sharing on the standby against the copy it already holds, so what crosses the network is the catalog and the link structure rather than the user data. That is the whole reason it is fast, and the whole reason link mode is a precondition rather than a suggestion.

Three constraints around it are easy to trip over and expensive to trip over late.

• The standbys must be caught up before anything is shut down, verified by comparing the “Latest checkpoint location” that pg_controldata reports on the old primary and on each old standby. Not roughly equal. The same.
• No server may be started until the rsync has run for every standby. Starting the new primary first is the mistake this constraint exists to prevent, and it is the natural mistake to make, because the primary is the part you have just finished.
• wal_level must not be minimal on the new primary, which is a thing to check on a fresh configuration file rather than assume.

Replication slots do not come along either, and the rule is finer than it looks. Slots that lived on a standby are never copied and are always recreated by hand. Logical slots on the primary are copied only when the old primary was version 17 or later, and on an older source they are dropped without a word in the output, which is a large enough trap to have a page of its own. Capture the inventory before the maintenance window; it is a five-minute query beforehand and an archaeology exercise afterwards.

Tablespaces decide two of the steps above

A user-defined tablespace is a directory outside the data directory with its own version-stamped subdirectory inside it, and it changes two things without announcing either.

SELECT spcname,
       pg_tablespace_location(oid) AS location,
       current_setting('data_directory') AS data_directory
FROM pg_tablespace
ORDER BY spcname;

An empty location is one of the two built-in tablespaces, which live inside the data directory and need no thought. Every non-empty path is a directory that has to exist on the target host, be owned correctly and have room, and that needs its own rsync invocation in the standby procedure above, one per tablespace, against the version-stamped subdirectory rather than the tablespace root.

The second consequence is smaller and surfaces a week later. pg_upgrade normally writes a script that deletes the old cluster’s data directories once you are satisfied, and it does not generate that script at all when a user-defined tablespace sits inside the old data directory. Nothing fails; the script you were expecting is simply not there, and the disk stays full until somebody works out why.

A location that begins with the data directory path is worth fixing before the upgrade rather than working around during it. That layout is the one the delete script refuses, and it is also the one that makes the rsync step ambiguous about what it is copying.

After it finishes

pg_upgrade prints warnings at the end and writes script files for anything that needs doing by hand, connecting to each database that requires it. Run them. If extension updates are available for the new version, it reports that and generates a script for those too; the operation behind it is ALTER EXTENSION ... UPDATE, and the easiest way to confirm nothing was missed is to compare installed against default:

SELECT name, installed_version, default_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
  AND installed_version <> default_version
ORDER BY name;

Every row there is an extension running its old SQL definitions against a new library, which is a supported state and not one to leave indefinitely.

The catalog is only half of what an upgrade moves, and the other half is the monitoring pointed at it, which is why a fleet leaving a version that is running out of support wants the end-of-life audit for it open in the next tab.

Two kinds of statistics, and the order they go back in

From PostgreSQL 18, pg_upgrade carries most optimizer statistics across the boundary unless --no-statistics is passed, which removes the long-standing cliff where a freshly upgraded database planned every query blind until something analysed it. What it does not carry is named in the documentation, and two of the three entries on that list are routinely forgotten.

Extended statistics, the objects somebody created by hand, are not transferred. Find them and rebuild them:

SELECT stxnamespace::regnamespace AS schema,
       stxname                    AS statistics_object,
       stxrelid::regclass         AS table_name
FROM pg_statistic_ext
ORDER BY table_name;

An ANALYZE on each named table regenerates them. On a schema that depends on one of them for a correlated-column estimate, the window between the upgrade and that ANALYZE is a window in which the planner is back to assuming independence, which is the failure those objects were created to prevent.

The other omission has no visible symptom at all. The cumulative statistics system is not carried either, so every counter in pg_stat_all_tables starts at zero on the new cluster: no dead tuples, no rows modified since the last analyze, no last-vacuum timestamp. Autovacuum’s triggers read precisely those counters, so a table that was two days away from an autovacuum before the upgrade is now an unbounded distance from one, and stays there until fresh churn crosses a threshold from a standing start. That is why the documentation gives two commands rather than one, and why the second is not optional:

vacuumdb --all --analyze-in-stages --missing-stats-only
vacuumdb --all --analyze-only

The first gets usable estimates in front of the planner quickly, running three passes at increasing statistics targets with the first at the lowest target available, so the database plans against something rough within minutes rather than against nothing for an hour. The second is there to restore the counters that decide when maintenance runs, and what analyze is for as against vacuum is the distinction those two commands are drawing.

--missing-stats-only is the part that is new in 18 and the part most easily got backwards. On an upgrade that preserved statistics, running --analyze-in-stages without it would temporarily replace good statistics with the low-target ones from the first pass, which is a planner regression introduced in the name of fixing one. With the flag, only relations holding no statistics are touched. It needs read access to the statistics catalogs, which is restricted to superusers by default, so it is a flag to exercise in the rehearsal rather than to meet on the night.

Before 18 there are no optimizer statistics at all after the upgrade, --missing-stats-only does not exist, and plain --analyze-in-stages is exactly right because there is nothing good to overwrite. Either way, add PGOPTIONS='-c vacuum_cost_delay=0' and a --jobs value if you want it finished inside the window: the throttle that protects a production server is protecting nothing on a cluster nobody has connected to yet.

What the new cluster does not inherit

Configuration on the new cluster is a fresh file. Everything set by hand on the old one has to be carried across deliberately, and the catalog will tell you what that is:

SELECT name, setting, unit, source
FROM pg_settings
WHERE source NOT IN ('default', 'override', 'client')
ORDER BY source, name;

Every row is something a person decided, whether in the configuration file, on the command line or through ALTER SYSTEM, and every row quietly reverts to a default unless somebody moves it. Run it before the upgrade and again after, and diff the two. It is the same failure mode as config drift between primary and standby, with the difference that here you get to compare against a snapshot taken on purpose.

Statement identifiers may not survive the boundary either, so any performance baseline keyed on them restarts empty. Capture the comparison you would want before the upgrade, because afterwards the row it would have joined to is not there.