postgres-extensions

What this skill does

# PostgreSQL Extensions & the contrib Catalog

## Overview

A PostgreSQL **extension** is a packaged bundle of SQL objects — functions, data
types, operators, index access methods, casts, even C libraries — that you install
into a database with a single `CREATE EXTENSION` command and remove just as cleanly
with `DROP EXTENSION`. PostgreSQL tracks every object an extension owns, so the whole
unit can be upgraded, relocated to another schema, or dumped/restored atomically.

**`contrib`** is the set of extensions and modules that ship *in the PostgreSQL source
tree itself* (under `contrib/`) and are packaged by most distributions as a
`postgresql-contrib` package. They are official, versioned with the server, and
maintained by the core project — but **not installed into a database until you ask**.
This skill is the map of that ecosystem plus the commands to manage it.

**Key mental model:**
- **An extension is a managed unit, not loose SQL.** `CREATE EXTENSION pg_trgm;` is not
  the same as running its SQL by hand — Postgres records membership in `pg_extension`
  so `DROP`/`ALTER ... UPDATE` work.
- **`contrib` ships with the server but is dormant.** The files live on disk (often a
  separate OS package); `CREATE EXTENSION` activates one *per database*.
- **Two version numbers, do not conflate them.** The extension's own version (e.g.
  `pg_stat_statements` 1.13, from its `.control` `default_version`) is independent of
  the PostgreSQL release (e.g. 18). See [Two version numbers](#two-version-numbers).
- **Some extensions need a server restart**, because they hook into the backend via
  `shared_preload_libraries` (e.g. `pg_stat_statements`, `auto_explain`). Most don't.

> **Disambiguation.** This skill = **extension management + the bundled contrib
> catalog**. For the **`psql`** client (`\dx`, meta-commands) use the **psql** skill;
> for **core SQL / dialect** use **postgres-sql**; for **using perf extensions to
> actually tune queries** use **postgres-performance** (this skill only points at
> them); for **server configuration** like editing `shared_preload_libraries` or
> `postgresql.conf` use **postgres-admin**. External extensions (PostGIS, pgvector,
> TimescaleDB) are **not** in contrib — see [External extensions](#external-not-in-contrib).

## Version annotations

This skill targets the contrib set shipped with **PostgreSQL 18 (stable)**, with notes
for **19 (beta)**. Inline `(pgNN+)` marks the **first PostgreSQL release** a module or
feature shipped in. Modules present since the **old 9.x era are "bedrock" and carry no
tag** (e.g. `pgcrypto`, `pg_trgm`, `hstore`, `pg_stat_statements`). A tag means
"requires PostgreSQL NN or newer." The full feature → minimum-version map with sources
is in [references/version-features.md](references/version-features.md). Always confirm
against the running server (`SELECT version();`).

## When to use which approach

| You want to… | Use |
|---|---|
| Turn on a packaged feature in a database | `CREATE EXTENSION name;` |
| See what's installed vs. available | `\dx` · `pg_available_extensions` |
| Upgrade an extension's objects after a server upgrade | `ALTER EXTENSION name UPDATE;` |
| Remove an extension and everything it owns | `DROP EXTENSION name CASCADE;` |
| Pull in dependencies automatically | `CREATE EXTENSION name CASCADE;` |
| Let a non-superuser install a safe extension | a **trusted** extension (pg13+) |
| Enable a backend hook (stats, logging) | add to `shared_preload_libraries` + restart |

## Managing extensions

### Install

```sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;              -- idempotent
CREATE EXTENSION hstore WITH SCHEMA extensions;      -- put its objects in a chosen schema
CREATE EXTENSION earthdistance CASCADE;              -- also installs its prereq (cube)
CREATE EXTENSION pg_stat_statements VERSION '1.13';  -- pin a specific extension version
```

- `IF NOT EXISTS` makes it safe to re-run in migrations.
- `WITH SCHEMA s` installs the objects into schema `s` (the schema must exist, or use
  `CASCADE` to create it for a relocatable extension). This matters for `search_path`:
  if you install `pg_trgm` into a schema not on the path, you must schema-qualify its
  operators/functions or add the schema to `search_path`.
- `CASCADE` (pg9.6+) auto-installs required extensions **and** creates a missing target
  schema. Great for `earthdistance` (needs `cube`).
- `VERSION 'x.y'` installs a specific version if multiple are available on disk.

### Discover

```sql
\dx                          -- (psql) installed extensions in this database
\dx+ pg_trgm                 -- (psql) list the objects an installed extension owns
SELECT * FROM pg_available_extensions;          -- everything installable on disk
SELECT * FROM pg_available_extension_versions;  -- per-version, incl. the `trusted` flag
SELECT extname, extversion FROM pg_extension;   -- catalog of what's installed
```

`pg_available_extensions.default_version` is what `CREATE EXTENSION` installs if you
don't pin a version; `installed_version` is NULL until you create it. (pg19+ adds a
`location` column reporting the on-disk directory.)

### Upgrade

```sql
ALTER EXTENSION pg_stat_statements UPDATE;            -- to the newest version on disk
ALTER EXTENSION postgres_fdw UPDATE TO '1.2';         -- to a specific version
```

Run `ALTER EXTENSION ... UPDATE` **after a major PostgreSQL upgrade** (or after
installing a newer contrib package) to apply the extension's upgrade scripts — a
`pg_upgrade` migrates data but does **not** bump extension versions for you. Check for
stragglers:

```sql
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS DISTINCT FROM default_version
  AND installed_version IS NOT NULL;
```

### Relocate / modify / remove

```sql
ALTER EXTENSION hstore SET SCHEMA extensions;   -- move a relocatable extension's objects
DROP EXTENSION IF EXISTS hstore;                -- fails if other objects depend on it
DROP EXTENSION hstore CASCADE;                  -- also drops dependents (be careful!)
```

`CASCADE` on `DROP` removes everything that depends on the extension (columns of its
types, indexes using its operator classes, …) — powerful and destructive. Prefer the
non-cascade form first to see what would break.

### Two version numbers

| Number | Example | Where it comes from | Meaning |
|---|---|---|---|
| **PostgreSQL release** | `18`, `19beta1` | `SELECT version();` / `server_version` | The database server version |
| **Extension version** | `pg_stat_statements 1.13` | the module's `.control` `default_version` | The extension's own object/schema version |

These move independently. `pg_stat_statements` being version **1.13** says nothing about
which PostgreSQL release you run — it's the extension's internal version, bumped by its
own upgrade scripts (`pg_stat_statements--1.12--1.13.sql`). In this skill, `(pgNN+)` tags
always mean the **PostgreSQL release**, never the extension's own number.

### Trusted extensions (pg13+)

By default `CREATE EXTENSION` requires superuser. A **trusted** extension can instead be
installed by any role with `CREATE` privilege on the database — these are the modules
that "cannot provide access to outside-the-database functionality." The contrib
extensions trusted in a default install are:

> `btree_gin`, `btree_gist`, `citext`, `cube`, `dict_int`, `fuzzystrmatch`, `hstore`,
> `intarray`, `isn`, `lo`, `ltree`, `pgcrypto`, `pg_trgm`, `seg`, `tablefunc`, `tcn`,
> `tsm_system_rows`, `tsm_system_time`, `unaccent`, `uuid-ossp` (plus the PL transforms
> like `bool_plperl`, `jsonb_plperl`).

Notably **not** trusted: `postgres_fdw`, `dblink`, `file_fdw`, `pg_stat_statements`,
`adminpack`, and the forensic/inspection modules — they reach outside the database or
need superuser.

### Extensions that need `shared_preload_libraries` (restart required)

A few modules hook into the backend at startup and must be listed in
`shared_preload_libraries` in `post