Franck PachotDatabase Developer Advocate
All minibooks
Database field guide · 37
Capability, trust, packaging, upgrades, and governance
Operate PostgreSQL extensions as versioned executable dependencies with explicit privileges, packaging, compatibility, observability, and recovery tests.
Build the mental model before choosing the mechanism.
This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.01
An extension can add SQL objects, planner hooks, access methods, background workers, and native code inside the database process. Installation therefore changes both capability and the trusted computing base.
02
PostgreSQL discovers extension metadata and versioned scripts from the installation. CREATE EXTENSION records a managed object set, but operating-system packages must already match the server major version and architecture.
03
ALTER EXTENSION UPDATE follows declared version transitions; it is not a generic promise that an extension supports a new PostgreSQL release. Dependencies, stored data formats, and catalog objects need rehearsal.
04
Managed services and PostgreSQL-compatible systems expose approved subsets or require different builds. SQL portability does not imply extension ABI, hook, or background-worker portability.
05
Hinting and statistics extensions can change plans without changing application SQL. Their configuration and matching rules become part of reproducibility and incident evidence.
06
Each extension adds patching, security, backup, replication, and upgrade obligations. A registry with owner, purpose, version, data footprint, and removal test keeps convenience from becoming hidden infrastructure.
07
Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.
Join installed extensions with the versions available on this server before planning an update or major-version migration.
SELECT e.extname, e.extversion, a.default_version, e.extnamespace::regnamespace
FROM pg_extension e LEFT JOIN pg_available_extensions a ON a.name=e.extname
ORDER BY e.extname;Extension membership identifies objects managed by extension scripts. External dependencies still need separate discovery before removal.
SELECT e.extname, d.classid::regclass, d.objid,
pg_describe_object(d.classid,d.objid,d.objsubid)
FROM pg_depend d JOIN pg_extension e ON e.oid=d.refobjid
WHERE d.deptype='e' ORDER BY e.extname;Build a clean instance with production package versions, restore representative data, execute supported extension updates, and validate objects and behavior.
SELECT * FROM pg_available_extension_versions
WHERE name = $1 ORDER BY version;
ALTER EXTENSION extension_name UPDATE TO 'target_version';Planner hooks and background workers may depend on preload settings and restart. Retain these settings with extension versions in incident evidence.
SHOW shared_preload_libraries;
SHOW session_preload_libraries;
SELECT name, setting, source, pending_restart
FROM pg_settings WHERE name LIKE '%preload_libraries';08
Optional deep dives with the complete experiments and product-version context behind this guide.