Использование dblink

Евгения Кузина

Содержание

Обзор
Подключение к удаленной базе данных
- Использование строки подключения
- Использование стороннего сервера
Работа с удаленной базой данных
Завершение соединения
Реализация автономных транзакций

Обзор

Расширение dblink позволяет осуществлять подключения к удаленным (remote) базам данных PostgreSQL в рамках текущей сессии базы данных.

ВАЖНО

В большинстве сценариев вместо dblink предпочтительнее использовать расширение postgres_fdw, которое покрывает 90% функциональности dblink и является более производительным. В статье Сторонние таблицы приведен пример использования postgres_fdw. Однако такие команды, как VACUUM, REINDEX и CREATE DATABASE можно выполнить в удаленной базе данных только через dblink.

Пакет, требуемый для установки dblink, поставляется с ADP. Чтобы использовать dblink, необходимо только выполнить команду CREATE EXTENSION в той базе данных, в которую вы хотите установить расширение:

CREATE EXTENSION dblink;

ПРИМЕЧАНИЕ

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

ADP использует версию 1.2 расширения dblink. Чтобы это проверить, можно выполнить следующий запрос:

SELECT extversion FROM pg_extension
    WHERE extname = 'dblink';

 extversion
------------
1.2

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

Подключение к удаленной базе данных

Используйте функцию dblink_connect, чтобы установить постоянное соединение с удаленной базой данных. Функция имеет следующий синтаксис:

dblink_connect(text <строка_подключения>) returns <текст>
dblink_connect(text <имя_подключения>, text <строка_подключения>) returns <текст>

где:

<строка_подключения> — стандартная строка подключения libpq или имя стороннего сервера PostgreSQL;
<имя_подключения> — имя, которое будет использоваться для подключения;
<текст> — статус, который всегда возвращает ОК; если происходит ошибка, функция вместо возврата значения генерирует исключение.

Можно установить несколько именованных соединений одновременно, но соединение без имени может быть только одно. Если <имя_подключения> опущено, ADP/PostgreSQL открывает новое безымянное соединение, заменяя уже существующее соединение без имени.

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

Если у ненадежных пользователей есть доступ к базе данных, не приведенной в соответствие с шаблоном безопасного использования схем, необходимо начинать каждую сессию с удаления схем, в которые они могут осуществлять запись, из шаблона пути поиска. Для этого вы можете добавить параметр options=-csearch_path= в строку подключения. Такой подход относится не только к dblink — он применим к каждому интерфейсу для выполнения произвольных команд SQL.

Только суперпользователи могут вызывать функцию dblink_connect для создания соединений, не требующих пароля или аутентификации GSSAPI. Если такая возможность нужна другим пользователям, используйте функцию dblink_connect_u, которая устанавливает незащищенные соединения с удаленной базой данных.

ПРИМЕЧАНИЕ

Все соединения, которые вы планируете использовать, должны быть разрешены в поле PG_HBA для ADP или в файле pg_hba.conf для PostgreSQL.

Использование строки подключения

Следующий код устанавливает подключение с именем test_connection к базе данных postgres на хосте с IP-адресом 10.92.41.60, указывая учетные данные, используемые по умолчанию: пользователь — postgres, пароль — postgres.

SELECT dblink_connect('test_connection', 'hostaddr=10.92.41.60 dbname=postgres user=postgres password=postgres');

 dblink_connect
----------------
 OK

Использование стороннего сервера

Как упоминалось выше, вместо строки подключения можно указать сторонний сервер PostgreSQL. Выполните команду CREATE SERVER, чтобы создать сервер. Для расширения dblink рекомендуется использовать обертку сторонних данных (foreign-data wrapper) dblink_fdw:

CREATE SERVER server_dblink FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr '10.92.41.60', dbname 'postgres');

Чтобы получить доступ к удаленной базе данных, необходимо предоставить пользователям локальной базы данных учетные данные пользователя удаленной базы данных. Используйте команду CREATE USER MAPPING для создания нового объекта сопоставления пользователей. Обратите внимание, что локальный пользователь должен иметь права суперпользователя для выполнения команды dblink_connect. Код ниже создает пользователя user1, добавляет сопоставление и предоставляет этому пользователю права на использование стороннего сервера server_dblink:

CREATE USER user1 WITH SUPERUSER PASSWORD 'password';
CREATE USER MAPPING FOR user1 SERVER server_dblink OPTIONS (user 'postgres', password 'postgres');
GRANT USAGE ON FOREIGN SERVER server_dblink TO user1;

Чтобы протестировать соединение, необходимо подключиться к локальному серверу ADP от имени нового пользователя. Например, можно использовать для этого psql:

