PITR Restore vs Schema-Level Rollback #

A decision guide for choosing between a point-in-time recovery of the whole cluster and a scoped schema-level rollback when an extension upgrade fails — weighing blast radius, data loss, and recovery time against how far the failed upgrade actually reached.

Up: Fallback Routing Strategies — this page is the rollback-route decision behind fallback routing, under the PostgreSQL Extension Architecture & Lifecycle Fundamentals area of the site.

Context & When This Applies #

When an extension upgrade goes wrong, you have two fundamentally different ways to undo it. A schema-level rollback reverses just the extension’s own changes — dropping the objects the failed update created, restoring prior function definitions, re-binding orphaned catalog entries — and touches nothing else. A PITR restore rewinds the entire cluster to a timestamp before the upgrade, discarding every change since. The right choice depends almost entirely on how far the failed upgrade reached: a clean transactional abort needs nothing; a partial catalog change needs a scoped reversal; a non-transactional step that corrupted shared state needs a full rewind. This applies to PostgreSQL 12–17 for any extension upgrade with a rollback plan.

Choosing wrong is costly in opposite directions: a PITR restore for a problem a schema rollback could fix throws away unrelated committed transactions; a schema rollback for damage that reached shared memory or cluster-global state leaves a subtly broken cluster running. The decision is the core of a snapshot and point-in-time recovery plan.

Concept: Blast Radius Follows Transactionality #

The dividing line between the two rollbacks is transactionality. Ordinary extension DDL — creating functions, types, operators, rewriting catalog rows — is transactional: if the update aborts, PostgreSQL rolls it back atomically and there is nothing to undo. When an update commits a change and then a later step fails, that committed change persists, and a schema-level rollback can reverse it precisely because it lives entirely in this extension’s catalog footprint (pg_depend links with deptype = 'e'). The blast radius is one extension, and unrelated transactions committed in the same window survive untouched.

The moment an upgrade performs a non-transactional action — registering a background worker, allocating shared memory, writing to cluster-global state — that change is outside any transaction and outside this extension’s catalog footprint. A schema-level rollback cannot see it, so the only faithful undo is to rewind the whole cluster to a timestamp before the upgrade with PITR, accepting the loss of everything committed since. Identifying which class an upgrade falls into is exactly what async upgrade simulation is for: it surfaces the non-transactional steps before the real run, so the rollback route is chosen in advance rather than during an incident.

Runnable Implementation #

The router below inspects the failure to recommend a route: it checks whether the update aborted cleanly, whether the damage is confined to one extension’s pg_depend footprint, and whether any non-transactional marker was seen.

#!/usr/bin/env python3
"""Recommend schema-level rollback vs PITR for a failed extension upgrade."""
import psycopg2


def rollback_route(conn, extname: str, saw_nontransactional_step: bool) -> dict:
    if saw_nontransactional_step:
        return {"route": "pitr_restore",
                "reason": "a non-transactional step (bgworker/shared memory) "
                          "cannot be reversed by a scoped rollback"}

    with conn.cursor() as cur:
        # Does the extension still have a coherent, self-contained footprint?
        cur.execute(
            """
            SELECT count(*) AS owned_objects
            FROM   pg_depend d
            JOIN   pg_extension e ON e.oid = d.refobjid
            WHERE  e.extname = %s AND d.deptype = 'e'
            """,
            (extname,),
        )
        owned = cur.fetchone()[0]

    if owned == 0:
        return {"route": "pitr_restore",
                "reason": "extension footprint missing/partial; scoped rollback "
                          "has no reliable object set to restore"}
    return {"route": "schema_level_rollback", "owned_objects": owned,
            "reason": "damage confined to this extension's catalog objects; "
                      "reverse them and preserve unrelated commits"}


if __name__ == "__main__":
    conn = psycopg2.connect("dbname=appdb")
    import json
    print(json.dumps(rollback_route(conn, "postgis", saw_nontransactional_step=False),
                     indent=2))

Route a pitr_restore decision to the procedure in snapshot and point-in-time recovery; route a schema_level_rollback through ALTER EXTENSION automation, which owns the transactional reversal steps.

Expected Output & Verification #

The router names a route and why:

{
  "route": "schema_level_rollback",
  "owned_objects": 312,
  "reason": "damage confined to this extension's catalog objects; reverse them and preserve unrelated commits"
}

Before executing a schema-level rollback, verify the damage really is extension-scoped — no orphaned objects escaped the footprint:

-- Objects that claim to belong to the extension but lost their dependency link
-- are a sign the footprint is NOT clean; prefer PITR if any appear.
SELECT c.relname, c.relkind
FROM   pg_class c
LEFT   JOIN pg_depend d
       ON d.objid = c.oid AND d.deptype = 'e'
WHERE  c.relname LIKE 'myext\_%'
  AND  d.objid IS NULL;

An empty result confirms a clean footprint and greenlights the scoped rollback. Record the chosen route with the incident under version control and branching so the decision is auditable.

Edge Cases & Gotchas #

A transactional abort needs no rollback at all — do not over-react. If the update failed inside its transaction (a 42P13, for instance), the catalog already rolled back cleanly. Running a PITR restore anyway needlessly discards good data. Confirm the abort with the extension’s current version before doing anything.

A schema-level rollback of a dropped-and-recreated function orphans dependents. If the failed upgrade dropped a function that views or triggers depend on, restoring it must recreate it with the exact prior signature, or the dependents stay broken. Keep prior definitions in version control and branching so the restore is exact, not reconstructed from memory.

PITR loses the transactions you were trying to protect. A full restore rewinds everything, including unrelated writes committed during the upgrade window. On a busy cluster that can be significant data loss. This is precisely why a scoped rollback is preferred whenever the damage is extension-confined — reserve PITR for genuine shared-state damage.

Replicas complicate both routes. A schema-level rollback replicates to standbys as normal DDL, but a PITR restore of the primary requires rebuilding or rewinding every replica to the same point. Plan the replica strategy alongside the route, per fallback routing strategies.

Related Pages #

• Up: Fallback Routing Strategies
• Best Practices for Isolating Experimental Extensions
• Snapshot & Point-in-Time Recovery
• ALTER EXTENSION Automation
• Async Upgrade Simulation