Error messages in Managed Service for PostgreSQL

• Why do I get an error when using the TimescaleDB extension?

• What should I do if I get a revocation check error when using PowerShell to obtain an SSL certificate?

• What should I do if I get the SSL is required error when connecting?

• What should I do if I get the too many active clients for user error when connecting?

• Why do I get an error when connecting to a custom database?

• Why do I get an error when creating a dump with pg_dumpall?

• Why do I get an error when connecting to a postgres database?

• Why does the connection terminate with terminating connection due to administrator command?

• Why cannot I connect to cluster hosts?

• Why cannot I stop a cluster?

• Why do I get the max_connections is less than sum of users connection limit error when changing a cluster?

• Why can a data transfer through creating and restoring a logical dump fail with an error?

• What should I do if I get the replication slot already exists error when performing logical replication?

• Why do I get an extension... is not available error when migrating my DB to Managed Service for PostgreSQL?

• Why do I get the must be owner of extension error when deploying a logical dump?

• Why do I get an error when setting up cascading replication?

• Why do I get the cannot execute <SQL_command> in a read-only transaction error?

• What should I do if logs display the too many connections for role "monitor" error?

• Why do I get an error when trying to install multiple extensions in the CLI?

Why do I get an error when using the TimescaleDB extension?Why do I get an error when using the TimescaleDB extension?

Error message:

SQL Error [0A000]: ERROR: functionality not supported under the current "apache" license
Hint: Upgrade your license to 'timescale' to use this free community feature.

This error occurs when you attempt to use a function available only in TimescaleDB Community Edition.

The community edition is distributed under the Timescale license (TSL). Clause 2.2 of the license prohibits using TimescaleDB Community Edition to provide DbaaS (Database as a Service) services, so this version is not available in Yandex Cloud.

The version installed in a Managed Service for PostgreSQL cluster is TimescaleDB Apache 2 Edition, and its features are limited compared to the community version.

What should I do if I get a revocation check error when using PowerShell to obtain an SSL certificate?What should I do if I get a revocation check error when using PowerShell to obtain an SSL certificate?

Here is the full text of the error:

curl: (35) schannel: next InitializeSecurityContext failed: Unknown error (0x80092012)
The revocation function was unable to check revocation for the certificate

This means, when connecting to the website, the service was unable to check whether or not its certificate was listed among revoked ones.

To fix this error:

• Make sure the corporate network settings do not block the check.

• Run the command with the --ssl-no-revoke parameter.

  mkdir $HOME\.postgresql; curl.exe --ssl-no-revoke -o $HOME\.postgresql\root.crt https://storage.yandexcloud.net/cloud-certs/CA.pem
  

What should I do if I get the error when connecting?What should I do if I get the SSL is required error when connecting?

The error occurs because you are trying to connect to the cluster with a public host. These hosts only support connections with an SSL certificate. However, you can:

• Obtain an SSL certificate and add it to the application you are using to connect to the cluster.
• Disable public access to hosts and connect to the cluster from a VM located in the same cloud network.

What should I do if I get the error when connecting?What should I do if I get the too many active clients for user error when connecting?

Connecting to cluster hosts may fail with the following error:

too many active clients for user (pool_size for user <username> reached <limit_value>)

By default, a cluster reserves 50 connections per host for each user. If the connection limit per user is reached, any attempt to establish a new connection will fail with an error.

Solution: Increase the connection limit in the Conn limit setting.

For instructions on updating PostgreSQL settings at the user level, see this guide.

Why do I get an error when connecting to a custom database?Why do I get an error when connecting to a custom database?

Connecting to a custom database may fail with the following error:

ERROR: odyssey: ce3ea075f4ffa: route for '<DB_name>.<username>' is not found

The error means that the connection parameters contain an invalid database name.

Why do I get an error when creating a dump using pg_dumpall?Why do I get an error when creating a dump using pg_dumpall?

You get this error when creating a dump using pg_dumpall:

ERROR: odyssey: c16b9035a1f78: route for 'template1.<username>' is not found

This error is there because pg_dumpall tries to export all databases: both custom and system ones.

You cannot create a dump of all Managed Service for PostgreSQL databases at the same time. Export dumps using pg_dump for each custom database one by one, excluding the system ones.

Why do I get an error when connecting to a postgres database?Why do I get an error when connecting to a postgres database?

Connecting to the postgres database fails with the following error:

ERROR: odyssey: c76e2c1283a7a: route for 'postgres.<username>' is not found

postgres is a system database; connecting to it is prohibited in Managed Service for PostgreSQL. Specify a different database in the connection parameters.

Why does a connection terminate with ?Why does a connection terminate with terminating connection due to administrator command?

A Managed Service for PostgreSQL cluster connection may be terminated with the following message:

FATAL: terminating connection due to administrator command

This message does not indicate an error, it means that the session/transaction duration has exceeded the Session duration timeout setting value (default value: 12 hours).

Why cannot I connect to cluster hosts?Why cannot I connect to cluster hosts?

Connecting to cluster hosts may fail with the following error:

could not translate host name "<regular or special FQDN>" to address: Name or service not known

This error occurs if public access to the host is denied or users are using custom DNS servers that do not allow domain names in the mdb.yandexcloud.net zone.

Solution:

• Enable public access for the host you are connecting to. When using a special FQDN, enable public access for the host the special FQDN points to.

  Warning

  Use master host special FQDN-based connections only for processes that can cope with database being unavailable for writing for up to 10 minutes.

• We recommend that you enable public access for all cluster hosts. This will avoid connection errors during automatic master failover.

