Installing and Uninstalling an Extension Using SQL Commands

RDS provides the PostgreSQL extension management solution for user root. Except the following extensions, you need to manually create other extensions by referring to this section.

auto_explain
passwordcheck
pg_profile_pro
pg_sql_history
plpgsql
wal2json
test_decoding

RDS for PostgreSQL extensions only take effect on the databases you created the extensions for. To use an extension on databases, it has to be created separately for each database.

The latest minor versions of RDS for PostgreSQL 11 and later versions allow user root to create extensions (create extension) or delete extensions (drop extension).

Creating an Extension

Connect to the database where an extension needs to be created as user root and run the following SQL statements:

select control_extension('create','<EXTENSION_NAME>', '<SCHEMA>');

EXTENSION_NAME indicates the extension name. For more information, see Supported Extensions.
SCHEMA indicates the name of the schema where the extension is created. If this parameter is not specified, the public schema is used by default.

Example:

Create postgis in the public schema.

-- Specify the public schema for creating the extension.
select control_extension('create','postgis', 'public');
       control_extension       
 ------------------------------
  create postgis successfully.
 (1 row)
-- If the schema parameter is not specified, the default schema is public.
select control_extension('create', 'postgis');
      control_extension       
------------------------------
 create postgis successfully.
(1 row)

Deleting an Extension

Connect to the database where an extension needs to be created as user root and run the following SQL statements:

select control_extension('drop','<EXTENSION_NAME>', '<SCHEMA>');

EXTENSION_NAME indicates the extension name. For more information, see Supported Extensions.
SCHEMA indicates the schema name. This parameter does not matter much when you delete an extension, so you do not need to specify this parameter.

Example:

select control_extension('drop','postgis');
      control_extension      
 ----------------------------
  drop postgis successfully.
 (1 row)

Common Errors

Error 1
```
ERROR:  permission denied for function control_extension
```
Solution: Use root to run the control_extension function.
Error 2
```
ERROR:  function control_extension(unknown, unknown) is not unique
```
Solution: Add the schema parameter in the function. If the schema is not specified, there may be functions with the same name, causing execution failures.
Error 3
```
ERROR:  function control_extension(unknown, unknown) does not exist
```
Solution: Do not create extensions in the postgres database. The control_extension function does not exist in the postgres database because this database is used as an O&M database.