Connecting to a database in a PostgreSQL cluster

You can connect to Managed Service for PostgreSQL cluster hosts:

• Via the internet, if you configured public access for the host. You can connect over the internet in the following ways:

  • Using an SSL connection.
  • Using IAM authentication.

• 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 the Serverless Containers container. For hosts without public access, the container must reside in the same cloud network.

Configuring security groupsConfiguring security groups

To connect to a cluster, security groups must include rules allowing traffic from certain ports, IP addresses, or from other security groups.

Rule settings depend on the chosen connection method:

For more information about security groups, see Security groups.

Obtaining an SSL certificateObtaining an SSL certificate

Publicly accessible PostgreSQL hosts only support encrypted connections. You must obtain an SSL certificate to connect to them:

mkdir -p ~/.postgresql && \
wget "https://storage.yandexcloud.net/cloud-certs/CA.pem" \
     --output-document ~/.postgresql/root.crt && \
chmod 0655 ~/.postgresql/root.crt

mkdir $HOME\.postgresql; curl.exe -o $HOME\.postgresql\root.crt https://storage.yandexcloud.net/cloud-certs/CA.pem

To use graphical IDEs, save a certificate to a local folder and specify the path to it in the connection settings.

PostgreSQL host FQDNPostgreSQL host FQDN

To connect to a host, you need its fully qualified domain name (FQDN). You can get it using one of the following methods:

• Request a list of cluster hosts.

• In the management console, copy the command for connecting to the cluster (it contains the host’s FQDN). To get the command, go to the cluster page and click Connect.

• Look up the FQDN in the management console:

  1. Navigate to the cluster page.
  2. Navigate to Hosts.
  3. Copy the Host FQDN column value.

Cluster hosts also use special FQDNs.

Special FQDNsSpecial FQDNs

Alongside regular FQDNs, Managed Service for PostgreSQL offers special FQDNs that can be also used for cluster connections.

In multiple-host clusters, special FQDNs may for some time (up to 10 minutes) point to the old host, even if it has changed its role (e.g., from a master to a replica). If using a special FQDN which points to the current master, some write requests may fail if routed to the replica. This is because it takes time to update DNS records for special FQDNs. If your write request returns an error, repeat it later.

Current masterCurrent master

An FQDN in c-<cluster_ID>.rw.mdb.yandexcloud.net format always points to the current master host in the cluster. You can get the cluster ID from the folder’s cluster list.

When connecting to this FQDN, you can perform read and write operations.

Most recent replicaMost recent replica

An FQDN in c-<cluster_ID>.ro.mdb.yandexcloud.net format points to the most up-to-date replica. You can get the cluster ID with the list of clusters in the folder.

Specifics:

• When connecting to this FQDN, you can only perform read operations.
• If there are no active replicas in the cluster, this FQDN will point to the current master host.
• Replicas with a manually configured replication source cannot be selected as most up-to-date replicas when using this FQDN.

Selecting an FQDN and cluster connection methodSelecting an FQDN and cluster connection method

You can connect to a cluster using its host FQDNs or special FQDNs. If the cluster consists of several hosts, keep in mind that the current master can become a replica at any moment, and vice versa.

Use one of the following methods to connect to the master host with read and write access:

• Connect using the special FQDN pointing to the current master.

  After a master failover, this FQDN may temporarily point to the previous master, now a replica, due to the time required for the DNS record to update.

  Therefore, applications using this FQDN must be designed to handle temporary master unavailability. For example, they should retry write requests after a short delay.

  Connection example

  In this example, we use the c9qash3nb1v9******** cluster ID:

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

• Connect by listing all cluster hosts and specifying target_session_attrs=read-write.

  Connection example

  Here, we list all the cluster’s hosts.

  The host IDs are rc1a-be***.mdb.yandexcloud.net, rc1b-5r***.mdb.yandexcloud.net, and rc1d-t4***.mdb.yandexcloud.net:

  psql "host=rc1a-be***.mdb.yandexcloud.net,rc1b-5r***.mdb.yandexcloud.net,rc1d-t4***.mdb.yandexcloud.net \
        port=6432 \
        sslmode=verify-full \
        dbname=<DB_name> \
        user=<username> \
        target_session_attrs=read-write"
  

Use one of the following methods to connect to the host with read access:

• Connect using a special FQDN pointing to the most up-to-date replica.

  Connection example

  In this example, we use the c9qash3nb1v9******** cluster ID:

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

