Подключение к базе данных в кластере 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 вида c-<идентификатор_кластера>.rw.mdb.yandexcloud.net всегда указывает на текущий хост-мастер в кластере. Идентификатор кластера можно запросить со списком кластеров в каталоге.

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

Пример подключения к хосту-мастеру для кластера с идентификатором c9qash3nb1v9********:

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

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

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

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

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

Пример подключения к наименее отстающей реплике для кластера с идентификатором c9qash3nb1v9********:

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

Автоматический выбор хоста-мастераАвтоматический выбор хоста-мастера

Чтобы гарантированно подключиться к хосту-мастеру:

1. Укажите в аргументе host на выбор:

   • или особый FQDN хоста-мастера, как сделано в примерах ниже;
   • или FQDN всех хостов кластера.

2. Передайте параметр target_session_attrs=read-write. Этот параметр поддерживается библиотекой libpq начиная с версии 10.

Чтобы обновить версию библиотеки, которую использует утилита psql:

• Для дистрибутивов Linux на основе Debian — установите пакет postgresql-client-10 или новее (например, через apt-репозиторий).
• Для ОС, использующих RPM-пакеты — воспользуйтесь дистрибутивом PostgreSQL, доступным в yum-репозитории.

Подключение из графических 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.