Configure database extensions

This page describes how to enable and disable PostgreSQL extensions in AlloyDB.

See Supported database extensions for the list of the extensions that AlloyDB supports.

Enable an extension

In general, you enable an extension by connecting to a database in an AlloyDB cluster's primary instance and entering a LOAD or CREATE EXTENSION command.

However, some extensions require you to set one or more database flags before you can use the LOAD or CREATE EXTENSION command to enable the extension. You set these flags on the AlloyDB instance where you want to use the extension, whether that instance is the primary instance or read pool instance.

Procedure

If required, set the appropriate database flags on the instance where you want to use the extension, either the primary instance or a read pool instance.

The list of supported extensions shows which extensions require this step and states which database flags you must set.
Connect a psql client to the cluster's primary instance, as described in Connect a psql client to an instance.

Note: You must connect to the primary instance even if you are enabling the extension on a read pool instance.
At the psql command prompt, connect to the database and create the extension:
```
\c DB_NAME
CREATE EXTENSION IF NOT EXISTS EXTENSION_NAME;
```
Note: To create the auto_explain extension, use the LOAD command.
If you want, repeat the previous step to connect to other databases and create the extension in each of them.

Update an extension

Although AlloyDB maintenance updates automatically install the latest extension binaries on your instance's underlying software, they don't automatically update the extension's database catalog. You are responsible for manually updating your extensions to make sure they use the latest supported versions.

To determine which extensions require an update, compare your installed version with the latest supported version listed in the Supported extensions reference table.

Connect to your database and run the following command to see your installed versions:

SELECT * FROM pg_extension;

To update an extension to the latest version available on your instance, use the ALTER EXTENSION command.

Connect to the cluster's primary instance using a client of your choice, such as AlloyDB Studio or a psql client, and run the following command for each extension that you want to update:

ALTER EXTENSION EXTENSION_NAME UPDATE;

If you need to update to a specific version, use the following syntax:

ALTER EXTENSION EXTENSION_NAME UPDATE TO 'VERSION_NUMBER';

Disable an extension

You disable an extension by reversing the procedure used to enable it.

Connect a psql client to the cluster's primary instance, as described in Connect a psql client to an instance.

Note: You must connect to the primary instance even if you are disabling an extension that is only being used on read pool instances.
At the psql command prompt, connect to the database that has the extension enabled and drop the extension:
```
\c DB_NAME
DROP EXTENSION EXTENSION_NAME;
```
Repeat the previous step to connect to any other databases that have the extension enabled and drop the extension in each of them.
If the extension required that database flags be set on the instances where it was used, remove the flags from each of the instances where it was used by following the instructions in Configure an instance's database flags.

The list of supported extensions shows which extensions require setting of database flags and identifies which flags.