Подключение к базе данных в кластере PostgreSQL

К хостам кластера Managed Service for PostgreSQL можно подключиться:

• Через интернет, если вы настроили публичный доступ для нужного хоста. К таким хостам подключиться можно только используя SSL-соединение.

• С виртуальных машин Yandex Cloud, расположенных в той же облачной сети. Если к хосту нет публичного доступа, для подключения с таких виртуальных машин необязательно использовать SSL-соединение.

• Из контейнера Serverless Containers. Если к хосту нет публичного доступа, контейнер должен располагаться в той же облачной сети.

Настройка групп безопасностиНастройка групп безопасности

Для подключения к кластеру необходимо, чтобы группы безопасности содержали правила, которые разрешают трафик с определенных портов, IP-адресов или из других групп безопасности.

Настройки правил будут различаться в зависимости от выбранного способа подключения:

Подробнее о группах безопасности см. в разделе Группы безопасности.

Получение SSL-сертификатаПолучение SSL-сертификата

Хосты PostgreSQL с публичным доступом поддерживают только шифрованные соединения. Чтобы использовать их, получите SSL-сертификат:

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

Для использования графических IDE сохраните сертификат в локальную папку и укажите путь к нему в настройках подключения.

FQDN хоста PostgreSQLFQDN хоста PostgreSQL

Для подключения к хосту потребуется его FQDN — доменное имя. Его можно получить несколькими способами:

• Запросите список хостов в кластере.

• Скопируйте команду для подключения к кластеру в консоли управления. Команда содержит заполненный FQDN хоста. Чтобы получить команду, перейдите на страницу кластера и нажмите кнопку Подключиться.

• Посмотрите FQDN в консоли управления:

  1. Перейдите на страницу кластера.
  2. Перейдите в раздел Хосты.
  3. Скопируйте значение в столбце FQDN хоста.

Для хостов кластера также используются особые FQDN.

Особые FQDNОсобые FQDN

Наравне с обычными FQDN, Managed Service for PostgreSQL предоставляет несколько особых FQDN, которые также можно использовать при подключении к кластеру.

В кластерах из нескольких хостов особые FQDN могут некоторое время (до 10 минут) указывать на старый хост, даже если он сменил роль (например, из мастера стал репликой). Если используется особый FQDN, который указывает на текущий мастер, то часть запросов на запись может завершиться неудачей — они могут быть направлены к реплике. Это связано с тем, что на обновление DNS-записей для особых FQDN требуется время. Если запрос на запись завершился ошибкой, повторите его позднее.

Текущий мастерТекущий мастер

FQDN вида c-<идентификатор_кластера>.rw.mdb.yandexcloud.net всегда указывает на текущий хост-мастер в кластере. Идентификатор кластера можно запросить со списком кластеров в каталоге.

При подключении к этому FQDN разрешено выполнять операции чтения и записи.

Наименее отстающая репликаНаименее отстающая реплика

FQDN вида c-<идентификатор_кластера>.ro.mdb.yandexcloud.net указывает на наименее отстающую от мастера реплику. Идентификатор кластера можно запросить со списком кластеров в каталоге.

Особенности:

• При подключении к этому FQDN разрешено выполнять только операции чтения.
• Если в кластере нет активных реплик, то этот FQDN укажет на текущий хост-мастер.
• Реплики, для которых вручную установлен источник репликации, не могут выбираться как наименее отстающие при использовании этого FQDN.

Выбор FQDN и способа подключения к кластеруВыбор FQDN и способа подключения к кластеру

К кластеру можно подключиться с использованием FQDN хостов или с использованием особых FQDN. Если кластер состоит из нескольких хостов, и для него настроено автоматическое переключение мастера, то это необходимо учитывать при подключении. Это важно, потому что текущий мастер может в следующий момент времени стать репликой и наоборот.

Воспользуйтесь одним из способов, чтобы подключиться к хостам-мастерам с возможностью чтения и записи:

• Подключитесь с помощью особого FQDN, который указывает на текущий мастер.

  При переключении мастера этот FQDN может некоторое время указывать на бывший мастер, который стал репликой, поскольку необходимо время для обновления DNS-записи.

  Поэтому если приложение использует такой FQDN, то оно должно корректно обрабатывать ситуацию, когда мастер временно недоступен. Например, можно повторять запросы на запись через некоторое время.

  Пример подключения

  В этом примере используется идентификатор кластера c9qash3nb1v9********:

  psql "host=c-c9qash3nb1v9********.rw.mdb.yandexcloud.net \
        port=6432 \
        sslmode=verify-full \
        dbname=<имя_БД> \
        user=<имя_пользователя>"
  

• Подключитесь, перечислив все хосты кластера и указав параметр target_session_attrs=read-write.

  Пример подключения

  В этом примере перечисляются все хосты кластера.

  Хосты имеют идентификаторы rc1a-be***.mdb.yandexcloud.net, rc1b-5r***.mdb.yandexcloud.net и 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=<имя_БД> \
        user=<имя_пользователя> \
        target_session_attrs=read-write"
  

Воспользуйтесь одним из способов, чтобы подключиться к хостам с возможностью чтения:

• Подключитесь с помощью особого FQDN, который указывает на наименее отстающую реплику.

  Пример подключения

  В этом примере используется идентификатор кластера c9qash3nb1v9********:

  psql "host=c-c9qash3nb1v9********.ro.mdb.yandexcloud.net \
        port=6432 \
        sslmode=verify-full \
        dbname=<имя_БД> \
        user=<имя_пользователя>"
  

• Подключитесь, перечислив все хосты кластера и указав параметр target_session_attrs=any.

  Пример подключения

  В этом примере перечисляются все хосты кластера.

  Хосты имеют идентификаторы rc1a-be***.mdb.yandexcloud.net, rc1b-5r***.mdb.yandexcloud.net и 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=<имя_БД> \
        user=<имя_пользователя> \
        target_session_attrs=any"
  

Подключение из графических IDEПодключение из графических IDE

Подключения проверялись в следующем окружении:

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

Подключаться из графических IDE можно только к хостам кластера в публичном доступе с использованием SSL-сертификата.

Чтобы избежать ошибок при подключении, сохраните сертификат в локальную папку, для доступа к которой не требуются права администратора.

Подключение из Yandex WebSQLПодключение из Yandex WebSQL

Вы можете отправлять SQL-запросы к базам данных в кластере Managed Service for PostgreSQL с помощью сервиса Yandex WebSQL.

WebSQL — это сервис Yandex Cloud, который позволяет подключаться к кластерам управляемых баз данных, работать с БД, таблицами и схемами и выполнять запросы. Сервис работает в браузере, не требует дополнительной авторизации и предлагает удобные подсказки для работы с SQL-командами.

Чтобы подключаться из WebSQL к кластеру Managed Service for PostgreSQL, необходимо создать подключение:

1. Перейдите на страницу каталога и выберите сервис Managed Service for PostgreSQL.
2. Нажмите на имя нужного кластера.
3. Включите опцию Доступ из WebSQL в настройках кластера, если она еще не включена.
4. Выберите вкладку WebSQL.
5. Нажмите кнопку Создать подключение и укажите параметры подключения:
   • Имя подключения.
   • Тип базы данных — PostgreSQL.
   • Кластер — будет автоматически выбран текущий кластер PostgreSQL.
   • Имя пользователя, от лица которого вы будете подключаться к базе данных в кластере.
   • Пароль пользователя.
   • Базы данных, к которым вы хотите подключиться. Вы можете подключиться только к существующим в этом кластере БД. У пользователя, которого вы указали, должен быть настроен доступ к ним.
6. Нажмите кнопку Создать.

Чтобы открыть SQL-редактор, нажмите на созданное подключение на вкладке WebSQL. Справочник по поддерживаемым запросам можно найти в документации PostgreSQL.

Подробнее о работе с WebSQL см. в документации сервиса.

Подключение из pgAdmin 4Подключение из pgAdmin 4

Подключение проверялось для pgAdmin 4 версии 7.0 в Ubuntu 20.04.

Подключаться из pgAdmin 4 можно только к хостам кластера в публичном доступе с использованием SSL-сертификата.

Создайте новое подключение к серверу:

1. Выберите в меню Object → Register → Server...

2. На вкладке General в поле Name укажите имя, под которым кластер будет отображаться в интерфейсе pgAdmin 4. Имя может быть любым.

3. На вкладке Connection укажите параметры подключения:

   • Host name/address — особый FQDN хоста-мастера или обычный FQDN хоста;
   • Port — 6432;
   • Maintenance database — имя БД для подключения;
   • Username — имя пользователя, от имени которого выполняется подключение;
   • Password — пароль пользователя.

4. На вкладке Parameters:

   • Установите параметр SSL mode в значение verify-full.
   • Добавьте новый параметр Root certificate и укажите в нем путь к сохраненному файлу SSL-сертификата.

5. Нажмите кнопку Save, чтобы сохранить настройки подключения к серверу.

Кластер появится в списке серверов в навигационном меню.

Подключение из Looker StudioПодключение из Looker Studio

Подключаться из Looker Studio можно только к хостам кластера в публичном доступе.

1. Сохраните сертификат сервера CA.pem в локальную папку.

2. В той же папке сгенерируйте сертификат клиента с приватным ключом:

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

   При создании сертификата программа предложит изменить ряд настроек. Нажмите Enter, чтобы использовать для них значения по умолчанию.

   В локальной папке появятся два файла: cert.pem и private.pem.

3. На странице навигации Looker Studio выберите Создать → Источник данных.

4. Выберите PostgreSQL.

5. Заполните поля:

   • Имя хоста или IP-адрес — особый FQDN хоста-мастера или обычный FQDN хоста;
   • Порт — 6432;
   • База данных — имя БД для подключения;
   • Имя пользователя — имя пользователя, от имени которого выполняется подключение;
   • Пароль — пароль пользователя.

6. Выберите опции Включить SSL и Включить аутентификацию клиента.

7. Укажите файлы сертификатов и приватный ключ клиента в соответствующих полях:

   • Server certificate — выберите файл CA.pem.
   • Client certificate — выберите файл cert.pem.
   • Client private key — выберите файл private.pem.

