E.32. postgres_fdw

Модуль postgres_fdw предоставляет обёртку сторонних данных postgres_fdw, используя которую можно обращаться к данным, находящимся на внешних серверах PostgreSQL.

Функциональность этого модуля во многом пересекается с функциональностью старого модуля dblink. Однако postgres_fdw предоставляет более прозрачный и стандартизированный синтаксис для обращения к удалённым таблицам и во многих случаях даёт лучшую производительность.

Чтобы подготовиться к обращению к удалённым данным через postgres_fdw:

  1. Установите расширение postgres_fdw с помощью команды CREATE EXTENSION.

  2. Создайте объект стороннего сервера, используя CREATE SERVER, который будет представлять удалённую базу данных, к которой вы хотите подключаться. Укажите свойства подключения, кроме user и password, в параметрах объекта сервера.

  3. Создайте сопоставление пользователей, используя CREATE USER MAPPING, для каждого пользователя базы, которому нужен доступ к удалённому серверу. Укажите имя и пароль удалённого пользователя в параметрах user и password сопоставления.

  4. Создайте стороннюю таблицу, используя CREATE FOREIGN TABLE, для каждой удалённой таблицы, к которой вы хотите обращаться. Колонки сторонней таблицы должны соответствовать колонкам целевой удалённой таблицы. Однако вы можете использовать локально имена таблиц и/или колонок, отличные от удалённых, если укажете корректные удалённые имена в параметрах объекта сторонней таблицы.

После этого для обращения к данным, хранящимся в нижележащей удалённой таблице, вам нужно только выполнять SELECT. Вы также можете изменять данные в удалённой таблице, выполняя INSERT, UPDATE или DELETE. (Разумеется, удалённый пользователь, указанный в сопоставлении, должен иметь необходимые права для этого.)

Обычно рекомендуется объявлять колонки сторонней таблицы точно с теми же типами данных и правилами сортировки, если они применимы, как у целевых колонок удалённой таблицы. Хотя postgres_fdw в настоящее время довольно лоялен к преобразованиям типов данных при необходимости, но когда типы или правила сортировки не совпадают, возможны неожиданные семантические аномалии, вследствие того, что удалённый сервер будет обрабатывать предложения WHERE не совсем так, как локальный сервер.

Заметьте, что сторонняя таблица может быть объявлена с меньшим количеством или с другим порядком колонок, чем в нижележащей удалённой таблице. Сопоставление колонок удалённой таблицы осуществляется по имени, а не по позиции.

E.32.1. Параметры обёртки для postgres_fdw

E.32.1.1. Параметры подключения

Для стороннего сервера, настраиваемого с использованием обёртки сторонних данных postgres_fdw, можно задать те же параметры, что принимает libpq в строках подключения, как описано в Подразделе 31.1.2, за исключением следующих недопустимых параметров:

  • user и password (их следует задавать в сопоставлениях пользователей)

  • client_encoding (автоматически принимается равной локальной кодировке сервера)

  • fallback_application_name (всегда postgres_fdw)

Подключаться к сторонним серверам без аутентификации по паролю могут только суперпользователи, поэтому в сопоставлениях для обычных пользователей всегда нужно задавать пароль (password).

E.32.1.2. Параметры имени объекта

Эти параметры позволяют управлять тем, как на удалённый сервер PostgreSQL будут передаваться имена, фигурирующие в операторах SQL. Данные параметры нужны, когда сторонняя таблица создаётся с именами, отличными от имён удалённой таблицы.

schema_name

Этот параметр, который может задаваться для сторонней таблицы, указывает имя схемы для обращения к этой таблице на удалённом сервере. Если данный параметр опускается, применяется схема сторонней таблицы.

table_name

Этот параметр, который может задаваться для сторонней таблицы, указывает имя таблицы для обращения к этой таблице на удалённом сервере. Если данный параметр опускается, применяется имя сторонней таблицы.

column_name

Этот параметр, который может задаваться для колонки сторонней таблицы, указывает имя колонки для обращения к этой колонке на удалённом сервере. Если данный параметр опускается, применяется исходное имя колонки.

