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 an SSL is required error when connecting?

• What should I do if I get a 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 my 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 a max_connections is less than sum of users connection limit error when changing a cluster?

• Why do I get an error when transferring data by creating and restoring a logical dump

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

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

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

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

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

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

• 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). Due to clause 2.2 of this license, which prohibits using TimescaleDB Community Edition for DbaaS (Database as a Service) offerings, this version is not available in Yandex Cloud.

The version installed in a Managed Service for PostgreSQL cluster is TimescaleDB Apache 2 Edition, which has reduced functionality compared to the Community Edition.

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?

Complete error message:

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

This means that, when connecting to the website, the function was unable to check if its certificate was listed as revoked.

To fix this error:

• Make sure your corporate network policies are not blocking the verification.

• 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 an error when connecting?What should I do if I get an SSL is required error when connecting?

This error occurs because you are trying to connect to a cluster with a publicly accessible host. Such hosts require an SSL certificate to connect. You can:

• Obtain an SSL certificate and add it to the application you use to connect.
• 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 a error when connecting?What should I do if I get a too many active clients for user error when connecting?

An attempt to connect to a cluster host 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?

An attempt to connect to a custom database may fail with the following error:

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

This error indicates that your connection settings contain an incorrect database name.

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

You can get this error when creating a dump with 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. Instead, use pg_dump to dump each custom database individually, skipping the system ones.

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

An attempt to connect to the postgres database may fail with the following error:

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

This error occurs because postgres is a system database and connecting to it is prohibited by Managed Service for PostgreSQL. Specify a different database in your connection settings.

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

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

FATAL: terminating connection due to administrator command

This message is not an error; it indicates that the session/transaction duration has exceeded the Session duration timeout setting (12 hours by default).

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

An attempt to connect to a cluster’s host 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 restricted or if you are using a custom DNS server that cannot resolve domain names in the mdb.yandexcloud.net zone.

Solution:

• Enable public access to your target host. When using a special FQDN, enable public access for the host associated with that FQDN.

  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 prevent 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

You cannot stop a cluster if it does not have any backups. To stop such a cluster, first create a backup for it.

Why do I get a max_connections is less than sum of users connection limit error when modifying a cluster?Why do I get a 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 do I get an error when transferring data by creating and restoring a logical dump?Why do I get an error when transferring data by creating and restoring a logical dump?

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>"

These errors occur because the target cluster lacks the user account or the user privileges that were used to create the logical dump in the source cluster.

To fix these 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 a replication slot already exists error during logical replication?What should I do if I get a replication slot already exists error during logical replication?

By default, the system creates a replication slot when you create a subscription. The replication slot already exists error means that the replication slot already exists.

You can resolve this error in one of the following ways:

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

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

Error message:

extension "<extension_name>" is not available

This error may occur during a database migration to Managed Service for PostgreSQL using a script that attempts to install and use the PostgreSQL extension. The error occurs because PostgreSQL extensions cannot be managed via SQL in Managed Service for PostgreSQL clusters.

To avoid this error:

1. For scripts and dumps in text format, remove any PostgreSQL extension creation commands from them.
2. Install all required extensions in the target database using the Yandex Cloud interfaces.

Why do I get the must be owner of extension 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. Remove any operations with extensions from the dump. For example, comment out the lines related to installing 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

This error occurs if you specify a replication source for the only non-cascading replica.

To ensure high availability, your cluster must have at least one replica without a replication source. This replica will be promoted to master if the master host fails during maintenance.

To learn more about replication, see this section.

Why do I get a cannot execute <SQL_command> in a read-only transaction error?Why do I get a 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.

You can prevent these errors in one 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 target_session_attrs=read-write and list all cluster hosts. This way, you will connect to the master host with read and write access.

For more details on connecting to the master host, see Connecting to a database.

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

The monitor user is reserved for monitoring purposes in the 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.