Connecting to a database in a PostgreSQL cluster

In Yandex.Cloud, you can connect to a DB cluster only from a VM that has an address in the the Yandex.Cloud subnet.

Authentication

Managed Service for PostgreSQL clusters only support encrypted connections, which is why an SSL certificate is required to connect to them. You can prepare all the necessary authentication data as follows:

$ mkdir ~/.postgresql
$ wget "https://storage.yandexcloud.net/cloud-certs/CA.pem" -O ~/.postgresql/CA.pem

Read about using certificates with libpq in the PostgreSQL documentation.

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 username>"

Automatic master host selection

Using libpq

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.

With a driver that supports only one host

If your database connection driver doesn't allow passing multiple hosts in the connection string (for example, pgx in Go), you can connect to a special host like c-<cluster ID>.rw.mdb.yandexcloud.net.

This domain name always indicates the current master in the cluster. For example, you can connect to the master of the cluster with the c9qash3nb1v9ulc8j9nm ID as follows:

$ psql "host=c-c9qash3nb1v9ulc8j9nm.rw.mdb.yandexcloud.net \
      port=6432 \
      sslmode=verify-full \
      dbname=<DB name> \
      user=<DB username>"