Manage PostgreSQL extensions

APPLIES TO: Azure Database for PostgreSQL - Flexible Server

Azure Database for PostgreSQL flexible server allows you to extend the functionality of your database using extensions. Extensions bundle multiple related SQL objects in a single package that can be loaded or removed from your database with a command. After being loaded into the database, extensions function like built-in features.

Allow extensions

Before installing extensions in Azure Database for PostgreSQL flexible server, you must allow these extensions to be listed for use.

Portal

Using the Azure portal:

1. Select your Azure Database for PostgreSQL flexible server instance.

2. From the resource menu, under Settings section, select Server parameters.

3. Select the extensions that you want to allowlist, from the ones available in the azure.extensions parameter, and select Save.

   

Azure CLI

You can allow extensions via the CLI parameter set command.

    az postgres flexible-server parameter set --resource-group <resource_group>  --server-name <server> --subscription <subscription_id> --name azure.extensions --value <extension_name>,<extension_name>

Resource Manager Template

Using the ARM Template:

The following example adds extensions to the allowlist dblink, dict_xsyn, pg_buffercache on a server whose name is postgres-test-server:

{
    "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
        "flexibleServers_name": {
            "defaultValue": "postgres-test-server",
            "type": "String"
        },
        "azure_extensions_set_value": {
            "defaultValue": " dblink,dict_xsyn,pg_buffercache",
            "type": "String"
        }
    },
    "variables": {},
    "resources": [
        {
            "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
            "apiVersion": "2021-06-01",
            "name": "[concat(parameters('flexibleServers_name'), '/azure.extensions')]",
            "properties": {
                "value": "[parameters('azure_extensions_set_value')]",
                "source": "user-override"
            }
        }
    ]
}

Load libraries

shared_preload_libraries is a server configuration parameter that determines which libraries have to be loaded when Azure Database for PostgreSQL flexible server starts. Any libraries that use shared memory must be loaded via this parameter. If your extension needs to be added to the shared preload libraries, follow these steps:

Portal

Using the Azure portal:

1. Select your Azure Database for PostgreSQL flexible server instance.

2. From the resource menu, under Settings section, select Server parameters.

3. Include the libraries you wish to add in the value of shared_preload_libraries, and select Save.

   

4. Because shared_preload_librariesis a static server parameter, it requires a server restart so that the changes take effect.

   

Azure CLI

You can set shared_preload_libraries using the CLI parameter set command.

az postgres flexible-server parameter set --resource-group <resource_group> --server-name <server> --name shared_preload_libraries --value <extension_name>,<extension_name>

And can restart the server using the CLI parameter set command.

az postgres flexible-server restart --resource-group <resource_group> --name <server>

Create extensions

1. Allowlist the extension.

2. If the extension requires it, also add it to shared_load_libraries.

3. The user that creates the extensions must be a member of the azure_pg_admin role.

4. Run the CREATE EXTENSION command to create or install a particular extension. This command loads the packaged objects into your database.

CREATE EXTENSION <extension>;

1. Some extensions require other extensions to be created first, because they depend on objects distributed by those other extensions. It's the case, for example, of the pg_diskann extension, which has dependencies on the vector extension. To install such extensions, you can proceed in two ways:

   • Allowlist and run CREATE EXTENSION on the depending extension first. Then, allowlist and run CREATE EXTENSION on the dependent extension.

   CREATE EXTENSION <depending_extension>;
   CREATE EXTENSION <dependent_extension>;
   

   • Allowlist and run CREATE EXTENSION on the dependent extension only, but add the CASCADE clause, so that it automatically creates all extensions on which it depends.

   CREATE EXTENSION <dependent_extension> CASCADE;
   

Note

Third-party extensions offered in Azure Database for PostgreSQL flexible server are open-source licensed code. We don't offer any third-party extensions or extension versions with premium or proprietary licensing models.

Azure Database for PostgreSQL flexible server instance supports a subset of key PostgreSQL extensions, as listed in supported extensions by name or in supported extensions by version of PostgreSQL. This information is also available by running SHOW azure.extensions;. Extensions not included in those lists aren't supported on Azure Database for PostgreSQL flexible server. You can't create or load your own extensions in Azure Database for PostgreSQL flexible server.

Drop extensions

1. Allowlist the extension.

2. The user that drops the extensions must be a member of the azure_pg_admin role.

3. Run the DROP EXTENSION command to drop or uninstall a particular extension. This command drops the objects packaged in the extension from your database.

DROP EXTENSION <extension>;

1. Some extensions might distribute objects which are required by other extension. That's the case, for example, of the vector extension, in which the pg_diskann extension depends. To drop such extensions, you can proceed in two ways:

   • Allowlist and run DROP EXTENSION on all the extensions that depend on the one that you're trying to drop first. Then, allowlist and run DROP EXTENSION on the extension on which other extensions depended.

   DROP EXTENSION <dependent_extension>;
   DROP EXTENSION <depending_extension>;
   

   • Allowlist and run DROP EXTENSION on the extension that you want to drop, that other extensions depend on, but add the CASCADE clause, so that it automatically drops all extensions on which it depends.

   DROP EXTENSION <depending_extension> CASCADE;
   

Update extensions

To update an installed extension to the latest available version supported by Azure, use the following SQL command:

ALTER EXTENSION <extension> UPDATE;

This command simplifies the management of database extensions by allowing users to manually upgrade to the latest version approved by Azure, enhancing both compatibility and security.

Limitations

While updating extensions is straightforward, there are certain limitations:

• Selection of a specific version: The command doesn't support updating to intermediate versions of an extension.

  • It constantly updates the latest available version.

• Downgrading: Doesn't support downgrading an extension to a previous version. If a downgrade is necessary, it might require support assistance and depends on the availability of the previous version.

View installed extensions

To list the extensions currently installed on your database, use the following SQL command:

SELECT * FROM pg_extension;

Possible errors

Extension "%s" is not allow-listed for "azure_pg_admin" users in Azure Database for PostgreSQL

This error occurs when you run a CREATE EXTENSION or DROP EXTENSION command referring to an extension that isn't allowlisted, or an extension that isn't supported yet on the instance of Azure Database for flexible server on which you're running the command.

Only members of "azure_pg_admin" are allowed to use CREATE EXTENSION

This error occurs when the user that runs a CREATE EXTENSION command isn't a member of azure_pg_admin role.

Only members of "azure_pg_admin" are allowed to use DROP EXTENSION

This error occurs when the user that runs a DROP EXTENSION command isn't a member of azure_pg_admin role.

Related content

• How to use extensions.
• Special considerations with extensions.
• List of extensions by name.