Extension Deprecation & Removal #

Removing a PostgreSQL extension is the most under-planned phase of the lifecycle, and the one that most reliably takes an application down: a single DROP EXTENSION ... CASCADE can silently delete every view, index, column default, and foreign table that ever referenced the extension’s types or functions. This guide is for DBAs and platform engineers who need to retire an extension — because it is deprecated, replaced, or unused — as a gated, reversible operation rather than a destructive one-liner. It covers how PostgreSQL tracks what depends on an extension, how to enumerate the true blast radius before dropping anything, and how to stage a removal so the catalog, the application, and the rollback plan stay in agreement.

Up: PostgreSQL Extension Architecture & Lifecycle Fundamentals — removal is the terminal phase of the lifecycle that section models, and this guide assumes its catalog-state and dependency concepts.

The Removal Decision Flow #

A safe removal is a pipeline, not a command: enumerate dependents, decide per dependent whether to migrate or drop it, stage the removal, and only then execute — with a snapshot taken first.

Each stage is an automation boundary. Enumerate and classify are read-only planning; snapshot is the safety interlock; execute is the only destructive step, and it is destructive by design, not by surprise, because every dependent was already decided. The rest of this guide develops each boundary.

Prerequisites #

• PostgreSQL version: 12 or newer. Every dependency query here reads pg_depend, pg_extension, and the object catalogs present since 9.x; the DROP EXTENSION grammar and pg_depend.deptype semantics used are stable across 12–17.
• Python packages: Python 3.8+ with psycopg2-binary for the enumeration and staging scripts. No other runtime dependency.
• Required privileges: Ownership of the extension (or membership in its owning role) to DROP it, plus privileges on any dependent object you intend to migrate or drop. A read-only role suffices for the enumeration and dry-run stages; reserve write access for execution, exactly as Security Boundaries & Permissions prescribes.
• Catalog state: A recent ANALYZE so dependency and size estimates are current, and a confirmed backup path — a base backup plus continuous WAL — so the snapshot stage can produce a real restore point via Snapshot & Point-in-Time Recovery.

Core Concept: pg_depend Is the Blast-Radius Map #

Every object PostgreSQL creates records its dependencies in pg_depend, and an extension is the root of a dependency tree. Two deptype values matter for removal. A deptype = 'e' (extension) edge means an object is part of the extension — it disappears when the extension is dropped, and that is expected. A deptype = 'n' (normal) or 'a' (auto) edge from a user object to an extension-owned object means something outside the extension depends on it — a view selecting an extension function, a column typed as an extension type, an index using an extension operator class. Those are the objects a CASCADE would silently destroy.

The entire discipline of safe removal is converting that hidden deptype-'n' set into an explicit, per-object decision before running DROP. DROP EXTENSION without CASCADE refuses to proceed while any external dependent exists, naming the first blocker; DROP EXTENSION ... CASCADE proceeds and takes every dependent with it. Neither is a plan. The plan is to enumerate the full external-dependent set with a recursive walk of pg_depend, decide each one, and reduce the final DROP to an operation with no surprise cascade — the same pre-flight-decision philosophy the compatibility validation pipeline applies to upgrades. Resolving the order in which dependents must be handled reuses the topological reasoning from Dependency Tree Analysis.

Step-by-Step Implementation #

Step 1 — Enumerate every external dependent #

Walk pg_depend outward from the extension to find every user object that depends on an extension-owned object. This is read-only and safe to run in production.

-- Every object OUTSIDE the extension that depends on something INSIDE it.
-- These are exactly what a blind CASCADE would destroy.
WITH ext AS (
    SELECT oid FROM pg_extension WHERE extname = 'deprecated_ext'
),
owned AS (            -- objects that belong to the extension (deptype 'e')
    SELECT d.objid
    FROM   pg_depend d, ext
    WHERE  d.refclassid = 'pg_extension'::regclass
      AND  d.refobjid = ext.oid
      AND  d.deptype = 'e'
)
SELECT DISTINCT
       dep.classid::regclass AS dependent_catalog,
       dep.objid,
       pg_describe_object(dep.classid, dep.objid, dep.objsubid) AS dependent_object
FROM   pg_depend dep
JOIN   owned ON dep.refobjid = owned.objid
WHERE  dep.deptype IN ('n', 'a')          -- normal / auto: EXTERNAL dependents
  AND  dep.classid <> 'pg_extension'::regclass
ORDER  BY dependent_object;

Step 2 — Classify each dependent: migrate or drop #

For every row from step 1, decide whether the dependent must be migrated off the extension (rewrite a view to not call the extension function, retype a column) or deliberately dropped with it. Record the decision as data, not as ad-hoc SQL.

#!/usr/bin/env python3
"""Classify each external dependent of an extension before removal."""
import json
import psycopg2

