postgres_fdw. Обертка сторонних данных для PostgreSQL#

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

Связанные компоненты: отсутствуют.

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

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

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

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

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

Параметры#

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

Для стороннего сервера можно задать те же параметры, которые принимает libpq в строках подключения (33.1. Database Connection Control Functions), за исключением следующих параметров, которые не допускаются или обрабатывается по-другому:

• user, password и sslpassword следует задавать в сопоставлениях пользователей или в файле описания служб;

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

• fallback_application_name всегда = postgres_fdw;

• sslkey и sslcert могут задаваться в свойствах соединения и/или сопоставления пользователей, при этом настройки сопоставления пользователей более приоритетны.

Создавать и изменять сопоставления пользователей, устанавливающие параметр sslcert или sslkey, могут только суперпользователи.

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

Суперпользователь может отключить проверку пароля на уровне сопоставления пользователей, установив параметр password_required в значение false, например:

ALTER USER MAPPING FOR some_non_superuser SERVER loopback_nopw
OPTIONS (ADD password_required 'false');

Имя объекта#

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

Оценка стоимости#

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

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

Когда поведение use_remote_estimate:

• включено, postgres_fdw:

  • получает количество строк и оценку стоимости с удаленного сервера;

  • добавляет к оценке стоимости fdw_startup_cost и fdw_tuple_cost.

• отключено, postgres_fdw:

  • рассчитывает число строк и оценку стоимости локально;

  • добавляет к этой оценке fdw_startup_cost и fdw_tuple_cost.

Локальная оценка может быть точной только при наличии локальной копии статистики удаленных таблиц. Обновить эту статистику для сторонней таблицы можно с помощью команды ANALYZE, при этом удаленная таблица будет просканирована, а по ее содержимому будут вычислена и сохранена статистика как для локальной таблицы.

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

Удаленное выполнение#

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

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

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

• extensions – перечисленные через запятую расширения PostgreSQL, которые установлены и имеют совместимые версии на локальном и на удаленном сервере. Относящиеся к перечисленным расширениям и при этом постоянные (immutable) функции и операторы могут передаваться на выполнение удаленному серверу. Этот параметр можно задать только для стороннего сервера, но не для таблицы; при использовании параметра extensions пользователь сам отвечает за то, чтобы перечисленные расширения существовали, и их поведение было одинаковым на локальном и удаленном сервере, в противном случае, удаленные запросы могут выдавать ошибки или неожиданные результаты;

• fetch_size – количество строк, которое должна получать postgres_fdw в одной операции выборки; можно задать для сторонней таблицы или стороннего сервера; значение по умолчанию — 100 строк.

Изменение данных#

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

Параметр updatable определяет, будет ли postgres_fdw допускать изменения в сторонних таблицах посредством команд INSERT, UPDATE и DELETE. Его можно задать для сторонней таблицы или для стороннего сервера.

Параметр, определенный на уровне таблицы, переопределяет параметр уровня сервера. Значение по умолчанию — true (изменения разрешены).

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

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

Импорт#

Обертка postgres_fdw позволяет импортировать определения сторонних таблиц с применением команды IMPORT FOREIGN SCHEMA, которая создает на локальном сервере определения сторонних таблиц, соответствующих таблицам или представлениям на удаленном сервере. Если импортируемые удаленные таблицы содержат столбцы пользовательских типов данных, на локальном сервере должны быть совместимые типы с теми же именами.

Параметры импорта и описание в документации PostgreSQL.

Управление соединением#

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

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

Управление транзакциями#

В процессе выполнения запроса, в котором участвуют какие-либо удаленные таблицы на стороннем сервере, postgres_fdw:

• открывает транзакцию на удаленном сервере, если такая транзакция еще не была открыта для текущей локальной транзакции;

• удаленная транзакция фиксируется или прерывается, когда фиксируется или прерывается локальная транзакция.

Подобным образом реализуется и управление точками сохранения.

Для удаленной транзакции выбирается режим изоляции SERIALIZABLE, когда локальная транзакция открыта в режиме SERIALIZABLE; в противном случае применяется режим REPEATABLE READ.

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

Это поведение ожидаемо для локальной транзакции в режимах SERIALIZABLE и REPEATABLE READ, но для локальной транзакции в режиме READ COMMITTED оно может быть неожиданным.

Оптимизация удаленных запросов#

Описание в разделе

Окружение удаленного выполнения запросов#

Описание в разделе

Доработка#

Доработка: Добавляется интеграция с хранилищем паролей. Расширение может получить пароль из хранилища по набору параметров host, port, database, username получаемых из настроек подключения к FOREIGN SERVER и USER MAPPING.

Версия: 6.6.0

Указание пароля в USER MAPPING теперь необязательно. Если раньше, в целях безопасности, для расширения была запрещена аутентификация без пароля, то теперь логика остается прежней, но с учетом возможности использования пароля из хранилища. Добавить запись без пароля по-прежнему можно, однако соединение не будет установлено, если пароль не был получен ни из хранилища, ни из настройки USER MAPPING (см. описание опции password_required в оригинальной версии расширения).

Для обеспечения обратной совместимости остается возможность задать пароль в явном виде при создании USER MAPPING (оригинальное поведение). При этом, если пароль задан и в USER MAPPING, и в хранилище, предпочтение отдается опции USER MAPPING.

Дополнительно, в хранилище паролей обязательно должны быть указаны параметры roles и appnames. В случае пустых полей, либо несоответствия текущего пользователя значению поля roles и несоответствию значения в поле appnames значению postgres_fdw – пароль получен не будет.

Схематично данный процесс можно представить следующим образом:

Схема процесса «выполнение запроса в стороннюю таблицу»#

Описание шагов процесса обновления, представленных на схеме:

Схема процесса «получение пароля из хранилища»#

Описание шагов процесса обновления, представленных на схеме:

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

• не поддерживаются операторы INSERT с предложением ON CONFLICT DO UPDATE;

• предложение ON CONFLICT DO NOTHING поддерживается при отсутствии указания для выбора уникального индекса;

• поддерживается перемещение строк, вызванное командами UPDATE для партиционированных таблиц; в то же время невозможно выполнить изменение, при котором удаленная партиция, выбранная для добавления перемещаемой строки, также является целевой партицией для UPDATE и должна модифицироваться позже той же командой.

Установка#

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

CREATE EXTENSION postgres_fdw SCHEMA ext;

Настройка#

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

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

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

1. Создайте объект стороннего сервера с помощью команды CREATE SERVER. Сторонний сервер будет представлять удаленную базу данных, к которой необходимо подключаться. В параметрах объекта сервера указываются свойства подключения:

   CREATE SERVER test_foreign_server
       FOREIGN DATA WRAPPER postgres_fdw
       OPTIONS (host '<host>', port '5433', dbname 'test_pgfdw');
   

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

   CREATE USER MAPPING FOR postgres
       SERVER test_foreign_server
       OPTIONS (user 'pgfdw', password '<password>');
   

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

   CREATE FOREIGN TABLE test_foreign_table (
       num int,
       name text,
       name1 varchar
       )
       SERVER test_foreign_server
       OPTIONS (
         schema_name 'ext',
         table_name 'tst_pgfdw'
         );
   

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

   В случае успешного создания, внешняя таблица Foreign table появится в списке объектов:

                                     Foreign table "ext.test_foreign_table"
   Column |       Type        | Collation | Nullable | Default | FDW options | Storage  | Stats target | Description
   --------+-------------------+-----------+----------+---------+-------------+----------+--------------+-------------
    num    | integer           |           |          |         |             | plain    |              |
    name   | text              |           |          |         |             | extended |              |
    name1  | character varying |           |          |         |             | extended |              |
   Server: test_foreign_server
   FDW options: (schema_name 'ext', table_name 'tst_pgfdw')
   

4. Для обращения к данным, хранящимся в удаленной таблице, можно выполнять SELECT. Также можно изменять данные в удаленной таблице, выполняя INSERT, UPDATE, DELETE или COPY. Удаленный пользователь, указанный в сопоставлении, должен иметь необходимые права для этого.

   select * from test_foreign_table;
    num |  name   |   name1
   -----+---------+-----------
      1 | Ivanov  | Ingeneer
      2 | Petrov  | Developer
      3 | Sidorov | QA
   (3 rows)
   

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

Дополнительно поставляемый модуль postgres_fdw.