E.32.1.3. Параметры оценки стоимости

Модуль postgres_fdw получает удалённые данные, выполняя запросы на удалённых серверах, поэтому в идеале ожидаемая стоимость сканирования сторонней таблицы должна равняться стоимости выполнения на удалённом сервере плюс издержки сетевого взаимодействия. Самый надёжный способ получить такие оценки — узнать стоимость у удалённого сервера и добавить некоторую надбавку — но для простых запросов может быть невыгодно передавать дополнительный запрос, только чтобы получить оценку стоимости. Поэтому postgres_fdw предоставляет следующие параметры, позволяющие управлять вычислением оценки стоимости:

use_remote_estimate

Этот параметр, который может задаваться для сторонней таблицы или для стороннего сервера, определяет, будет ли postgres_fdw выполнять удалённо команды EXPLAIN для получения оценок стоимости. Параметр, заданный для сторонней таблицы, переопределяет параметр сервера, но только для данной таблицы. Значение по умолчанию — false (выкл.).

fdw_startup_cost

Этот параметр, который может задаваться для стороннего сервера, устанавливает числовое значение, добавляемое к оценке стоимости запуска для любого сканирования сторонней таблицы на этом сервере. Он выражает дополнительные издержки на установление подключения, разбор и планирование запроса на удалённой стороне и т. д. Значение по умолчанию — 100.

fdw_tuple_cost

Этот параметр, который может задаваться для стороннего сервера, устанавливает числовое значение, выражающее дополнительную цену чтения одного кортежа из сторонней таблицы на этом сервере. Это число можно увеличить или уменьшить, отражая меньшую или большую фактическую скорость сетевого взаимодействия с удалённым сервером. Значение по умолчанию — 0.01.

Когда поведение use_remote_estimate включено, postgres_fdw получает количество строк и оценку стоимости с удалённого сервера, а затем добавляет к оценке стоимости fdw_startup_cost и fdw_tuple_cost. Когда поведение use_remote_estimate отключено, postgres_fdw рассчитывает число строк и оценку стоимости локально, а затем так же добавляет к этой оценке fdw_startup_cost и fdw_tuple_cost. Локальная оценка может быть точной только при условии наличия локальной копии статистики удалённых таблиц. Обновить эту статистику для сторонней таблицы можно с помощью команды ANALYZE; при этом удалённая таблица будет просканирована, и по её содержимому будут вычислена и сохранена статистика как для локальной таблицы. Локальное хранение статистики может быть полезно для сокращения издержек планирования для удалённой таблицы — но если удалённая таблица меняется часто, локальная статистика будет быстро устаревать.

E.32.1.4. Параметры изменения данных

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

updatable

Этот параметр определяет, будет ли postgres_fdw допускать изменения в сторонних таблицах посредством команд INSERT, UPDATE и DELETE. Его можно задать для сторонней таблицы или для стороннего сервера. Параметр, определённый на уровне таблицы, переопределяет параметр уровня сервера. Значение по умолчанию — true (изменения разрешены).

Конечно, если удалённая таблица на самом деле не допускает изменения, всё равно произойдёт ошибка. Использование этого параметра прежде всего позволяет выдать ошибку локально, не обращаясь к удалённому серверу. Заметьте, однако, что представление information_schema будет показывать, что определённая сторонняя таблица postgres_fdw является изменяемой (или нет), согласно значению данного параметра, не проверяя это на удалённом сервере.

E.32.2. Управление соединением

Модуль postgres_fdw устанавливает соединение со сторонним сервером при первом запросе, в котором участвует сторонняя таблица, связанная со сторонним сервером. Это соединение сохраняется и повторно используется для последующих запросов в том же сеансе. Однако, если для обращения к стороннему серверу задействуются разные пользователи (сопоставления пользователей), отдельное соединение устанавливается для каждого сопоставления пользователей.

E.32.3. Управление транзакциями

В процессе выполнения запроса, в котором участвуют какие-либо удалённые таблицы на стороннем сервере, postgres_fdw открывает транзакцию на удалённом сервере, если такая транзакция ещё не была открыта для текущей локальной транзакции. Эта удалённая транзакция фиксируется или прерывается, когда фиксируется или прерывается локальная транзакция. Подобным образом реализуется и управление точками сохранения.

