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

Настройка групп безопасности
Автоматический выбор хоста-мастера
- С библиотекой libpq
- С драйвером, поддерживающим только один хост
Настройка SSL-сертификата
Примеры строк подключения
Особые FQDN
- Текущий мастер
- Наименее отстающая реплика

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

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

Примечание

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

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

Группы безопасности могут препятствовать подключению к кластеру. В этом случае внесите изменения в правила групп.

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

Через интернет

С ВМ в Облаке

Настройте все группы безопасности кластера так, чтобы они разрешали входящий трафик с любых IP-адресов на порт 6432. Для этого создайте следующее правило для входящего трафика:

протокол: TCP;
диапазон портов: 6432;
тип источника: CIDR;
источник: 0.0.0.0/0.

Настройте все группы безопасности кластера так, чтобы они разрешали входящий трафик из группы безопасности, в которой находится ВМ, на порт 6432. Для этого в этих группах создайте следующее правило для входящего трафика:
- протокол: TCP;
- диапазон портов: 6432;
- тип источника: Группа безопасности;
- источник: группа безопасности, в которой находится ВМ. Если она совпадает с настраиваемой группой, то укажите Текущая.
Настройте группу безопасности, в которой находится ВМ так, чтобы можно было подключаться к ВМ и был разрешен трафик между ВМ и хостами кластера.

Пример правил для ВМ:
- Для входящего трафика:
  - протокол: TCP;
  - диапазон портов: 22;
  - тип источника: CIDR;
  - источник: 0.0.0.0/0.
  Это правило позволяет подключаться к ВМ по протоколу SSH.
- Для исходящего трафика:
  - протокол: Any;
  - диапазон портов: 0-65535;
  - тип назначения: CIDR;
  - назначение: 0.0.0.0/0.
  Это правило разрешает любой исходящий трафик, что позволяет не только подключаться к кластеру, но и устанавливать сертификаты и утилиты, необходимые для подключения к кластеру.

Примечание

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

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

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

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

С библиотекой libpq

Чтобы гарантированно подключиться к хосту-мастеру, укажите FQDN всех хостов кластера в аргументе host и передайте параметр target_session_attrs=read-write, как в сделано в большинстве примеров ниже. Этот параметр поддерживается библиотекой libpq начиная с версии 10.

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

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

С драйвером, поддерживающим только один хост

Если ваш драйвер для подключения к базе данных не позволяет передавать несколько хостов в строке подключения (например, pg в Node.js), используйте для подключения к кластеру особый FQDN, который указывает на хост-мастер.

Настройка SSL-сертификата

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

mkdir ~/.postgresql && \
wget "https://storage.yandexcloud.net/cloud-certs/CA.pem" -O ~/.postgresql/root.crt && \
chmod 0600 ~/.postgresql/root.crt

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

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

Виртуальная машина в Облаке c 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.

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

В этих примерах предполагается, что SSL-сертификат root.crt расположен в директории /home/<домашняя директория>/.postgresql/.

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

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

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

Bash

Python

PHP

Java

Node.js

Ruby

C# EfCore

ODBC

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

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

Подключение с использованием SSL-соединения:

psql "host=<FQDN одного или нескольких хостов PostgreSQL> \
    port=6432 \
    sslmode=verify-full \
    dbname=<имя БД> \
    user=<имя пользователя> \
    target_session_attrs=read-write"

Подключение без использования SSL-соединения:

psql "host=<FQDN одного или нескольких хостов PostgreSQL> \
    port=6432 \
    sslmode=disable \
    dbname=<имя БД> \
    user=<имя пользователя> \
    target_session_attrs=read-write"

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

После подключения к СУБД выполните команду SELECT version();.

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

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

Пример кода для подключения с использованием SSL-соединения:

connect.py

import psycopg2

conn = psycopg2.connect("""
    host=<FQDN одного или нескольких хостов PostgreSQL>
    port=6432
    sslmode=verify-full
    dbname=<имя БД>
    user=<имя пользователя>
    password=<пароль пользователя>
    target_session_attrs=read-write
""")

q = conn.cursor()
q.execute('SELECT version()')

print(q.fetchone())

conn.close()

Пример кода для подключения без использования SSL-соединения:

connect.py

import psycopg2

conn = psycopg2.connect("""
    host=<FQDN одного или нескольких хостов PostgreSQL>
    port=6432
    sslmode=disable
    dbname=<имя БД>
    user=<имя пользователя>
    password=<пароль пользователя>
    target_session_attrs=read-write
""")

