# Enable an extension

Turn a PostgreSQL extension from the catalog on or off in one database, such as vector, postgis or pg_cron.

The **Extensions** tab lists every extension the platform supports, with a switch per database.
The state is read live from the server, so an extension your application's own migration created
(`CREATE EXTENSION IF NOT EXISTS vector`) shows as enabled too. Replace _ewdb-0123456789ab_ with
your database's id and _appdb_ with the database inside it.

**Warning:**
Disabling with CASCADE drops the extension **and every object that depends on it**, columns and
their data included. That cannot be undone except by a restore.

## Prerequisites

- Permission for `rds:EnableDBExtension`, or `rds:DisableDBExtension` to turn one off;
  `rds:DescribeDBExtensions` to see the **Extensions** tab and list them. **Reboot now** needs
  `rds:RebootDBInstance`.
- The database is **available**. Otherwise the tab and the API answer
  `This database is <status>. Extensions can be read and changed once it is available.` (409).
- In the console, also `rds:DescribeDBInstances` to open the database's page (without it the page is
  not found).

## Enable an extension

**Console**
1. Open the database, then choose **Extensions**.
2. If the server has more than one database, choose it under **Database**.
3. Find the extension with **Search extensions** or a category.
4. Turn on its switch.
5. For an extension marked **Needs preload**, the console asks first: choose **Add to preload**,
   reboot the database, then turn the switch on again (see below).

**CLI**
```sh
dcloud rds extensions list ewdb-0123456789ab --database appdb
dcloud rds extensions enable ewdb-0123456789ab vector --database appdb
```

**API**
```http
POST /rds/api/instances/ewdb-0123456789ab/extensions/vector/enable
{ "database": "appdb" }
```

Without `database`, the database created with the instance.

**Terraform**
```hcl
resource "dcloud_db_extension" "vector" {
  db_instance_id = dcloud_db_instance.orders.id
  name           = "vector"
}
```

The resource enables the extension in the database created with the instance only.

Turning one on runs `CREATE EXTENSION IF NOT EXISTS … CASCADE` as the master user in that
database. `CASCADE` installs what it depends on (`earthdistance` brings `cube`, `postgis_raster`
brings `postgis`), and the result says what else it installed.

**Extensions that need a preload.** `pg_stat_statements` and `pg_cron` need their library in
`shared_preload_libraries`. Every database provisioned since extension support existed preloads both
from its first boot, so their switches need no restart. `pgaudit` and `timescaledb` are not
preloaded: switching one on adds its library to the configuration (through `ALTER SYSTEM` on a
single node, through Patroni's cluster-wide configuration on a cluster) and answers `202`; the extension is marked **Restart required**. Reboot the database (**Reboot now**
in the **Restart required.** banner, or see
[Stop, start or reboot a database](stop-start-or-reboot.md)), then switch it on again.

**pg_cron** runs in one database per server, `cron.database_name`. A database provisioned with
extension support sets it to the database created with the instance; where it is not set, it is
`postgres`. Enabling it in another database is refused (409):
`pg_cron can live in one database per server, and on this one it is <database> (cron.database_name). Enable it there; from it, cron.schedule_in_database() can run jobs in any other database.`

## Who can create an extension

Extensions marked **Superuser** can only be created by a superuser. The master user is one, so the
switch always works. An app bound with its own role can still create most of them from its
migrations: every database provisioned since the allowlist was added allowlists the catalog for app roles
(pgextwlist), so `CREATE EXTENSION vector` in a migration works without the master credential. The
extension is created with superuser rights; the role gains nothing else. Left out of that
allowlist, and still master-only: anything that opens connections from the database server
(`postgres_fdw`, `dblink`, `http`), the cluster-wide monitoring views (`pg_stat_statements`,
`pg_walinspect`, …) and `pg_cron`, `pgaudit`, `pg_repack`. The rest are _trusted_, and any role
with `CREATE` on the database can create them. A database provisioned before the allowlist existed
can be given it by the platform operator; it applies on reload, without a restart.

Do not also switch an extension on before the first deploy when the app's migrations create it with
a plain `CREATE EXTENSION` (no `IF NOT EXISTS`): the migration then fails with "already exists".

## Disable an extension

**Console**
1. On the **Extensions** tab, turn off the extension's switch.
2. In **Disable vector?**, choose **Disable**.
3. If objects depend on it, nothing is dropped and **Objects depend on vector** lists them. To drop
   them too, type the extension's name and choose **Disable with CASCADE**; otherwise choose
   **Cancel**.

