Managing extensions
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 work correctly.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.timescaledb
: Required to be able to use the TimescaleDB extension .
Warning
Enabling a shared library will cause PostgreSQL to restart on the master host.
Retrieving a list of installed extensions
- Go to the folder page 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 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 enabled extensions will be listed in the extensions
list.
To get a list of extensions for a database, use the get REST API method for the Database resource or the DatabaseService/Get gRPC API call and provide the following in the request:
- Cluster ID in the
clusterId
parameter. To find out the cluster ID, get a list of clusters in the folder. - Database name in the
databaseName
parameter.
Editing a list of installed extensions
- Go to the folder page and select Managed Service for PostgreSQL.
- Click the cluster name and open the Databases tab.
- In the required database row, click
and select Manage. - 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 change extensions for a database, provide their list in the --extensions
argument of a CLI command. In this case, extensions that are not included in 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 with an infrastructure plan.
For more information about how to create this file, see Creating clusters.
For a complete list of available Managed Service for PostgreSQL cluster database configuration fields, see the Terraform provider documentation
. -
Add one or more
extension
blocks (one block 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.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
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.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
To edit the list of extensions for a database, use the update REST API method for the Database resource or the DatabaseService/Update gRPC API call and provide the following in the request:
- Cluster ID in the
clusterId
parameter. To find out the cluster ID, get a list of clusters in the folder. - Name of the database to update in the
databaseName
parameter. To find out the database name, retrieve a list of databases in the cluster. - One or more objects containing extension settings in the
extensions
parameter. - List of database settings to update (
extensions
in this case) in theupdateMask
parameter.
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).
Supported PostgreSQL extensions and utilities
Extension \ PostgreSQL version |
11 |
12 |
13 |
14 |
15 |
16 |
Provides functions that normalize postal addresses sent as a line. |
2.5.2 |
3.0.0 |
3.1.4 |
3.1.4 |
3.3.2 |
3.4.0 |
Contains rules for standardizing US and Canadian mailing addresses for the |
2.5.2 |
3.0.0 |
3.1.4 |
3.1.4 |
3.3.2 |
3.4.0 |
Provides functions that allow you to verify the logical consistency of the relation structure. To use the extension, you need the |
1.1 |
1.2 |
1.2 |
1.3 |
1.3 |
1.3 |
Provides the
|
1.0 |
1.0 |
1.0 |
1.0 |
1.0 |
1.0 |
Enables access to database indexes based on Bloom filters |
1.0 |
1.0 |
1.0 |
1.0 |
1.0 |
1.0 |
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 |
Contains GiST (Generalized Search Tree) operator classes. Unlike a B-tree index, GiST supports such operators as |
1.5 |
1.5 |
1.5 |
1.6 |
1.7 |
1.7 |
Provides the |
1.5 |
1.6 |
1.6 |
1.6 |
1.6 |
1.6 |
Adds support for Foreign Data Wrapper |
1.3 |
1.3 |
1.3 |
- |
1.4 |
- |
Provides the |
1.4 |
1.4 |
1.4 |
1.5 |
1.5 |
1.5 |
Allows connecting to other PostgreSQL databases from the current session. To use the extension, you need the |
1.2 |
1.2 |
1.2 |
1.2 |
1.2 |
1.2 |
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 |
(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 |
Provides a module for calculating distances between two points on the surface of the Earth. There are two ways to calculate them:
|
1.1 |
1.1 |
1.1 |
1.1 |
1.1 |
1.1 |
Provides functions to determine similarities and distance between strings. |
1.1 |
1.1 |
1.1 |
1.1 |
1.1 |
1.2 |
Implements the |
1.5 |
1.6 |
1.7 |
1.8 |
1.8 |
1.8 |
Adds support for virtual, or hypothetical, indexes. They are useful to discover if PostgreSQL will use real indexes to handle problematic queries without having to spend resources to create them. |
1.3.1 |
1.3.1 |
1.3.1 |
1.3.1 |
1.3.1 |
1.4.0 |
Provides functions and operators for manipulating arrays of integers that do not contain any |
1.2 |
1.2 |
1.3 |
1.5 |
1.5 |
1.5 |
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 |
Adds support for the JsQuery language that is used to query the |
1.1 |
1.1 |
1.1 |
1.1 |
1.1 |
1.1 |
Provides support for the |
1.1 |
1.1 |
1.1 |
1.1 |
1.1 |
1.1 |
Implements the |
1.1 |
1.1 |
1.2 |
1.2 |
1.2 |
1.2 |
Provides the |
1.0 |
1.0 |
1.0 |
1.0 |
1.0 |
1.0 |
Adds support for Foreign Data Wrapper To use the extension, you need the |
1.2 |
1.2 |
1.2 |
1.2 |
1.2 |
1.2 |
Adds functions and operators that emulate Oracle functionality and packages. |
3.18 |
3.18 |
3.18 |
3.18 |
3.25 |
4.6 |
Provides functions for monitoring the shared buffer cache. To use the extension, you need the |
1.3 |
1.3 |
1.3 |
1.3 |
1.3 |
1.4 |
Enables you to add scheduled jobs to a database and execute SQL commands directly from a job. Requires enabling the To use the extension, you need the 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 |
Provides functions for managing the PostgreSQL planner. Requires enabling the |
1.3.4 |
1.3.5 |
1.3.7 |
1.4 |
- |
1.6.0 |
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 |
Allows collecting statistics on predicates found in Requires enabling the |
2.0.3 |
2.0.3 |
2.0.3 |
2.0.3 |
2.0.4 |
2.1.0 |
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 |
Provides functions to remove bloat from tables and indexes Unlike To use the extension, you need the |
1.4.6 |
1.4.6 |
1.4.6 |
1.4.7 |
1.4.8 |
1.4.8 |
Allows gathering statistics about reads and writes performed at the file system level. Requires enabling the To use the extension, you need the |
2.1.1 |
2.1.1 |
2.1.3 |
2.2.0 |
2.2.1 |
2.2.1 |
Tracks planning and execution statistics of all SQL queries run in a cluster. To use the extension, you need the |
1.6 |
1.7 |
1.8 |
1.9 |
1.10 |
1.10 |
Enables you to create a logical replication slot in the past. |
1.0 |
1.0 |
1.0 |
1.0 |
1.1 |
1.1.1 |
Provides tools for fast searching for similar strings based on trigram matching. |
1.4 |
1.4 |
1.5 |
1.6 |
1.6 |
1.6 |
The extension provides additional logging tools and enhances the audit features. Requires enabling the pgaudit For more information, see Using pgaudit. |
1.0.0 |
1.0.0 |
1.0.0 |
1.0.0 |
1.7 |
16.0 |
Provides cryptographic functions for PostgreSQL. For more information, see Using pgcrypto. |
1.3 |
1.3 |
1.3 |
1.3 |
1.3 |
1.3 |
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 |
Extends the PostGIS |
2.6.2 |
2.6.2 |
3.0.2 |
3.3.0 |
3.4.1 |
3.5.0 |
Provides the |
1.2 |
1.2 |
1.2 |
1.2 |
1.2 |
1.2 |
Provides functions to obtain tuple-level statistics. To use the extension, you need the |
1.5 |
1.5 |
1.5 |
1.5 |
1.5 |
1.5 |
Adds a vector similarity search. |
0.2.5 |
0.2.5 |
0.2.5 |
0.2.5 |
0.3.2 |
- |
Adds support for PLV8, a V8 JavaScript-based procedural language. |
3.0.0 |
3.0.0 |
3.0.0 |
3.0.0 |
- |
3.2.0 |
Allows GIS (Geographic Information Systems) objects to be stored and handled in PostgreSQL databases. |
2.5.2 |
3.0.0 |
3.1.4 |
3.1.4 |
3.3.2 |
3.4.0 |
Provides functions for geocoding based on TIGER |
2.5.2 |
3.0.0 |
3.1.4 |
3.1.4 |
3.3.2 |
3.4.0 |
Contains |
2.5.2 |
3.0.0 |
3.1.4 |
3.1.4 |
3.3.2 |
3.4.0 |
Adds support for Foreign Data Wrapper To use the extension, you need the |
1.0 |
1.0 |
1.0 |
1.1 |
1.1 |
1.1 |
Provides an access method to work with |
1.3 |
1.3 |
1.3 |
1.3 |
1.3 |
1.3 |
Implements the |
1.3 |
1.3 |
1.3 |
1.4 |
1.4 |
1.4 |
Provides a set of functions for computing similarity of two arrays. |
1.0 |
1.0 |
1.0 |
1.0 |
1.0 |
1.0 |
Provides a set of functions that return tables, i.e., multiple rows. |
1.0 |
1.0 |
1.0 |
1.0 |
1.0 |
1.0 |
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 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 |
Provides a text search dictionary that removes diacritic signs. |
1.1 |
1.1 |
1.1 |
1.1 |
1.1 |
1.1 |
Provides functions to generate universally unique identifiers (UUIDs) using standard algorithms. |
1.1 |
1.1 |
1.1 |
1.1 |
1.1 |
1.1 |
Provides XPath querying and XSLT (Extensible Stylesheet Language Transformations) functionality. |
1.1 |
1.1 |
1.1 |
1.1 |
1.1 |
1.1 |
pgcompacttable
Command syntax:
./bin/pgcompacttable \
-h c-<cluster_ID>.rw.mdb.yandexcloud.net \
-p 6432 \
-U <usename> \
-W <password> \
-d <DB_name> \
-n <schema_name>
-t <table_name>
Requires enabling the pgstattuple
extension.
To use the extension, you need the mdb_admin
role.
ClickHouse® is a registered trademark of ClickHouse, Inc