Questions and answers about Managed Service for PostgreSQL
General questions
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's requirements.
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 provides data replication between database hosts (both inside and between availability zones) and automatically switches the load over to a backup replica in the event of a failure.
Which tasks should I use Managed Service for PostgreSQL for and for which VMs with databases?
Nebius AI offers two ways to work with databases:
- Managed Service for PostgreSQL allows you to operate template databases with no need to worry about administration.
- 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?
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?
Managed Service for PostgreSQL is available to any registered Nebius AI user.
To create a database cluster in Managed Service for PostgreSQL, you must define its characteristics:
- Host class (performance characteristics such as CPUs, memory, and so on).
- Storage size (reserved in full when you create the cluster).
- The network your cluster will be connected to.
- The number of hosts for the cluster and the availability zone for each host.
For detailed instructions, see Getting started with Managed Service for PostgreSQL.
How many DB hosts can a cluster contain?
Minimum number of hosts: one.
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?
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?
MDB technical and organizational limits are given in Quotas and limits in Managed Service for PostgreSQL.
How do I maintain database clusters?
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 version of PostgreSQL does Managed Service for PostgreSQL use?
Managed Service for PostgreSQL supports PostgreSQL 14, 15, and 16.
What happens when a new DBMS version is released?
The database software is updated when new minor versions are released. The owners of the affected DB clusters receive advanced notice of expected work times and DB availability.
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 for major versions. Deprecated major versions are upgraded even if you disabled automatic updates.
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.
Is DB host backup enabled by default?
Yes, backup is enabled by default. For PostgreSQL, a full backup is performed once a day, saving all the database 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?
The backup window is an interval during which a full daily backup of the DB cluster is performed. The backup window is from 22:00 to 02:00 (UTC+00:00).
Clusters remain fully accessible during the backup window.
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?
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?
For all DBMS types, you can track:
- CPU, memory, network, or disk usage, in absolute terms.
- The amount of data in the DB cluster and the remaining free space in 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 queries per second
- Number of errors in logs.
Monitoring can be performed with a minimum granularity of 5 seconds.
Can I get logs of my operations with services?
Yes, you can request log records about your resources from Nebius AI services. For more information, see Data requests.
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?
The list of supported PostgreSQL extensions is provided in Managing extensions.
Which data center stores PostgreSQL cluster backups?
Cluster backups are stored and available in all three data centers.
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.
Are there any specifics of or restrictions for using the garbage collector in Managed Service for PostgreSQL clusters?
Managed Service for PostgreSQL clusters support all parameters of the VACUUM command
- To run
VACUUM FULL
, the user must have themdb_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 theAUTO
value by default. This means that theVACUUM
command skips index cleanup if it is considered unfeasible. To ensure backward compatibility with the previous PostgreSQL versions, setINDEX_CLEANUP
toON
.
Why is a cluster working slowly even though it still has free computing resources?
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 |
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
by restoring the cluster from a backup.
Connection
Is the cluster accessible from inside Nebius AI?
You can connect to Managed Service for PostgreSQL cluster hosts:
- Over the internet, if you configured public access for the appropriate host. You can only connect to such hosts over an SSL connection.
- From Nebius AI virtual machines located in the same cloud network. If there is no public access to a host, using SSL for connections from such virtual machines is not required.
For more information, see the service documentation.
Can I connect to a cluster from a Docker container?
Yes, you can. To do this, configure the Dockerfile.
How do I obtain an SSL certificate to connect to Managed Service for PostgreSQL on Windows?
You can obtain an SSL certificate using PowerShell:
mkdir $HOME\AppData\Roaming\postgresql; `
curl.exe -o $HOME\AppData\Roaming\postgresql\root.crt https://storage.nemax.nebius.cloud/certs/CA.pem
The certificate will be available at $HOME\AppData\Roaming\postgresql\root.crt
.
For more information about obtaining a certificate and connecting to a database, see the service documentation.
How do I install an SSL certificate to connect Power BI to Managed Service for PostgreSQL via psql?
-
Install Windows Subsystem for Linux
(WSL) and run the following command in the terminal window:mkdir /mnt/c/temp && \ curl "https://storage.nemax.nebius.cloud/certs/CA.pem" -o /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 available at
C:\temp\CA.pfx
. -
Place the obtained certificate in the Windows certificate store
.
What should I do if I get the "SSL is required" error?
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.
You can obtain an SSL certificate and add it to the application you are using to connect to the cluster.
What is the maximum allowed number of concurrent connections to a single host in Managed Service for PostgreSQL?
The number of concurrent connections is specified at the cluster level in the Max connections setting. By default, the maximum value is set, which is calculated by the following formula:
200 × <number of vCPUs per host>
For information about how to update the PostgreSQL settings at the cluster level, see our documentation.
What is the allowed number of connections per user?
By default, a cluster reserves 50 connections to each host per user. You can change this number in the Conn limit setting.
If the connection limit per user is reached, any attempt to establish a new connection will fail with the following error:
too many active clients for user (pool_size for user <username> reached <limit value>)
To learn how to update the PostgreSQL settings at the user level, see our documentation.
Why does an error occur when trying to connect to a database from Looker Studio?
To connect from Looker Studio, be sure to generate a client certificate file and a private key and specify them in the connection settings. For more information about how to do this, see Connecting from Looker Studio.
Why does a connection fail?
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).
Editing clusters
Why did the cluster change its status to Updating during an unscheduled backup?
The cluster changes its status to Updating right after the backup process is launched. You cannot cancel this operation. Wait for it to complete instead.
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 is about 100 Mbps.
Can I create two PostgreSQL databases at the same time?
You cannot apply multiple transactions to a cluster at the same time. It is an architectural feature of the technologies utilized by Nebius AI.
Can I change the number of connections available to a user?
Yes, you can. To do this, change the values of the relevant settings:
- Conn limit: The maximum number of host connections per user. The default is 50. The minimum is 10.
- Max connections: The maximum number of connections reserved for all users. The default is
200 × <number of vCPUs on each host>
. You have to keep in mind that this number includes 15 service connections: for instance, if a cluster has"max_connections": 100
, you are only able to reserve at most 85 connections per cluster host for users.
Can I change an existing host's class (standard, memory-optimized, burstable)?
Yes, you can by following the procedure in Changing cluster settings.
Can I change the disk type in an existing cluster?
Yes, you can. To do this, restore the cluster from a backup and specify the desired disk type when configuring the new cluster.
Can I manage a cluster using SQL commands?
There are some restrictions for cluster management using SQL commands. For more information, see SQL command limits.
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.
Cluster configuration
Is the autovacuum setting enabled for all tables by default?
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 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 the PostgreSQL documentation
Which LC_COLLATE and LC_CTYPE values are set for databases by default?
As databases are created, LC_CTYPE=C
and LC_COLLATE=C
are set by default. You can't change these settings for the database you create with clusters. However, you can create a new database and set the values you need for it.
Can I change the LC_COLLATE and LC_CTYPE values?
You cannot change locale settings after you create a database. You can:
- Create a new database with the desired settings.
- Set a sorting locale (
LC_COLLATE
) for elements of an existing database:- When calling a function:
SELECT lower(t1 COLLATE "en_US.utf8") FROM test;
- When creating and updating a table:
CREATE TABLE test (t1 text COLLATE "en_US.utf8");
- When calling a function:
Can I change the DB owner?
Once you create a DB, you cannot change its owner.
Moving and restoring a cluster
Can I restore a cluster backup to a running Managed Service for PostgreSQL instance on a different cloud network?
You can only recover a cluster from a backup within a single cloud network.
To migrate data between cloud networks, follow the migration instructions.
Do I need the wal2json plugin if there is only data replication and no copying?
Yes, you need the plugin even if you do not copy data.
Can I make a local dump of a database backup?
You cannot use the service to make a local dump of a DB backup from Nebius AI but you can use the pg_dump utility
How do I move a local PostgreSQL database dump to Nebius AI?
Follow the instructions in Migrating databases.
How do I import data to a PostgreSQL database cluster in Managed Service for PostgreSQL?
Use the pg_dump
built-in tool to migrate data to a PostgreSQL cluster in Managed Service for PostgreSQL.
Why can 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:
- 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.
- Use this user to restore the logical dump or grant their privileges to the user who is restoring the logical dump.
Monitoring and logs
Where can I track my use of disk space to avoid exceeding the 95% read-only limit?
You can track your disk space in the management console using cluster status monitoring tools.
Are logs stored on the same disk as PostgreSQL data?
Logs are stored on different disks than data and rotated automatically. You can view log information using the CLI command below:
ncp managed-postgresql cluster list-logs <cluster ID>
What is the retention period for logs?
Cluster logs are stored for 30 days.
What is WAL and what is it used for?
Write-Ahead Logs
For more information about PostgreSQL, see the official documentation
What does the Cached parameter mean for PostgreSQL cluster host RAM monitoring?
Cached stores cached data. Most operations are performed in random-access memory, since this is where all database information is stored. Caching is applied both to databases and to disks, which may cause the amount of cached data to exceed the storage size.
If the amount of RAM used by applications increases, some of the cache memory may be released.
For more information about caching disk data in Linux, see the documentation