The essentials

Quick reference

One focused task per row. Jump to the related section for complete, working examples.

UseSyntaxExamples
List available extensionsSELECT name, default_version, installed_version FROM pg_available_extensions ORDER BY name;View examples
Inspect installable versionsSELECT version, installed, superuser, trusted, relocatable FROM pg_available_extension_versions WHERE name = 'pgcrypto';View examples
Create a controlled schemaCREATE SCHEMA extensions AUTHORIZATION extension_owner;View examples
Protect the target schemaREVOKE CREATE ON SCHEMA extensions FROM PUBLIC;View examples
Install a pinned versionCREATE EXTENSION pgcrypto WITH SCHEMA extensions VERSION '1.3';View examples
Install only when absentCREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA extensions VERSION '1.3';View examples
Inspect installed extensionsSELECT extname, extversion, extnamespace::regnamespace, extrelocatable FROM pg_extension ORDER BY extname;View examples
Inspect update pathsSELECT source, target, path FROM pg_extension_update_paths('pgcrypto') ORDER BY source, target;View examples
Update to a reviewed versionALTER EXTENSION pgcrypto UPDATE TO '1.3';View examples
Move a relocatable extensionALTER EXTENSION pgcrypto SET SCHEMA extensions_v2;View examples
List extension membersSELECT pg_describe_object(d.classid,d.objid,d.objsubid) FROM pg_depend d WHERE d.refobjid = ( SELECT oid FROM pg_extension WHERE extname='pgcrypto' );View examples
Attach an existing objectALTER EXTENSION app_tools ADD FUNCTION app_tools.normalize_email(text);View examples
Drop without cascadingDROP EXTENSION pgcrypto RESTRICT;View examples
Preserve configuration dataSELECT pg_catalog.pg_extension_config_dump('app_tools.settings', 'WHERE NOT built_in');View examples
Secure an update script pathSELECT pg_catalog.set_config('search_path', 'pg_catalog, pg_temp', true);View examples
Pin a function search pathALTER FUNCTION extensions.secure_length(text) SET search_path = pg_catalog, pg_temp;View examples

A PostgreSQL extension is executable database supply chain: server-side files provide installation and update scripts that create a dependency-tracked set of SQL objects in one database. Safe operation requires more than issuing CREATE EXTENSION. Verify the package and version on every server, install into schemas untrusted roles cannot modify, understand the control file's privilege model, rehearse each update path, and coordinate dumps, failovers, and logical subscribers.

Step by step

Detailed examples

01

Distinguish server packages from database installations

pg_available_extensions reflects control files reachable by this server, while pg_extension records what is installed in the current database. An extension must be created separately in every database that needs it, and its supporting files must already be present on the server. Compare exact available, default, and installed versions across every primary, standby, image, and recovery environment; a catalog entry alone does not prove that a shared library can be loaded after failover. Benchmark performance-sensitive extensions after every binary or SQL upgrade because planner hooks, operator costs, and native code can change.

Inventory package and installation state
SELECT name, default_version, installed_version, comment
FROM pg_catalog.pg_available_extensions
ORDER BY name;

SELECT e.extname, e.extversion,
       e.extnamespace::regnamespace AS object_schema,
       e.extrelocatable,
       pg_get_userbyid(e.extowner) AS extension_owner
FROM pg_catalog.pg_extension AS e
ORDER BY e.extname;
Back to quick reference ↑
02

Install reviewed code into a schema untrusted roles cannot modify

CREATE EXTENSION runs a package-provided SQL script and tracks its objects as one unit. For a trusted extension, a role with CREATE on the database may install it even when its scripts require elevated privileges; the extension is owned by the caller while protected members are normally owned by the bootstrap superuser. Trusted describes the control-file privilege mechanism, not an independent security audit. Pin the reviewed version, avoid CASCADE unless every dependency and default version is approved, and never expose the target or dependency schemas to untrusted CREATE privileges.

Prepare a protected schema and install explicitly
CREATE ROLE extension_owner NOLOGIN;
GRANT CREATE ON DATABASE appdb TO extension_owner;
SET ROLE extension_owner;
CREATE SCHEMA extensions AUTHORIZATION extension_owner;
REVOKE CREATE ON SCHEMA extensions FROM PUBLIC;

-- Verify the packaged version and control metadata before this step.
CREATE EXTENSION pgcrypto
  WITH SCHEMA extensions
  VERSION '1.3';
RESET ROLE;
REVOKE CREATE ON DATABASE appdb FROM extension_owner;
Back to quick reference ↑
03

Rehearse the exact update path and its locking behavior

ALTER EXTENSION UPDATE runs one update script or the shortest available chain to the requested version. The scripts execute inside an implicit transaction and therefore cannot contain transaction control or commands such as VACUUM that are forbidden in a transaction block. Atomicity does not imply low impact: scripts can rewrite tables, rebuild indexes, acquire strong locks, invalidate plans, or change result semantics. Test against production-scale data, inspect every intermediate script, set a deliberate maintenance window, and retain a restore-based rollback because downgrade scripts are not guaranteed.

Review paths and bound lock acquisition
SELECT source, target, path
FROM pg_catalog.pg_extension_update_paths('pgcrypto')
ORDER BY source, target;

BEGIN;
SET LOCAL lock_timeout = '5s';
SET LOCAL statement_timeout = '15min';
ALTER EXTENSION pgcrypto UPDATE TO '1.3';
COMMIT;
Back to quick reference ↑
04

Preserve the extension dependency boundary