q = conn.cursor()
q.execute('SELECT version()')

print(q.fetchone())

conn.close()

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

python3 connect.py

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

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

Пример кода для подключения с использованием SSL-соединения:

connect.php

<?php
  $conn = pg_connect("
      host=<FQDN одного или нескольких хостов PostgreSQL>
      port=6432
      sslmode=verify-full
      dbname=<имя БД>
      user=<имя пользователя>
      password=<пароль пользователя>
      target_session_attrs=read-write
  ");

  $q = pg_query($conn, "SELECT version()");
  $result = pg_fetch_row($q);
  echo($result[0]);

  pg_close($conn);
?>

Пример кода для подключения без использования SSL-соединения:

connect.php

<?php
  $conn = pg_connect("
      host=<FQDN одного или нескольких хостов PostgreSQL>
      port=6432
      sslmode=disable
      dbname=<имя БД>
      user=<имя пользователя>
      password=<пароль пользователя>
      target_session_attrs=read-write
  ");

  $q = pg_query($conn, "SELECT version()");
  $result = pg_fetch_row($q);
  echo($result[0]);

  pg_close($conn);
?>

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

php connect.php

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

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

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

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

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

Создайте конфигурационный файл для 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.

Пример кода для подключения с использованием SSL-соединения:

src/java/com/example/App.java

package com.example;

import java.sql.*;

public class App {
  public static void main(String[] args) {
    String DB_URL     = "jdbc:postgresql://<хост 1 PostgreSQL:6432,...,хост N PostgreSQL:6432>/<имя БД>&targetServerType=master&ssl=true&sslmode=verify-full";
    String DB_USER    = "<имя пользователя>";
    String DB_PASS    = "<пароль пользователя>";
    
    try {
      Class.forName("org.postgresql.Driver");

      Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
      ResultSet q = conn.createStatement().executeQuery("SELECT version()");
      if(q.next()) {System.out.println(q.getString(1));}

      conn.close();
    }
    catch(Exception ex) {ex.printStackTrace();}
  }
}

Пример кода для подключения без использования SSL-соединения:

src/java/com/example/App.java

package com.example;

import java.sql.*;

public class App {
  public static void main(String[] args) {
    String DB_URL     = "jdbc:postgresql://<хост 1 PostgreSQL:6432,...,хост N PostgreSQL:6432>/<имя БД>&targetServerType=master&ssl=true&sslmode=disable";
    String DB_USER    = "<имя пользователя>";
    String DB_PASS    = "<пароль пользователя>";
    
    try {
      Class.forName("org.postgresql.Driver");

      Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
      ResultSet q = conn.createStatement().executeQuery("SELECT version()");
      if(q.next()) {System.out.println(q.getString(1));}

      conn.close();
    }
    catch(Exception ex) {ex.printStackTrace();}
  }
}

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

mvn clean package && \
java -jar target/app-0.1.0-jar-with-dependencies.jar

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

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

Пример кода для подключения с использованием SSL-соединения:

app.js

"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()
})

В отличие от других способов подключения, в этом коде необходимо указывать полный путь к сертификату root.crt для PostgreSQL в переменной ca.

Пример кода для подключения без использования SSL-соединения:

app.js

"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()
})

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

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

node app.js

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

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

Пример кода для подключения с использованием SSL-соединения:

connect.go

package main

import (
  "context"
  "crypto/tls"
  "crypto/x509"
  "fmt"
  "io/ioutil"
  "os"

  "github.com/jackc/pgx/v4"
)

const (
  host     = "<FQDN одного или нескольких хостов PostgreSQL>"
  port     = 6432
  user     = "<имя пользователя>"
  password = "<пароль пользователя>"
  dbname   = "<имя БД>"
  ca       = "/home/<домашняя директория>/.postgresql/root.crt"
)

func main() {

  rootCertPool := x509.NewCertPool()
  pem, err := ioutil.ReadFile(ca)
  if err != nil {
    panic(err)
  }

  if ok := rootCertPool.AppendCertsFromPEM(pem); !ok {
    panic("Failed to append PEM.")
  }

  connstring := fmt.Sprintf(
    "host=%s port=%d dbname=%s user=%s password=%s sslmode=verify-full target_session_attrs=read-write",
    host, port, dbname, user, password)

  connConfig, err := pgx.ParseConfig(connstring)
  if err != nil {
    fmt.Fprintf(os.Stderr, "Unable to parse config: %v\n", err)
    os.Exit(1)
  }

  connConfig.TLSConfig = &tls.Config{
    RootCAs:            rootCertPool,
    InsecureSkipVerify: true,
  }

  conn, err := pgx.ConnectConfig(context.Background(), connConfig)
  if err != nil {
    fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
    os.Exit(1)
  }

  defer conn.Close(context.Background())

  var version string

  err = conn.QueryRow(context.Background(), "select version()").Scan(&version)
  if err != nil {
    fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
    os.Exit(1)
  }

  fmt.Println(version)  
}

