Extension Lifecycle: Security Review, Upgrade Compatibility, Backup, and Disaster Recovery

Learning outcomes

Installing an extension is the beginning of ownership, not the end. Every extension becomes a dependency for security response, operating-system packages, PostgreSQL upgrades, backup/restore, replicas, failover nodes, and incident recovery. ServiceHub therefore needs an inventory that answers not merely “what is installed?” but “where did it come from, who may update it, what files/preloads does it require, and can we restore this database on a clean target?”

1. Build an extension SBOM-style inventory table

SBOM means Software Bill of Materials. PostgreSQL catalogs can provide installed version, schema, owner and some control-file fields. Provenance, package checksums, upstream repository, CVE/security contact, preload requirement, target-major test evidence, and business owner must be added by your deployment process.

DROP TABLE IF EXISTS app.ch19_extension_inventory;CREATE TABLE app.ch19_extension_inventory (  extension_name name PRIMARY KEY,  installed_version text NOT NULL,  default_available_version text,  schema_name name NOT NULL,  extension_owner name NOT NULL,  trusted boolean,  superuser_install boolean,  relocatable boolean,  requires name[],  package_source text NOT NULL,  native_shared_library boolean NOT NULL,  shared_preload_required boolean NOT NULL,  target_major_tested text NOT NULL,  backup_restore_tested_at timestamptz,  business_owner text NOT NULL,  removal_runbook text NOT NULL,  reviewed_at timestamptz NOT NULL DEFAULT clock_timestamp());

2. Populate catalog-derived fields, then add reviewed facts

WITH installed AS (  SELECT e.extname, e.extversion, e.extrelocatable,         n.nspname AS schema_name,         pg_get_userbyid(e.extowner) AS owner_name  FROM pg_extension AS e  JOIN pg_namespace AS n ON n.oid = e.extnamespace  WHERE e.extname IN ('hstore','postgres_fdw')),available AS (  SELECT a.name, a.default_version,         v.superuser, v.trusted, v.relocatable, v.requires  FROM pg_available_extensions AS a  LEFT JOIN pg_available_extension_versions AS v    ON v.name = a.name   AND v.version = a.default_version  WHERE a.name IN ('hstore','postgres_fdw'))SELECT i.*, a.default_version, a.superuser, a.trusted, a.requiresFROM installed AS iLEFT JOIN available AS a ON a.name = i.extnameORDER BY i.extname;

Do not fabricate the remaining columns from guesses. For this training lab, record reviewed facts explicitly. In production, source them from package-manager metadata, signed artifacts, vendor/upstream release notes, build pipelines, and tested configuration.

INSERT INTO app.ch19_extension_inventory (  extension_name, installed_version, default_available_version,  schema_name, extension_owner, trusted, superuser_install,  relocatable, requires, package_source,  native_shared_library, shared_preload_required,  target_major_tested, business_owner, removal_runbook)SELECT e.extname, e.extversion, a.default_version,       n.nspname, pg_get_userbyid(e.extowner),       a.trusted, a.superuser, a.relocatable, a.requires,       'PostgreSQL 18 supplied/contrib package for this host',       true,       false,       '18',       'ServiceHub database platform team',       CASE         WHEN e.extname = 'postgres_fdw'           THEN 'Remove foreign tables, user mappings and servers; DROP EXTENSION RESTRICT; restore test'         ELSE 'Remove dependent hstore-typed objects; DROP EXTENSION RESTRICT; restore test'       ENDFROM pg_extension AS eJOIN pg_namespace AS n ON n.oid = e.extnamespaceLEFT JOIN LATERAL (  SELECT pe.default_version,         pev.trusted, pev.superuser, pev.relocatable, pev.requires  FROM pg_available_extensions AS pe  LEFT JOIN pg_available_extension_versions AS pev    ON pev.name = pe.name   AND pev.version = pe.default_version  WHERE pe.name = e.extname  LIMIT 1) AS a ON trueWHERE e.extname IN ('hstore','postgres_fdw')ON CONFLICT (extension_name) DO UPDATESET installed_version = EXCLUDED.installed_version,    default_available_version = EXCLUDED.default_available_version,    reviewed_at = clock_timestamp();

The native_shared_library field above is an explicit reviewed training fact, not something PostgreSQL's catalog universally tells you. Real extension architecture can change between versions; inventory evidence must be version-specific.

3. Update-readiness is a path plus package plus release review

SELECT e.extname,       e.extversion AS installed_version,       a.default_version,       (e.extversion = a.default_version) AS at_packaged_defaultFROM pg_extension AS eJOIN pg_available_extensions AS a ON a.name = e.extnameWHERE e.extname IN ('hstore','postgres_fdw');SELECT 'hstore' AS extension_name, *FROM pg_extension_update_paths('hstore')WHERE source = (SELECT extversion FROM pg_extension WHERE extname='hstore')UNION ALLSELECT 'postgres_fdw', *FROM pg_extension_update_paths('postgres_fdw')WHERE source = (SELECT extversion FROM pg_extension WHERE extname='postgres_fdw')ORDER BY extension_name, target;