Для удалённой транзакции выбирается режим изоляции SERIALIZABLE, когда локальная транзакция открыта в режиме SERIALIZABLE; в противном случае применяется режим REPEATABLE READ. Этот выбор гарантирует, что если запрос сканирует несколько таблиц на удалённом сервере, он будет получать согласованные данные одного снимка для всех сканирований. Как следствие, последовательные запросы в одной транзакции будут видеть одни данные удалённого сервера, даже если на нём параллельно происходят изменения, вызванные другими действиями. Это поведение ожидаемо для локальной транзакции в режимах SERIALIZABLE и REPEATABLE READ, но для локальной транзакции в режиме READ COMMITTED оно может быть неожиданным. В будущих выпусках PostgreSQL эти правила могут быть изменены.

E.32.4. Оптимизация удалённых запросов

Модуль postgres_fdw пытается оптимизировать удалённые запросы с целью сокращения объёма данных, получаемых от сторонних серверов. Для этого удалённому серверу на выполнение передаются предложения WHERE, а колонки, не требующиеся в текущем запросе, с него не запрашиваются. Чтобы сократить риск некорректного выполнения запросов, предложения WHERE передаются на удалённый сервер, только если в них используются встроенные типы данных, операторы и функции. Кроме того, операторы и функции в таких запросах должны быть постоянными (IMMUTABLE).

Запрос, фактически отправляемый удалённому серверу для выполнения, можно изучить с помощью команды EXPLAIN VERBOSE.

E.32.5. Совместимость с разными версиями

Модуль postgres_fdw может применяться с удалёнными серверами версий, начиная с PostgreSQL 8.3. Способность только чтения данных доступна, начиная с 8.1. Однако, при этом есть ограничение, вызванное тем, что postgres_fdw полагает, что постоянные встроенные функции и операторы могут безопасно передаваться на удалённый сервер для выполнения, если они фигурируют в предложении WHERE для сторонней таблицы. Таким образом, встроенная функция, добавленная в более новой версии, чем на удалённом сервере, может быть отправлена на выполнение, что в результате приведёт к ошибке "функция не существует" или подобной. Отказы такого типа можно предотвратить, переписав запрос, например, поместив ссылку на стороннюю таблицу во вложенный SELECT с OFFSET 0 в качестве защиты от оптимизации, и применив проблематичную функцию или оператор снаружи этого вложенного SELECT.

E.32.6. Примеры

Ниже приведёт пример создания сторонней таблицы с применением postgres_fdw. Сначала установите расширение:

CREATE EXTENSION postgres_fdw;

Затем создайте сторонний сервер с помощью команды CREATE SERVER. В данном примере мы хотим подключиться к серверу PostgreSQL, работающему по адресу 192.83.123.89, порт 5432. База данных, к которой устанавливается подключение, на удалённом сервере называется foreign_db:

CREATE SERVER foreign_server
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');

Для определения роли, которая будет задействована на удалённом сервере, с помощью CREATE USER MAPPING задаётся сопоставление пользователей:

CREATE USER MAPPING FOR local_user
        SERVER foreign_server
        OPTIONS (user 'foreign_user', password 'password');

Теперь можно создать стороннюю таблицу, применив команду CREATE FOREIGN TABLE. В этом примере мы хотим обратиться к таблице some_schema.some_table на удалённом сервере. Локальным именем этой таблицы будет foreign_table:

CREATE FOREIGN TABLE foreign_table (
        id serial NOT NULL,
        data text
)
        SERVER foreign_server
        OPTIONS (schema_name 'some_schema', table_name 'some_table');

Важно, чтобы типы данных и другие свойства колонок, объявленных в CREATE FOREIGN TABLE, соответствовали фактической удалённой таблице. Также должны соответствовать имена колонок, если только вы не добавите параметры column_name для отдельных колонок, задающие их реальные имена в удалённой таблице.

E.32.7. Автор

Шигеру Ханада