Oracle Cloud Infrastructure Documentation

Enabling Extensions for a Database

Some extensions for an OCI Database with PostgreSQL database system must be enabled by administrators through a configuration before they can be made available at the database level.

For a list of extensions that must be enabled through a configuration, see Administrator-enabled Extensions. Default extensions only need to be enabled at the database level.

To enable extensions for an existing database system by copying its configuration, see the following steps. If you already have a configuration with extensions enabled, you can change the database system's configuration without creating a new one.

    1. Open the navigation menu and select Databases. Under PostgreSQL, select DB Systems.
    2. (Optional) In the List scope section, under Compartment, select a compartment.
    3. Select the database system for which you want to enable extensions.
    4. On the database system details page, select the value next to Configuration, then select the configuration name.
    5. On the configuration's details page, select Copy configuration and extensions.
    6. In the Copy configuration and extensions panel, in the Configure extensions section, provide the following information:
      • PostgreSQL extensions: Select an extension or extensions from the list.
      • Select + Another variable if an extension supports configurable variables.
      • Variable name: Select a variable from the list.
      • Variable value: Select or enter a value for the variable.
    7. Select Create.

  • Use the oci psql configuration create command and required parameters with existing information from oci psql default-configuration get to copy a default configuration:

    oci psql default-configuration get --configuration-id <configuration_OCID> [OPTIONS]
    oci psql configuration create --compartment-id <compartment_OCID> [OPTIONS]

    Use --db-configuration-overrides along with a key of oci.allowed_extensions to enable extensions for the configuration. For more information, see Supported PostgreSQL Extensions for OCI Database with PostgreSQL.

    For a complete list of parameters and values for CLI commands, see the CLI Command Reference.

  • Run the CreateConfiguration operation with existing information to copy a configuration.

    Use dbConfigurationOverrides along with a configKey of oci.allowed_extensions to enable extensions. For more information, see Supported PostgreSQL Extensions for OCI Database with PostgreSQL.

    For information about using the API and signing requests, see REST API documentation and Security Credentials. For information about SDKs, see SDKs and the CLI.

Next Steps

After you enable the extensions in the configuration, you can apply the configuration to the database system and then enable the extension at the database level using the following commands.

Client Commands to Enable Extensions

To enable default extensions, or extensions enabled by administrators as shown in the preceding steps, connect to the database and use a PostrgreSQL client to provide the following commands:

  1. Verify the availability of the extension on the database. For example:

    SELECT * FROM pg_available_extensions WHERE name = '<extension_name>';

  2. After the availability of the extension is verified, enable it for the selected database:

    CREATE EXTENSION <extension_name>;

  3. Verify that the extension was enabled:

    SELECT * FROM pg_extension WHERE extname = '<extension_name>';