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, orrds:DisableDBExtensionto turn one off;rds:DescribeDBExtensionsto see the Extensions tab and list them. Reboot now needsrds: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:DescribeDBInstancesto open the database's page (without it the page is not found).
Enable an extension
- Open the database, then choose Extensions.
- If the server has more than one database, choose it under Database.
- Find the extension with Search extensions or a category.
- Turn on its switch.
- 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).
dcloud rds extensions list ewdb-0123456789ab --database appdb
dcloud rds extensions enable ewdb-0123456789ab vector --database appdbPOST /rds/api/instances/ewdb-0123456789ab/extensions/vector/enable
{ "database": "appdb" }Without database, the database created with the instance.
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), 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
- On the Extensions tab, turn off the extension's switch.
- In Disable vector?, choose Disable.
- 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.
dcloud rds extensions disable ewdb-0123456789ab vector --database appdbAdd --cascade only after a first attempt listed the dependent objects.
POST /rds/api/instances/ewdb-0123456789ab/extensions/vector/disable
{ "database": "appdb", "cascade": false }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 EXTENSIONfails.
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: the extension builds each minor pins.
POST /rds/api/instances/{id}/extensions/{extension}/enable·dcloud rds extensions enable·dcloud_db_extension