General questions about Managed Service for PostgreSQL

• What is Managed Service for PostgreSQL?

• What part of database management and maintenance is Managed Service for PostgreSQL responsible for?

• Which tasks are best addressed using Managed Service for PostgreSQL, and which using VMs with databases?

• What is a database host and database cluster?

• How do I get started with Managed Service for PostgreSQL?

• How many database hosts can a cluster contain?

• How can I access a running database host?

• How many clusters can I create within a single cloud?

• How are DB clusters maintained?

• Which PostgreSQL version does Managed Service for PostgreSQL use?

• Which PostgreSQL version and settings are best when creating a database for 1С?

• What happens when a new DBMS version is released?

• What happens when a DBMS version becomes deprecated?

• How is usage cost calculated for a database host?

• How can I change the computing resources and storage size for a database cluster?

• Can I configure auto increase of cluster storage size?

• Is database host backup enabled by default?

• When are backups performed? Is a database cluster available during backup?

• Are PostgreSQL database cluster connections encrypted?

• What is a read-only replica in PostgreSQL?

• What metrics and processes can be tracked using monitoring?

• How do I get the logs of my actions in the services?

• What limitations are imposed on PostgreSQL database clusters?

• What PostgreSQL extensions are supported in Managed Service for PostgreSQL?

• Which data center stores PostgreSQL cluster backups?

• Can I get superuser privileges in PostgreSQL?

• Can I copy data from a Managed Service for PostgreSQL table to a local file? Сan I populate a table with data from a local file?

• Are there any special aspects or restrictions on the use of a garbage collector in Managed Service for PostgreSQL clusters?

• Why is the cluster slow even though the computing resources are not used fully?

What is Managed Service for PostgreSQL?What is Managed Service for PostgreSQL?

Managed Service for PostgreSQL is a service that helps you create, operate, and scale PostgreSQL databases in a cloud infrastructure.

With Managed Service for PostgreSQL, you can:

• Create a database with the required performance characteristics.
• Scale processing power and storage dedicated for your databases as needed.
• Get database logs.

Managed Service for PostgreSQL takes on time-consuming PostgreSQL infrastructure administration tasks:

• Monitors resource usage.
• Automatically creates DB backups.
• Provides fault tolerance through automatic failover to backup replicas.
• Keeps database software updated.

You interact with database clusters in Managed Service for PostgreSQL the same way you interact with regular databases in your local infrastructure. This allows you to manage internal database settings to meet your app requirements.

What part of database management and maintenance is Managed Service for PostgreSQL responsible for?What part of database management and maintenance is Managed Service for PostgreSQL responsible for?

When creating clusters, Managed Service for PostgreSQL allocates resources, installs the DBMS, and creates databases.

For the created and running databases, Managed Service for PostgreSQL automatically creates backups and applies fixes and updates to the DBMS.

Managed Service for PostgreSQL also allows you to replicate data between database hosts (both within and across availability zones) and automatically routes the load to a backup replica in the event of a failure.

Which tasks are best addressed using Managed Service for PostgreSQL, and which using VMs with databases?Which tasks are best addressed using Managed Service for PostgreSQL, and which using VMs with databases?

Yandex Cloud offers two ways to work with databases:

• Managed Service for PostgreSQL allows you to operate template databases with no need to worry about administration.
• Yandex Compute Cloud virtual machines allow you to create and configure your own databases. This approach allows you to use any database management systems, access databases via SSH, etc.

What is a database host and database cluster?What is a database host and database cluster?

A database host is an isolated database environment in the cloud infrastructure with dedicated computing resources and reserved data storage.

A database cluster is one or more database hosts between which replication can be configured.

How do I get started with Managed Service for PostgreSQL?How do I get started with Managed Service for PostgreSQL?

Managed Service for PostgreSQL is available to any registered Yandex Cloud user.

To create a database cluster in Managed Service for PostgreSQL, you must define its characteristics:

• Host class (performance characteristics, such as CPUs, memory, etc.).
• Storage size (reserved in full when you create the cluster).
• Network your cluster will be connected to.
• Number of hosts for the cluster and the availability zone for each host.