• For custom DNS servers, configure DNS forwarding for the mdb.yandexcloud.net zone.

Why cannot I stop a cluster?Why cannot I stop a cluster?

Error message:

ERROR: rpc error: code = FailedPrecondition desc = Cluster has no backups

A cluster that has no backups cannot be stopped. To fix the error and stop the cluster, create its backup.

Why do I get the error when modifying a cluster?Why do I get the max_connections is less than sum of users connection limit error when modifying a cluster?

This error may occur when downgrading a host class in a cluster if the sum of connection limits for all users is less then the total cluster connection limit (Max connections).

Solution: First reduce the limits set for users so their sum is less than <Max_connections_value> — 15 and then proceed with host class downgrade.

Why does a data transfer through creating and restoring a logical dump fail with an error?Why does a data transfer through creating and restoring a logical dump fail with an error?

Restoring a logical dump may fail with one of the following errors:

• ERROR: role "<source_username>" does not exist
• ERROR: must be member of role "<source_username>"

The errors occur because the target cluster does not have the user (or the privileges of the user) who created the logical dump in the source cluster.

To resolve the errors:

1. In the target cluster, add a user with access to the migrated database and the same name as the user who created the logical dump in the source cluster.
2. As this user, restore the logical dump or grant their privileges to another account you want to use to restore the logical dump.

What should I do if I get the error when performing logical replication?What should I do if I get the replication slot already exists error when performing logical replication?

By default, when you create a subscription, a replication slot is also created. The replication slot already exists error means that a replication slot already exists.

You can fix this error by doing one of the following:

1. Link your subscription to an existing replication slot. To do this, add the create_slot = false parameter to the request to create a subscription.
2. Delete the existing replication slot and try creating the subscription again.

Why do I get an error when migrating my DB to Managed Service for PostgreSQL?Why do I get an extension... is not available error when migrating my DB to Managed Service for PostgreSQL?

Error message:

extension "<extension_name>" is not available

You may get this error when migrating a DB to Managed Service for PostgreSQL with a script attempting to install and use the PostgreSQL extension. The reason for the error is that in Managed Service for PostgreSQL clusters, you cannot use SQL commands to manage PostgreSQL extensions.

To avoid this error:

1. If the script or logical dump is in text format, remove the operators for creating PostgreSQL extensions from them.
2. Install all required extensions in the target database using the Yandex Cloud interfaces.

Why do I get the error when deploying a logical dump?Why do I get the must be owner of extension error when deploying a logical dump?

Restoring a logical dump may fail with this error: ERROR: must be owner of extension.

The error is there because the logical dump contains installation or update operations of PostgreSQL extensions. In Managed Service for PostgreSQL clusters, you cannot manage extensions using SQL commands.

To fix this error:

1. Before restoring the dump, enable all required extensions in the target database.
2. Exclude any operations with extensions from the dump. For example, you can comment out lines related to installation of extensions.
3. Perform logical dump recovery again.

Why do I get an error when setting up cascading replication?Why do I get an error when setting up cascading replication?

Error message:

cluster should have at least 2 HA hosts to use cascade host

The error occurs if you specify a replication source for a single non-cascading replica.

To ensure high availability, your cluster must have at least one replica without a replication source. During maintenance or if the master host fails, this replica will take over as the master.

To learn more about replication, see this section.

Why do I get the error?Why do I get the cannot execute <SQL_command> in a read-only transaction error?

Error options:

ERROR: cannot execute ALTER EXTENSION in a read-only transaction

ERROR: cannot execute CREATE TABLE in a read-only transaction

ERROR: cannot execute UPDATE in a read-only transaction

ERROR: cannot execute INSERT in a read-only transaction

Such errors can occur after master failover if you connected to a read-only replica.

To prevent such errors, use any of the following ways:

• Connect to the cluster using a special FQDN that always points to the current master.

  Warning

  Use master host special FQDN-based connections only for processes that can cope with database being unavailable for writing for up to 10 minutes.

• When connecting, specify the target_session_attrs=read-write parameter and list all cluster hosts. This way, you will connect to the master host with read and write access.

For more information on how to connect to the master host, see Connecting to a database.

What should I do if logs display the error?What should I do if logs display the too many connections for role "monitor" error?

The monitor user is reserved for monitoring purposes in a Managed Service for PostgreSQL cluster. You can ignore too many connections warnings for this user.

Why do I get an error when trying to install multiple extensions in the CLI?Why do I get an error when trying to install multiple extensions in the CLI?

Installing multiple extensions in the CLI may fail with one of these errors:

• ERROR: accepts 1 arg(s), received 2

  This error may occur due to an incorrect command format.

  Solution: Make sure to list the extensions with no spaces in between. Here is an example:

  yc managed-postgresql database update db1 --cluster-id cat0adul1fj0******** --extensions cube,pg_logic,timescaledb
  

• ERROR: rpc error: code = InvalidArgument desc = Invalid extensions '<extension_name>', allowed extension: <extension_list>

  You may get this error if an extension in the list is incompatible with the PostgreSQL version in the cluster.

  Solution: Check the compatibility of the extensions specified in the command in the list of supported extensions.

• ERROR: rpc error: code = InvalidArgument desc = The specified extension '<extension_name>' is not present in shared_preload_libraries

  This error may occur if the cluster does not contain the required shared library.

  Solution: Check the shared library requirements in the list of supported extensions. To add the required library, when updating the PostgreSQL cluster settings, specify its name in the Shared preload libraries parameter.