8. Нажмите Выполнить аутентификацию.

Подготовка к подключению из Docker-контейнераПодготовка к подключению из Docker-контейнера

Чтобы подключаться к кластеру Managed Service for PostgreSQL из Docker-контейнера, добавьте в 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

Примеры строк подключенияПримеры строк подключения

Примеры проверялись в следующем окружении:

• Виртуальная машина в Yandex Cloud с 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 с 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

Подключиться к хостам PostgreSQL в публичном доступе можно только с использованием SSL-сертификата. Перед подключением к таким хостам подготовьте сертификат.

В примерах ниже предполагается, что SSL-сертификат root.crt расположен в директории:

• /home/<домашняя_директория>/.postgresql/ для Ubuntu;
• $HOME\AppData\Roaming\postgresql для Windows.

Подключение без использования SSL-сертификата поддерживается только для хостов, находящихся не в публичном доступе. В этом случае трафик внутри облачной сети при подключении к БД шифроваться не будет.

Подключиться к кластеру возможно как с использованием обычных FQDN хостов (можно передавать список из нескольких таких FQDN, разделенных запятой), так и особых FQDN. В примерах используется особый FQDN текущего хоста-мастера.

Примеры кода с заполненным FQDN хоста доступны в консоли управления по нажатию кнопки Подключиться на странице кластера.

1C:Предприятие1C:Предприятие

Если кластер использует версию PostgreSQL, оптимизированную для работы с системой 1С:Предприятие, укажите в настройках:

• Защищенное соединение — отключено.
• Тип СУБД — PostgreSQL.
• Сервер баз данных — с-<идентификатор_кластера>.rw.mdb.yandexcloud.net port=6432.
• Имя базы данных — <имя_БД>.
• Пользователь базы данных — <имя_пользователя>.
• Пароль пользователя — <пароль>.
• Создать базу данных в случае ее отсутствия — отключено.

BashBash

Перед подключением установите зависимости:

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

C++ (фреймворк userver)C++ (фреймворк userver)

Асинхронный фреймворк userver предоставляет богатый набор абстракций для создания утилит, сервисов и микросервисов на языке C++. В том числе фреймворк предоставляет возможности для взаимодействия с PostgreSQL.

Перед подключением получите доступ к фреймворку одним из способов:

• Создайте виртуальную машину Yandex Compute Cloud из образа userver. Этот образ уже содержит фреймворк и все необходимые зависимости.
• Вручную установите фреймворк и все необходимые зависимости.

После запуска сервис будет ожидать поступления POST-запроса от пользователя. В ходе ожидания запроса сервис будет периодически проверять доступность кластера PostgreSQL, выполняя запрос SELECT 1 as ping. Информация об этом содержится в логах работы сервиса.

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

Для подключения к кластеру необходим пакет Npgsql.

using Npgsql;

namespace ConsoleApp
{
    class Program
    {
        static async Task Main(string[] args)
        {
            var host       = "c-<идентификатор_кластера>.rw.mdb.yandexcloud.net";
            var port       = "6432";
            var db         = "<имя_БД>";
            var username   = "<имя_пользователя>";
            var 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

Перед подключением установите зависимости:

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

JavaJava

Перед подключением:

1. Установите зависимости:

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

2. Создайте директорию для проекта Maven:

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

3. Создайте конфигурационный файл для Maven:

   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>
   

   Актуальная версия зависимости для Maven: postgresql.

Node.jsNode.js

Перед подключением установите зависимости:

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

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

const config = {
    connectionString:
        "postgres://<имя_пользователя>:<пароль_пользователя>@c-<идентификатор_кластера>.rw.mdb.yandexcloud.net:6432/<имя_БД>"
};

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://<имя_пользователя>:<пароль_пользователя>@c-<идентификатор_кластера>.rw.mdb.yandexcloud.net:6432/<имя_БД>",
    ssl: {
        rejectUnauthorized: true,
        ca: fs
            .readFileSync("/home/<домашняя_директория>/.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();
});

Идентификатор кластера можно получить со списком кластеров.

Подключение:

node app.js

ODBCODBC

Перед подключением установите зависимости:

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

Драйвер PostgreSQL ODBC будет автоматически зарегистрирован в файле /etc/odbcinst.ini.

PHPPHP

Перед подключением установите зависимости:

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

PowerShellPowerShell

Перед подключением установите PostgreSQL для Windows той же версии, которая используется в кластере. Выберите только установку Command Line Tools.

PythonPython

Перед подключением установите зависимости:

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

RR

Перед подключением:

1. Установите зависимости:

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

2. Установите библиотеку RPostgres:

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

RubyRuby

Перед подключением установите зависимости:

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

При успешном подключении к кластеру и выполнении тестового запроса будет выведена версия PostgreSQL. Исключение — пример для фреймворка userver, в котором будет выполняться тестовый запрос SELECT 1 as ping для периодической проверки доступности кластера PostgreSQL.

ClickHouse® является зарегистрированным товарным знаком ClickHouse, Inc.