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.

Before you begin

• The Google Cloud project you are using must have been enabled to access AlloyDB.
• You must have one of these IAM roles in the Google Cloud project you are using:
  • roles/alloydb.admin (the AlloyDB Admin predefined IAM role)
  • roles/owner (the Owner basic IAM role)
  • roles/editor (the Editor basic IAM role)

  If you don't have any of these roles, contact your Organization Administrator to request access.

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

1. 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.

2. 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.
3. 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.
4. If desired, repeat the previous step to connect to other databases and create the extension in each of them.

Disable an extension

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

1. 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.
2. 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;
   

3. Repeat the previous step to connect to any other databases that have the extension enabled and drop the extension in each of them.

4. 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.