An available update path proves only that SQL update scripts are packaged. It does not prove your workload, indexes, native ABI, replica fleet, or dependent applications are compatible. Test the real extension on a copy of the target PostgreSQL major before the major upgrade window.

4. pg_dump understands extensions—but the restore host must provide the package

For extension member objects, pg_dump normally writes a CREATE EXTENSION command rather than dumping each member's definition. This avoids restoring stale member SQL, but it means the destination server must already have matching extension control/scripts/shared libraries installed.

pg_dump   --dbname="service=servicehub-lab-admin"   --format=custom   --extension=hstore   --extension=postgres_fdw   --file=ch19_extensions.dumppg_restore --list ch19_extensions.dump

This extension-only artifact is not a full database backup. It is a targeted packaging check: can a clean database recreate the extension objects from the packages installed on the restore server?

5. Restore the extension package into a disposable database

dropdb --if-exists servicehub_ch19_ext_restorecreatedb servicehub_ch19_ext_restorepsql servicehub_ch19_ext_restore -c   "CREATE SCHEMA ch19_ext;"pg_restore   --dbname=servicehub_ch19_ext_restore   --no-owner   ch19_extensions.dumppsql servicehub_ch19_ext_restore -c   "SELECT extname, extversion FROM pg_extension ORDER BY extname;" 

Expected acceptance: the target contains hstore and postgres_fdw at versions supported by the installed PostgreSQL 18 package. If the target host lacks one extension's files, restore fails at CREATE EXTENSION. That failure is valuable DR evidence: copying only PGDATA or a dump file is not enough to reconstruct external package dependencies.

6. Full backup has more dependencies than extension membership

The postgres_fdw extension owns the foreign-data-wrapper implementation, but your foreign servers, foreign tables, and user mappings are database objects/configuration around it. User mappings can contain passwords. Protect logical dumps that include them, rotate credentials after suspected exposure, and document remote endpoint/DNS/certificate dependencies separately from extension packages.

SELECT srvname, srvoptionsFROM pg_foreign_serversWHERE srvname = 'ch19_partner_server';SELECT srvname, usename,       CASE WHEN umoptions IS NULL THEN 'hidden-or-none'            ELSE 'visible-to-current-role'       END AS mapping_option_visibilityFROM pg_user_mappingsWHERE srvname = 'ch19_partner_server';

7. Wrong rollback plan: DROP EXTENSION CASCADE

DROP EXTENSION postgres_fdw;-- Expected while ch19_partner_server exists:-- ERROR: cannot drop extension postgres_fdw because other objects depend on it-- Do NOT change that to CASCADE reflexively.-- First inventory dependents and approved teardown order.

The RESTRICT failure is a safety feature. CASCADE can recursively remove foreign servers and foreign tables, destroying a federation contract unexpectedly. Extension removal is a schema migration with business dependencies.

SELECT pg_describe_object(d.classid, d.objid, d.objsubid) AS dependent_object,       d.deptypeFROM pg_depend AS dJOIN pg_extension AS e ON e.oid = d.refobjidWHERE e.extname = 'postgres_fdw'ORDER BY dependent_object;

8. Controlled Chapter 19 teardown

DROP FOREIGN TABLE IF EXISTS app.ch19_partner_dispatch;DROP USER MAPPING IF EXISTS FOR servicehub_owner SERVER ch19_partner_server;DROP SERVER IF EXISTS ch19_partner_server;DROP EXTENSION IF EXISTS postgres_fdw;DROP EXTENSION IF EXISTS hstore;DROP TABLE IF EXISTS app.ch19_extension_inventory;DROP SCHEMA IF EXISTS ch19_ext CASCADE;DROP ROLE IF EXISTS ch19_extension_installer;

\connect postgresDROP DATABASE IF EXISTS servicehub_partner WITH (FORCE);DROP ROLE IF EXISTS ch19_partner_fdw;\connect servicehub_lab

Before production removal, verify no application code, views, types, columns, indexes, FDW objects, backup procedures, or replicas depend on the extension. Keep the package installed on DR/failover nodes until restore and rollback windows have closed.

9. Adoption gate

10. Checkpoint

Authoritative references

Extension, FDW, language, and server-code behavior is privilege-, package-, version-, and platform-sensitive. These PostgreSQL 18 primary sources define the mechanisms used here.

• PostgreSQL 18 — Extension Packaging and pg_dump Behavior
• PostgreSQL 18 — ALTER EXTENSION
• PostgreSQL 18 — pg_dump
• PostgreSQL 18 — DROP EXTENSION
• PostgreSQL 18 — postgres_fdw