Connecting to a database in a cluster PostgreSQL

Authentication
Connection string
Automatic master host selection
- Using libpq
- With a driver that supports only one host

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/cloud-certs/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>"

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 does not 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 user name>"