ENUM_SQL = """
WITH ext AS (SELECT oid FROM pg_extension WHERE extname = %s),
owned AS (
    SELECT d.objid FROM pg_depend d, ext
    WHERE d.refclassid = 'pg_extension'::regclass
      AND d.refobjid = ext.oid AND d.deptype = 'e')
SELECT pg_describe_object(dep.classid, dep.objid, dep.objsubid) AS obj
FROM   pg_depend dep JOIN owned ON dep.refobjid = owned.objid
WHERE  dep.deptype IN ('n','a')
  AND  dep.classid <> 'pg_extension'::regclass;
"""


def classify_dependents(conn, extname: str) -> list[dict]:
    with conn.cursor() as cur:
        cur.execute(ENUM_SQL, (extname,))
        dependents = [r[0] for r in cur.fetchall()]
    # A real plan maps each to an explicit disposition. Default to 'block' so
    # nothing is dropped without a human decision.
    return [{"object": obj, "disposition": "block",   # migrate | drop | block
             "note": "set to migrate or drop before execution"} for obj in dependents]


if __name__ == "__main__":
    conn = psycopg2.connect("dbname=appdb")
    print(json.dumps(classify_dependents(conn, "deprecated_ext"), indent=2))

Step 3 — Snapshot, then execute the staged removal #

Only after every dependent is classified — and a restore point exists — run the removal. Migrate or drop the dependents in dependency order, then drop the extension without CASCADE, so a stray unhandled dependent aborts the operation instead of being silently destroyed.

-- Take/confirm the restore point FIRST (see Snapshot & PITR), then:
BEGIN;

-- 1. Handle classified dependents explicitly (examples):
DROP VIEW IF EXISTS legacy_report;                 -- a 'drop' disposition
ALTER TABLE events ALTER COLUMN tags TYPE text;    -- a 'migrate' disposition

-- 2. Drop the extension WITHOUT cascade. If any external dependent remains,
--    this ERRORs and the whole transaction rolls back — the desired safety.
DROP EXTENSION deprecated_ext;

COMMIT;

Drive the whole sequence through ALTER EXTENSION automation, which owns the transactional-safety rules, and note that a non-relocatable or shared-state extension may still leave residue a transaction cannot reverse — the case PITR restore vs schema-level rollback exists to route.

Dry-Run & Validation Gate #

Never let the first DROP be the live one. PostgreSQL gives a built-in dry run: attempt the drop inside a transaction you roll back, capturing exactly what a CASCADE would remove.

BEGIN;
DROP EXTENSION deprecated_ext CASCADE;   -- do NOT commit
-- Inspect the server NOTICEs: each "drop cascades to ..." line is an object
-- CASCADE would destroy. This is your blast-radius report.
ROLLBACK;                                -- undo the whole probe

Archive the emitted drop cascades to ... notices as the deploy artifact. A clean removal is one where that list contains only objects you classified as drop in step 2 — any object you expected to migrate, or did not anticipate at all, means the plan is incomplete and the gate must block. Feed unexpected dependents back into the classification and re-run the probe until the cascade list matches the plan exactly.

Failure Modes & Error Taxonomy #

When these recur, route them through the Error Categorization Frameworks so a removal failure is triaged the same way an upgrade failure is.

Rollback / Recovery Path #

Because the execution step is destructive, its rollback is the snapshot — there is no in-catalog “undrop.”

1. Restore the pre-removal snapshot. The restore point taken in step 3 is the authoritative rollback; drive it through Snapshot & Point-in-Time Recovery.
2. Prefer the transaction abort when the failure is caught early. If the staged DROP aborts inside its transaction (a remaining dependent), the ROLLBACK restores everything with no snapshot needed — this is the common, cheap case.
3. Reinstall and re-migrate only from a known artifact. If you must reinstate the extension, install the exact prior version and checksum from Pinning Extension Versions in Deployment Manifests, never “latest.”
4. Record the incident against the plan. Keep the classification, the dry-run cascade list, and the outcome under Version Control & Branching so a re-attempt starts from the corrected plan.

Performance & Scale Considerations #

• DROP EXTENSION takes an AccessExclusiveLock on each dependent object. On a large fleet or a busy schema, the removal serializes behind and ahead of live queries; size the window with Threshold Tuning for Downtime Windows exactly as you would an upgrade.
• Migrating a dependent can be heavier than the drop. Retyping a column off an extension type rewrites the table — proportional to row count — so the migration step, not the DROP, often dominates the window. Estimate it like any rewrite.
• The enumeration is cheap and safe to run continuously. Because step 1 is read-only, run it on a schedule for extensions you intend to deprecate, so the dependent set is known long before the removal window rather than discovered in it.
• Cross-database removals are per-database. pg_extension is per-database, so an extension must be removed from each database in the whole cluster independently; plan the sweep the way Extension Registry Mapping reconciles across nodes.

FAQ #

Related Pages #

• Up: PostgreSQL Extension Architecture & Lifecycle Fundamentals
• Safely Dropping an Extension with Dependent Objects
• Dependency Tree Analysis
• PITR Restore vs Schema-Level Rollback
• Snapshot & Point-in-Time Recovery