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.

Prerequisites

  • The Cloud project you are using must have been enabled to access AlloyDB.
  • You must have one of these IAM roles in the 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 CREATE EXTENSION command.

However, some extensions require you to set one or more database flags before you can use the 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.

    Console

    1. In the Google Cloud console, go to the Clusters page.

      Go to Clusters

    2. Click a cluster in the Resource Name column.
    3. In the Overview page, go to Instances in your cluster, select the primary instance or read pool instance where you want to use the extension, and then click Edit.
    4. Repeat these steps for each flag your are required to add:
      1. Click Add flag.
      2. Select the flag from the New database flag list.
      3. Provide the value for the flag.
    5. After you've added all required flags, click Done.
    6. Click Update instance.

    gcloud

    To use the gcloud CLI, you can install and initialize the Google Cloud CLI (version 383.0.1 or later), or you can use Cloud Shell.

    Use the instances update command with the --database-flags option to set the required database flags.

    gcloud beta alloydb instances update INSTANCE_ID \
        --database-flags FLAGS_LIST \
        --region=REGION_ID \
        --cluster=CLUSTER_ID \
        --project=PROJECT_ID
    • INSTANCE_ID: The ID of the primary instance or read pool instance where you want to use the extension.
    • FLAGS_LIST: A comma-separated list of the database flags you are required to set plus the database flags that are already set. The specification of each flag consists of the name of the flag, an equals sign (=), and the value to assign to the flag. For database flags that do not take values, provide the name of the flag followed by an equals sign (=).
    • REGION_ID: The region where the instance is placed.
    • CLUSTER_ID: The ID of the cluster where the instance is placed.
    • PROJECT_ID: The ID of the project where the cluster is placed.
  2. Connect a psql client to the cluster's primary instance, as described in Connect a psql client to an 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;
    
  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.

  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.