pg_upgrade vs a Logical Replication Upgrade Path #

A decision guide for choosing between an in-place pg_upgrade and a logical-replication cutover when a major-version upgrade must also carry compiled extensions like PostGIS, TimescaleDB, or pgvector across the boundary.

Up: Async Upgrade Simulation — this page is the route-selection companion to that simulation stage, and it feeds the wider Extension Upgrade Planning & Compatibility Validation pipeline.

Context & When This Applies #

When you cross a PostgreSQL major (say 15 → 16) you must choose how the data and its extensions move. Two routes dominate: pg_upgrade, which relinks the existing data files under a new server binary in place, and logical replication, which streams changes from the old cluster to a freshly initialized new one and cuts over when caught up. The choice is rarely about the data alone — it is dominated by the extensions, because pg_upgrade requires binary-compatible extension builds on both clusters, while logical replication requires the publication-compatible subset of your schema. This guide applies to PostgreSQL 12–17 with any compiled extension in play, and it is the decision that precedes every major-version window sized in threshold tuning for downtime windows.

Getting the route wrong is expensive: choosing pg_upgrade when an extension has no matching build on the new major strands the whole cluster at the --check step; choosing logical replication for a schema full of large objects or tables without replica identity strands it mid-sync. Both routes must be proven in an async upgrade simulation on a topology-matched clone first.

Concept: Two Different Compatibility Contracts #

pg_upgrade and logical replication enforce different compatibility contracts, and that is the crux of the decision. pg_upgrade operates at the storage layer: it keeps the on-disk relation files and rewrites only the catalog, so it demands that every extension have a shared library on the new cluster whose version is greater than or equal to the old one — otherwise the first load of, say, postgis-3.so fails. It is fast (with --link, near-instant regardless of data size) but requires the whole cluster offline for the swap, and it carries everything, including objects logical replication cannot.

Logical replication operates at the row layer: it decodes changes from the old cluster’s WAL and replays them as SQL on the new one, so it needs every replicated table to have a replica identity and cannot carry sequences’ current values, large objects, or DDL automatically. It buys a near-zero cutover because the new cluster is built and caught up while the old one still serves traffic, but the schema and extensions must be created on the target up front — exactly the per-extension quirks documented for PostGIS, TimescaleDB, and pgvector. The choice, then, is a trade of downtime against setup complexity and object coverage.

Runnable Implementation #

Before committing to a route, run the preconditions probe below. It answers the two gates that actually decide the route: are the extensions binary-ready for pg_upgrade, and do all tables have a replica identity for logical replication.

#!/usr/bin/env python3
"""Probe both upgrade routes' preconditions and recommend one."""
import psycopg2


def recommend_route(conn, tolerated_downtime_seconds: int) -> dict:
    with conn.cursor() as cur:
        # Extensions installed here — each needs a >= build on the new major
        # for pg_upgrade. (Availability on the target is checked out of band.)
        cur.execute("SELECT extname, extversion FROM pg_extension "
                    "WHERE extname NOT IN ('plpgsql') ORDER BY extname;")
        extensions = cur.fetchall()

        # Tables lacking a replica identity block logical replication of updates.
        cur.execute(
            """
            SELECT n.nspname, c.relname
            FROM   pg_class c
            JOIN   pg_namespace n ON n.oid = c.relnamespace
            WHERE  c.relkind = 'r'
              AND  n.nspname NOT IN ('pg_catalog', 'information_schema')
              AND  c.relreplident = 'n'          -- 'n' = nothing (no identity)
            ORDER  BY 1, 2;
            """
        )
        no_identity = cur.fetchall()

    if tolerated_downtime_seconds >= 300 and not no_identity:
        route = "pg_upgrade"          # short window fine, simplest path
    elif no_identity:
        route = "pg_upgrade"          # logical blocked until identities added
    else:
        route = "logical_replication" # near-zero downtime and tables are ready

    return {
        "recommended": route,
        "extensions": [f"{n} {v}" for n, v in extensions],
        "tables_without_replica_identity": [f"{s}.{t}" for s, t in no_identity],
        "note": "Confirm each extension has a >= build on the target before "
                "pg_upgrade; add REPLICA IDENTITY before logical replication.",
    }


if __name__ == "__main__":
    conn = psycopg2.connect("dbname=appdb")
    import json
    print(json.dumps(recommend_route(conn, tolerated_downtime_seconds=120), indent=2))

Whichever route the probe recommends, drive its execution through ALTER EXTENSION automation and keep a pre-cutover restore point via snapshot and point-in-time recovery.

Expected Output & Verification #

The probe returns a route plus the blockers it found:

{
  "recommended": "pg_upgrade",
  "extensions": ["pg_stat_statements 1.10", "postgis 3.4.2"],
  "tables_without_replica_identity": ["public.audit_log"],
  "note": "Confirm each extension has a >= build on the target before pg_upgrade; add REPLICA IDENTITY before logical replication."
}

For a pg_upgrade route, the authoritative verification is a dry --check run on the target binaries:

# Non-destructive: validates catalog and extension compatibility only.
/usr/lib/postgresql/16/bin/pg_upgrade \
  --old-datadir /var/lib/postgresql/15/main \
  --new-datadir /var/lib/postgresql/16/main \
  --old-bindir  /usr/lib/postgresql/15/bin \
  --new-bindir  /usr/lib/postgresql/16/bin \
  --check

A clean *Clusters are compatible* is the go signal. Feed the result into compatibility matrix synchronization so the approved route and extension tuple are recorded together.

Edge Cases & Gotchas #

pg_upgrade --check fails on a missing extension library. If the new cluster lacks a matching build:

Your installation contains extension "postgis" which is not present in the new
cluster. Install the missing extension before continuing.

Install the target-major extension package on the new cluster before re-running --check. This is the single most common pg_upgrade blocker for extension-heavy fleets.

Logical replication silently skips tables without a replica identity. Updates and deletes on such a table error on the subscriber or are dropped, depending on configuration. Add REPLICA IDENTITY FULL (or a suitable key) to every replicated table before starting, and verify with the probe’s relreplident check.

Sequences do not advance on the logical target. Logical replication copies row data but not sequence state, so the new cluster’s sequences sit at their initial value. Copy last_value for every sequence during cutover or the first insert collides with existing IDs.

TimescaleDB and other preload extensions constrain both routes. A preload extension must be in shared_preload_libraries on the target before either route starts, and some versions restrict pg_upgrade across specific majors — reconcile with TimescaleDB extension upgrade quirks and preload ordering before choosing.

Related Pages #

• Up: Async Upgrade Simulation
• Simulating Async Extension Upgrades with pg_upgrade
• Threshold Tuning for Downtime Windows
• Cloning Production with pg_basebackup for Upgrade Rehearsal
• Snapshot & Point-in-Time Recovery