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 Supported PostgreSQL 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.
- Open the navigation menu and select Databases. Under PostgreSQL, select DB Systems.
- (Optional) In the List scope section, under Compartment, select a compartment.
- Select the database system for which you want to enable extensions.
- On the database system details page, select the value next to Configuration, then select the configuration name.
- On the configuration details page, select Copy configuration and extensions.
- In the Copy configuration panel, in the Configure extensions section, select an extension or extensions from the PostgreSQL extensions list.
- Select Create.
Use the
oci psql configuration createcommand and required parameters with existing information fromoci psql default-configuration getto 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-overridesalong with a key ofoci.allowed_extensionsto 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
dbConfigurationOverridesalong with aconfigKeyofoci.allowed_extensionsto 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:
-
Verify the availability of the extension on the database. For example:
SELECT * FROM pg_available_extensions WHERE name = '<extension_name>'; -
After the availability of the extension is verified, enable it for the selected database:
CREATE EXTENSION <extension_name>; -
Verify that the extension was enabled:
SELECT * FROM pg_extension WHERE extname = '<extension_name>';