Подключение к базе данных в кластере PostgreSQL
К хостам кластера 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 хоста вы можете посмотреть в консоли управления, нажав на кнопку Подключиться на странице кластера.
Перед подключением установите зависимости:
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 get github.com/jackc/pgx && go mod init example
Пример кода для подключения с использованием SSL-соединения:
connect.go
package main
import (
"io/ioutil"
"crypto/tls"
"crypto/x509"
"fmt"
"github.com/jackc/pgx"
)
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.")
}
config := pgx.ConnConfig {
Host: host,
Port: port,
User: user,
Password: password,
Database: dbname,
TargetSessionAttrs: "read-write",
TLSConfig: &tls.Config {
RootCAs: rootCertPool,
InsecureSkipVerify: true,
},
}
conn, err := pgx.Connect(config)
if err != nil {
panic(err)
}
defer conn.Close()
var version string
err = conn.QueryRow("SELECT version()").Scan(&version)
if err != nil {
panic(err)
}
fmt.Println(version)
}
В отличие от других способов подключения, в этом коде необходимо указывать полный путь к сертификату root.crt
для PostgreSQL в переменной ca
.
Пример кода для подключения без использования SSL-соединения:
connect.go
package main
import (
"fmt"
"github.com/jackc/pgx"
)
const (
host = "<FQDN одного или нескольких хостов PostgreSQL>"
port = 6432
user = "<имя пользователя>"
password = "<пароль пользователя>"
dbname = "<имя БД>"
)
func main() {
config := pgx.ConnConfig {
Host: host,
Port: port,
User: user,
Password: password,
Database: dbname,
TargetSessionAttrs: "read-write",
}
conn, err := pgx.Connect(config)
if err != nil {
panic(err)
}
defer conn.Close()
var version string
err = conn.QueryRow("SELECT version()").Scan(&version)
if err != nil {
panic(err)
}
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
Перед подключением установите зависимости:
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"