Пример кода для подключения без использования SSL-соединения:

connect.go

package main

import (
  "context"
  "fmt"
  "os"

  "github.com/jackc/pgx/v4"
)

const (
  host     = "<FQDN одного или нескольких хостов PostgreSQL>"
  port     = 6432
  user     = "<имя пользователя>"
  password = "<пароль пользователя>"
  dbname   = "<имя БД>"
)

func main() {

  connstring := fmt.Sprintf(
    "host=%s port=%d dbname=%s user=%s password=%s target_session_attrs=read-write",
    host, port, dbname, user, password)

  connConfig, err := pgx.ParseConfig(connstring)
  if err != nil {
    fmt.Fprintf(os.Stderr, "Unable to parse config: %v\n", err)
    os.Exit(1)
  }

  conn, err := pgx.ConnectConfig(context.Background(), connConfig)
  if err != nil {
    fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
    os.Exit(1)
  }

  defer conn.Close(context.Background())

  var version string

  err = conn.QueryRow(context.Background(), "select version()").Scan(&version)
  if err != nil {
    fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
    os.Exit(1)
  }

  fmt.Println(version)  
}

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

go run connect.go

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

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

Пример кода для подключения с использованием SSL-соединения:

connect.rb

require "pg"

conn = PG.connect("
        host=<FQDN одного или нескольких хостов PostgreSQL>
        port=6432
        dbname=<имя БД>
        user=<имя пользователя>
        password=<пароль пользователя>
        target_session_attrs=read-write
        sslmode=verify-full
")

q = conn.exec("SELECT version()")
puts q.getvalue 0, 0

conn.close()

Пример кода для подключения без использования SSL-соединения:

connect.rb

require "pg"

conn = PG.connect("
        host=<FQDN одного или нескольких хостов PostgreSQL>
        port=6432
        dbname=<имя БД>
        user=<имя пользователя>
        password=<пароль пользователя>
        target_session_attrs=read-write
        sslmode=disable
")

q = conn.exec("SELECT version()")
puts q.getvalue 0, 0

conn.close()

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

ruby connect.rb

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

Install-Package Npgsql.EntityFrameworkCore.PostgreSQL

Пример настройек в файле appsettings.json для подключения с использованием SSL-соединения:

...
"ConnectionStrings": {
  "Default": "Server=<FQDN одного или нескольких хостов PostgreSQL>;Database=<имя БД>;Port=6432;User Id=<имя пользователя>;Password=<пароль пользователя>;Ssl Mode=Require; Trust Server Certificate=true;"
},
...

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

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

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

Пример настроек в файле /etc/odbc.ini для подключения с использованием SSL-соединения:

odbc.ini

[postgresql]
Driver=PostgreSQL Unicode
Servername=<хост 1 PostgreSQL>,...,<хост N PostgreSQL>
Username=<имя пользователя>
Password=<пароль пользователя>
Database=<имя БД>
Port=6432
Pqopt=target_session_attrs=read-write
Sslmode= verify-full

Пример настроек в файле /etc/odbc.ini для подключения без использования SSL-соединения:

odbc.ini

[postgresql]
Driver=PostgreSQL Unicode
Servername=<хост 1 PostgreSQL>,...,<хост N PostgreSQL>
Username=<имя пользователя>
Password=<пароль пользователя>
Database=<имя БД>
Port=6432
Pqopt=target_session_attrs=read-write

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

isql -v postgresql

После подключения к СУБД выполните команду SELECT version();.

При успешном подключении к кластеру и выполнении тестового запроса будет выведена версия PostgreSQL.

Особые FQDN

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

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

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

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

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

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

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

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

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

При подключении к этому FQDN разрешено выполнять только операции чтения.
Если в кластере нет активных реплик, то подключиться к этому FQDN невозможно: соответствующая CNAME-запись в DNS будет указывать «в никуда» (null).

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

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