skip to content

Extensions & Contrib Modules

PostgreSQL extensions package SQL objects and C code behind CREATE EXTENSION, versioned upgrade scripts and shared_preload_libraries. Interviewers probe trusted installs, restarts and upgrades.

part ofPostgreSQLread it →
on this pageshow

explore

questions

6

In PostgreSQL, what does CREATE EXTENSION actually do, and what must already exist on the server before it can succeed?

level: juniorimportance: must knowfreq 55%

answer

  1. a package, not loose objects
  2. files on disk come first
  3. control file plus versioned script
  4. per database, recorded in pg_extension

basics

~20 s

CREATE EXTENSION runs an extension's install script in the current database and records every object it creates as a member of one package. The control file, SQL scripts and any C library must already be installed on the server.

solid answer

~40 s

`CREATE EXTENSION ltree` finds `ltree.control` (in `SHAREDIR/extension`, or a directory listed in PostgreSQL 18's new `extension_control_path`). It picks `default_version` unless you pass `VERSION`, and runs the matching `ltree--<version>.sql` script inside the current transaction. The extension gets a row in `pg_extension`, and every object the script creates is recorded as a member in `pg_depend`. That is why `DROP EXTENSION` removes them all together and `pg_dump` writes one `CREATE EXTENSION` line. The command acts on one database only. It does not download anything: an OS package or a build must already have put the control file, scripts and shared library on the server and on every standby. If they are missing, PostgreSQL 18 answers `extension "postgis" is not available`. `pg_available_extensions` lists what is on disk, and `\dx` lists what is installed.

code

sql · 18 lines
sql
-- on disk vs installed in this database
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE name IN ('ltree', 'postgis');

CREATE EXTENSION IF NOT EXISTS ltree;

CREATE TABLE product_category (
  category_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  path        ltree NOT NULL UNIQUE      -- e.g. 'grocery.dairy.cheese'
);
CREATE INDEX product_category_path_gist ON product_category USING gist (path);

-- every category under grocery.dairy, including itself
SELECT path FROM product_category WHERE path <@ 'grocery.dairy';

-- fails (RESTRICT): product_category.path depends on the ltree type
DROP EXTENSION ltree;

go deeper

for a junior

Know that an extension is files on the server plus objects in one database, that CREATE EXTENSION installs it per database, and that pg_available_extensions and \dx show what exists.

for a middle

Explain the control file, versioned scripts and update chains, pg_extension membership, and why DROP EXTENSION defaults to RESTRICT.

for a senior

Show operational care: packages on every standby before CREATE EXTENSION, read CASCADE dependency lists before dropping, and know that 18's extension_control_path suits immutable images.

for a principal

Discuss which extensions a platform should allow at all, how their packages are versioned and rolled out across environments, and who owns installing them.

## What an extension is An **extension** is how PostgreSQL packages related SQL objects (functions, data types, operators, operator classes, sometimes configuration tables) together with optional C code. The contrib modules that ship with PostgreSQL, such as `ltree`, `hstore`, `pgcrypto` and `amcheck`, are extensions. So are external projects such as PostGIS and Citus. Packaging matters because PostgreSQL then *knows* the objects belong together: it can drop them together, upgrade them together and dump them as one line. An extension has two parts: **files on the server** and **catalog state inside one database**. The files come first: | File | Example | Location | Installed by | |---|---|---|---| | Control file | `ltree.control` | `SHAREDIR/extension` (`pg_config --sharedir`) | OS package or `make install` | | Install script | `ltree--1.1.sql` | next to the control file | same | | Update scripts | `ltree--1.2--1.3.sql` | next to the control file | same | | Shared library | `ltree.so` | `$libdir` (`pg_config --pkglibdir`) | same | The control file is a small `key = value` file. Its settings include `default_version`, `module_pathname`, `relocatable`, `requires`, `superuser` and `trusted`. ## What CREATE EXTENSION does, step by step 1. **Finds `<name>.control`** on the extension control path. If there is no such file, PostgreSQL 18 stops with `extension "<name>" is not available`, plus a hint that the extension must first be installed on the system where PostgreSQL is running. 2. **Picks the version**: the `VERSION` clause if you gave one, otherwise `default_version` from the control file. 3. **Checks privileges**: you must be a superuser, unless the control file says `trusted = true` and you hold `CREATE` on the database. 4. **Runs the scripts** inside the current transaction (so they cannot contain `VACUUM` or `COMMIT`). If only an older base script exists, it follows the update chain. Installing `ltree` 1.3 runs `ltree--1.1.sql`, then `ltree--1.1--1.2.sql`, then `ltree--1.2--1.3.sql`. 5. **Records the result**: a row in `pg_extension` (`extname`, `extversion`, `extnamespace`, `extowner`), plus a membership dependency (`deptype = 'e'` in `pg_depend`) for each object the script created. The C library is usually **not** loaded at this point. A backend loads it the first time one of its functions is called. Libraries that must be in memory before any backend starts are listed in `shared_preload_libraries`, which is a separate step. ## Scope: files per installation, objects per database - The files serve the whole installation, but `CREATE EXTENSION` acts on **the current database only**. Each database that needs `ltree` runs it, unless that database was created from a template such as `template1` that already had the extension. - An extension's name is unique within the database, and the extension itself does not live in a schema; its member objects do. `CREATE EXTENSION ltree SCHEMA ext` puts the objects in `ext`, unless the control file pins its own `schema`. - `CASCADE` first installs any extensions named in `requires`. `IF NOT EXISTS` turns a duplicate into a notice, but it never checks that the existing extension matches the files on disk. - **Physical standbys** receive the catalog rows through WAL, but never the files. Install the same package on every standby, or calls into the library fail there. ## Seeing what exists - `pg_available_extensions` lists every control file found on disk, with its `default_version` and its `installed_version` (NULL if it is not installed in this database). - `pg_available_extension_versions` has one row per installable version, with the `superuser`, `trusted`, `relocatable` and `requires` flags. - `pg_extension` lists what is installed in this database. psql's `\dx` shows the same. In PostgreSQL 18, `\dx` also gains a *Default version* column, so you can see at a glance when an installed version lags behind the files. ## Removing an extension `DROP EXTENSION` removes every member object. It defaults to `RESTRICT`: if a table column uses the `ltree` type, the command fails with *cannot drop extension ltree because other objects depend on it*. `CASCADE` drops those dependents too, including that column and its data, so read the list of dependents first. You cannot drop a single member on its own. Changes to members belong in an update script, which you apply with `ALTER EXTENSION ... UPDATE`. ## PostgreSQL 18: extension_control_path Before 18, the control files had to live in the compiled-in `SHAREDIR/extension` directory. PostgreSQL 18 adds `extension_control_path`, a superuser-only setting that defaults to `'$system'` (that compiled-in directory). It takes a colon-separated list of directories, each expected to contain an `extension` subdirectory. This lets extensions live outside a read-only installation tree, such as an immutable container image. It is normally set together with `dynamic_library_path`, which points at the matching library directory. If two directories hold an extension with the same name, the first one on the path wins.

  • Why does a PostgreSQL streaming standby need the extension's package installed even though CREATE EXTENSION ran only on the primary?
    Physical replication ships WAL. WAL carries the catalog rows (`pg_extension`, `pg_proc`, the new types) but never files outside the data directory. The control file, scripts and shared library live in the installation's share and lib directories. A standby without the package therefore has catalog entries that point at a library it cannot load, and the first query that calls one of its C functions fails. Install the same package version on every standby before running `CREATE EXTENSION`.
  • What does PostgreSQL's DROP EXTENSION ... CASCADE remove that a plain DROP EXTENSION refuses to?
    Plain `DROP EXTENSION` means `RESTRICT`: it drops the members only if nothing outside the extension depends on them. If `product_category.path` has type `ltree`, the command errors and lists that column. `CASCADE` also drops the dependents, which here means the column and its data along with any index on it. On a production database, read the dependency list before adding `CASCADE`.

Installing an extension is like flat-pack furniture: the OS package delivers the boxed parts to the building's storeroom (files on the server), and CREATE EXTENSION assembles one set in one room (a database). Each room that wants the furniture assembles its own set, and DROP EXTENSION takes the whole set out of that room only.

  • CREATE EXTENSION downloads and builds the extension from the internet.
  • Running CREATE EXTENSION once makes it available in every database of the cluster.
  • An extension lives inside a schema, so two schemas can each install their own copy.
  • DROP EXTENSION by default also drops the tables and columns that use its types.
  • A streaming standby gets the extension's shared library through WAL replay.

Assumes PostgreSQL 18: the extension_control_path setting and the Default version column in psql's \dx are new in 18.

open as a page

In PostgreSQL, why do some extensions need shared_preload_libraries and a server restart while others work right after CREATE EXTENSION?

level: middleimportance: must knowfreq 62%

basics

~20 s

Libraries that reserve shared memory, start background workers or install global hooks must be loaded by the postmaster at startup through shared_preload_libraries, which takes a restart. Libraries that only provide functions load on first call, so CREATE EXTENSION is enough.

open as a page

Using PostGIS in PostgreSQL for a delivery-zone feature, how do you store zones and addresses and query them so a GiST index is used?

level: middleimportance: should knowfreq 47%

basics

~20 s

Install PostGIS, store zones and points as geography in SRID 4326, index the columns with GiST, and filter with index-aware predicates such as ST_Covers and ST_DWithin. A comparison like ST_Distance(...) < 3000 cannot use the index.

open as a page

In PostgreSQL, why can a non-superuser application role run CREATE EXTENSION ltree but get permission denied for amcheck?

level: middleimportance: should knowfreq 38%

basics

~20 s

ltree's control file says trusted = true, so any role with CREATE privilege on the current database may install it, and its script runs as the bootstrap superuser. amcheck is not marked trusted, so only a superuser can install it.

open as a page

After pg_upgrade moves a PostgreSQL 17 cluster to 18, why does amcheck still lack gin_index_check, and how should extension versions be managed across upgrades?

level: seniorimportance: should knowfreq 44%

basics

~20 s

pg_upgrade recreates each extension at its old SQL version, so amcheck stays at 1.4 while the 18 files offer 1.5 with gin_index_check. Running pg_upgrade's update_extensions.sql, or ALTER EXTENSION amcheck UPDATE in each database, brings the SQL objects forward.

open as a page

After an OS upgrade on a PostgreSQL 18 host, how would you use amcheck to check product_category's B-tree indexes for corruption without blocking writes?

level: seniorimportance: nice to knowfreq 21%

basics

~20 s

As a superuser, install amcheck and run bt_index_check on each B-tree index, optionally with heapallindexed => true. It takes only an AccessShareLock, so writes continue. Avoid bt_index_parent_check on a busy primary, because its ShareLock blocks writes.

open as a page