For a detailed guide, see Getting started with Managed Service for PostgreSQL.

How many DB hosts can a cluster contain?How many DB hosts can a cluster contain?

The minimum number of hosts depends on the selected type of storage:

• If you use non-replicated SSD (network-ssd-nonreplicated) or local SSD storage (local-ssd), the minimum number of hosts is 3.
• If using network SSD (network-ssd) or network HDD (network-hdd) storage, you can create single-host clusters.

The maximum number of hosts in a cluster is only limited by the requested computing resources and the size of the storage for the cluster.

For more information, see Quotas and limits in Managed Service for PostgreSQL.

How can I access a running DB host?How can I access a running DB host?

You can connect to Managed Service for PostgreSQL databases using standard DBMS methods.

Learn more about connecting to clusters.

How many clusters can I create within a single cloud?How many clusters can I create within a single cloud?

You can find MDB technical and organizational limitations in Quotas and limits in Managed Service for PostgreSQL.

How are DB clusters maintained?How are DB clusters maintained?

Maintenance in Managed Service for PostgreSQL implies:

• Automatic installation of DBMS updates and revisions for DB hosts (including disabled clusters).
• Changes to the host class and storage size.
• Other Managed Service for PostgreSQL maintenance activities.

For more information, see Maintenance in Managed Service for PostgreSQL.

Which PostgreSQL version does Managed Service for PostgreSQL use?Which PostgreSQL version does Managed Service for PostgreSQL use?

Managed Service for PostgreSQL supports PostgreSQL 11, 12, 13, 14, 15, and 16, as well as PostgreSQL 11, 12, 13, 14, and 15 for 1C.

Which PostgreSQL version and settings are best when creating a database for 1C?Which PostgreSQL version and settings are best when creating a database for 1C?

We recommend using PostgreSQL version 12-1c (current as of March 2022). This version was tested with 1C version 8.3.18: compatibility with earlier 1C versions is not guaranteed.

Please contact 1C tech support for settings optimized for cluster operation.

What happens when a new DBMS version is released?What happens when a new DBMS version is released?

The database software is updated when new minor versions are released. Owners of the affected DB clusters are notified of expected work times and DB availability in advance.

What happens when a DBMS version becomes deprecated?What happens when a DBMS version becomes deprecated?

One month after the database version becomes deprecated, Managed Service for PostgreSQL automatically sends email notifications to the owners of DB clusters created with this version.

New hosts can no longer be created using deprecated DBMS versions. Database clusters are automatically upgraded to the next supported version seven days after notification for minor versions and one month after notification for major versions. Deprecated major versions are going to be upgraded even if you have disabled automatic updates.

How do you calculate usage cost for a database host?How do you calculate usage cost for a database host?

In Managed Service for PostgreSQL, the usage cost is calculated based on the following parameters:

• Selected host class.
• Size of the storage reserved for the database host.
• Size of the database cluster backups. Backup space in the amount of the reserved storage is free of charge. Backup storage that exceeds this size is charged at special rates.
• Number of hours of database host operation. Partial hours are rounded to an integer value. The cost per hour of operation for each host class is given in Managed Service for PostgreSQL pricing policy.

How can I change the computing resources and storage size for a database cluster?How can I change the computing resources and storage size for a database cluster?

You can change computing resources and storage size in the management console. All you need to do is choose a different host class for the required cluster.

The cluster characteristics change within 30 minutes. During this period, other maintenance activities may also be enabled for the cluster, such as installing updates.

Can I configure auto increase of cluster storage size?Can I configure auto increase of cluster storage size?

Yes, you can set up automatic increase of the storage size when creating or updating a cluster.

Is DB host backup enabled by default?Is DB host backup enabled by default?

Yes, backup is enabled by default. For PostgreSQL, a full backup takes place once a day and saves all DB cluster transaction logs. This allows you to restore the cluster state to any point in time during the backup storage period, except for the last 30 seconds.

By default, backups are stored for seven days.

