Oracle Cloud Infrastructure Documentation

Enabling Extensions for an OCI Database with PostgreSQL Database System

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 Supported Extensions for OCI Database with PostgreSQL.

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. On the Database systems list page, select the database system that you want to work with. If you need help finding the list page or the database system, see Listing OCI Database with PostgreSQL Database Systems.
      The database system's details page opens.
    2. Select the value next to Configuration, then select the configuration name.
      The configuration's details page opens.
    3. Select Copy configuration and extensions.
      The Copy configuration panel opens.
    4. In the Configure extensions section, select an extension or extensions from the PostgreSQL extensions list.
    5. 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 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 Extensions for OCI Database with PostgreSQL.

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>';