Installing Extension In Aurora Postgres Serverless Cluster

In this post, we’ll see how we can install an extension in Aurora Postgres Serverless Cluster. We’ll be installing btree_gin and pg_trgm extensions.

Step 1: Connect to your database and execute following query to see a list of installed extensions.

SELECT * FROM pg_extension;

Step 2: Now execute following query to install extensions. Make sure you specify correct schema name. If you don’t specify schema name, extension will be installed to public schema.

CREATE EXTENSION btree_gin SCHEMA cms; 
CREATE EXTENSION pg_trgm SCHEMA cms;

You can verify the extension details by running following query:

SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Description" 
FROM pg_catalog.pg_extension e 
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace 
LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass 
ORDER BY 1;

Note: If you rename schema, schema name for extension will also change. You can delete and reinstall the extension with following command:

DROP EXTENSION IF EXISTS btree_gin;
DROP EXTENSION IF EXISTS pg_trgm; 

CREATE EXTENSION IF NOT EXISTS btree_gin SCHEMA cms;
CREATE EXTENSION IF NOT EXISTS  pg_trgm SCHEMA cms;