Managing extensions

Written by

Updated at May 13, 2025

Enabling libraries for extensions
Retrieving a list of installed extensions
Editing a list of installed extensions
Supported PostgreSQL extensions and utilities

Managed Service for PostgreSQL supports many standard and some non-standard PostgreSQL extensions. A complete list of available extensions and versions based on the PostgreSQL version is provided below.

Warning

In Managed Service for PostgreSQL clusters, you cannot manage PostgreSQL extensions using SQL commands.

Enabling libraries for extensions

For some extensions, you need to enable shared libraries. To link a library when creating or editing a cluster, specify its name in the Shared preload libraries parameter.

You can install the following libraries in Managed Service for PostgreSQL:

auto_explain: Required for the auto_explain extension to function.
pgaudit: Required for the pgaudit extension to function.
pg_cron: Required for the pg_cron extension to function.
pg_hint_plan: Required for the pg_hint_plan extension to function.
pg_qualstats: Required for the pg_qualstats extension to function.
pg_stat_query_plans: Required for the pg_stat_query_plans extension to function.
timescaledb: Required to use the TimescaleDB extension.
anon: Required to use the postgresql_anonymizer extension.

Warning

Enabling a shared library will cause PostgreSQL to restart on the master host.

Retrieving a list of installed extensions

Management console

CLI

REST API

gRPC API

Navigate to the folder dashboard and select Managed Service for PostgreSQL.
Click the cluster name and select the Databases section.
In the PostgreSQL extensions column, you will see a list of extensions enabled for each database.

If you do not have the Yandex Cloud (CLI) command line interface yet, install and initialize it.

The folder specified when creating the CLI profile is used by default. To change the default folder, use the yc config set folder-id <folder_ID> command. You can specify a different folder using the --folder-name or --folder-id parameter.

To get a list of extensions for a database, run the command:

yc managed-postgresql database get <DB_name> \
   --cluster-name <cluster_name>

The extensions list will show the enabled extensions.

Get an IAM token for API authentication and put it into the environment variable:
```
export IAM_TOKEN="<IAM_token>"
```
Use the Database.Get method and send the following request, e.g., via cURL:
```
curl \
  --request GET \
  --header "Authorization: Bearer $IAM_TOKEN" \
  --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<cluster_ID>/databases/<DB_name>'
```
You can request the cluster ID with the list of clusters in the folder, and the DB name, with the list of DBs in the cluster.
View the server response to make sure the request was successful.

You can see the list of installed extensions in the extensions parameter of the command output.

Get an IAM token for API authentication and put it into the environment variable:
```
export IAM_TOKEN="<IAM_token>"
```
Clone the cloudapi repository:
```
cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
```
Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

Use the DatabaseService.Get call and send the following request, e.g., via gRPCurl:

grpcurl \
  -format json \
  -import-path ~/cloudapi/ \
  -import-path ~/cloudapi/third_party/googleapis/ \
  -proto ~/cloudapi/yandex/cloud/mdb/postgresql/v1/database_service.proto \
  -rpc-header "Authorization: Bearer $IAM_TOKEN" \
  -d '{
        "cluster_id": "<cluster_ID>",
        "database_name": "<DB_name>"
      }' \
  mdb.api.cloud.yandex.net:443 \
  yandex.cloud.mdb.postgresql.v1.DatabaseService.Get

You can request the cluster ID with the list of clusters in the folder, and the DB name, with the list of DBs in the cluster.

View the server response to make sure the request was successful.

You can see the list of installed extensions in the extensions parameter of the command output.

Editing a list of installed extensions

Management console

CLI

Terraform

REST API

gRPC API

Navigate to the folder dashboard and select Managed Service for PostgreSQL.
Click the cluster name and open the Databases tab.
In the row with the DB you need, click and select Manage.
Select the extensions you need and click Configure.

If you do not have the Yandex Cloud (CLI) command line interface yet, install and initialize it.

To update extensions for a database, provide their list in the --extensions argument of the CLI command. The extensions that are not on the list will be disabled.

yc managed-postgresql database update <DB_name> \
   --cluster-name <cluster_name>
   --extensions <extension_name>=<version>,<extension_name>=<version>...

Note

The extension version is not considered when handling the command: you can pass any non-empty string as a version.

Open the current Terraform configuration file that defines your infrastructure.

For more information about creating this file, see Creating clusters.

For a complete list of editable Managed Service for PostgreSQL cluster database configuration fields, see the Terraform provider documentation.

Add one or more extension sections (one section for each extension) to the description of the appropriate cluster database:

resource "yandex_mdb_postgresql_database" "<DB_name>" {
  ...
  extension {
    name    = "<extension_name>"
    version = "<extension_version>"
  }
  ...
}

Make sure the settings are correct.
1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.
2. Run this command:
```
terraform validate
```
  Terraform will show any errors found in your configuration files.
Confirm updating the resources.
1. Run this command to view the planned changes:
```
terraform plan
```
  If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.
2. If everything looks correct, apply the changes:
  1. Run this command:
```
terraform apply
```
  2. Confirm updating the resources.
  3. Wait for the operation to complete.

Get an IAM token for API authentication and put it into the environment variable:
```
export IAM_TOKEN="<IAM_token>"
```
Use the Database.Update method and send the following request, e.g., via cURL:

Warning

The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the updateMask parameter as a single comma-separated string.
```
curl \
  --request PATCH \
  --header "Authorization: Bearer $IAM_TOKEN" \
  --header "Content-Type: application/json" \
  --url 'https://mdb.api.cloud.yandex.net/managed-postgresql/v1/clusters/<cluster_ID>/databases/<DB_name>' \
  --data '{
            "updateMask": "extensions",
            "extensions": [
              {
                "name": "<extension_name>",
                "version": "<extension_version>"
              },
              { <similar_configuration_for_extension_2> },
              { ... },
              { <similar_configuration_for_extension_N> }
            ]
          }'
```
Where:
- updateMask: List of parameters to update as a single string, separated by commas.
  
  In this case, only one parameter is provided.
- extensions: Array of DB extensions. Each object represents one extension and has the following structure:
  - name: Extension name.
  - version: Extension version.
  Specify the name and version from the list of supported PostgreSQL extensions and utilities.
You can request the cluster ID with the list of clusters in the folder, and the DB name, with the list of DBs in the cluster.
View the server response to make sure the request was successful.

Get an IAM token for API authentication and put it into the environment variable:
```
export IAM_TOKEN="<IAM_token>"
```
Clone the cloudapi repository:
```
cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
```
Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

Use the DatabaseService.Update call and send the following request, e.g., via gRPCurl:

Warning

The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the update_mask parameter as an array of paths[] strings.

Format for listing settings

"update_mask": {
    "paths": [
        "<setting_1>",
        "<setting_2>",
        ...
        "<setting_N>"
    ]
}

grpcurl \
  -format json \
  -import-path ~/cloudapi/ \
  -import-path ~/cloudapi/third_party/googleapis/ \
  -proto ~/cloudapi/yandex/cloud/mdb/postgresql/v1/database_service.proto \
  -rpc-header "Authorization: Bearer $IAM_TOKEN" \
  -d '{
        "cluster_id": "<cluster_ID>",
        "database_name": "<DB_name>",
        "update_mask": {
          "paths": [
            "extensions"
          ]
        },
        "extensions": [
          {
            "name": "<extension_name>",
            "version": "<extension_version>"
          },
          { <similar_configuration_for_extension_2> },
          { ... },
          { <similar_configuration_for_extension_N> }
        ]
      }' \
  mdb.api.cloud.yandex.net:443 \
  yandex.cloud.mdb.postgresql.v1.DatabaseService.Update

Where:

update_mask: List of parameters to update as an array of paths[] strings.

Only one parameter is provided in this case.
extensions: Array of DB extensions. One array element contains settings for a single extension and has the following structure:
- name: Extension name.
- version: Extension version.
Specify the name and version from the list of supported PostgreSQL extensions and utilities.

You can request the cluster ID with the list of clusters in the folder, and the DB name, with the list of DBs in the cluster.

View the server response to make sure the request was successful.

Supported PostgreSQL extensions and utilities

Extension \ PostgreSQL version	11	12	13	14	15	16	17
address_standardizer Provides normalization functions for postal addresses provided as a string.	2.5.2	3.0.0	3.1.4	3.1.4	3.3.2	3.4.0	3.4.3
address_standardizer_data_us Contains normalization rules for the US and Canadian mailing addresses for the `address_standardizer` extension.	2.5.2	3.0.0	3.1.4	3.1.4	3.3.2	3.4.0	3.4.3
amcheck Provides functions that allow you to verify the logical consistency of the relation structure. To use this extension, you need the `mdb_admin` role or the `mdb_superuser` role.	1.1	1.2	1.2	1.3	1.3	1.3	1.4
autoinc Contains the `autoinc()` function. It stores the next value of a sequence into an integer field. Unlike the built-in types that control sequences, `autoinc()`: Overrides attempts to insert a different field value from a query. Allows modifying field values during updates.	1.0	1.0	1.0	1.0	1.0	1.0	1.0
bloom Enables access to database indexes based on Bloom filters. These probabilistic data structures require much less memory than hash tables; however, false positives are possible.	1.0	1.0	1.0	1.0	1.0	1.0	1.0
btree_gin Contains examples of GIN (Generalized Inverted Index) operator classes used for inverted searches.	1.3	1.3	1.3	1.3	1.3	1.3	1.3
btree_gist Contains GiST (Generalized Search Tree) operator classes. Unlike a B-tree index, GiST supports such operators as `<>` (not equal to) and `<->` (distance). However, it does not ensure the uniqueness of values it returns.	1.5	1.5	1.5	1.6	1.7	1.7	1.7
citext Contains the `citext` data type that allows using case-insensitive strings.	1.5	1.6	1.6	1.6	1.6	1.6	1.6
clickhouse_fdw Adds support for Foreign Data Wrapper for accessing ClickHouse® database, supports `SELECT FROM` and `INSERT INTO` transactions. The extension is not supported by PostgreSQL versions 16 or higher. Use Yandex Data Transfer to upload data to a ClickHouse® DB.	1.3	1.3	1.3	-	1.4	-	-
cube Contains the `cube` data type used for representing multidimensional cubes.	1.4	1.4	1.4	1.5	1.5	1.5	1.5
dblink Allows connecting to other PostgreSQL databases from the current session. To use this extension, you need the `mdb_admin` role or the [`mdb_superuser` role]../../concepts/roles.md#mdb-superuser).	1.2	1.2	1.2	1.2	1.2	1.2	1.2
dict_int Contains an example of an add-on dictionary template for full-text search. It allows preventing excessive growth of a list of unique words and speeding up search. In addition to extensions, you can also use Hunspell dictionaries for full-text search.	1.0	1.0	1.0	1.0	1.0	1.0	1.0
dict_xsyn (Extended Synonym Dictionary) contains an example of an add-on dictionary template for full-text search. It allows searching for a word using any of its synonyms. In addition to extensions, you can also use Hunspell dictionaries for full-text search.	1.0	1.0	1.0	1.0	1.0	1.0	-
earthdistance Provides a module for calculating distances between geographical points. There are two ways to calculate them: Using the `cube` data type (you need to enable the `cube` extension). Using the built-in `point` data type.	1.1	1.1	1.1	1.1	1.1	1.1	1.1
fuzzystrmatch Provides functions to determine similarities and distance between strings.	1.1	1.1	1.1	1.1	1.1	1.2	1.2
hstore Contains the `hstore` data type for storing and efficient use of key:value pairs within a single table field.	1.5	1.6	1.7	1.8	1.8	1.8	1.8
hypopg Contains virtual indexes. These allow you to check using minimum resources whether PostgreSQL will use real indexes for problematic queries.	1.3.1	1.3.1	1.3.1	1.3.1	1.3.1	1.4.0	1.4.1
intarray Contains functions and operators for working with arrays of integers that do not contain any `NULL` values.	1.2	1.2	1.3	1.5	1.5	1.5	1.5
isn Provides data types for international product numbering standards such as EAN13, UPC, ISBN, ISMN, and ISSN. Numbers are validated on input according to a hard-coded list of prefixes.	1.2	1.2	1.2	1.2	1.2	1.2	1.2
jsquery Adds support for the JsQuery language to work with the `jsonb` data type. JsQuery provides a simple and effective way to search in nested objects and more comparison operators with index support.	1.1	1.1	1.1	1.1	1.1	1.1	1.1
lo Contains the `lo` data type and `lo_manage()` function. They are used for managing Binary Large Objects (BLOBs) based on the JDBC and ODBC driver specifications (standard PostgreSQL behavior does not conform to them).	1.1	1.1	1.1	1.1	1.1	1.1	1.1
logerrors Enables you to collect statistics about log messages.	-	-	-	-	-	-	2.0
ltree Contains the `ltree` data type to represent labels of data stored in a hierarchical tree-like structure.	1.1	1.1	1.2	1.2	1.2	1.2	1.3
lwaldump Enables you to retrieve the last log sequence number (LSN) written to the replica disk.	-	-	-	-	-	-	1.0
moddatetime Contains the `moddatetime()` function that allows tracking the last modification time of a table row.	1.0	1.0	1.0	1.0	1.0	1.0	1.0
oracle_fdw Adds support for Foreign Data Wrapper for Oracle database access. To use this extension, you need the `mdb_admin` role or the `mdb_superuser` role.	1.2	1.2	1.2	1.2	1.2	1.2	1.2
orafce Adds functions and operators that emulate Oracle functionality and packages.	3.18	3.18	3.18	3.18	3.25	4.6	4.13
pg_buffercache Provides functions for monitoring the shared buffer cache. To use this extension, you need the `mdb_admin` role or the `mdb_superuser` role.	1.3	1.3	1.3	1.3	1.3	1.4	1.5
pg_cron Enables you to add scheduled jobs to a database and execute SQL commands directly from a job. Requires enabling the `pg_cron` shared library. To use this extension, you need the `mdb_admin` role or the `mdb_superuser` role. Enabling the extension causes all hosts to restart. For more information, see Using pg_cron.	1.4.1	1.4.1	1.4.1	1.4.1	1.4.1	1.5	1.6
pg_hint_plan Provides functions for managing the PostgreSQL planner. Requires enabling the `pg_hint_plan` shared library.	1.3.4	1.3.5	1.3.7	1.4	-	1.6.0	1.7.0
pg_partman Extends support for table partitioning, including time-based and serial-based.	4.0.0	4.2.0	4.4.0	4.6.0	4.7.0	4.7.4	5.1.0
pg_qualstats Allows collecting statistics on predicates found in `WHERE` expressions and `JOIN` clauses. Requires enabling the `pg_qualstats` shared library.	2.0.3	2.0.3	2.0.3	2.0.3	2.0.4	2.1.0	2.1.1
pg_prewarm Allows loading relation data into either the operating system buffer cache or the PostgreSQL buffer cache.	1.2	1.2	1.2	1.2	1.2	1.2	1.2
pg_repack Contains functions to remove bloat from tables and indexes. Unlike `CLUSTER` and `VACUUM FULL`, it does not require exclusive locking of tables. To use this extension, you need the `mdb_admin` role or the `mdb_superuser` role.	1.4.6	1.4.6	1.4.6	1.4.7	1.4.8	1.4.8	1.4.8
pg_stat_kcache Allows gathering statistics about reads and writes performed at the file system level. Requires enabling the `pg_stat_statements` extension. To use this extension, you need the `mdb_admin` role or the `mdb_superuser` role.	2.1.1	2.1.1	2.1.3	2.2.0	2.2.1	2.2.1	2.3.0
pg_stat_query_plans Enables you to track SQL query execution statistics and plans. Requires enabling the `pg_stat_query_plans` shared library.	-	-	-	-	-	-	1.0
pg_stat_statements Tracks planning and execution statistics of all SQL queries run in a cluster. To use this extension, you need the `mdb_monitor` role.	1.6	1.7	1.8	1.9	1.10	1.10	1.11
pg_tm_aux Enables you to create a logical replication slot in the past.	1.0	1.0	1.0	1.0	1.1	1.1.1	1.1.1
pg_trgm Provides tools for fast searching for similar strings based on trigram matching.	1.4	1.4	1.5	1.6	1.6	1.6	1.6
pgaudit The extension provides additional logging tools and enhances the audit features. Requires enabling the `pgaudit` shared library. For more information, see Using pgaudit.	1.0.0	1.0.0	1.0.0	1.0.0	1.7	16.0	17.0
pgcrypto Provides cryptographic functions for PostgreSQL. For more information, see Using pgcrypto.	1.3	1.3	1.3	1.3	1.3	1.3	1.3
pglogical Adds support for logical streaming replication using the publish-subscribe mechanism.	2.4.1	2.4.1	2.4.1	2.4.1	-	2.4.3	2.4.5
pgrouting Extends the PostGIS database to provide geospatial routing functionality.	2.6.2	2.6.2	3.0.2	3.3.0	3.4.1	3.5.0	3.6.2
pgrowlocks Contains the `pgrowlocks()` function to return row locking information for the specified table.	1.2	1.2	1.2	1.2	1.2	1.2	1.2
pgstattuple Contains functions you need to get tuple-level statistics. To use this extension, you need the `mdb_admin` role or the `mdb_superuser` role.	1.5	1.5	1.5	1.5	1.5	1.5	1.5
pgvector Adds a vector similarity search.	0.2.5	0.2.5	0.2.5	0.2.5	0.3.2	0.5.1	0.5.1
plpgsql Adds support for the PL/pgSQL procedural language.	-	-	-	-	-	-	1.0
plv8 Adds support for plv8, a procedural language based on JavaScript and the V8 engine.	3.0.0	3.0.0	3.0.0	3.0.0	-	3.2.0	-
postgis Allows GIS (Geographic Information Systems) objects to be stored and processed in PostgreSQL databases.	2.5.2	3.0.0	3.1.4	3.1.4	3.3.2	3.4.0	3.4.3
postgis_tiger_geocoder Provides functions for geocoding based on TIGER data.	2.5.2	3.0.0	3.1.4	3.1.4	3.3.2	3.4.0	3.4.3
postgis_topology Contains `postgis` extension data types and functions to manage topology objects.	2.5.2	3.0.0	3.1.4	3.1.4	3.3.2	3.4.0	3.4.3
postgres_fdw Adds support for Foreign Data Wrapper for access to external PostgreSQL servers. To use this extension, you need the `mdb_admin` role or the `mdb_superuser` role.	1.0	1.0	1.0	1.1	1.1	1.1	1.1
postgresql_anonymizer Masks or replaces data in a PostgreSQL database. Requires enabling the `anon` shared library. To use this extension, you need the `mdb_admin` role or the `mdb_superuser` role.	-	-	-	-	1.3.2	1.3.2	-
rum Provides an access method to work with `RUM` indexes.	1.3	1.3	1.3	1.3	1.3	1.3	1.3
seg Contains the `seg` data type to represent line segments or floating point intervals.	1.3	1.3	1.3	1.4	1.4	1.4	1.4
smlar Provides a set of functions for computing similarity of two arrays.	1.0	1.0	1.0	1.0	1.0	1.0	-
tablefunc Provides a set of functions that return tables, i.e., multiple rows.	1.0	1.0	1.0	1.0	1.0	1.0	1.0
timescaledb Supports automatic partitioning across time and space (partitioning key), yet retains the standard PostgreSQL interface for data. This makes PostgreSQL scalable for time-series. Requires enabling the `timescaledb` shared library. The TimescaleDB edition installed for the service is TimescaleDB Apache 2 Edition. It has limited functionality compared to TimescaleDB Community Edition. You cannot change the edition. For more information about the limitations, see the TimescaleDB documentation.	2.3.1	2.4.2	2.5.2	2.6.1	2.9.0	2.13.0-dev	2.16.1
unaccent Provides a text search dictionary that removes diacritic signs.	1.1	1.1	1.1	1.1	1.1	1.1	1.1
uuid-ossp Provides functions to generate universally unique identifiers (UUIDs) using standard algorithms.	1.1	1.1	1.1	1.1	1.1	1.1	1.1
xml2 Provides XPath querying and XSLT (Extensible Stylesheet Language Transformations) functionality.	1.1	1.1	1.1	1.1	1.1	1.1	1.1

pgcompacttable is designed to reorganize data in tables in order to revert back disk space without database performance impact.

Command syntax:

./bin/pgcompacttable \
  -h c-<cluster_ID>.rw.mdb.yandexcloud.net \
  -p 6432 \
  -U <username> \
  -W <password> \
  -d <DB_name> \
  -n <schema_name> \
  -t <table_name>

Requires enabling the pgstattuple extension.

To use it, you need the mdb_admin role or the mdb_superuser role.

ClickHouse® is a registered trademark of ClickHouse, Inc.

Managing extensions

Enabling libraries for extensions

Retrieving a list of installed extensions

Editing a list of installed extensions

Supported PostgreSQL extensions and utilities

Was the article helpful?