• Connect by listing all cluster hosts and specifying target_session_attrs=any.

  Connection example

  Here, we list all the cluster’s hosts.

  The host IDs are rc1a-be***.mdb.yandexcloud.net, rc1b-5r***.mdb.yandexcloud.net, and rc1d-t4***.mdb.yandexcloud.net:

  psql "host=rc1a-be***.mdb.yandexcloud.net,rc1b-5r***.mdb.yandexcloud.net,rc1d-t4***.mdb.yandexcloud.net \
        port=6432 \
        sslmode=verify-full \
        dbname=<DB_name> \
        user=<username> \
        target_session_attrs=any"
  

Connecting from graphical IDEsConnecting from graphical IDEs

Connections were tested in the following environment:

• Ubuntu 20.04, DBeaver: 22.2.4
• MacOS Monterey 12.7:
  • JetBrains DataGrip: 2023.3.4
  • DBeaver Community: 24.0.0

From graphical IDEs, you can only connect to public cluster hosts using an SSL certificate.

To avoid connection errors, save the certificate to a local folder that does not require administrator rights to access.

Connecting with IAM authenticationConnecting with IAM authentication

You can connect to a PostgreSQL database via the Yandex Cloud CLI using IAM authentication. This method is available to Yandex accounts, federated accounts, and local users. When connecting with IAM authentication, you do not need to obtain an SSL certificate or specify the cluster hosts’ FQDNs.

Before connecting, install the PostgreSQL client:

sudo apt update && sudo apt install --yes postgresql-client

Set up your PostgreSQL cluster for connection:

To connect to the PostgreSQL database, run this command:

yc managed-postgresql connect <cluster_name_or_ID> --db <DB_name>

Connecting from Yandex WebSQLConnecting from Yandex WebSQL

You can use Yandex WebSQL to send SQL queries to Managed Service for PostgreSQL cluster databases.

WebSQL is a Yandex Cloud service that enables you to connect to managed database clusters, work with databases, tables, and schemas, and run queries. It is a web-based tool that requires no additional authorization and simplifies working with SQL commands by prompting the user.

To connect from WebSQL, activate the WebSQL access option in the cluster settings. You can enable it when creating or updating a cluster.

In the Managed Service for PostgreSQL cluster, a Connection Manager connection is automatically created for each database user, which you can use to connect to the database from WebSQL. If required, you can also create a new connection.

To connect to the database from WebSQL:

For more information on how to work with WebSQL, see these guides.

Connecting from pgAdmin 4Connecting from pgAdmin 4

Connection testing was performed for pgAdmin 4 version 7.0 on Ubuntu 20.04.

Connections from pgAdmin 4 are only permitted to publicly accessible cluster hosts and require an SSL certificate.

Create a new server connection:

1. Select Object → Register → Server...

2. On the General tab, in the Name field, specify the cluster name to be shown in the pgAdmin 4 interface. You can set any name.

3. In the Connection tab, specify the connection settings:

   • Host name/address: Master FQDN or regular host FQDN.
   • Port: 6432.
   • Maintenance database: Target database name.
   • Username: Username used to establish the connection.
   • Password: User password.

4. In the Parameters tab:

   • Set the SSL mode parameter to verify-full.
   • Add a new Root certificate parameter and specify the path to the saved SSL certificate file in it.

5. Click Save to save the server connection settings.

Your cluster will appear in the server list located in the navigation menu.

Connecting from Looker StudioConnecting from Looker Studio

Connections from Looker Studio are only permitted to publicly accessible hosts.

1. Save the CA.pem server certificate to a local directory.

2. In the same directory, generate a client certificate with a private key:

   openssl req -newkey rsa:2048 -nodes -keyout private.pem -out cert.pem
   

   During certificate creation, the program will ask you to modify several settings. Press Enter to keep the default values.

   You will see two files in your local directory: cert.pem and private.pem.

3. On the Looker Studio navigation page, select Create → Data source.

4. Select PostgreSQL.

5. Fill out the fields as follows:

   • Host name or IP address: Master FQDN or regular host FQDN.
   • Port: 6432.
   • Database: DB to connect to.
   • Username: Username used to establish the connection.
   • Password: User password.

6. Check Enable SSL and Enable client authentication.

7. Specify the certificate files and the client private key in the appropriate fields:

   • Server certificate: Select the CA.pem file.
   • Client certificate: Select the cert.pem file.
   • Client private key: Select the private.pem file.

8. Click Authenticate.

Before you connect from a Docker containerBefore you connect from a Docker container

To connect to a Managed Service for PostgreSQL cluster from a Docker container, add the following lines to the Dockerfile:

RUN apt-get update && \
    apt-get install postgresql-client --yes

RUN apt-get update && \
    apt-get install wget postgresql-client --yes && \
    mkdir --parents ~/.postgresql && \
    wget "https://storage.yandexcloud.net/cloud-certs/CA.pem" \
         --output-document ~/.postgresql/root.crt && \
    chmod 0655 ~/.postgresql/root.crt

Examples of connection stringsExamples of connection strings

The examples were tested in the following environment:

• Yandex Cloud virtual machine running Ubuntu 20.04 LTS:
  • Bash: 5.0.16.
  • Python: 3.8.2; pip3: 20.0.2.
  • PHP: 7.4.3.
  • OpenJDK: 11.0.8; Maven: 3.6.3.
  • Node.JS: 10.19.0, npm: 6.14.4.
  • Go: 1.13.8.
  • Ruby: 2.7.0p0.
  • unixODBC: 2.3.6.
• Yandex Cloud virtual machine running Windows Server 2019 Datacenter:
  • PostgreSQL: 13.
  • PowerShell: 5.1.17763.1490 Desktop.
  • .NET 5
  • Microsoft.EntityFrameworkCore 5.0.9
  • Npgsql.EntityFrameworkCore.PostgreSQL 5.0.7

Connections to public PostgreSQL hosts require an SSL certificate. Prepare a certificate before connecting to such hosts.

The examples below assume that the root.crt SSL certificate is located in the following directory:

• /home/<home_directory>/.postgresql/ for Ubuntu.
• $HOME\AppData\Roaming\postgresql for Windows.

Connections without an SSL certificate are only supported for hosts that are not publicly accessible. In this case, internal cloud network traffic will not be encrypted during database connections.

You can connect to a cluster using either regular host FQDNs, including a comma-separated list of several FQDNs, or special FQDNs. In our examples, we use the special FQDN pointing to the current master host.

To see code examples with the host FQDN filled in, open the cluster page in the management console and click Connect.

1C:Enterprise1C:Enterprise

If your cluster uses a 1C:Enterprise-optimized PostgreSQL version, specify the following settings:

• Secure connection: Disabled.
• DBMS type: PostgreSQL.
• Database server: c-<cluster_ID>.rw.mdb.yandexcloud.net port=6432.
• Database name: <DB_name>.
• Database user: <username>.
• User password: <password>.
• Create database if none present: Disabled.

BashBash

Before connecting, install the required dependencies:

sudo apt update && sudo apt install --yes postgresql-client

C++ (userver framework)C++ (userver framework)

The asynchronous userver framework provides a rich set of abstractions for creating utilities, services, and microservices in C++. This framework also provides capabilities for PostgreSQL integration.

Before connecting, access the framework using one of the following methods:

• Create a Yandex Compute Cloud virtual machine using the userver image. This image contains both the framework and all required dependencies.
• Manually install the framework and all required dependencies.

Once started, the service will wait for a POST request from the user. While waiting, the service will periodically check the PostgreSQL cluster's availability by running the SELECT 1 as ping request. You can find this information in the service logs.

tskv ... level=INFO      module=MakeQuerySpan ( userver/postgresql/src/storages/postgres/detail/connection_impl.cpp:647 )
...
db_statement=SELECT 1 AS ping
db_type=postgres
db_instance=********
peer_address=c-********.rw.mdb.yandexcloud.net:6432
...

C# EF CoreC# EF Core

To connect to a cluster, you need the Npgsql package.

using Npgsql;

namespace ConsoleApp
{
    class Program
    {
        static async Task Main(string[] args)
        {
            var host       = "c-<cluster_ID>.rw.mdb.yandexcloud.net";
            var port       = "6432";
            var db         = "<DB_name>";
            var username   = "<username>";
            var password   = "<user_password>";
            var connString = $"Host={host};Port={port};Database={db};Username={username};Password={password};Ssl Mode=VerifyFull;";

            await using var conn = new NpgsqlConnection(connString);
            await conn.OpenAsync();

            await using (var cmd = new NpgsqlCommand("SELECT VERSION();", conn))
            await using (var reader = await cmd.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    Console.WriteLine(reader.GetInt32(0));
                }
            }
        }
    }
}

GoGo

Before connecting, install the required dependencies:

sudo apt update && sudo apt install --yes golang git && \
go mod init example && go get github.com/jackc/pgx/v4

JavaJava

Before connecting:

1. Install the dependencies:

   sudo apt update && sudo apt install --yes default-jdk maven
   

