Contact UsGet started

Managing extensions

Written by
Updated at October 28, 2024

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:

Warning

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

Retrieving a list of installed extensions

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

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

The folder specified in the CLI profile is used by default. 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.

  1. Get an IAM token for API authentication and place it in the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Use the Database.get method and make a 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 get the cluster ID with a list of clusters in your folder, and the DB name with the list of databases in your cluster.

  3. 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.

  1. Get an IAM token for API authentication and place it in the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. 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.

  3. Use the DatabaseService/Get call and make a 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 get the cluster ID with a list of clusters in your folder, and the DB name with the list of databases in your cluster.

  4. 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

  1. Go to the folder page and select Managed Service for PostgreSQL.
  2. Click the cluster name and open the Databases tab.
  3. In the row with the DB you need, click and select Manage.
  4. Select the extensions you need and click Configure.

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

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

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.

  1. Open the current Terraform configuration file with an infrastructure plan.

    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.

  2. 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>"
  }
  ...
}

  3. Make sure the settings are correct.

    1. Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.

    2. Run the command:

      terraform validate

      If there are errors in the configuration files, Terraform will point to them.

  4. Confirm updating the resources.

    1. Run the command to view planned changes:

      terraform plan

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

    2. If you are happy with the planned changes, apply them:

      1. Run the command:

        terraform apply

      2. Confirm the update of resources.

      3. Wait for the operation to complete.

  1. Get an IAM token for API authentication and place it in the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Use the Database.update method and make a request, e.g., via cURL:

    Warning

    This API method overrides all parameters of the object being modified that were not explicitly passed in the request to the default values. To avoid this, list the settings you want to change in the updateMask parameter (one line separated by commas).

    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 get the cluster ID with the list of clusters in your folder, and the DB name with the list of databases in your cluster.

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

  1. Get an IAM token for API authentication and place it in the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. 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.

  3. Use the DatabaseService/Update call and make a request, e.g., via gRPCurl:

    Warning

    This API method will assign default values to all object parameters not explicitly set in the 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.

      In this case, only one parameter is provided.

    • 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 get the cluster ID with the list of clusters in your folder, and the DB name with the list of databases in your cluster.

  4. 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

address_standardizer

Contains functions for normalizing mailing addresses provided as a string.

2.5.2

3.0.0

3.1.4

3.1.4

3.3.2

3.4.0

address_standardizer_data_us

Contains rules for normalizing 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

amcheck

Contains functions for checking the logical consistency of the database relational structure.

To use this extension, you need the mdb_admin role.

1.1

1.2

1.2

1.3

1.3

1.3

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 when updating a record.

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 significantly less storage space than hash tables. However, false positives are possible.

1.0

1.0

1.0

1.0

1.0

1.0

btree_gin

Contains example classes of the GIN (Generalized Inverted Index) operator used for inverted searches.

1.3

1.3

1.3

1.3

1.3

1.3

btree_gist

Contains GiST (Generalized Search Tree) operator classes. Unlike B-tree indexes, GiST supports such operators as <> (not equal to) and <-> (distance); however, the values it returns may not be unique.

1.5

1.5

1.5

1.6

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

clickhouse_fdw

Adds Foreign Data Wrapper support for ClickHouse® database access. Supports the SELECT FROM and INSERT INTO operations.

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

dblink

Allows connecting to other PostgreSQL databases from the current session.

To use this extension, you need the mdb_admin role.

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. This template allows you to contain the growth of the unique words list and makes search faster.

Aside from the extension, you can also use Hunspell dictionaries for full-text search.

1.0

1.0

1.0

1.0

1.0

1.0

dict_xsyn

Contains an example of an add-on dictionary (Extended Synonym Dictionary) template for full-text search: when you search for a word, the results will also return all its synonyms.

Aside from the extension, 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

fuzzystrmatch

Contains functions used to identify similarities and differences between strings.

1.1

1.1

1.1

1.1

1.1

1.2

hstore

Contains the hstore type that allows you to store key:value pairs in a single table field and manage them efficiently.>

1.5

1.6

1.7

1.8

1.8

1.8

hypopg

Adds support for virtual, or hypothetical, indexes. They allow you to check if PostgreSQL will use real indexes to handle problematic queries with minimal resources required.>

1.3.1

1.3.1

1.3.1

1.3.1

1.3.1

1.4.0

intarray

Contains functions and operators for manipulating arrays of integers that do not contain any NULL values.

1.2

1.2

1.3

1.5

1.5

1.5

isn

Provides data types for international product numbering standards such as EAN13, UPC, ISBN, ISMN, and ISSN. The numbers are checked and generated according to the specified list of prefixes.

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 simplifies search in nested objects and offers additional comparison operators with index support.

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 to manage binary large objects (BLOBs) based on the JDBC and ODBC driver specifications, which the standard PostgreSQL behavior does not comply with.

1.1

1.1

1.1

1.1

1.1

1.1

ltree

Contains the ltree data type for representing labels of data stored in a hierarchical tree-like structure.

1.1

1.1

1.2

1.2

1.2

1.2

moddatetime

Contains the moddatetime() function that allows tracking the last modification time of a particular row within a table.

1.0

1.0

1.0

1.0

1.0

1.0

oracle_fdw

Adds Foreign Data Wrapper support to access Oracle databases.

To use this extension, you need the mdb_admin role.

1.2

1.2

1.2

1.2

1.2

1.2

orafce

Adds functions and operators that emulate a set of Oracle DB functions and packages.

3.18

3.18

3.18

3.18

3.25

4.6

pg_buffercache

Provides functions for monitoring a shared buffer cache.

To use this extension, you need the mdb_admin role.

1.3

1.3

1.3

1.3

1.3

1.4

pg_cron

Enables you to add scheduled jobs to a database and run SQL queries directly from a job.

Requires enabling the pg_cron shared library.

To use this extension, you need the mdb_admin 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

pg_hint_plan

Contains 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

pg_partman

Provides extended table partitioning features, including time-based and serial-based partitioning.

4.0.0

4.2.0

4.4.0

4.6.0

4.7.0

4.7.4

pg_qualstats

Enables collecting statistics on predicates in WHERE statements 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

pg_prewarm

Enables 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

pg_repack

Contains functions to remove bloat from tables and indexes. Unlike CLUSTER and VACUUM FULL, it does not require holding an exclusive lock on the processed tables.

To use this extension, you need the mdb_admin role.

1.4.6

1.4.6

1.4.6

1.4.7

1.4.8

1.4.8

pg_stat_kcache

Enables collecting statistics on the read and write operations performed at the file system level.

Requires enabling the pg_stat_statements extension.

To use this extension, you need the mdb_admin role.

2.1.1

2.1.1

2.1.3

2.2.0

2.2.1

2.2.1

pg_stat_statements

Enables you to track planning and execution statistics of all SQL queries running 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

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

pg_trgm

Contains tools to quickly search for similar strings based on trigram matching.

1.4

1.4

1.5

1.6

1.6

1.6

pgaudit

Provides additional logging tools and enhances audit capabilities.

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

pgcrypto

Provides cryptographic functions for PostgreSQL. For more information, see Using pgcrypto.

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

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

pgrowlocks

Contains the pgrowlocks() function to return row locking information for a specified table.

1.2

1.2

1.2

1.2

1.2

1.2

pgstattuple

Contains functions to obtain tuple-level statistics.

To use this extension, you need the mdb_admin role.

1.5

1.5

1.5

1.5

1.5

1.5

pgvector

Enables vector similarity search.

0.2.5

0.2.5

0.2.5

0.2.5

0.3.2

-

plv8

Adds support for the 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 you to store and process GIS (Geographic Information Systems) objects in PostgreSQL databases.

2.5.2

3.0.0

3.1.4

3.1.4

3.3.2

3.4.0

postgis_tiger_geocoder

Contains 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

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

postgres_fdw

Adds Foreign Data Wrapper support to access external PostgreSQL servers.

To use this extension, you need the mdb_admin role.

1.0

1.0

1.0

1.1

1.1

1.1

rum

Provides an access method to work with RUM indexes.

1.3

1.3

1.3

1.3

1.3

1.3

seg

Contains the seg data type for representing line segments or floating point intervals.

1.3

1.3

1.3

1.4

1.4

1.4

smlar

Contains functions for determining array similarity.

1.0

1.0

1.0

1.0

1.0

1.0

tablefunc

Contains functions that return tables, i.e., multiple rows.

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 data.

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 its limitations, see the TimescaleDB documentation.

2.3.1

2.4.2

2.5.2

2.6.1

2.9.0

2.13.0-dev

unaccent

Contains a text search dictionary that removes diacritic signs.

1.1

1.1

1.1

1.1

1.1

1.1

uuid-ossp

Contains functions to generate UUIDs using standard algorithms.

1.1

1.1

1.1

1.1

1.1

1.1

xml2

Adds XPath querying and XSLT functionality.

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.

ClickHouse® is a registered trademark of ClickHouse, Inc.

Previous
Assigning privileges and roles
Next
pg_cron