Перейти к основному содержимому

dblink. Подключение к другим базам данных из сеанса базы данных

В исходном дистрибутиве установлено по умолчанию: нет.

Связанные компоненты: pg_profile для использования доработанной функциональности.

Схема размещения: ext.

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

ФункцияОписание
dblink_connectУстанавливает подключение к удаленной базе данных PostgreSQL. Целевой сервер и база данных указываются в стандартной строке подключения libpq. Если требуется, этому подключению можно назначить имя. В один момент времени могут быть открытыми несколько именованных подключений, но только одно подключение без имени. Подключение будет сохраняться, пока не будет закрыто или до завершения сеанса базы данных.
dblink_connect_uОткрывает постоянное подключение к удаленной базе данных. Необходимо внимательно и осторожно пользоваться этой функцией, поскольку это небезопасно. Функция dblink_connect_u() не отличается от dblink_connect(), за исключением того, что она позволяет подключаться с любым методом аутентификации обычным пользователям.
dblink_disconnectЗакрывает подключение, ранее открытое функцией dblink_connect(). Форма без аргументов закрывает безымянное подключение.
dblinkВыполняет запрос в удаленной базе данных. Обычно выполняется SELECT, но это может быть и любой другой возвращающий строки оператор SQL.
dblink_execВыполняет команду в удаленной базе данных. Это может быть любой не возвращающий строки оператор SQL.
dblink_openОткрывает курсор в удаленной базе данных. Открытым курсором можно манипулировать функциями dblink_fetch() и dblink_close().
dblink_fetchВозвращает строки из курсора, ранее открытого функцией dblink_open().
dblink_closeЗакрывает курсор, ранее открытый функцией dblink_open().
dblink_get_connectionsВозвращает имена всех открытых именованных подключений dblink или NULL, если они отсутствуют.
dblink_error_messageВозвращает самое последнее сообщение ошибки для заданного именованного удаленного подключения или OK, если в сеансе этого подключения не было ошибок.
dblink_send_queryПередает в удаленную базу данных запрос для асинхронного выполнения, то есть не дожидается получения результата. С этим подключением не должен быть связан уже выполняющийся асинхронный запрос.
dblink_is_busyПроверяет, не выполняется ли через заданное именованное удаленное подключение асинхронный запрос.
dblink_get_notifyВыдает асинхронные уведомления подключения.
dblink_get_resultПолучает результаты асинхронного запроса, запущенного ранее вызовом dblink_send_query, и будет ожидать завершения выполнени запроса.
dblink_cancel_queryОтменяет или пытается отменить любой запрос, который выполняется через заданное подключение. Вызов функции не обязательно будет успешным, например, потому что удаленный запрос уже завершился. Запрос отмены просто увеличивает вероятность того, что выполняющийся запрос будет вскоре прерван. При этом все равно нужно завершить обычную процедуру обработки запроса, например, вызвать dblink_get_result. Возвращает OK, если запрос отмены был отправлен, либо текст сообщения об ошибке в случае неудачи.
dblink_get_pkeyВозвращает позиции и имена полей первичного ключа отношения в локальной базе данных.
dblink_build_sql_insertФормирует оператор INSERT из локального кортежа, заменяя значения полей первичного ключа переданными альтернативными значениями. Функция может быть полезна при избирательной репликации локальной таблицы с удаленной базой данных. Функция выбирает строку из локальной таблицы по заданному первичному ключу, а затем формирует SQL-команду INSERT, дублирующую эту строку, но заменяет в ней значения первичного ключа данными из последнего аргумента.
dblink_build_sql_deleteФормирует оператор DELETE со значениями, передаваемыми для полей первичного ключа. Функция может быть полезна при избирательной репликации локальной таблицы с удаленной базой данных.
dblink_build_sql_updateФормирует оператор UPDATE из локального кортежа, заменяя значения полей первичного ключа переданными альтернативными значениями. Функция может быть полезна при избирательной репликации локальной таблицы с удаленной базой данных.

Свойства функций и их аргументов, примеры использования можно найти в документации: https://www.postgresql.org/docs/15/dblink.html.

Доработка​

Доработка: Добавлена совместимость с защищенным шифрованным хранилищем паролей pg_auth_config (функция dblink_connect_se).

Версия: 4.4.0.

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

Функция dblink_connect_se() использует пароль из шифрованного хранилища паролей (утилита pg_auth_config). В случае отсутствия шифрованного хранилища паролей или самого пароля dblink не сможет открыть соединение к БД.

Шифрованное хранилище паролей формируется инсталлятором при развертывании кластера базы данных и привязывается к конкретному узлу/хосту.

Примечание:

Функция dblink_connect_se недоступна по умолчанию при установке Pangolin. Установка функции выполняется при установке расширения pg_profile в специальную схему pgse_profile, на которую рекомендуется ограничивать доступ.

Ограничения​

  1. Функция dblink_connect_se устанавливается в специальную схему pgse_profile.

  2. Отличия параметров адреса сервера в аргументе connstr для функций:

    Параметрdblink_connectdblink_connect_se
    hostIP-адресIP-адрес или FQDN
    hostaddrIP-адресне используется

Установка​

При наличии прав администратора СУБД включение модуля возможно выполнить вручную:

CREATE EXTENSION dblink SCHEMA ext;

Настройка​

Настройка не требуется.

Использование модуля​

  1. Стандартная функция dblink_connect.

    Создать именованное подключение:

    SELECT dblink_connect('myTest2', 'hostaddr=<IP-адрес> port=5433 dbname=test1 user=user1 password=<password> options=-csearch_path=ext');

    Пример вывода результата:

     dblink_connect 
    ----------------
     OK
    (1 row)

  2. Функция dblink

    SELECT * FROM dblink('myTest2', 'SELECT * FROM mdt') AS t(a_int int, b_text text, c_time timestamp);

    Пример вывода результата SQL-запроса:

     a_int | b_text |           c_time           
    -------+--------+----------------------------
         4 | fourth | 2023-02-21 13:20:57.977069
        11 | first  | 2023-02-21 13:22:30.738639
        22 | second | 2023-02-21 13:22:30.743876
        33 | third  | 2023-02-21 13:22:31.993623
    (4 rows)

  3. Стандартная функция dblink_connect с использованием параметра host вместо hostaddr в аргументе connstr.

    SELECT dblink_connect('myTest3', 'host=<IP-адрес> port=5433 dbname=test1 user=user1 password=<password> options=-csearch_path=ext');

    Пример вывода результата:

     dblink_connect 
    ----------------
     OK
    (1 row)

  4. Доработанная функция dblink_connect_se.

    Создать схему pgse_profile.

    CREATE SCHEMA pgse_profile;

    Установить необходимые дополнительные расширения – pg_profile, pg_stat_statements.

    CREATE EXTENSION IF NOT EXISTS pg_stat_statements SCHEMA ext;
    CREATE EXTENSION IF NOT EXISTS pg_profile SCHEMA pgse_profile;

    Добавить данные аутентификации пользователя базы данных в шифрованное хранилище паролей.

    pg_auth_config add --host <FQDN_hostname-OR-IPaddress> --port 5433 --database test1 --user user1

    По запросу утилиты ввести интерактивно дважды пароль пользователя.

    enter password:
    *******************
    confirm password:
    *******************

    В случае успешного завершения утилита выдает сообщение:

    Going to add auth record for user: "user1", host: "<FQDN_hostname-OR-IPaddress>", port: "5433", database: "test1"
    new record added

    Проверка корректности записи в хранилище паролей.

    pg_auth_config check | grep user1

    Успешный результат - OK:

    Connection settings for host: "<FQDN_hostname-OR-IPaddress>", port "5433", database "test1", user "user1" are OK

    Создание подключений dblink функцией dblink_connect_se с использованием хранилища паролей:

    • подключение myTestNew1:

      SELECT pgse_profile.dblink_connect_se('myTestNew1','host=<FQDN_hostname> port=5433 dbname=test1 user=user1');

    • подключение myTestNew2:

      SELECT pgse_profile.dblink_connect_se('myTestNew2','host=<IPaddress> port=5433 dbname=test1 user=user1 options=-csearch_path=ext');

    Успешный результат - OK:

    dblink_connect_se 
    -------------------
    OK
    (1 row)

  5. Функция dblink

    SELECT * FROM dblink('myTestNew2', 'SELECT * FROM mdt') AS t(a_int int, b_text text, c_time timestamp);

    Пример вывода результата SQL-запроса:

     a_int | b_text |           c_time           
    -------+--------+----------------------------
         4 | fourth | 2023-02-21 13:20:57.977069
        11 | first  | 2023-02-21 13:22:30.738639
        22 | second | 2023-02-21 13:22:30.743876
        33 | third  | 2023-02-21 13:22:31.993623
    (4 rows)

  6. Функция dblink_get_connections выводит имена всех именованных подключений, которые были созданы на данном сервере.

    SELECT dblink_get_connections();

    Пример вывода списка именованных подключений:

      dblink_get_connections  
    -------------------------
     {myTestNew1,myTestNew2}
    (1 row)

Ссылки на документацию разработчика​

Дополнительно поставляемый модуль dblink: https://www.postgresql.org/docs/15/dblink.html.