Подключиться
Yandex Managed Service for PostgreSQL

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

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

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

Примечание

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

Настройка 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 хоста вы можете посмотреть в консоли управления, нажав на кнопку Подключиться на странице кластера.

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

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 хоста> \
    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

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

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

    sudo apt update && sudo apt install -y 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.

Пример кода для подключения с использованием 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/lib/pq

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

connect.go

package main

import (
  "database/sql"
  "fmt"

  _ "github.com/lib/pq"
)

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

func main() {
  psqlInfo := fmt.Sprintf("host=%s port=%d user=%s " +
                          "password=%s dbname=%s " +
                          "sslmode=verify-full " +
                          "sslrootcert=%s, host, port, user, password, dbname, ca)
  conn, err := sql.Open("postgres", psqlInfo)
  if err != nil {
    panic(err)
  }

  defer conn.Close()

  q, err := db.Query("SELECT version()")
  if err != nil {
    panic(err)
  }

  var result string

  for q.Next() {
   q.Scan(&result)
   fmt.Println(result)
  }
}

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

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

connect.go

package main

import (
  "database/sql"
  "fmt"

  _ "github.com/lib/pq"
)

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

func main() {
  psqlInfo := fmt.Sprintf("host=%s port=%d user=%s " +
                          "password=%s dbname=%s " +
                          "sslmode=disable", host, port, user, password, dbname)
  conn, err := sql.Open("postgres", psqlInfo)
  if err != nil {
    panic(err)
  }

  defer conn.Close()

  q, err := conn.Query("SELECT version()")
  if err != nil {
    panic(err)
  }

  var result string

  for q.Next() {
   q.Scan(&result)
   fmt.Println(result)
  }
}

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

go run connect.go

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

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

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

connect.rb

require "pg"

conn = PG.connect("
        host=<FQDN хоста>
        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 хоста>
        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.

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

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

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

psql "host=<FQDN хоста 1>,<FQDN хоста 2>,<FQDN хоста 3> \
      port=6432 \
      sslmode=verify-full \
      dbname=<имя базы данных> \
      user=<имя пользователя базы данных> \
      target_session_attrs=read-write"

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

  • Для дистрибутивов Linux на основе Debian — установите пакет postgresql-client-10 (например, через apt-репозиторий).

  • Для ОС, использующих RPM-пакеты, дистрибутив PostgreSQL доступен в yum-репозитории.

Адреса всех хостов в кластере БД можно найти на странице нужного кластера в консоли управления.

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

Если ваш драйвер для подключения к базе данных не позволяет передавать несколько хостов в строке подключения (например, pg в node.js), вы можете подключаться на специальный хост вида c-<идентификатор кластера>.rw.mdb.yandexcloud.net.

Это доменное имя всегда указывает на текущий мастер в кластере. Например, для кластера с идентификатором c9qash3nb1v9ulc8j9nm к мастеру можно подключиться так:

psql "host=c-c9qash3nb1v9ulc8j9nm.rw.mdb.yandexcloud.net \
      port=6432 \
      sslmode=verify-full \
      dbname=<имя базы данных> \
      user=<имя пользователя базы данных>"
В этой статье: