[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for PostgreSQL](../index.md) > FAQ > All questions on one page # FAQ about Managed Service for PostgreSQL ### General questions {#toc-general} * [What is Managed Service for PostgreSQL?](#what-is) * [What is the role of Managed Service for PostgreSQL in database management and maintenance?](#services) * [When to use Managed Service for PostgreSQL and when database VMs?](#mdb-advantage) * [What is a database host and database cluster?](#what-is-cluster) * [How do I get started with Managed Service for PostgreSQL?](#quickstart) * [How many database hosts can there be in a cluster?](#how-many-hosts) * [How can I access a running database host?](#db-access) * [How many clusters can I create within a single cloud?](#db-limit) * [How are database clusters maintained?](#service-window) * [Which PostgreSQL version does Managed Service for PostgreSQL use?](#dbms-version) * [What happens when a new DBMS version is released?](#new-version) * [What happens when a DBMS version becomes deprecated?](#dbms-deprecated) * [How do you calculate usage cost for a database host?](#db-cost) * [How can I change the computing resources and storage size for a database cluster?](#resources-change) * [Can I configure automatic storage expansion for a cluster?](#storage-autoscale) * [Are database host backups enabled by default?](#default-backup) * [What is the backup schedule? Is a database cluster available during backups?](#backup-window) * [Is encryption enabled for PostgreSQL database cluster connections?](#encryption) * [What is a read-only replica in PostgreSQL?](#read-only-instance) * [What metrics and processes can be monitored?](#monitoring) * [How do I get the logs of my activity in the Yandex Cloud services?](#logs) * [What limitations apply to PostgreSQL database clusters?](#instance-limitations) * [What PostgreSQL extensions are supported in Managed Service for PostgreSQL?](#pg-extension) * [Which data center stores the PostgreSQL cluster backups?](#data-center) * [Can I get superuser privileges in PostgreSQL?](#superuser) * [Can I copy data from a Managed Service for PostgreSQL table to a local file? Can I populate a table with data from a local file?](#copy-write-data) * [Are there any specific features or restrictions for the garbage collector in a Managed Service for PostgreSQL cluster?](#vacuum) * [Why is my cluster slow even though the computing resources are not fully utilized?](#throttling) * [What block size is used on the cluster disks?](#block-size) ### Connection {#toc-connection} * [Can I access the cluster from within Yandex Cloud?](#conn-from-yc) * [Can I connect to the cluster from a Docker container?](#conn-from-docker) * [How do I obtain an SSL certificate for connecting to Managed Service for PostgreSQL on Windows?](#get-ssl) * [How do I install an SSL certificate to connect Power BI to Managed Service for PostgreSQL via psql?](#power-bi) * [Can I connect to cluster hosts over SSH?](#connect-ssh) * [What is the maximum allowed number of concurrent connections per host in Managed Service for PostgreSQL?](#host-conn) * [What is the maximum allowed number of connections per user?](#user-conn) * [Why am I getting an error when trying to connect to a database from Looker Studio?](#google-looker) * [How to ensure I am always connecting to the master host?](#connect-to-master) * [How to ensure I am always connecting to the most up-to-date replica?](#connect-to-replica) * [How do I connect to the `postgres` database?](#postgres-db) * [How do I get the `postgres` user password?](#postgres-user) ### Updating a cluster {#toc-update} * [Why did my cluster switch to the _Updating_ status during an unscheduled backup?](#updating-while-backup) * [Can I create two PostgreSQL databases at the same time?](#multi-db) * [How can I change the number of connections available for a user?](#user-conn-number) * [Can I change the host class (standard, memory-optimized, burstable) for an existing host?](#host-class) * [Can I change the disk type in an existing cluster?](#disk-type) * [Can I manage a cluster using SQL commands?](#sql-control) * [Can I create a database using SQL commands?](#create-db-sql) * [How do I take a cluster out of read-only mode?](#read-only-exit) ### Cluster settings {#toc-configuration} * [Is `autovacuum` enabled for all tables by default?](#autovacuum) * [What are the default values for LC_COLLATE and LC_CTYPE database settings?](#lc-default) * [Can I change the LC_COLLATE and LC_CTYPE values?](#lc-edit) * [Can I change the database owner?](#db-owner) ### Moving and restoring a cluster {#toc-backup} * [Can I restore a cluster backup to a running Managed Service for PostgreSQL instance in another cloud network?](#diff-network) * [Can I change the retention period for automatic backups?](#backup-retain-days) * [Do I need the wal2json plugin if I am only doing replication and not copying?](#wal2json) * [Can I download a database backup to my local machine?](#backup-local-dump) * [How do I move a local PostgreSQL database dump to Yandex Cloud?](#dump-to-yc) * [How do I migrate a database or a table from one cluster to another?](#transfer-db-table) * [How do I transfer data between clusters in different folders or clouds?](#transfer-data) * [How do I move a cluster to another cloud?](#transfer-cluster) * [How do I restore a backup to an existing cluster?](#restore-existing-cluster) * [How do I restore a cluster to the state of the selected backup?](#cluster-existing-backup) * [Can I recover a single database from a backup?](#restore-one-database) ### Upgrading the PostgreSQL major version {#toc-version-update} * [Do I need to run `ANALYZE` and/or `REINDEX` and refresh statistics (`pg_stat_user_tables` and `pg_statistic`) manually after upgrading the major PostgreSQL version ?](#version-update-manual) * [Does Managed Service for PostgreSQL check whether the collation version has changed after upgrading the major PostgreSQL version?](#version-update-collation) * [How do I access logs and generated scripts after upgrading the major PostgreSQL version?](#version-update-get-logs) * [Can a major version upgrade fail?](#version-update-error) ### Users and roles {#toc-users} * [Can I migrate users from a PostgreSQL source cluster to a Managed Service for PostgreSQL destination cluster?](#user-migration) * [How to create a role using SQL queries?](#create-role) ### High availability and load balancing {#toc-ha} * [How to remove a host from the master selection process?](#excluded-host) * [Why do I get an error when setting up cascading replication?](#cascade-error) * [How to ensure I am always connecting to the master host?](#connect-to-master-ha) * [How to ensure I am always connecting to the most up-to-date replica?](#connect-to-replica-ha) * [Why did the master and replicas swap roles?](#failover) * [Can I configure multi-master within a single cluster or between two clusters?](#setting-multimaster) * [How to configure load balancing so that all read requests are redirected to replicas?](#balancing-replicas) * [When does automatic failover occur?](#auto-failover) ### Monitoring and logs {#toc-monitoring} * [Where can I track my disk space utilization to avoid exceeding the 95% limit triggering read-only mode?](#disk-space) * [Are logs stored on the same disk as PostgreSQL data? How are they billed?](#logs-storage) * [How longs are logs retained?](#log-keeping) * [What are WALs and what is their purpose?](#wal) * [What does the `Cached` metric indicate in the RAM monitoring of a PostgreSQL cluster host?](#cached) * [How do I get alerts on critical PostgreSQL cluster metrics?](#notifications) * [How do I set up an alert that triggers when a certain percentage of disk space is used?](#disk-space-percentage) ### Error messages {#toc-errors} * [Why do I get an error when using the TimescaleDB extension?](#timescale) * [What should I do if I get a revocation check error when using PowerShell to obtain an SSL certificate?](#get-ssl-error) * [What should I do if I get an `SSL is required` error when connecting?](#ssl-req) * [What should I do if I get a `too many active clients for user` error when connecting?](#connection-limit-error) * [Why do I get an error when connecting to a custom database?](#database-error) * [Why do I get an error when creating a dump with pg_dumpall?](#dump-error) * [Why do I get an error when connecting to a postgres database?](#database-postgres-error) * [Why does my connection terminate with `terminating connection due to administrator command`?](#connection-error) * [Why cannot I connect to cluster hosts?](#host-error) * [Why do I get an error when stopping the cluster?](#stop-cluster-error) * [Why do I get a `max_connections is less than sum of users connection limit` error when changing a cluster?](#max-connections-error) * [Why do I get an error when transferring data by creating and restoring a logical dump](#backup-error) * [What should I do if I get a `replication slot already exists` error when performing logical replication?](#repl-slot-exists) * [Why do I get an `extension... is not available` error when migrating my database to Managed Service for PostgreSQL](#extension-is-not-available)? * [Why do I get a `must be owner of extension` error when restoring a logical dump?](#owner-of-extension) * [Why do I get an error when setting up cascading replication?](#cascade-errors) * [Why do I get a `cannot execute in a read-only transaction` error?](#read-only-error) * [What should I do if I see a `too many connections for role "monitor"` error in the logs?](#monitor-role-error) * [Why do I get an error when trying to install multiple extensions in the CLI?](#cli-extensions-errors) * [Why do I get the `could not open extension control file "/vector.control": No such file or directory` error when accessing the `vector` extension?](#vector-error) ## General questions {#general} #### What is Managed Service for PostgreSQL? {#what-is} Managed Service for PostgreSQL is a solution that helps you create, operate, and scale PostgreSQL databases in the cloud. With Managed Service for PostgreSQL, you can: * Create a database with performance tailored to your needs. * Scale computing power and dedicated storage capacity for your databases as needed. * Get database logs. Managed Service for PostgreSQL handles the heavy lifting of PostgreSQL infrastructure management: * Monitors resource usage. * Performs automated database backups. * Ensures fault tolerance through automatic failover to standby replicas. * Keeps your DBMS software up to date. You can work with a Managed Service for PostgreSQL database cluster the same way as with your regular local database. Thus, you can manage internal database settings to meet your application requirements. #### What is the role of Managed Service for PostgreSQL in database management and maintenance? {#services} When you create clusters, Managed Service for PostgreSQL allocates resources, installs the DBMS, and creates databases. For all created and running databases, Managed Service for PostgreSQL automatically creates backups as well as applies DBMS patches and updates. Furthermore, Managed Service for PostgreSQL ensures data replication across database hosts, both within and across [availability zones](../../overview/concepts/geo-scope.md), with automatic failover to a standby replica if a failure occurs. Be mindful of what is managed by the service and what, by the Yandex Cloud customer. Understanding these zones of control makes you an efficient user of cloud resources and helps avoid potential database-related issues. For more information, see [Zones of control between managed database (MDB) service users and Yandex Cloud](../../overview/concepts/mdb-responsibilities.md). #### When to use Managed Service for PostgreSQL and when database VMs? {#mdb-advantage} Yandex Cloud offers two approaches for working with databases: * With Managed Service for PostgreSQL, you can use template databases, with no administrative effort required. * Alternatively, with Yandex Compute Cloud virtual machines, you can create and customize your own databases. This approach allows you to use any database management system, connect to databases over SSH, and many more. #### What is a database host and database cluster? {#what-is-cluster} A _database host_ is a cloud-based isolated database environment with dedicated computing resources and reserved storage capacity. A _database cluster_ consists of one or more database hosts with configurable replication across them. #### How do I get started with Managed Service for PostgreSQL? {#quickstart} Managed Service for PostgreSQL is available to all registered Yandex Cloud users. Before creating a database cluster in Managed Service for PostgreSQL, you need to decide on its configuration: * [Host class](../concepts/instance-types.md) that will determine your computing power, i.e., vCPUs, RAM, and more. * Storage size (fully reserved when creating the cluster). * Network for your cluster. * Number of hosts in your cluster and availability zone for each host. Learn more in [Getting started with Managed Service for PostgreSQL](../quickstart.md). #### How many database hosts can a cluster contain? {#how-many-hosts} The minimum number of hosts depends on the selected [storage](../concepts/storage.md) type: * A minimum of three hosts is required for the following disk types: * Local SSDs (`local-ssd`) * Non-replicated SSDs (`network-ssd-nonreplicated`) * At least one host for the following disk types: * Network HDDs (`network-hdd`) * Network SSDs (`network-ssd`) * Ultra high-speed network SSDs with three replicas (`network-ssd-io-m3`) The maximum number of hosts per cluster cannot exceed the set limits. For more information, see [Managed Service for PostgreSQL quotas and limits](../concepts/limits.md). #### How can I access a running database host? {#db-access} You can connect to Managed Service for PostgreSQL databases using native DBMS connection methods. Learn more about connecting to clusters in [this guide](../operations/connect/index.md). #### How many clusters can I create within a single cloud? {#db-limit} To learn about MDB quotas and limits, see [Managed Service for PostgreSQL quotas and limits](../concepts/limits.md). #### How are database clusters maintained? {#service-window} Maintenance in Managed Service for PostgreSQL includes: * Automatic installation of DBMS updates and patches for database hosts (including for stopped clusters). * Changes in the host class and storage size. * Other Managed Service for PostgreSQL maintenance activities. Learn more in [Maintenance in Managed Service for PostgreSQL](../concepts/maintenance.md). #### Which PostgreSQL version does Managed Service for PostgreSQL use? {#dbms-version} Managed Service for PostgreSQL supports PostgreSQL versions 14, 15, 16, 17, and 18, and PostgreSQL versions 14, 15, 16, 17, and 18 for 1C. Version 17 is used by default in the management console. #### What happens when a new DBMS version is released? {#new-version} The database software is updated whenever new minor versions are released. Owners of affected database clusters get advance notice of the upcoming maintenance schedule and database availability. #### What happens when a DBMS version becomes deprecated (`Deprecated`)? {#dbms-deprecated} Existing clusters based on `Deprecated` versions continue to operate normally. Six months before the end of support, an active notification effort starts about the need to update. From this point on, you cannot create new clusters. Owners of clusters based on `Deprecated` versions will get a series of official email notifications: the first one 6 months before `EOL`, and then 90, 30, 7, and 1 day before the scheduled forced update. #### How do you calculate usage cost for a database host? {#db-cost} In Managed Service for PostgreSQL, the usage cost is calculated based on the following: * Selected host class. * Reserved storage capacity for the database host. * Size of database cluster backups. You do not pay for backups as long as their size does not exceed the storage capacity. Backup storage exceeding this limit is charged according to our [pricing policy](../pricing.md). * Database host uptime in hours. Partial hours are rounded to the nearest whole hour. The hourly rates for each host class are listed in [Managed Service for PostgreSQL pricing policy](../pricing.md). #### How can I change the computing resources and storage size for a database cluster? {#resources-change} You can scale your computing resources and storage size directly in the management console by selecting a different host class for your cluster. The cluster settings will update within 30 minutes. This window may also be used for other maintenance activities, such as installing updates. #### Can I configure automatic storage expansion for a cluster? {#storage-autoscale} Yes, you can enable automatic storage expansion when [creating](../operations/cluster-create.md) or [updating](../operations/storage-space.md#disk-size-autoscale) your cluster. #### Are database host backups enabled by default? {#default-backup} Yes, backups are enabled by default. The PostgreSQL backup policies provide full daily backups and continuous transaction log archiving. This enables point-in-time recovery, allowing you to restore your cluster to any moment within the backup retention period, with the exception of the last 30 seconds. By default, backups are retained for seven days. #### When does a backup run? Can I access my database cluster during backup? {#backup-window} The backup window, i.e., the scheduled time for the full daily backup of a database cluster, is from 01:00 to 05:00 (UTC+3). Clusters remain fully available during the backup window. #### Is encryption enabled for PostgreSQL database cluster connections? {#encryption} Connections between your database cluster and application are always encrypted with SSL. You cannot disable encryption for cluster connections. #### What is a read-only replica in PostgreSQL? {#read-only-instance} A read-only replica is a PostgreSQL database cluster host available only for reads that synchronizes its data with the master host. This applies only to clusters with more than one host. You can use a read-only replica to reduce the load on the master host experiencing high read traffic to its database. #### What metrics and processes can be monitored? {#monitoring} For all DBMS types, you can monitor: * Absolute CPU, memory, network, and disk utilization. * Amount of data in a database cluster and the remaining free space in your data storage. For all database hosts, you can monitor metrics specific to their DBMS type. For example, for PostgreSQL, you can monitor: * Average query execution time. * Number of queries per second. * Number of errors in logs, and more. You can monitor metrics with a minimum granularity of five seconds. #### Can I get logs of my operations in Yandex Cloud? {#logs} Yes, you can request information about operations with your resources from Yandex Cloud logs. Do it by contacting [support](https://center.yandex.cloud/support). #### What restrictions are placed on PostgreSQL database clusters? {#instance-limitations} To learn about Managed Service for PostgreSQL quotas and limits, see [Managed Service for PostgreSQL quotas and limits](../concepts/limits.md). Also refer to [PostgreSQL host classes](../concepts/instance-types.md) for the specifications of clusters you can create with Managed Service for PostgreSQL. #### What PostgreSQL extensions are supported in Managed Service for PostgreSQL? {#pg-extension} See the list of supported PostgreSQL extensions in [Managing extensions](../operations/extensions/cluster-extensions.md). #### Which data center stores the PostgreSQL cluster backups? {#data-center} #### Can I get superuser privileges in PostgreSQL? {#superuser} No, superuser privileges are not available to Managed Service for PostgreSQL users. Cluster actions of all types are available to users with the [mdb_admin](../concepts/roles.md#mdb-admin) role. #### Can I copy data from a Managed Service for PostgreSQL table to a local file? Can I populate a table with data from a local file? {#copy-write-data} Yes, you can both copy data from a table to a local file and import data from a file into a table. For more information, see [Saving table data locally and populating a table from a local machine](../operations/copy-write-data.md). #### Are there any specific features or restrictions for the garbage collector in a Managed Service for PostgreSQL cluster? {#vacuum} Managed Service for PostgreSQL clusters support all [VACUUM command](https://www.postgresql.org/docs/current/sql-vacuum.html) options. However, please note the following when using them: * To run `VACUUM FULL`, the user must have the [`mdb_admin`](../concepts/roles.md#mdb-admin) role. The VACUUM FULL operation does not affect system tables. * In PostgreSQL version 14, the `INDEX_CLEANUP` parameter functionality has been enhanced: it now has the `AUTO` value by default. This means the `VACUUM` command skips index cleaning if it considers it unnecessary. For backward compatibility with older PostgreSQL versions, set `INDEX_CLEANUP` to `ON`. #### Why is my cluster slow even though the computing resources are not fully utilized? {#throttling} Your storage may have insufficient maximum [IOPS and bandwidth](../../compute/concepts/storage-read-write.md) to process the current number of requests. In this case, [throttling](../../compute/concepts/storage-read-write.md#throttling) occurs, which degrades the entire cluster performance. The maximum IOPS and bandwidth values increase by a fixed value when the storage size increases by a certain step. The step and increment values depend on the disk type: | Disk type | Step, GB | Max IOPS increase (read/write) | Max bandwidth increase (read/write), MB/s | |-----------------------------|---------|------------------------------------|-----------------------------------------------| | `network-hdd` | 256 | 300/300 | 30/30 | | `network-ssd` | 32 | 1,000/1,000 | 15/15 | | `network-ssd-nonreplicated`, `network-ssd-io-m3` | 93 | 28,000/5,600 | 110/82 | To increase the maximum IOPS and bandwidth values and make throttling less likely, expand the storage when [updating your cluster](../operations/update.md#change-disk-size). For storage using the `network-hdd` disk type, we recommend switching to `network-ssd` or `network-ssd-nonreplicated` via a [cluster restore](../operations/cluster-backups.md#restore) from backup. When using `local-ssd` storage, cluster hosts may experience a minor increase in CPU load due to the services required for disk operation. #### What block size is used on the cluster disks? {#block-size} [Block](https://en.wikipedia.org/wiki/Block_(data_storage)) size for all disk types is 4 KB. ## Connection {#connection} #### Can I access a cluster from within Yandex Cloud? {#conn-from-yc} You can connect to Managed Service for PostgreSQL cluster hosts: * Via the internet, if you configured public access for these hosts. You can only access such hosts over SSL. * From Yandex Cloud virtual machines located in the same cloud network. For hosts without public access, SSL is not required to connect to them from these virtual machines. * From a [Yandex Serverless Containers container](../../serverless-containers/concepts/index.md). For hosts without public access, this container must be located in the same cloud network. For more information, see [this guide](../operations/connect/index.md). #### Can I connect to a cluster from a Docker container? {#conn-from-docker} Yes, you can. To do this, [configure the Dockerfile](../operations/connect/clients.md#connection-docker). See a Yandex Serverless Containers container connection example in [this tutorial](../../serverless-containers/tutorials/pg-connect.md). #### How do I obtain an SSL certificate for connecting to Managed Service for PostgreSQL on Windows? {#get-ssl} You can obtain an SSL certificate using PowerShell: ```powershell mkdir $HOME\.postgresql; curl.exe --output $HOME\.postgresql\root.crt https://storage.yandexcloud.net/cloud-certs/CA.pem ``` The certificate will be saved to the `$HOME\.postgresql\root.crt` file. For details on getting a certificate and connecting to a database, see the [service guide](../operations/connect/index.md). #### How do I install an SSL certificate to connect Power BI to Managed Service for PostgreSQL via psql? {#power-bi} 1. Install [Windows Subsystem for Linux](https://docs.microsoft.com/en-us/windows/wsl/) (WSL) and run the following command in the terminal: ```bash mkdir /mnt/c/temp && \ curl "https://storage.yandexcloud.net/cloud-certs/CA.pem" --output /mnt/c/temp/CA.pem && \ openssl pkcs12 -export -out /mnt/c/temp/CA.pfx -nokeys -in /mnt/c/temp/CA.pem ``` The certificate will be located at `C:\temp\CA.pfx`. 1. [Install the obtained certificate in the Windows Certificate Store](https://docs.microsoft.com/en-us/skype-sdk/sdn/articles/installing-the-trusted-root-certificate). #### Can I connect to cluster hosts over SSH? {#connect-ssh} You cannot connect to hosts via SSH. This is done for the sake of security and user cluster fault tolerance because direct changes inside a host can render it completely inoperable. #### What is the maximum allowed number of concurrent connections per host in Managed Service for PostgreSQL? {#host-conn} The number of concurrent connections is configured at the cluster level using the [**Max connections** setting](../concepts/settings-list.md#setting-max-connections). By default it is set to the maximum value calculated using the following formula: ```text 200 × ``` For instructions on updating PostgreSQL settings at the cluster level, see [this guide](../operations/update.md#change-postgresql-config). #### What is the maximum allowed number of connections per user? {#user-conn} By default, a cluster reserves 50 connections per host for each user. You can change this number in the [**Conn limit** setting](../concepts/settings-list.md#setting-conn-limit). If the user’s connection limit is reached, new connection attempts will fail with the following error: ```text too many active clients for user (pool_size for user reached ) ``` For instructions on updating PostgreSQL settings at the user level, see [this guide](../operations/cluster-users.md#update-settings). #### Why am I getting an error when trying to connect to a database from Looker Studio? {#google-looker} To connect from Looker Studio, you need to generate a client certificate file and a private key and specify them in the connection settings. For instructions, see [Connecting from Looker Studio](../operations/connect/clients.md#connection-google-looker). #### How to ensure I am always connecting to the master host? {#connect-to-master} To connect to the current master host, use a [special FQDN](../operations/connect/fqdn.md#special-fqdns) in the `c-.rw.mdb.yandexcloud.net` format. This FQDN supports read and write access. {% cut "Example command for connecting to the master host" %} ```bash psql "host=c-.rw.mdb.yandexcloud.net \ port=6432 \ sslmode=verify-full \ dbname= \ user=" ``` {% endcut %} #### How to ensure I am always connecting to the most up-to-date replica? {#connect-to-replica} To connect to the most up-to-date replica, use a [special FQDN](../operations/connect/fqdn.md#special-fqdns) in the `c-.ro.mdb.yandexcloud.net` format. This FQDN only supports read access. {% cut "Example command for connecting to a replica" %} ```bash psql "host=c-.ro.mdb.yandexcloud.net \ port=6432 \ sslmode=verify-full \ dbname= \ user=" ``` {% endcut %} If there are no active replicas in the cluster, this FQDN will point to the current master host. #### How do I connect to the `postgres` database? {#postgres-db} `postgres` is a system database. For security reasons, you cannot connect to system databases. #### How do I get the `postgres` user's password? {#postgres-user} For security reasons, the system does not permit obtaining the `postgres` user's password to connect to the database on their behalf. ## Updating a cluster {#update} #### Why did my cluster switch to the _Updating_ status during an unscheduled backup? {#updating-while-backup} Once the backup process is started, the cluster changes its status to **Updating**. You cannot cancel this operation. Please wait for it to complete. The time this operation is performed depends on a number of factors, such as DB size, cluster configuration, and the nature of data being stored. The average speed when [recovering a cluster from a backup](../operations/cluster-backups.md#restore) is about 100 Mbps. #### Can I create two PostgreSQL databases at the same time? {#multi-db} You cannot apply multiple transactions to a cluster at the same time. It is an architectural feature of the technologies utilized by Yandex Cloud. #### Can I change the number of connections available to a user? {#user-conn-number} Yes, you can. To do this, [change the values of the relevant settings](../operations/cluster-users.md#update-settings): * [**Conn limit**](../concepts/settings-list.md#setting-conn-limit): Maximum number of host connections per user. The default value is 50. The minimum value is 10. * [**Max connections**](../concepts/settings-list.md#setting-max-connections): Maximum number of connections reserved for all users. The default value is `200 × `. You have to keep in mind that this number includes 15 service connections, e.g., given a cluster with `"max_connections": 100`, you can reserve a maximum of 85 connections per cluster host for users. #### Can I change the class of an existing host (standard, memory-optimized, burstable)? {#host-class} Yes, you can do this by following the procedure in [Changing cluster settings](../operations/update.md#change-resource-preset). #### Can I change the disk type in an existing cluster? {#disk-type} Yes, you can. To do this, [restore the cluster from a backup](../operations/cluster-backups.md#restore) and specify the required disk type when configuring the new cluster. #### Can I manage a cluster using SQL commands? {#sql-control} Cluster management via SQL commands has certain restrictions. For more information, see [SQL command limits](../concepts/sql-limits.md). #### Can I create a database using SQL commands? {#create-db-sql} No, you cannot create a database via SQL. Managed Service for PostgreSQL clusters only support database creation via Yandex Cloud interfaces. #### How to take a cluster out of read-only mode? {#read-only-exit} Follow the steps described in [Recovering a cluster from read-only mode](../concepts/storage.md#read-only-solutions) and [Manually disable read-only mode for the cluster](../operations/storage-space.md#read-only-solutions). ## Cluster settings {#configuration} #### Is `autovacuum` enabled for all tables by default? {#autovacuum} Yes, `AUTOVACUUM` is enabled for all tables by default. Autovacuuming does not run at a specific time. Instead, it runs when a certain value specified in the [settings](../concepts/settings-list.md#dbms-cluster-settings) is reached, for example, when the share of updated or deleted table records becomes equal to the **Autovacuum vacuum scale factor**. For more information, see [this PostgreSQL guide](https://www.postgresql.org/docs/11/runtime-config-autovacuum.html). #### What are the default values for LC_COLLATE and LC_CTYPE database settings? {#lc-default} The default settings for new databases are `LC_CTYPE=C` and `LC_COLLATE=C`. You cannot change these settings for the database created alongside the cluster. However, you can [create a new database](../operations/databases.md) and specify the required values for it. #### Can I change the LC_COLLATE and LC_CTYPE values? {#lc-edit} Once a database is created, you cannot change its locale settings. However, you can: * [Create](../operations/databases.md#add-db) a new database with the required settings. * Specify the collation locale (`LC_COLLATE`) for the database objects: * When calling a function: ```sql SELECT lower(t1 COLLATE "ru_RU.utf8") FROM test; ``` * When creating or altering a table: ```sql CREATE TABLE test (t1 text COLLATE "ru_RU.utf8"); ``` #### Can I change the database owner? {#db-owner} Once a database is created, you cannot change its owner. Changing the `owner` setting after [creating a database with Terraform](../operations/databases.md#add-db) will trigger database recreation, resulting in data loss. ## Moving and restoring a cluster {#backup} #### Can I restore a cluster backup to a running Managed Service for PostgreSQL instance in another cloud network? {#diff-network} Yes, you can switch to another cloud network when restoring a cluster from a backup. #### Can I change the retention period for automatic backups? {#backup-retain-days} You can set a retention period for automatic backups: * When [creating](../operations/cluster-create.md) or [updating](../operations/update.md#change-additional-settings) a cluster. Possible values: between 7 and 60 days. * When [creating](../operations/backup-retention-policies.md#create-policy) backup policies. Possible values: between 7 and 1095 days (up to three years). #### Do I need the wal2json plugin if I am only doing data replication and not copying? {#wal2json} Yes, you need this plugin even if you do not copy data. #### Can I download a database backup to my local machine? {#backup-local-dump} While Yandex Cloud service does not support local backup downloads, you can use the [pg_dump utility](https://www.postgresql.org/docs/current/app-pgdump.html) as an alternative. #### How do I move a local PostgreSQL database dump to Yandex Cloud? {#dump-to-yc} Follow the steps described in this [Migrating databases tutorial](../tutorials/data-migration.md). #### How do I migrate a database or a table from one cluster to another? {#transfer-db-table} You can migrate a database or a table using Data Transfer or [pg_dump](https://www.postgresql.org/docs/current/app-pgdump.html). For more information on using Data Transfer for migration, see [Database migration](../tutorials/outbound-replication.md). #### How do I transfer data between clusters in different folders or clouds? {#transfer-data} You can transfer data using Data Transfer or [pg_dump](https://www.postgresql.org/docs/current/app-pgdump.html). For more information on using Data Transfer for migration, see [Database migration](../tutorials/outbound-replication.md). #### How do I move a cluster to another cloud? {#transfer-cluster} You can move a cluster using Data Transfer or [pg_dump](https://www.postgresql.org/docs/current/app-pgdump.html). For more information on using Data Transfer for migration, see [Database migration](../tutorials/outbound-replication.md). #### How do I restore a backup to an existing cluster? {#restore-existing-cluster} You can only restore a backup to a new cluster. #### How do I restore a cluster to the state of the selected backup? {#cluster-existing-backup} You can only restore a backup to a new cluster. #### Can I restore a single database from a backup? {#restore-one-database} No, you cannot select specific databases. You can only restore the entire cluster with all its databases. ### Upgrading the PostgreSQL major version {#version-update} #### Do I need to run ANALYZE and/or REINDEX and refresh statistics (pg_stat_user_tables and pg_statistic) manually after upgrading the major PostgreSQL version? {#version-update-manual} You do not need to run `ANALYZE` and/or `REINDEX` and refresh statistics manually after upgrading the major version. This is done automatically after the upgrade. [Learn more about upgrading the major PostgreSQL version in Managed Service for PostgreSQL](../concepts/upgrade.md). #### Does Managed Service for PostgreSQL check that the collation version has changed after upgrading the major PostgreSQL version? {#version-update-collation} `collation` versions do not change when upgrading the major version, so version checks are not performed. #### How do I access logs and generated scripts after upgrading the major PostgreSQL version?{#version-update-get-logs} The process logs are not available to users. If a preliminary check (`--check`) or an upgrade fails, the necessary details are displayed in an error message. #### Can a major version upgrade fail? {#version-update-error} An upgrade may fail due to conflicts in the data schema, incompatibility of extensions, or stored procedures. The specific reason is shown in the error message. ## Users and roles {#users} #### Can I migrate users from a PostgreSQL source cluster to a Managed Service for PostgreSQL destination cluster? {#user-migration} No, you cannot automatically migrate users from a third-party PostgreSQL cluster or Managed Service for PostgreSQL cluster to a Managed Service for PostgreSQL cluster. You will need to [re-create the users](../operations/cluster-users.md#adduser) in the target cluster. #### How to create a role using SQL queries? {#create-role} You cannot create a role using SQL queries. In PostgreSQL, a role is a database user or group of users. In Managed Service for PostgreSQL, you can [create a user](../operations/cluster-users.md#adduser) only via the management console, Yandex Cloud CLI, Terraform, or API. For more information about creating users, see [this PostgreSQL guide](https://www.postgresql.org/docs/current/sql-createuser.html). You can [assign user privileges](../operations/grant.md), which decide the actions the user can perform with database objects. ## High availability and load balancing {#ha} #### How to remove a host from the master selection process? {#excluded-host} You can exclude a host from the master selection when replacing the master host automatically or manually. To do this, set up [cascade replication](../concepts/replication.md#replication-manual): [specify](../operations/hosts.md#update) a replication source for the host you want to exclude. {% note warning %} To ensure [high availability](../../architecture/fault-tolerance.md#mdb-ha) during maintenance, your cluster must have at least one replica without a replication source. {% endnote %} #### Why do I get an error when setting up cascading replication? {#cascade-error} Error message: ```text 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](../../architecture/fault-tolerance.md#mdb-ha), 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. Learn more about replication in [this section](../concepts/replication.md). #### How to ensure I am always connecting to the master host? {#connect-to-master-ha} To connect to the current master host, use a [special FQDN](../operations/connect/fqdn.md#special-fqdns) in the `c-.rw.mdb.yandexcloud.net` format. This FQDN supports read and write access. {% cut "Example command for connecting to the master host" %} ```bash psql "host=c-.rw.mdb.yandexcloud.net \ port=6432 \ sslmode=verify-full \ dbname= \ user=" ``` {% endcut %} #### How to ensure I am always connecting to the most up-to-date replica? {#connect-to-replica-ha} To connect to the most up-to-date replica, use a [special FQDN](../operations/connect/fqdn.md#special-fqdns) in the `c-.ro.mdb.yandexcloud.net` format. This FQDN only supports read access. {% cut "Example command for connecting to a replica" %} ```bash psql "host=c-.ro.mdb.yandexcloud.net \ port=6432 \ sslmode=verify-full \ dbname= \ user=" ``` {% endcut %} If there are no active replicas in the cluster, this FQDN will point to the current master host. #### Why did the master and replicas swap roles? {#failover} This means the master has [failed over](../../architecture/fault-tolerance.md#mdb-ha) to the replica. Automatic master failover guarantees cluster availability during maintenance or in the event of the master host failure. To connect to the current master host, use a [special FQDN](../operations/connect/fqdn.md#special-fqdns). {% note 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. {% endnote %} #### Can I configure multi-master within a single cluster or between two clusters? {#setting-multimaster} No, Managed Service for PostgreSQL does not support multi-master configuration. For more on how to achieve high cluster availability, see [High availability managed databases (MDB)](../../architecture/fault-tolerance.md#mdb-ha). #### How to configure load balancing so that all read requests are redirected to replicas? {#balancing-replicas} As Managed Service for PostgreSQL does not provide load balancing, you need to configure it in your application backend. To send read requests to replicas, your application must first identify the master and replica hosts, e.g., by using `libpq`. For more information, see [this PostgreSQL article](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNECT-TARGET-SESSION-ATTRS). Alternatively, use a [special FQDN](../operations/connect/fqdn.md#fqdn-replica) pointing to the most up-to-date replica. For more information, see [High availability](../concepts/high-availability.md). #### When does automatic failover occur? {#auto-failover} The master host can automatically fail over to another host: * During [cluster updates](../operations/update.md). * On master host failure. * Upon cluster recovery. * During [maintenance](../concepts/high-availability.md#maintenance-settings). Master failover ensures the cluster works correctly under the listed conditions. {% note info %} To ensure [high availability](../concepts/high-availability.md), the cluster always has at least one replica without an explicitly defined [replication source](../concepts/replication.md#replication-manual). This replica can take over as master when needed. {% endnote %} ## Monitoring and logs {#monitoring} #### Where can I track my disk space utilization to avoid exceeding the 95% limit triggering read-only mode? {#disk-space} You can monitor your disk space usage: * In the management console using the [cluster health monitoring tools](../operations/monitoring.md#monitoring-cluster). * In [Yandex Monitoring](https://monitoring.yandex.cloud) that also allows you to [set up alerts](../operations/monitoring.md#monitoring-integration) for specified metrics. #### Are logs stored on the same disk as PostgreSQL data? How are they billed? {#logs-storage} Logs are stored on separate disks from data and are rotated automatically. You can view log information using the following [CLI](../../cli/index.md) command: ```bash yc managed-postgresql cluster list-logs ``` Cluster log storage is free of charge. #### What is the retention period for logs? {#log-keeping} Cluster logs are stored for 45 days. #### What are WALs and what is their purpose? {#wal} [Write-Ahead Logs](https://postgrespro.com/docs/postgresql/12/wal-intro) are used for writing data to the disk and for data replication. WALs are created during write requests and occupy disk space until log data is fully written to host disks, which ensures DBMS reliability and fault tolerance. For more details on how PostgreSQL works, refer to its [official documentation](https://postgrespro.com/docs/postgresql/12). #### What does Cached mean in the RAM monitoring of a PostgreSQL cluster host? {#cached} `Cached` stands for cached data. Most operations are performed in RAM where the entire database working set is stored. Since caching occurs both for databases and the disk itself, the amount of cached data can exceed the storage size. If application RAM consumption increases, some of the memory used for cache can be freed up. For more details on disk data caching in Linux, refer to the relevant [guides](https://www.linuxatemyram.com/). #### How do I get alerts on critical PostgreSQL cluster metrics? {#notifications} Use [Yandex Monitoring](https://monitoring.yandex.cloud) and [set up alerts](../operations/monitoring.md#monitoring-integration) for the metrics critical for you. #### How can I configure an alert for when disk usage reaches a certain percentage? {#disk-space-percentage} [Create an alert](../operations/monitoring.md#monitoring-integration) for the `disk.used_bytes` metric in Yandex Monitoring. This metric shows the disk space usage in a Managed Service for PostgreSQL cluster. For `disk.used_bytes`, use notification thresholds with the following recommended values: * `Alarm`: 90% of disk space. * `Warning`: 80% of disk space. Thresholds are set in bytes only. For example, the recommended values for a 100 GB disk are as follows: * `Alarm`: `96636764160` bytes (90%). * `Warning`: `85899345920` bytes (80%). ## Error messages {#errors} #### Why do I get an error when using the TimescaleDB extension? {#timescale} Error message: ```text 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)](https://www.timescale.com/legal/licenses). 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? {#get-ssl-error} Complete error message: ```text 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. ```powershell 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 `SSL is required` error when connecting? {#ssl-req} This error occurs because you are trying to connect to a cluster with a [publicly accessible host](../concepts/network.md#public-access-to-a-host). Such hosts require an SSL certificate to connect. You can: * [Obtain an SSL certificate](../operations/connect/index.md#get-ssl-cert) and add it to the application you use to connect. * [Disable public access to hosts](../operations/hosts.md#update) and connect to the cluster from a VM located in the same cloud network. #### What should I do if I get a `too many active clients for user` error when connecting? {#connection-limit-error} An attempt to connect to a cluster host may fail with the following error: ```text too many active clients for user (pool_size for user reached ) ``` 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](../concepts/settings-list.md#setting-conn-limit). For instructions on updating PostgreSQL settings at the user level, see [this guide](../operations/cluster-users.md#update-settings). #### Why do I get an error when connecting to a custom database? {#database-error} An attempt to connect to a custom database may fail with the following error: ```text ERROR: odyssey: ce3ea075f4ffa: route for '.' 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? {#dump-error} You can get this error when creating a dump with `pg_dumpall`: ```text ERROR: odyssey: c16b9035a1f78: route for 'template1.' 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? {#database-postgres-error} An attempt to connect to the `postgres` database may fail with the following error: ```text ERROR: odyssey: c76e2c1283a7a: route for 'postgres.' 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 `terminating connection due to administrator command` message? {#connection-error} A Managed Service for PostgreSQL cluster connection may be terminated with the following message: ```text 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](../concepts/settings-list.md#setting-session-duration-timeout) setting (12 hours by default). #### Why cannot I connect to cluster hosts? {#host-error} An attempt to connect to a cluster’s host may fail with the following error: ```text could not translate host name "" 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](../operations/connect/fqdn.md#special-fqdns), enable public access for the host associated with that FQDN. {% note 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. {% endnote %} * 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 do I get an error when stopping the cluster? {#stop-cluster-error} Error variations: ```text ERROR: rpc error: code = FailedPrecondition desc = Cluster has no backups ``` ```text cluster has no backups. If you want to stop the cluster, make a backup ``` The error occurs because you can [stop](../operations/cluster-stop.md#stop-cluster) a Managed Service for PostgreSQL cluster only if there is at least one backup of the cluster. Solution: Wait until an automatic backup is created or [create one manually](../operations/cluster-backups.md#create-backup). #### Why do I get a `max_connections is less than sum of users connection limit` error when modifying a cluster? {#max-connections-error} 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](../concepts/settings-list.md#setting-max-connections)). Solution: First reduce the limits set for users so their sum is less than ` — 15` and then proceed with host class downgrade. #### Why do I get an error when transferring data by creating and restoring a logical dump? {#backup-error} [Restoring a logical dump](../tutorials/data-migration.md#backup) may fail with one of the following errors: * `ERROR: role "" does not exist` * `ERROR: must be member of role ""` 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](../operations/cluster-users.md#adduser) with access to the migrated database and the same name as the user who created the logical dump in the source cluster. 1. As this user, [restore the logical dump](../tutorials/data-migration.md#restore) or [grant their privileges](../operations/grant.md#grant-privilege) 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? {#repl-slot-exists} By default, the system creates a replication slot when you [create a subscription](../../tutorials/dataplatform/postgresql-data-migration.md#create-publication-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. 1. [Delete the existing replication slot](../operations/replication-slots.md#delete) 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? {#extension-is-not-available} Error message: ```text extension "" 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](../operations/extensions/cluster-extensions.md). 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. 1. [Install](../operations/extensions/cluster-extensions.md#update-extensions) 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? {#owner-of-extension} [Restoring a logical dump](../tutorials/data-migration.md#backup) 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](../operations/extensions/cluster-extensions.md). In Managed Service for PostgreSQL clusters, you cannot manage extensions using SQL commands. To fix this error: 1. Before restoring the dump, [enable](../operations/extensions/cluster-extensions.md#update-extensions) all required extensions in the target database. 1. Remove any operations with extensions from the dump. For example, comment out the lines related to installing extensions. 1. Perform logical dump recovery again. #### Why do I get an error when setting up cascading replication? {#cascade-errors} Error message: ```text 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](../../architecture/fault-tolerance.md#mdb-ha), 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. Learn more about replication in [this section](../concepts/replication.md). #### Why do I get a `cannot execute in a read-only transaction` error? {#read-only-error} Error options: ```text ERROR: cannot execute ALTER EXTENSION in a read-only transaction ``` ```text ERROR: cannot execute CREATE TABLE in a read-only transaction ``` ```text ERROR: cannot execute UPDATE in a read-only transaction ``` ```text ERROR: cannot execute INSERT in a read-only transaction ``` Such errors can occur after master [failover](../../architecture/fault-tolerance.md#mdb-ha) 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](../operations/connect/fqdn.md#special-fqdns) that always points to the current master. {% note 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. {% endnote %} * 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](../operations/connect/fqdn.md#automatic-master-host-selection). #### What should I do if I see a `too many connections for role "monitor"` error in the logs? {#monitor-role-error} 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? {#cli-extensions-errors} 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: ```bash yc managed-postgresql database update db1 --cluster-id cat0adul1fj0******** --extensions cube,pg_logic,timescaledb ``` * `ERROR: rpc error: code = InvalidArgument desc = Invalid extensions '', allowed extension: ` 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](../operations/extensions/cluster-extensions.md#postgresql). * `ERROR: rpc error: code = InvalidArgument desc = The specified extension '' 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](../operations/extensions/cluster-extensions.md#postgresql). To add the required library, when [updating the PostgreSQL cluster settings](../operations/update.md#change-postgresql-config), specify its name in the [Shared preload libraries parameter](../concepts/settings-list.md#setting-shared-libraries). #### Why do I get the `could not open extension control file "/vector.control": No such file or directory` error when accessing the `vector` extension? {#vector-error} The error occurs because the `vector` extension name in Managed Service for PostgreSQL is `pgvector`. Solution: when accessing the extension, use `pgvector` instead of `vector`.