Nov 18, 20266 min read

Postgres Extensions on Amazon RDS: How to Check, Enable and Upgrade Them

Which Postgres extensions Amazon RDS supports, how to list them with SHOW rds.extensions, who can run CREATE EXTENSION, which ones need shared_preload_libraries, how rds.allowed_extensions restricts them, and how to keep extensions in migrations.

Illustration for Postgres Extensions on Amazon RDS: How to Check, Enable and Upgrade Them

Amazon RDS for PostgreSQL offers a fixed set of extensions per engine version, and nothing outside that list installs. pg_trgm, pgcrypto, citext and pgvector are all there, but the errors get confusing once an extension needs a server library or the instance restricts installs.

This guide covers how to check what an instance supports, who can run CREATE EXTENSION, which extensions need a parameter group change, and how to keep extensions in migrations that Encore runs both locally and on RDS.

RDS extension rules at a glance

QuestionAnswer on RDS for PostgreSQL
Which extensions can I use?The list for your engine version, from SHOW rds.extensions; or the RDS extension tables
Who can install them?rds_superuser on version 12 and earlier; on 13 and later, any role with CREATE on the database for trusted extensions
Which need a reboot?Libraries loaded at start, such as pg_cron, through shared_preload_libraries in a custom parameter group
Can I block some?Yes, list the allowed ones in rds.allowed_extensions
What happens on an engine upgrade?Newer extension versions become available, and ALTER EXTENSION ... UPDATE moves each database to them
Can I install my own C extension?No, but Trusted Language Extensions (pg_tle) allow extensions in trusted languages

The permission and parameter rules come from the RDS guide to PostgreSQL extensions.

How do you check which extensions an instance supports?

The first query below returns the allowed extension names for that instance, and the second shows the versions the server can install and the ones already installed in the current database.

SHOW rds.extensions; SELECT name, default_version, installed_version FROM pg_available_extensions WHERE name IN ('pg_trgm', 'citext', 'vector', 'pg_cron', 'postgis') ORDER BY name;

AWS notes that an extension added in a minor version release can show up inaccurately in rds.extensions, so for a planned dependency, check the release notes table for your exact version too. Extensions are per database: enabling pg_trgm in orders does nothing for billing on the same instance.

Who can run CREATE EXTENSION?

On RDS for PostgreSQL 12 and earlier, installing an extension takes rds_superuser, which the master user created with the instance holds. On 13 and later, a role with CREATE on the database can install any trusted extension, which covers common ones like pg_trgm, citext, pgcrypto and hstore, while untrusted ones still need rds_superuser. The practical rule is to run migrations that create extensions as a role that can, and to keep the application role at the privileges it needs for queries.

Which extensions need shared_preload_libraries?

Some extensions are a library that the server loads at start, not only SQL objects. On RDS the default parameter group typically loads only pg_stat_statements, so anything else goes into a custom DB parameter group. pg_cron is the usual example:

aws rds modify-db-parameter-group \ --db-parameter-group-name app-pg17 \ --parameters "ParameterName=shared_preload_libraries,ParameterValue='pg_stat_statements,pg_cron',ApplyMethod=pending-reboot" aws rds reboot-db-instance --db-instance-identifier app-db

After the reboot, CREATE EXTENSION pg_cron; works in the database named by cron.database_name, which defaults to postgres. Running it anywhere else, or before the reboot, is where the "must be loaded via shared_preload_libraries" error comes from.

How does rds.allowed_extensions work?

rds.allowed_extensions is a parameter that lists the only extensions that can be installed on the instance, which suits shared instances where you want to stop a role with CREATE from adding anything trusted it likes. Once it is set, every extension a migration creates has to be on the list, including pg_cron after you preload it, and a missing entry fails the migration on that instance while it passes everywhere else.

How do you upgrade extensions?

A minor or major engine upgrade can ship a newer extension version, and existing databases keep the old one until you update it. Check with pg_available_extensions and then update in a migration, so staging and production move together:

ALTER EXTENSION vector UPDATE; ALTER EXTENSION pg_trgm UPDATE;

Major version upgrades also check extension compatibility before they run, and some extensions have to be updated, or dropped and recreated, before the upgrade can start, as the RDS major version upgrade docs describe.

How do you keep extensions in migrations with Encore?

