Connecting to a PostgreSQL cluster

• Can I access the cluster from within Yandex Cloud?

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

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

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

• Can I connect to cluster hosts over SSH?

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

• What is the maximum allowed number of connections per user?

• Why am I getting an error when trying to connect to a database from Looker Studio?

• How to ensure I am always connecting to the master host?

• How to ensure I am always connecting to the most up-to-date 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:

• Via the internet, if you configured public access for these hosts. These hosts can only be accessed 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. For hosts without public access, this 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.

See a Yandex Serverless Containers container connection example in this tutorial.

How do I obtain an SSL certificate for connecting to Managed Service for PostgreSQL on Windows?How do I obtain an SSL certificate for connecting 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 details on 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 located at C:\temp\CA.pfx.

2. Install 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 per host in Managed Service for PostgreSQL?What is the maximum allowed number of concurrent connections per host in Managed Service for PostgreSQL?

The number of concurrent connections is configured at the cluster level using the Max connections setting. By default it is set to the maximum value calculated using the following formula:

200 × <number_of_vCPUs_per_host>

For instructions on updating PostgreSQL settings at the cluster level, see this guide.

What is the maximum allowed number of connections per user?What is the maximum allowed number of connections per user?

By default, a cluster reserves 50 connections per host for each user. You can change this number in the Conn limit setting.

If the user’s connection limit is reached, new connection attempts will fail with the following error:

too many active clients for user (pool_size for user <username> reached <limit_value>)

For instructions on updating PostgreSQL settings at the user level, see this guide.

Why am I getting an error when trying to connect to a database from Looker Studio?Why am I getting an error when trying to connect to a database from Looker Studio?

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 more information, see Connecting from Looker Studio.

How to ensure I am always connecting to the master host?How to ensure I am always connecting to the master host?

To connect to the current master host, use a special FQDN in the c-<cluster_ID>.rw.mdb.yandexcloud.net format. This FQDN supports read and write access.

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

How to ensure I am always connecting to the most up-to-date replica?How to ensure I am always connecting to the most up-to-date replica?

To connect to the most up-to-date replica, use a special FQDN in the c-<cluster_ID>.ro.mdb.yandexcloud.net format. This FQDN only supports read access.

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 postgres database?How do I connect to the postgres database?

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

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

For security reasons, the system does not permit obtaining the postgres user's password to connect to the database on their behalf.