$ psql -U user1 -d postgres -h localhost;

Выполните команду dblink_connect:

SELECT dblink_connect('test_connection_server', 'server_dblink');

 dblink_connect
----------------
 OK

Работа с удаленной базой данных

В этом разделе описываются некоторые функции для работы с удаленной базой данных. За полным списком функций обратитесь к официальной документации dblink — connect to other PostgreSQL databases.

dblink_exec

Функция dblink_exec выполняет оператор SQL, который не возвращает строки, в удаленной базе данных. Она имеет следующий синтаксис:

dblink_exec(<имя_подключения>, text <sql> [, bool <завершение_при_ошибке>]) returns text
dblink_exec(<строка_подключения>, text <sql> [, bool <завершение_при_ошибке>]) returns text
dblink_exec(text <sql> [, bool <завершение_при_ошибке>]) returns text

где:

<строка_подключения> — стандартная строка подключения libpq или имя стороннего сервера PostgreSQL.
<имя_подключения> — имя, которое будет использоваться для подключения; не указывайте этот параметр, чтобы использовать безымянное соединение.
<sql> — SQL-запрос для выполнения в удаленной базе данных.
<завершение_при_ошибке> — если параметр равен true (значение по умолчанию), ошибка, возникающая на удаленной стороне, приводит к возникновению ошибки локально. Если для параметра установлено значение false, информация об ошибке удаленного сервера отображается в выводе как сообщение NOTICE, а сама функция возвращает значение ERROR.

Когда указаны два текстовых аргумента, первый считается именем постоянного соединения. Если такое соединение не найдено, первый аргумент рассматривается как строка подключения. В этом случае соединение устанавливается только на время выполнения SQL-запроса, переданного в качестве параметра.

Примеры

Создайте таблицу в удаленной базе данных:

SELECT dblink_exec('test_connection', 'CREATE TABLE test_table (field1 integer, field2 text)');

 dblink_exec
--------------
 CREATE TABLE

Заполните таблицу случайными значениями:

SELECT dblink_exec('test_connection', 'INSERT INTO test_table SELECT i, md5(random()::text) FROM generate_series(1, 100) AS i');

 dblink_exec
--------------
 INSERT 0 100

Установите параметр <завершение_при_ошибке> равным false и попробуйте вставить некорректную строку:

SELECT dblink_exec('test_connection', 'INSERT INTO test_table VALUES (102, 103, 104)', false);

Функция dblink_exec выведет сообщение NOTICE и вернет ERROR:

NOTICE:  INSERT has more expressions than target columns
dblink_exec
-------------
 ERROR

dblink

Функция dblink выполняет запрос, возвращающий строки, в удаленной базе данных. Она имеет следующий синтаксис:

dblink(text <имя_подключения>, text <sql> [, bool <завершение_при_ошибке>]) returns setof record
dblink(text <строка_подключения>, text <sql> [, bool <завершение_при_ошибке>]) returns setof record
dblink(text <sql> [, bool <завершение_при_ошибке>]) returns setof record

Функция dblink имеет те же параметры, что и dblink_exec, за исключением <завершение_при_ошибке> . Параметр <завершение_при_ошибке> имеет одно отличие: если для него установлено значение false, об ошибке удаленного сервера также сообщается в виде сообщения NOTICE, но функция не возвращает строк.

Когда указаны два текстовых аргумента, первый считается именем постоянного соединения. Если сервер не смог найти такое соединение, он рассматривает первый аргумент как строку подключения. В этом случае соединение устанавливается только на время выполнения SQL-запроса, переданного в качестве параметра.

Поскольку dblink можно использовать с любым запросом, в ее декларации объявлено, что функция возвращает запись, а не набор столбцов. Это означает, что следует указывать ожидаемый набор столбцов в вызывающем запросе:

SELECT * FROM dblink('test_connection', 'SELECT * FROM test_table LIMIT 10') AS table1(f1 integer, f2 text);

 f1 |                f2
----+----------------------------------
  1 | fdd8565308de9fb9e997e68460ed01d3
  2 | 10c9ba817a368129be55e0ff9dee0235
  3 | 91aa1a9332a7e900f3d08f7c42414884
  4 | 10f202262002d62585f791de703b278b
  5 | 5bd6c9c063d564c8869d32e1251b93a8
  6 | 6f1570d67f3c31c334781aad0dcc89b5
  7 | 172cb5f628946db212b8fead6838e8a7
  8 | e8de5993d5e114e6b1698de637275aec
  9 | 89a9c7e86d6a65eccfcd371efd0f9235
 10 | 765f1de7e23afb773d2599a9747bfd62

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