Encore

Encore brings infrastructure from code, so the database is a declaration in the service that uses it and the extension is a line in that database's first migration. encore run starts Postgres locally and applies the migration, using a build that includes extensions such as pg_trgm, citext, pgcrypto and vector, which the Postgres extensions docs list with versions. On deploy to AWS, Encore provisions RDS for PostgreSQL in your account, as the AWS infrastructure docs describe, and runs the same migration there, so the extension has to be on the RDS list for that engine version too.

Application code passes through Encore and infrastructure guardrails to resources provisioned in your own AWS or GCP account.
Get started
brew install encoredev/tap/encore &&
encore app create
Copy

Run in your terminal to get started locally.

-- customers/migrations/1_create_customers.up.sql CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE EXTENSION IF NOT EXISTS citext; CREATE TABLE customers ( id BIGSERIAL PRIMARY KEY, email CITEXT NOT NULL UNIQUE, name TEXT NOT NULL ); CREATE INDEX customers_name_trgm ON customers USING gin (name gin_trgm_ops);

The service declares the database and uses the trigram operator in a search endpoint:

// customers/customers.go package customers import ( "context" "encore.dev/storage/sqldb" ) var db = sqldb.NewDatabase("customers", sqldb.DatabaseConfig{ Migrations: "./migrations", }) type SearchParams struct { Q string `query:"q"` } type Customer struct { ID int64 `json:"id"` Email string `json:"email"` Name string `json:"name"` } type SearchResponse struct { Customers []Customer `json:"customers"` } //encore:api public method=GET path=/customers/search func Search(ctx context.Context, p *SearchParams) (*SearchResponse, error) { rows, err := db.Query(ctx, ` SELECT id, email::text, name FROM customers WHERE name % $1 ORDER BY similarity(name, $1) DESC LIMIT 10`, p.Q) if err != nil { return nil, err } defer rows.Close() out := []Customer{} for rows.Next() { var c Customer if err := rows.Scan(&c.ID, &c.Email, &c.Name); err != nil { return nil, err } out = append(out, c) } return &SearchResponse{Customers: out}, rows.Err() }

citext makes the unique constraint on email case-insensitive, and the GIN trigram index serves the % similarity operator, so a search for jon smth still finds Jon Smith. A coding agent that needs fuzzy search adds the extension and the index in the migration it is already writing, runs it locally with encore run, and the same file runs on every environment instead of a manual psql session against production.

Extensions that need shared_preload_libraries are a server setting rather than a migration. For scheduled work, an Encore cron job declared in Go is the in-code option, and it is managed by the Encore platform rather than by pg_cron on the database.

Best fit: Go or TypeScript backends on AWS that want extensions such as pg_trgm, citext or pgvector enabled by migrations that run the same way locally and on RDS.

Frequently asked questions

How do I see which extensions Amazon RDS for PostgreSQL supports?

Connect to the instance and run SHOW rds.extensions; to list the extensions that engine version allows, and SELECT name, default_version, installed_version FROM pg_available_extensions; to see versions. AWS also publishes a table per major version in the RDS for PostgreSQL release notes, and notes that extensions added in a minor release can show up inaccurately in rds.extensions.

Who can run CREATE EXTENSION on RDS?

On RDS for PostgreSQL 12 and earlier, installing an extension requires the rds_superuser role, which the master user has. On version 13 and later, any role with CREATE permission on the database can install trusted extensions, and other extensions still need rds_superuser.

Why does CREATE EXTENSION pg_cron fail on RDS?

pg_cron is a library that has to be loaded at server start, so add pg_cron to shared_preload_libraries in a custom DB parameter group, reboot the instance, and then run CREATE EXTENSION pg_cron in the database named by cron.database_name. If the instance uses rds.allowed_extensions, pg_cron also has to be on that list.

How do I enable a Postgres extension with Encore?

Put CREATE EXTENSION IF NOT EXISTS in a migration of the database declared with sqldb.NewDatabase. encore run applies it to the local Postgres, which ships extensions such as pg_trgm, citext and vector, and a deploy runs the same migration on the RDS database in your AWS account.

Deploy to your cloud on AWS and Google Cloud
without managing infrastructure

Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.

~/orders — claude
Claude Code
Claude Code v2.1.180Opus 5.5~/orders