PostgreSQL Extensions

PostgreSQL extensions add capabilities to your database - things like case-insensitive text (citext), trigram search (pg_trgm), vector similarity (pgvector), or a message queue (pgmq). Enabling one is a one-time action: CREATE EXTENSION records it permanently in that database, so it survives Postgres restarts, app redeploys, and server reboots. You never need a startup script to re-enable an extension on boot.

How you enable an extension depends on whether it's a trusted extension or a third-party one.

The Two Steps Behind Every Extension

Under the hood there are always two parts:

  1. The extension's files must exist on the server (a .control file, SQL scripts, and sometimes a compiled library). Without them, CREATE EXTENSION fails with could not open extension control file ....
  2. You register it into a specific database with CREATE EXTENSION. Extensions are per-database, so you enable it in your app's database, not the cluster as a whole.

The good news is that step 1 is already done for the most common extensions.

Trusted Extensions (the easy path)

Postgres ships a set of trusted extensions whose files are already installed on your Potions server, and which your app's database role is allowed to enable on its own (no superuser required). These include citext, pg_trgm, hstore, uuid-ossp, unaccent, fuzzystrmatch, pgcrypto, ltree, btree_gin, and btree_gist - plus a handful of others. (See PostgreSQL's contrib list for the full set; "trusted" extensions are the ones a database owner can install.)

For these, the clean, repeatable way is an Ecto migration. Potions runs your migrations automatically on every deploy, so it's version-controlled and hands-off:

defmodule MyApp.Repo.Migrations.AddExtensions do
  use Ecto.Migration

  def change do
    execute "CREATE EXTENSION IF NOT EXISTS citext",
            "DROP EXTENSION IF EXISTS citext"
  end
end

Commit it, deploy, and the extension is enabled. CREATE EXTENSION IF NOT EXISTS is safe to run repeatedly - if it's already there, the migration is skipped with a notice instead of erroring.

Third-Party Extensions (the SSH path)

Extensions that aren't bundled with Postgres - for example pgvector, pgmq, or anything that runs a background worker - aren't among the trusted extensions above, and they need two things your app can't do on its own: their files have to be installed on the server first (which needs root), and many also require a superuser to run CREATE EXTENSION. Your app's database role is deliberately not a superuser, so the migration path won't cover these. (If a migration fails with ERROR: permission denied to create extension "..." and HINT: Must be superuser to create this extension., that's the tell.) Do it once over SSH instead.

First, make sure your SSH key is added and grab your server's IP and Deploy Password from the server's Overview tab. See SSH Access & Keys for the full walkthrough. Then:

ssh deploy@<your-server-ip>

# 1. Install the extension's package if it isn't already on the box.
#    This needs the Deploy Password (shown on the Overview tab):
sudo apt-get update && sudo apt-get install -y <package-for-the-extension>

# 2. Enable it in your app's database. Your database name is on the app's
#    Database tab (it looks like potions_<yourappname>). This step is passwordless:
sudo -u postgres psql -d potions_<yourappname> -c "CREATE EXTENSION <name>;"

Extensions That Need a Background Worker

Some extensions ship a background worker and must be added to PostgreSQL's shared_preload_libraries, which only takes effect after a restart - so set it before you create the extension:

# Check what's already preloaded - ALTER SYSTEM SET *replaces* this value, it doesn't append:
sudo -u postgres psql -tAc "SHOW shared_preload_libraries;"

# Include any libraries it already lists plus the new one (a fresh Potions server has none):
sudo -u postgres psql -c "ALTER SYSTEM SET shared_preload_libraries = '<lib>';"
sudo systemctl restart postgresql
sudo -u postgres psql -d potions_<yourappname> -c "CREATE EXTENSION <name>;"

The extension's own documentation will tell you whether it needs this step and what to put in <lib>.

Not sure which package or extension name you need? Check the extension's documentation, or reach out to support and we'll point you at the exact commands.