Интеграция PostgreSQL с другими СУБД через dblink

Привет, Хабр!

Иногда типичная IT-инфраструктура крупной компании выгляд так: PostgreSQL для аналитики, и Oracle для корпоративных данных, и MySQL для каких-нибудь микросервисов. В таких условиях взаимодействие между различными системами становится настоящим мастхевом. И тут может помочь герой нашей статьи - dblink.

dblink позволяет нам безболезненно дергать данные из одной базы в другую, будь то PostgreSQL или что-то вроде Oracle или MySQL.

Установим

Первое, что нужно сделать, — установить расширение dblink в PostgreSQL. Расширение входит в стандартный набор contrib-модулей PostgreSQL.

Открываем консоль psql и выполняем следующую команду:

CREATE EXTENSION dblink;

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

После установки dblink нужно настроить подключения к удалённой базе данных. Для этого используется функция dblink_connect. Пример команды для подключения:

SELECT dblink_connect('connection_name', 'hostaddr=127.0.0.1 dbname=remote_db user=your_username password=your_password');

connection_name — это имя для подключения, которое будет использоваться в дальнейших запросах. Шаг создаёт постоянное соединение с удалённой базой данных, используя указанные параметры подключения.

Теперь dblink готов к работе!

Основные функции dblink

Функции dblink_connect и dblink_connect_u открывают постоянное соединение с удалённой базой данных.

• dblink_connect использует безопасное соединение.

• dblink_connect_u позволяет использовать небезопасные соединения, например, с незашифрованными паролями.

-- безопасное соединение
SELECT dblink_connect('connection_name', 'hostaddr=127.0.0.1 dbname=mydb user=myuser password=mypassword');

-- небезопасное соединение
SELECT dblink_connect_u('connection_name', 'hostaddr=127.0.0.1 dbname=mydb user=myuser password=mypassword');

-- подключение к удалённой базе данных
SELECT dblink_connect('myconn', 'hostaddr=192.168.1.100 dbname=remote_db user=remote_user password=remote_pass');

Команда создаёт соединение с удалённой базой данных remote_db, используя указанное имя подключения myconn.

Функция dblink_disconnect закрывает ранее установленное соединение:

SELECT dblink_disconnect('connection_name');

-- отключение от удалённой базы данных
SELECT dblink_disconnect('myconn');

Команда закрывает соединение, установленное с именем myconn.

Функция dblink_exec выполняет SQL-команду в удалённой базе данных. Обычно используется для команд типа INSERT, UPDATE, DELETE:

SELECT dblink_exec('connection_name', 'SQL_command');

-- вставка данных в удалённую таблицу
SELECT dblink_exec('myconn', 'INSERT INTO remote_table (id, name) VALUES (1, ''Test'')');

Команда вставляет данные в таблицу remote_table в удалённой базе данных.

Функция dblink_query выполняет запрос SELECT в удалённой базе данных и возвращает результат в виде набора строк:

SELECT * FROM dblink('connection_name', 'SQL_query') AS alias(column_definitions);

-- запрос данных из удалённой таблицы
SELECT * FROM dblink('myconn', 'SELECT id, name FROM remote_table') AS t(id int, name text);

Команда выбирает данные из таблицы remote_table в удалённой базе данных и возвращает их с локальными именами и типами колонок.

Функция dblink_get_connections возвращает список всех активных соединений:

SELECT * FROM dblink_get_connections();

-- получение списка всех активных соединений
SELECT * FROM dblink_get_connections();

Команда возвращает имена всех текущих активных соединений.

Как работать с Postqre, Oracle и MySQL

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

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

SELECT dblink_connect('conn1', 'host=127.0.0.1 dbname=remote_db user=remote_user password=remote_pass');

Выполним запрос:

SELECT * FROM dblink('conn1', 'SELECT id, name FROM remote_table') AS t(id int, name text);

Закрываем соединение:

SELECT dblink_disconnect('conn1');

Здесь все достаточно просто, нежели чем с Ораклом.

Подключение PostgreSQL к Oracle

Подключение PostgreSQL к Oracle требует использования стороннего ODBC-драйвера и настройки соответствующего подключения.

Сначала необходимо установить ODBC-драйвер для Oracle и настроить ODBC DSN. Это можно сделать с помощью UnixODBC и драйвера Oracle ODBC:

sudo apt-get install unixodbc unixodbc-dev
sudo apt-get install oracle-odbc-driver

Добавляем следующие строки в файл odbc.ini:

[oracle_dsn]
Driver = /path/to/oracle/odbc/driver
Description = Oracle ODBC Connection
ServerName = YOUR_ORACLE_DB_SERVER
UserID = your_user
Password = your_password

Далее все как и везде. Устанавливаем расширение:

CREATE EXTENSION dblink;

Устанавливаем соединение:

SELECT dblink_connect_u('conn2', 'dsn=oracle_dsn user=your_user password=your_password');

Устанавливаем соединение:

SELECT * FROM dblink('conn2', 'SELECT id, name FROM oracle_table') AS t(id int, name text);

Подключение PostgreSQL к MySQL

Для подключения PostgreSQL к MySQL также необходимо использовать ODBC-драйвер и соответствующую настройку.

Устанавливаем ODBC-драйвер для MySQL:

sudo apt-get install unixodbc unixodbc-dev
sudo apt-get install libmyodbc

Добавляем следующие строки в файл odbc.ini:

[mysql_dsn]
Driver = /path/to/mysql/odbc/driver
Description = MySQL ODBC Connection
Server = YOUR_MYSQL_DB_SERVER
User = your_user
Password = your_password
Database = your_database

Далее все как и везде начиная с установки и коннектом.

Ошибки, с которыми можно столкнуться

Ну, без них никак.

Ошибка подключения: could not establish connection

Эта ошибка возникает, когда не удается установить соединение с удалённой базой данных. Возможные причины:

• Неправильные учетные данные: убедитесь, что логин и пароль верны.

• Недоступность сервера: проверьте, доступен ли сервер по указанному адресу и порту.

• Сетевые проблемы: убедитесь, что нет блокировки соединения брандмауэром.

Ошибка схемы: relation does not exist

Эта ошибка указывает на то, что таблица или объект, к которому вы пытаетесь обратиться, не существует в указанной схеме.

Ошибка функции: function dblink(unknown, unknown) does not exist

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

Ошибка управления транзакциями

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

Пример ошибки:

-- неправильное использование транзакций
SELECT dblink_exec('conn1', 'BEGIN');
SELECT dblink_exec('conn1', 'INSERT INTO remote_table (id, name) VALUES (1, ''Test'')');
-- транзакция остается незавершенной

Решение:

-- правильное завершение транзакции
SELECT dblink_exec('conn1', 'BEGIN');
SELECT dblink_exec('conn1', 'INSERT INTO remote_table (id, name) VALUES (1, ''Test'')');
SELECT dblink_exec('conn1', 'COMMIT');

---

dblink - мощный и полезный инструмент. Подробнее с ним можно ознакомиться в документации.

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