Connecting to a database in a cluster PostgreSQL

Inside Yandex.Cloud, you can connect to a DB cluster only from a VM whose address is in the same Cloud subnet.

Authentication

PostgreSQL clusters in Managed Service for PostgreSQL support only encrypted connections. Therefore, an SSL certificate is required to connect to such a cluster. You can prepare all the necessary authentication data as follows:

$ mkdir ~/.postgresql
$ wget "https://storage.yandexcloud.net/mdb/CA.pem" -O ~/.postgresql/root.crt
$ chmod 0600 ~/.postgresql/root.crt

For information about how to use a certificate via libpq, read the documentation on PostgreSQL.

Connection string

Now you can connect to the database using the psql command:

psql "host=<DB host address> \
      port=6432 \
      sslmode=verify-full \
      dbname=<DB name> \
      user=<DB user name>"

To guarantee a connection to the master host, specify the addresses of all the cluster hosts in the host argument and pass the target_session_attrs=read-write parameter. This parameter is supported by the libpq library starting from version 10:

psql " host=<address of host 1>,<address of host 2>,<address of host 3> \
      port=6432 \
      sslmode=verify-full \
      dbname=<DB name> \
      user= \
      target_session_attrs=read-write"

To upgrade the library version used by the psql utility:

• For Debian-based Linux distributions, install the postgresql-client-10 package (for example, using an APT repository).
• For operating systems that use RPM packages, a PostgreSQL distribution is available from a YUM repository.

You can find the addresses of all the hosts in the DB cluster on the appropriate cluster page in the management console.