2. Create a directory for the Maven project:

   cd ~/ && mkdir -p project/src/java/com/example && cd project/
   

3. Create a Maven configuration file:

   pom.xml

   <?xml version="1.0" encoding="utf-8"?>
   <project xmlns="http://maven.apache.org/POM/4.0.0"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
   
     <modelVersion>4.0.0</modelVersion>
     <groupId>com.example</groupId>
     <artifactId>app</artifactId>
     <packaging>jar</packaging>
     <version>0.1.0</version>
     <properties>
       <maven.compiler.source>1.8</maven.compiler.source>
       <maven.compiler.target>1.8</maven.compiler.target>
     </properties>
     <dependencies>
       <dependency>
         <groupId>org.postgresql</groupId>
         <artifactId>postgresql</artifactId>
         <version>42.2.16</version>
       </dependency>
     </dependencies>
     <build>
       <finalName>${project.artifactId}-${project.version}</finalName>
       <sourceDirectory>src</sourceDirectory>
       <resources>
         <resource>
           <directory>src</directory>
         </resource>
       </resources>
       <plugins>
         <plugin>
           <groupId>org.apache.maven.plugins</groupId>
           <artifactId>maven-assembly-plugin</artifactId>
           <executions>
             <execution>
               <goals>
                 <goal>attached</goal>
               </goals>
               <phase>package</phase>
               <configuration>
                 <descriptorRefs>
                   <descriptorRef>
                   jar-with-dependencies</descriptorRef>
                 </descriptorRefs>
                 <archive>
                   <manifest>
                     <mainClass>com.example.App</mainClass>
                   </manifest>
                 </archive>
               </configuration>
             </execution>
           </executions>
         </plugin>
         <plugin>
           <groupId>org.apache.maven.plugins</groupId>
           <artifactId>maven-jar-plugin</artifactId>
           <version>3.1.0</version>
           <configuration>
             <archive>
               <manifest>
                 <mainClass>com.example.App</mainClass>
               </manifest>
             </archive>
           </configuration>
         </plugin>
       </plugins>
     </build>
   </project>
   

   Latest Maven dependency version: postgresql.

Node.jsNode.js

Before connecting, install the required dependencies:

sudo apt update && sudo apt install --yes nodejs npm && \
npm install pg

"use strict";
const pg = require("pg");

const config = {
    connectionString:
        "postgres://<username>:<user_password>@c-<cluster_ID>.rw.mdb.yandexcloud.net:6432/<DB_name>"
};

const conn = new pg.Client(config);

conn.connect((err) => {
    if (err) throw err;
});
conn.query("SELECT version()", (err, q) => {
    if (err) throw err;
    console.log(q.rows[0]);
    conn.end();
});

"use strict";
const fs = require("fs");
const pg = require("pg");

const config = {
    connectionString:
        "postgres://<username>:<user_password>@c-<cluster_ID>.rw.mdb.yandexcloud.net:6432/<DB_name>",
    ssl: {
        rejectUnauthorized: true,
        ca: fs
            .readFileSync("/home/<home_directory>/.postgresql/root.crt")
            .toString(),
    },
};

const conn = new pg.Client(config);

conn.connect((err) => {
    if (err) throw err;
});
conn.query("SELECT version()", (err, q) => {
    if (err) throw err;
    console.log(q.rows[0]);
    conn.end();
});

You can get the cluster ID from the cluster list.

Connecting:

node app.js

ODBCODBC

Before connecting, install the required dependencies:

sudo apt update && sudo apt install --yes unixodbc odbc-postgresql

The system will automatically register the PostgreSQL ODBC driver in /etc/odbcinst.ini.

PHPPHP

Before connecting, install the required dependencies:

sudo apt update && sudo apt install --yes php php-pgsql

PowerShellPowerShell

Before connecting, install PostgreSQL for Windows using the same version that is installed in the cluster. Install only the Command Line Tools.

PythonPython

Before connecting, install the required dependencies:

sudo apt update && sudo apt install -y python3 python3-pip && \
pip3 install psycopg2-binary

RR

Before connecting:

1. Install the dependencies:

   sudo apt update && sudo apt install libpq-dev r-base --yes
   

2. Install the RPostgres library:

   sudo R --interactive
   install.packages("RPostgres")
   quit()
   

RubyRuby

Before connecting, install the required dependencies:

sudo apt update && sudo apt install --yes ruby ruby-pg

If your cluster connection and test query are successful, you will see the PostgreSQL version. One exception is the userver framework example, where the SELECT 1 as ping test query will be executed for periodic PostgreSQL cluster availability checks.