Managing PostgreSQL extensions: installing, versioning, updating and dumping them

What it is

The documentation describes an extension as a script file with the SQL commands that create its objects plus a control file with properties such as the default version, relocatability and whether it is trusted, optionally with a shared library. CREATE EXTENSION name [SCHEMA s] [VERSION v] [CASCADE] loads it into the current database; pg_available_extensions and pg_available_extension_versions show what the server's installation offers. ALTER EXTENSION name UPDATE [TO v] runs the author's update scripts from the installed version to the target. DROP EXTENSION removes all member objects at once, and pg_dump writes only the CREATE EXTENSION command, not the members.

Why it matters

Three versions exist at the same time: the files installed on the server (usually an OS package), the version recorded in the database catalog, and, for extensions with C code, the library loaded by the running server. They drift apart on package upgrades, on pg_upgrade, and when a dump is restored on a server whose files are older or missing. A dump therefore depends silently on the extension files being present at restore time.

How to apply

• Install extension files by the same route as the server (OS packages, or the managed service's allow-list), and record the required extensions and versions in the migration history so that a fresh environment recreates them.
• Use CREATE EXTENSION IF NOT EXISTS ... SCHEMA extensions in migrations where the extension allows a schema choice (one that names a schema in its control file cannot be overridden), so that member objects do not shadow application objects and search paths stay clean.
• Installation normally needs superuser; an extension marked trusted can be installed by anyone with CREATE on the database. The documentation warns that a carelessly written script can be exploited through trojan-horse objects in its installation schema, so install into schemas where untrusted users hold no CREATE privilege.
• After package upgrades, compare installed_version with default_version in pg_available_extensions and run ALTER EXTENSION ... UPDATE in a maintenance window; after pg_upgrade, run the update script it generates.
• Modules that must be in shared_preload_libraries (pg_stat_statements, auto_explain) take effect only after a server restart; schedule it.
• Never change member objects by hand with CREATE OR REPLACE FUNCTION; the documentation states that such changes are not dumped.

Pitfalls

Extension names are unique per database, not per schema. CASCADE installs dependencies at their default versions. Extensions that define data types used in tables (postgis, hstore, vector) must be present before the tables can be restored, or the restore fails for those tables, not just for functions. Managed services publish allow-lists; an extension outside the list blocks a migration to that service.