When are backups performed? Is a DB cluster available during backup?When are backups performed? Is a DB cluster available during backup?

The backup window is an interval during which a full daily backup of the DB cluster is performed. The backup window is from 01:00 to 05:00 (UTC+3).

Clusters remain fully accessible during the backup window.

Are PostgreSQL database cluster connections encrypted?Are PostgreSQL database cluster connections encrypted?

Connections between a database cluster and an application are always encrypted using SSL. You cannot disable cluster connection encryption.

What is a read-only replica in PostgreSQL?What is a read-only replica in PostgreSQL?

A read-only replica is a host in a PostgreSQL DB cluster that can only be read. Its data is synced with the master host (applies only if the cluster has more than 1 host). You can use a read-only replica to reduce the load on the DB master host with a large number of read requests.

What metrics and processes can be tracked using monitoring?What metrics and processes can be tracked using monitoring?

For all DBMS types, you can track:

• CPU, memory, network, or disk usage, in absolute terms.
• Amount of data in the DB cluster and the remaining free space in the data storage.

For DB hosts, you can track metrics specific to the corresponding type of DBMS. For example, for PostgreSQL, you can track:

• Average query execution time.
• Number of requests per second.
• Number of errors in logs, etc.

Monitoring can be performed with a minimum granularity of 5 seconds.

Can I get logs of my operations with services?Can I get logs of my operations with services?

Yes, you can request log records about your resources from Yandex Cloud services. For more information, see Data requests.

What limitations are imposed on PostgreSQL database clusters?What limitations are imposed on PostgreSQL database clusters?

For more information about Managed Service for PostgreSQL limits, see Quotas and limits in Managed Service for PostgreSQL. Characteristics of clusters that can be created using Managed Service for PostgreSQL are given in PostgreSQL host classes.

What PostgreSQL extensions are supported in Managed Service for PostgreSQL?What PostgreSQL extensions are supported in Managed Service for PostgreSQL?

The list of supported PostgreSQL extensions is provided in Managing extensions.

Which data center stores PostgreSQL cluster backups?Which data center stores PostgreSQL cluster backups?

Cluster backups are stored and available in all three data centers.

Does the service meet the requirements under Russian Federation Federal Law No. 152-FZ on Personal Data?Does the service meet the requirements under Russian Federation Federal Law No. 152-FZ on Personal Data?

Yes, it does. You can read the full security audit conclusion.

Can I get superuser privileges in PostgreSQL?Can I get superuser privileges in PostgreSQL?

No, you cannot. Superuser privileges are not available to Managed Service for PostgreSQL users. The highest privileges for working with clusters are granted to users with the 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?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?

Yes, you can both copy data from a table to a local file and populate a table with data from a local file. For more information, see Copying and populating a table from a local machine.

Are there any special aspects or restrictions on the use of a garbage collector in Managed Service for PostgreSQL clusters?Are there any special aspects or restrictions on the use of a garbage collector in Managed Service for PostgreSQL clusters?

Managed Service for PostgreSQL clusters support all parameters of the VACUUM command. However, you should consider the following specifics when using them:

• To run VACUUM FULL, the user must have the mdb_admin role. The VACUUM FULL command does not affect system views.
• In PostgreSQL version 14, the functionality of the INDEX_CLEANUP parameter is enhanced: it now has the AUTO value by default. This means that the VACUUM command skips index cleanup if it is considered unfeasible. To ensure backward compatibility with the previous PostgreSQL versions, set INDEX_CLEANUP to ON.

Why is the cluster slow even though the computing resources are not used fully?Why is the cluster slow even though the computing resources are not used fully?

Perhaps, the maximum storage IOPS and bandwidth values are insufficient for processing the current number of requests. In this case, throttling is triggered and the performance of the entire cluster degrades.

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	93	28,000/5,600	110/82

To increase the maximum IOPS and bandwidth values and make throttling less likely, increase the storage size when you update your cluster.

If you are using the network-hdd storage type, consider switching to network-ssd or network-ssd-nonreplicated by restoring the cluster from a backup.