An extension itself has a database-wide unqualified name; extnamespace records the schema containing most or all of its objects, not a namespace containing the extension. Member objects normally cannot be dropped independently. ALTER EXTENSION ADD and DROP change membership without creating or deleting the object and are intended mainly for controlled update scripts. Moving an extension works only when it is relocatable, and dependent extensions can prohibit relocation when their references embed its schema name.

Audit members and their dependency kind
SELECT pg_catalog.pg_describe_object(
  d.classid, d.objid, d.objsubid
) AS member, d.deptype
FROM pg_catalog.pg_depend AS d
JOIN pg_catalog.pg_extension AS e
  ON e.oid = d.refobjid
WHERE d.refclassid = 'pg_extension'::regclass
  AND d.deptype = 'e'
  AND e.extname = 'pgcrypto'
ORDER BY member;
Back to quick reference ↑
05

Package versions and configuration data deliberately

A control file declares the default version, dependencies, privilege requirements, trust, and relocatability; versioned SQL files supply installation and update paths. PostgreSQL can chain update scripts, so test both clean installation and upgrades from every supported starting version. pg_dump normally emits CREATE EXTENSION rather than member definitions and omits member table contents. Mark only genuinely user-maintained configuration rows with pg_extension_config_dump, use stable filters, and avoid circular foreign keys between configuration tables because dump ordering cannot resolve them.

Register user-maintained configuration rows
CREATE TABLE app_tools.settings (
  setting_name text PRIMARY KEY,
  setting_value text NOT NULL,
  built_in boolean NOT NULL DEFAULT false
);

SELECT pg_catalog.pg_extension_config_dump(
  'app_tools.settings',
  'WHERE NOT built_in'
);
Back to quick reference ↑
06

Defend installation scripts and functions from search-path attacks

Extension installation and update scripts can be subverted when unqualified functions, operators, casts, or relations resolve to hostile objects. Trusted extensions face the highest bar because the installer can select a target schema while the script may execute as the bootstrap superuser. For general-purpose SQL, set a local path of pg_catalog, pg_temp and qualify intended extension objects explicitly. Secure SECURITY DEFINER and other privileged functions with a safe path, exact argument casts, reviewed privileges, and no writable schema before trusted names.

Constrain resolution inside an extension script
SELECT pg_catalog.set_config(
  'search_path',
  'pg_catalog, pg_temp',
  true
);

-- @extschema@ is substituted with the quoted installation schema.
CREATE FUNCTION @extschema@.secure_length(payload text)
RETURNS integer
LANGUAGE sql
IMMUTABLE
STRICT
SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
RETURN pg_catalog.octet_length(payload);

REVOKE ALL ON FUNCTION @extschema@.secure_length(text) FROM PUBLIC;
Back to quick reference ↑
07

Coordinate extensions across backups, replicas, and removal

Install matching extension packages before restoring a dump because restore recreates the extension and then loads marked configuration data. Physical replication replays database catalog changes, but shared libraries and control files are host-level assets and must exist on every promotion candidate. Logical replication does not provision extension DDL, so install and update compatible extension versions on subscribers before replicated data or expressions depend on them. Use DROP EXTENSION RESTRICT first, inventory outside dependencies, and expect CASCADE to remove dependent application objects.

Check outside dependencies before a restricted drop
WITH members AS (
  SELECT d.classid, d.objid, d.objsubid
  FROM pg_catalog.pg_depend AS d
  JOIN pg_catalog.pg_extension AS e ON e.oid = d.refobjid
  WHERE d.refclassid = 'pg_extension'::regclass
    AND d.deptype = 'e'
    AND e.extname = 'pgcrypto'
)
SELECT pg_catalog.pg_describe_object(
         x.classid, x.objid, x.objsubid
       ) AS dependent_object,
       pg_catalog.pg_describe_object(
         m.classid, m.objid, m.objsubid
       ) AS referenced_member,
       x.deptype
FROM members AS m
JOIN pg_catalog.pg_depend AS x
  ON x.refclassid = m.classid
 AND x.refobjid = m.objid
 AND x.refobjsubid = m.objsubid
WHERE NOT EXISTS (
  SELECT 1 FROM members AS own
  WHERE (own.classid, own.objid, own.objsubid) =
        (x.classid, x.objid, x.objsubid)
)
ORDER BY dependent_object;

DROP EXTENSION pgcrypto RESTRICT;
Back to quick reference ↑

Keep exploring

More SQL cheat sheets

Sources and further reading

References

Authoritative documentation used to verify and expand this cheat sheet.

  1. PostgreSQL Global Development GroupPostgreSQL: CREATE EXTENSIONpostgresql.org
  2. PostgreSQL Global Development GroupPostgreSQL: ALTER EXTENSIONpostgresql.org
  3. PostgreSQL Global Development GroupPostgreSQL: Packaging Related Objects into an Extensionpostgresql.org
  4. PostgreSQL Global Development GroupPostgreSQL: pg_available_extensionspostgresql.org
  5. PostgreSQL Global Development GroupPostgreSQL: pg_available_extension_versionspostgresql.org
  6. PostgreSQL Global Development GroupPostgreSQL: pg_extensionpostgresql.org
  7. PostgreSQL Global Development GroupPostgreSQL: Logical Replication Restrictionspostgresql.org
  8. PostgreSQL Global Development GroupPostgreSQL: pg_dumppostgresql.org
  9. PostgreSQL Global Development GroupPostgreSQL: pgcryptopostgresql.org

Help us improve

Found a typo or missing example?

Tell us what would make this cheat sheet clearer, more complete, or more useful.

Share feedback