**CLI**
```sh
dcloud rds extensions disable ewdb-0123456789ab vector --database appdb
```

Add `--cascade` only after a first attempt listed the dependent objects.

**API**
```http
POST /rds/api/instances/ewdb-0123456789ab/extensions/vector/disable
{ "database": "appdb", "cascade": false }
```

**Terraform**
Remove the `dcloud_db_extension` resource and run `terraform apply`.

Turning one off runs `DROP EXTENSION`, `RESTRICT` first. If anything depends on the extension (a
`vector` column, an index using `gin_trgm_ops`, another extension), nothing is dropped: the answer
is `409` `Objects in appdb depend on vector. Disabling it with CASCADE drops them too.` with the
list of dependent objects (`dependents`). Only a second request with `cascade: true` drops the
extension and every object in that list.

## If enabling is refused

- 404 `<name> is not an extension this platform supports.` for a name outside the catalog.
- 409 `The package for this extension is not installed on this database server. …` (the console
  marks it **Not installed on server**).
- 409 for the pg_cron database, above, and with Postgres's own message when `CREATE EXTENSION`
  fails.

A preloaded library stays in `shared_preload_libraries` after its extension is disabled. It costs
nothing while unused, and removing it would need another restart.

## Result

Every enable, disable, refused disable and preload change is a line in **Activity** (phase
`extensions`).

## The catalog

**Source** `contrib` ships with the server package; `pgdg` is a package from apt.postgresql.org,
installed on every node (primary, replicas and restore targets), so a replica can answer a query on
a `vector` column and a restore of a PostGIS database can start.

| Category                  | Extensions                                                                                                                                                                                                             |
| ------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| AI & vectors              | `vector` (pgvector, pgdg)                                                                                                                                                                                              |
| Search & text             | `pg_trgm`, `unaccent`, `fuzzystrmatch`, `dict_int`, `dict_xsyn` (contrib); `rum` (pgdg)                                                                                                                                |
| Data types                | `citext`, `hstore`, `ltree`, `cube`, `seg`, `isn`, `lo`, `uuid-ossp` (contrib); `pg_uuidv7`, `semver`, `ip4r`, `prefix`, `unit`, `hll`, `roaringbitmap`, `tdigest` (pgdg)                                              |
| Geospatial                | `earthdistance` (contrib); `postgis`, `postgis_raster`, `postgis_topology`, `postgis_sfcgal`, `postgis_tiger_geocoder`, `address_standardizer`, `address_standardizer_data_us`, `pgrouting`, `h3`, `h3_postgis` (pgdg) |
| Indexing                  | `btree_gist`, `btree_gin`, `bloom`, `intarray` (contrib); `hypopg` (pgdg)                                                                                                                                              |
| Scheduling & partitioning | `pg_cron` (preloaded), `pg_partman`, `timescaledb` (Apache-2 edition; needs preload), `pg_ivm` (pgdg)                                                                                                                  |
| Monitoring & tuning       | `pg_stat_statements` (preloaded), `pg_buffercache`, `pg_prewarm`, `pgstattuple`, `pgrowlocks`, `pg_freespacemap`, `pg_visibility`, `pg_walinspect`, `amcheck` (contrib); `pg_repack` (pgdg)                            |
| Security & crypto         | `pgcrypto`, `sslinfo` (contrib); `pgaudit` (pgdg; needs preload)                                                                                                                                                       |
| Foreign data              | `postgres_fdw`, `dblink` (contrib); `http` (pgdg)                                                                                                                                                                      |
| Testing & development     | `pgtap`, `plpgsql_check`, `orafce` (pgdg)                                                                                                                                                                              |
| Utilities                 | `tablefunc`, `moddatetime`, `autoinc`, `insert_username`, `refint`, `tcn`, `intagg`, `tsm_system_rows`, `tsm_system_time`, `xml2` (contrib)                                                                            |

`postgis_tiger_geocoder` is not offered on PostgreSQL 15: since PostGIS 3.7 it is a package of its
own, and PGDG does not build it for 15. A node built from the PostGIS 3.7 package before the
catalog installed those separate packages lacks `postgis_tiger_geocoder` and
`address_standardizer` (marked **Not installed on server**); the platform operator installs them.

A database provisioned before extension support has the contrib extensions but none of the pgdg
packages; the console marks those **Not installed on server**. The platform operator installs them
once, without a restart.

## Related

- [Versions and maintenance](versions-and-maintenance.md): the extension builds each minor pins.
- `rds.extensions.enable` · `dcloud rds extensions enable` · `dcloud_db_extension`
