Connecting to a PostgreSQL cluster

• Is the cluster accessible from inside Yandex Cloud?

• Can I connect to the cluster from a Docker container?

• How do I obtain an SSL certificate to connect to Managed Service for PostgreSQL on Windows?

• How do I install an SSL certificate to connect Power BI to Managed Service for PostgreSQL using psql?

• Can I connect to cluster hosts over SSH?

• What is the maximum allowed number of concurrent connections to a single host in Managed Service for PostgreSQL?

• What is the allowed number of connections per user?

• Why does an error occur when trying to connect to a database from Looker Studio?

• How do I always connect to the master host?

• How do I always connect to the most recent replica?

• How do I connect to the postgres database?

• How do I get the postgres user password?

Can I access a cluster from within Yandex Cloud?Can I access a cluster from within Yandex Cloud?

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 Yandex Cloud virtual machines located in the same cloud network. If the host is not publicly accessible, there is no need to use SSL for connections from such virtual machines.
• From a container in Yandex Serverless Containers. If the host is not publicly accessible, the container must be located in the same cloud network.

For more information, see the service documentation.

Can I connect to a cluster from a Docker container?Can I connect to a cluster from a Docker container?

Yes, you can. To do this, configure the Dockerfile.

You can find an example of connection from a container in Yandex Serverless Containers in this tutorial.

How do I obtain an SSL certificate to connect to Managed Service for PostgreSQL on Windows?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\.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 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?How do I install an SSL certificate to connect Power BI to Managed Service for PostgreSQL via psql?

1. Install Windows Subsystem for Linux (WSL) and run the following command in the terminal:

   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 available at C:\temp\CA.pfx.

2. Place the obtained certificate in the Windows certificate store.

Can I connect to cluster hosts over SSH?Can I connect to cluster hosts over 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 to a single host in Managed Service for PostgreSQL?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?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 PostgreSQL settings at the user level, see this tutorial.

Why do I get an error when trying to connect to a database from Looker Studio?Why do I get an error 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.

How do I always connect to the master host?How do I always connect to the master host?

To connect to the current master host, use a special FQDN. It has this format: c-<cluster_ID>.rw.mdb.yandexcloud.net. When connected to this FQDN, you will be able to perform read and write operations.

psql "host=c-<cluster_ID>.rw.mdb.yandexcloud.net \
      port=6432 \
      sslmode=verify-full \
      dbname=<DB_name> \
      user=<username>"

How do I always connect to the most recent replica?How do I always connect to the most recent replica?

To connect to the most recent replica, use a special FQDN. It has this format: c-<cluster_ID>.ro.mdb.yandexcloud.net. When connected to this FQDN, you can perform only read operations.

psql "host=c-<cluster_ID>.ro.mdb.yandexcloud.net \
      port=6432 \
      sslmode=verify-full \
      dbname=<DB_name> \
      user=<username>"

If there are no active replicas in the cluster, this FQDN will point to the current master host.

How do I connect to the database?How do I connect to the postgres database?

postgres is a housekeeping database. For security reasons, you cannot connect to housekeeping databases.

How do I get the user's password?How do I get the postgres user's password?

For security reasons, you cannot get the postgres user's password nor connect to the database as that user.