CREATE VIEW remote_table AS
  SELECT *
    FROM dblink('test_connection', 'SELECT * FROM test_table')
    AS table1(f1 integer, f2 text);

SELECT * FROM remote_table WHERE f1 < 20;

 f1 |                f2
----+----------------------------------
  1 | fdd8565308de9fb9e997e68460ed01d3
  2 | 10c9ba817a368129be55e0ff9dee0235
  3 | 91aa1a9332a7e900f3d08f7c42414884
  4 | 10f202262002d62585f791de703b278b
  5 | 5bd6c9c063d564c8869d32e1251b93a8
  6 | 6f1570d67f3c31c334781aad0dcc89b5
  7 | 172cb5f628946db212b8fead6838e8a7
  8 | e8de5993d5e114e6b1698de637275aec
  9 | 89a9c7e86d6a65eccfcd371efd0f9235
 10 | 765f1de7e23afb773d2599a9747bfd62
 11 | 40170f8041d6d14e64d9fe85d4b1341d
 12 | a5c4daa3c95c263338c520c901adb076
 13 | 4bbee39fbc8bb084392b6db3c3cd953c
 14 | 280762ef18f7ccf1cb42d3016ea8d096
 15 | 978a4cdb33644799710a00240d8273c2
 16 | 6e9facc41e99a7d72560eedbaa347964
 17 | 8d0dfc0d641cb911172cb97f2d3ac1a0
 18 | ec2f06f1606d4d463aa5a66c2a7a6091
 19 | f4673b0474c2682354539218ad42365f

Завершение соединения

Функция dblink_disconnect закрывает постоянное соединение с удаленной базой данных, которое ранее было открыто с помощью dblink_connect. Она имеет следующий синтаксис:

dblink_disconnect() returns text
dblink_disconnect(text <имя_соединения>) returns text

Функция dblink_disconnect, вызываемая без аргументов, закрывает безымянное соединение.

Возвращаемый статус всегда ОК. Если происходит ошибка, функция вместо возврата значения генерирует исключение.

SELECT dblink_disconnect('test_connection');

 dblink_disconnect
-------------------
 OK

Если вам нужно определить имя открытого соединения, используйте функцию dblink_get_connections, которая возвращает имена всех открытых поименованных соединений dblink:

SELECT dblink_get_connections();

 dblink_get_connections
------------------------
 {test_connection}

Реализация автономных транзакций

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

Создайте таблицу для хранения логов и тестовую таблицу:

CREATE TABLE logs(
    log_id BIGSERIAL PRIMARY KEY,
    level TEXT NOT NULL,
    message TEXT NOT NULL,
    time TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL);

CREATE TABLE test_table_logs (field1 integer, field2 text);

Определите функцию на языке PL/pgSQL, которая добавляет запись в таблицу logs, используя для этого dblink:

CREATE OR REPLACE FUNCTION logging(param_level text, param_message text) RETURNS void AS
$$
DECLARE
  INSERT_SQL text;

BEGIN
  INSERT_SQL := format('INSERT INTO logs (level, message) VALUES (''%s'',''%s'');', param_level, param_message);

  PERFORM dblink_connect('connection1', 'dbname=postgres');
  PERFORM dblink_exec('connection1', INSERT_SQL);
  PERFORM dblink_exec('connection1','COMMIT;');
  PERFORM dblink_disconnect('connection1');
END
$$ LANGUAGE 'plpgsql';

Начните транзакцию с помощью команды BEGIN и вставьте данные в таблицу test_table_logs:
```
BEGIN;

INSERT INTO test_table_logs (field1, field2) VALUES (1, 'test row');
```
Вызовите функцию logging, определенную выше:
```
SELECT logging('ERROR', 'An error occurs');
```

Проверьте содержимое таблиц:

SELECT * FROM logs;

 log_id | level |     message     |            time
--------+-------+-----------------+----------------------------
      1 | ERROR | An error occurs | 2026-01-15 10:31:50.537001

SELECT * FROM test_table_logs;

 field1 |  field2
--------+----------
      1 | test row

В каждой таблице содержится по одной строке.

Откатите транзакцию с помощью команды ROLLBACK, а затем также проверьте содержимое таблиц:

ROLLBACK;

SELECT * FROM logs;

 log_id | level |     message     |            time
--------+-------+-----------------+----------------------------
      1 | ERROR | An error occurs | 2026-01-15 10:31:50.537001

В таблице logs изменения сохранились.

SELECT * FROM test_table_logs;

 field1 | field2
--------+--------
(0 rows)

В таблице test_table_logs строки отсутствуют.

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

Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней