postgres_fdw — доступ к данным на внешних серверах PostgreSQL
Эта страница переведена при помощи нейросети GigaChat.
Модуль postgres_fdw предоставляет внешнюю обертку для данных postgres_fdw, которая может использоваться для доступа к данным, хранящимся на внешних серверах PostgreSQL.
Функциональность, предоставляемая этим модулем, существенно перекрывается с функциональностью более старого модуля dblink. Но postgres_fdw предоставляет более прозрачный и соответствующий стандартам синтаксис для доступа к удаленным таблицам и может обеспечить лучшую производительность во многих случаях.
Чтобы подготовиться к удаленному доступу с использованием postgres_fdw:
- Установите расширение
postgres_fdwс помощью CREATE EXTENSION. - Создайте объект внешнего сервера с использованием CREATE SERVER для представления каждой удаленной базы данных, к которой нужно подключиться. Укажите информацию о подключении, за исключением
userиpassword, в качестве параметров объекта сервера. - Создайте сопоставление пользователей с использованием CREATE USER MAPPING для каждого пользователя базы данных, которому требуется разрешить доступ к каждому удаленному серверу. Укажите имя удаленного пользователя и пароль, которые будут использоваться в качестве параметров
userиpasswordсопоставления пользователей. - Создайте внешнюю таблицу с использованием CREATE FOREIGN TABLE или IMPORT FOREIGN SCHEMA для каждой удаленной таблицы, к которой нужно получить доступ. Столбцы внешней таблицы должны соответствовать столбцам целевой удаленной таблицы. Однако можно использовать имена таблиц и/или столбцов, отличные от имен удаленной таблицы, если указать правильные удаленные имена в качестве параметров объекта внешней таблицы.
Теперь нужно только выполнить SELECT из внешней таблицы, чтобы получить доступ к данным, хранящимся в ее базовой удаленной таблице. Также можно изменить удаленную таблицу с помощью INSERT, UPDATE, DELETE, COPY, или TRUNCATE (удаленный пользователь, указанный в сопоставлении пользователей, должен иметь привилегии для выполнения этих действий).
Обратите внимание, что параметр ONLY, указанный в SELECT, UPDATE, DELETE или TRUNCATE, не имеет никакого эффекта при чтении или изменении удаленной таблицы.
В настоящее время postgres_fdw не поддерживает операторы INSERT с предложением ON CONFLICT DO UPDATE. Однако предложение ON CONFLICT DO NOTHING поддерживается при условии, что спецификация вывода уникального индекса опущена. postgres_fdw поддерживает перемещение строки, вызванное выполнением операторов UPDATE на партиционированных таблицах, но в данный момент он не обрабатывает случай, когда удаленная секция, выбранная для вставки перемещаемой строки, также является целевой секцией UPDATE, которая будет обновлена в другом месте той же команды.
В общем случае рекомендуется, чтобы столбцы внешней таблицы были объявлены с точно такими же типами данных и сопоставлениями (если применимо), что и у столбцов удаленной таблицы. Хотя postgres_fdw в настоящее время довольно снисходительно относится к выполнению преобразований типов данных при необходимости, могут возникнуть удивительные семантические аномалии, когда типы или сопоставления не совпадают из-за того, что удаленный сервер интерпретирует условия запроса иначе, чем локальный сервер.
Внешняя таблица может быть объявлена с меньшим количеством столбцов или с другим порядком столбцов, чем у основной удаленной таблицы. Соответствие столбцов удаленной таблице осуществляется по имени, а не по позиции.
Параметры FDW для postgres_fdw
Параметры подключения
Сторонний сервер, использующий postgres_fdw обертку для сторонних данных, может иметь те же параметры, которые libpq принимает в строках подключения, за исключением того, что эти параметры не допускаются или имеют специальную обработку:
user,passwordиsslpassword(указываются в сопоставлении пользователя или через сервисный файл);client_encoding(автоматически устанавливается в соответствии с кодировкой локального сервера);application_nameможет быть указан как в строке подключения, так и в параметреpostgres_fdw.application_name. Если заданы оба значения,postgres_fdw.application_nameимеет приоритет. В отличие отlibpq,postgres_fdwразрешает использовать «escape-последовательности» вapplication_name;fallback_application_name(всегда устанавливается вpostgres_fdw);sslkeyиsslcertмогут быть указаны как в строке подключения, так и в сопоставлении пользователя. Если заданы оба, используется значение из сопоставления пользователя.
Только суперпользователи могут создавать или изменять сопоставление пользователей с настройками sslcert или sslkey.
Обычные пользователи могут подключаться к внешним серверам с использованием аутентификации по паролю или делегированных учетных данных GSSAPI, поэтому укажите параметр password для сопоставлений пользователей, принадлежащих обычным пользователям, где требуется аутентификация по паролю.
Только суперпользователи могут подключаться к удаленным серверам без аутентификации по паролю, поэтому всегда указывайте параметр password для сопоставлений пользователей, не являющихся суперпользователями.
Привилегированный пользователь может переопределить эту проверку на основе каждого сопоставления пользователя путем установки параметра сопоставления пользователя password_required 'false', например:
ALTER USER MAPPING FOR some_non_superuser SERVER loopback_nopw
OPTIONS (ADD password_required 'false');
Чтобы предотвратить использование непривилегированными пользователями прав аутентификации пользователя Unix, от имени которого работает сервер postgres, для повышения до привилегий суперпользователя, только суперпользователь может установить этот параметр на сопоставление пользователя.
Необходимо соблюдать осторожность, чтобы убедиться, что это не позволяет сопоставленному пользователю возможность подключения в качестве суперпользователя к сопоставленной базе данных в соответствии с CVE-2007-3278 и CVE-2007-6601. Не устанавливайте password_required=false на роль public. Помните, что сопоставленный пользователь потенциально может использовать любые клиентские сертификаты, .pgpass, .pg_service.conf и т.д. в домашнем каталоге Unix системного пользователя, от имени которого запущен сервер postgres. Они также могут использовать любое доверительное отношение, предоставленное режимами аутентификации, такими как peer или ident аутентификация.
Параметры имени объекта
Эти параметры можно использовать для управления именами, используемыми в операторах SQL, отправляемых на удаленный сервер PostgreSQL. Они необходимы при создании внешней таблицы с именами, отличными от имен базовой удаленной таблицы.
schema_name (string)
: Параметр, который может быть указан для внешней таблицы, задает имя схемы, которое следует использовать для внешней таблицы на удаленном сервере. Если этот параметр опущен, используется имя схемы внешней таблицы.
table_name (string)
: Параметр, который может быть указан для внешней таблицы, задает имя таблицы, которое следует использовать для внешней таблицы на удаленном сервере. Если этот параметр опущен, используется имя внешней таблицы.
column_name (string)
: Параметр, который можно указать для столбца внешней таблицы, задает имя столбца, которое будет использоваться для этого столбца на удаленном сервере. Если этот параметр опущен, используется имя столбца.
Параметры оценки затрат
postgres_fdw извлекает удаленные данные путем выполнения запросов к удаленным серверам, поэтому идеальная оценка стоимости сканирования внешней таблицы должна быть такой же, как и стоимость ее выполнения на удаленном сервере, плюс некоторые накладные расходы на связь. Самый надежный способ получить такую оценку - это спросить у удаленного сервера, а затем добавить что-то для накладных расходов - но для простых запросов может не стоить затрат на дополнительный удаленный запрос для получения оценки стоимости. Поэтому postgres_fdw предоставляет следующие параметры для управления тем, как выполняется оценка стоимости:
use_remote_estimate (boolean)
: Параметр, который можно задать для внешней таблицы или внешнего сервера, контролирует, выдает ли postgres_fdw удаленные команды EXPLAIN для получения оценок стоимости. Настройка для внешней таблицы перекрывает любую настройку для ее сервера, но только для этой таблицы. По умолчанию установлено значение false.
fdw_startup_cost (floating point)
: Параметр, который можно указать для внешнего сервера, представляет собой значение с плавающей запятой, которое добавляется к расчетной начальной стоимости любого сканирования внешней таблицы на этом сервере. Это отражает дополнительные накладные расходы на установление соединения, синтаксический анализ и планирование запроса на удаленной стороне и т.д. Значение по умолчанию равно 100.
fdw_tuple_cost (floating point)
: Этот параметр, который может быть задан для внешнего сервера, представляет собой значение с плавающей запятой, используемое в качестве дополнительной стоимости на каждую строку при сканировании внешней таблицы на данном сервере. Он отражает дополнительную нагрузку на передачу данных между серверами. Можно увеличить или уменьшить это число, чтобы отразить более высокую или низкую задержку сети до удаленного сервера. Значение по умолчанию: 0.2.
Когда use_remote_estimate истинно, postgres_fdw получает оценку количества строк и затрат от удаленного сервера, а затем добавляет fdw_startup_cost и fdw_tuple_cost к оценкам затрат. Когда use_remote_estimate ложно, postgres_fdw выполняет локальную оценку количества строк и затрат, а затем добавляет fdw_startup_cost и fdw_tuple_cost к оценкам затрат. Эта локальная оценка вряд ли будет очень точной, если не доступны локальные копии статистики удаленной таблицы. Запуск ANALYZE на внешней таблице - это способ обновить локальную статистику; это выполнит сканирование удаленной таблицы, а затем рассчитает и сохранит статистику так, как будто таблица была локальной. Поддержание местной статистики может быть полезным способом снижения накладных расходов на планирование каждого запроса для удаленной таблицы - но если удаленная таблица часто обновляется, местная статистика вскоре устареет.
Следующая опция контролирует поведение такой операции ANALYZE:
analyze_sampling (string)
: Этот параметр, который может быть указан для внешней таблицы или внешнего сервера, определяет, должна ли операция ANALYZE над внешней таблицей выборочно получать данные со стороны удаленного сервера или считывать и передавать все данные и выполнять выборку локально. Допустимые значения: off, random, system, bernoulli и auto. off отключает удаленную выборку, поэтому все данные передаются и выборка выполняется локально. random выполняет удаленную выборку, используя функцию random() для выбора возвращаемых строк, тогда как system и bernoulli полагаются на встроенные методы этих названий. random работает во всех версиях удаленного сервера, тогда как TABLESAMPLE поддерживается начиная с версии 9.5. auto (по умолчанию) автоматически выбирает рекомендуемый метод выборки в зависимости от версии удаленного сервера. Сейчас это означает либо bernoulli, либо random в зависимости от версии удаленного сервера.
Параметры выполнения удаленных команд
По умолчанию для выполнения на удаленном сервере рассматриваются только предложения, использующие встроенные операторы и функции. Предложения, включающие нестандартные функции, проверяются локально после извлечения строк. Если такие функции доступны на удаленном сервере и могут быть использованы для получения тех же результатов, что и локально, производительность можно улучшить, отправив такие предложения для выполнения на удаленном сервере. Это поведение можно контролировать с помощью следующей опции:
extensions (string)
: Параметр представляет собой список имен расширений PostgreSQL, разделенных запятыми, которые установлены в совместимых версиях как на локальном, так и на удаленном серверах. Функции и операторы, которые являются неизменяемыми и принадлежат к перечисленному расширению, будут рассматриваться как отправляемые на удаленный сервер. Этот параметр может быть указан только для внешних серверов, а не для каждой таблицы отдельно.
При использовании опции extensions, ответственность пользователя заключается в том, чтобы указанные расширения существовали и вели себя одинаково как на локальном, так и на удаленном серверах. В противном случае запросы на удаленном сервере могут завершиться сбоем или вести себя непредсказуемо.
fetch_size (integer)
: Параметр указывает количество строк, которое postgres_fdw должно получать при каждой операции выборки. Она может быть указана для внешней таблицы или внешнего сервера. Параметр, указанный для таблицы, перекрывает параметр, указанный для сервера. Значение по умолчанию - 100.
batch_size (integer)
: Параметр указывает количество строк, которое postgres_fdw должно вставлять при каждой операции вставки. Она может быть указана для внешней таблицы или внешнего сервера. Параметр, указанный для таблицы, перекрывает параметр, указанный для сервера. Значение по умолчанию - 1.
Обратите внимание, что фактическое количество строк postgres_fdw вставляется за один раз зависит от количества столбцов и предоставленного значения batch_size. Пакет выполняется как единый запрос, а протокол libpq (который postgres_fdw использует для подключения к удаленному серверу) ограничивает количество параметров в одном запросе до 65 535. Когда количество столбцов * batch_size превышает предел, batch_size будет скорректировано, чтобы избежать ошибки.
Эта опция также применяется при копировании данных во внешние таблицы. В этом случае реальное количество строк postgres_fdw, копируемых одновременно, определяется аналогичным образом, как и в случае вставок, но ограничено максимумом в 1000 из-за ограничений реализации команды COPY.
Параметры асинхронного выполнения
postgres_fdw поддерживает асинхронное выполнение, которое запускает несколько частей узла Append параллельно, а не последовательно для улучшения производительности. Это выполнение можно контролировать с помощью следующего параметра:
async_capable (boolean)
: Этот параметр контролирует, позволяет ли postgres_fdw сканировать сторонние таблицы для асинхронного выполнения одновременно. Он может быть указан для сторонней таблицы или стороннего сервера. Параметр уровня таблицы переопределяет параметр уровня сервера. По умолчанию используется false.
Чтобы гарантировать согласованность данных, возвращаемых с стороннего сервера, postgres_fdw откроет только одно соединение для данного стороннего сервера и будет выполнять все запросы к этому серверу последовательно, даже если задействовано несколько сторонних таблиц, если эти таблицы не подчиняются разным отображениям пользователей. В таком случае может оказаться более эффективным отключить эту опцию, чтобы устранить накладные расходы, связанные с выполнением запросов асинхронно.
Асинхронное выполнение применяется даже тогда, когда узел Append содержит подплан(ы), выполняемый синхронно, а также подплан(ы), выполняемые асинхронно. В этом случае, если асинхронные подпланы обрабатываются с использованием postgres_fdw, кортежи из асинхронных подпланов не возвращаются до тех пор, пока хотя бы один синхронный подплан не вернет все кортежи, поскольку этот подплан выполняется во время ожидания асинхронными подпланами результатов асинхронных запросов, отправленных на сторонние серверы. Это поведение может измениться в будущих выпусках.
Параметры управления транзакциями
Как описано в разделе управления транзакциями, в postgres_fdw управление транзакциями осуществляется путем создания соответствующих удаленных транзакций, а под транзакции управляются путем создания соответствующих удаленных под транзакций. Когда несколько удаленных транзакций участвуют в текущей локальной транзакции, по умолчанию postgres_fdw фиксирует или прерывает эти удаленные транзакции последовательно при фиксации или прерывании локальной транзакции. Когда несколько удаленных подтранзакций участвуют в текущей локальной подтранзакции, по умолчанию postgres_fdw фиксирует или прерывает эти удаленные подтранзакции последовательно при фиксации локальной подтранзакции. Производительность можно улучшить с помощью следующего параметра:
parallel_commit (boolean)
: Параметр контролирует, выполняет ли postgres_fdw параллельное подтверждение удаленных транзакций, открытых на внешнем сервере в локальной транзакции, когда локальная транзакция фиксируется. Этот параметр также применяется к удаленным и локальным под транзакциям. Эта опция может быть указана только для внешних серверов, но не для каждой таблицы. По умолчанию используется значение false.
Если в локальной транзакции задействовано несколько внешних серверов с включенной этой опцией, то несколько удаленных транзакций на этих внешних серверах подтверждаются параллельно между этими внешними серверами при фиксации локальной транзакции.
Когда эта опция включена, внешний сервер с большим количеством удаленных транзакций может столкнуться с негативным влиянием на производительность при фиксации локальной транзакции.
parallel_abort (boolean)
: Этот параметр контролирует, разрешает ли postgres_fdw параллельный откат удаленных транзакций, открытых на внешнем сервере в рамках локальной транзакции, при ее отмене. Этот параметр применим также к удаленным и локальным подпроцессам транзакций. Его можно задать только для внешних серверов, а не отдельно для каждой таблицы. Значение по умолчанию — false.
Если в одной локальной транзакции участвуют несколько внешних серверов с включенными этими параметрами, то при завершении или отмене локальной транзакции удаленные транзакции на всех указанных внешних серверах будут завершаться или отменяться параллельно.
При включении этих параметров внешний сервер с большим числом удаленных транзакций может испытывать снижение производительности при завершении или отмене локальных транзакций.
Параметры изменения данных
По умолчанию предполагается, что все внешние таблицы, использующие postgres_fdw, могут быть обновлены. Это можно переопределить с помощью следующего параметра:
updatable (boolean)
: Этот параметр контролирует, позволяет ли postgres_fdw изменять внешние таблицы с использованием команд INSERT, UPDATE и DELETE. Он может быть указан для внешней таблицы или внешнего сервера. Табличный параметр перекрывает серверный параметр. По умолчанию используется true.
Конечно, если удаленная таблица фактически не обновляется, все равно возникнет ошибка. Использование этого параметра позволяет в первую очередь локально выбросить ошибку без запроса к удаленному серверу. Обратите внимание, однако, что представления будут сообщать о том, что внешняя таблица является изменяемой (или нет) в соответствии с настройкой этого параметра, без какой-либо проверки удаленного сервера.
Параметры операции опустошения таблиц
По умолчанию предполагается, что все внешние таблицы, использующие , могут быть усечены. Это можно переопределить с помощью следующего варианта:
truncatable (boolean)
: Этот параметр контролирует, допускает ли усечение внешних таблиц с использованием команды . Он может быть указан для внешней таблицы или внешнего сервера. Табличный вариант заменяет серверный вариант. По умолчанию используется значение .
Конечно, если удаленная таблица фактически не подлежит усечению, ошибка все равно возникнет. Использование этой опции позволяет в первую очередь локально выбросить ошибку без запроса к удаленному серверу.
Параметры импорта
postgres_fdw может импортировать определения сторонних таблиц с использованием IMPORT FOREIGN SCHEMA. Эта команда создает определения сторонних таблиц на локальном сервере, которые соответствуют таблицам или представлениям, присутствующим на удаленном сервере. Если удаленные таблицы, которые должны быть импортированы, имеют столбцы пользовательских типов данных, у локального сервера должны быть совместимые типы с теми же именами.
Поведение импорта можно настроить с помощью следующих параметров (указанных в команде IMPORT FOREIGN SCHEMA):
import_collate (boolean)
: Этот параметр контролирует, включаются ли параметры столбца COLLATE в определения внешних таблиц, импортированных с внешнего сервера. По умолчанию используется значение true. Возможно, придется отключить эту опцию, если удаленный сервер имеет другой набор имен сопоставления, чем локальный сервер, что вероятно, если он работает под управлением другой операционной системы. Если это сделать, существует очень серьезный риск того, что сопоставление символов в импортированных столбцах таблицы не будет соответствовать базовым данным, что приведет к аномальному поведению запросов.
Даже когда этот параметр установлен на true, импорт столбцов, у которых сопоставление является значением по умолчанию для удаленного сервера, может быть рискованным. Они будут импортированы со значением COLLATE "default", которое выберет сопоставление по умолчанию для локального сервера, и оно может отличаться.
import_default (boolean)
: Этот параметр управляет тем, включены ли выражения столбца DEFAULT в определения внешних таблиц, импортированных с внешнего сервера. Значение по умолчанию - false. Если активировать эту опцию, будьте осторожны с параметрами по умолчанию, которые могут вычисляться иначе на локальном сервере, чем они были бы на удаленном сервере; nextval() часто становится источником проблем. Импорт завершится неудачей, если выражение импортируемого значения по умолчанию использует функцию или оператор, который отсутствует на локальной машине.
import_generated (boolean)
: Этот параметр контролирует, включаются ли выражения столбцов GENERATED в определения внешних таблиц, импортированных с внешнего сервера. По умолчанию используется значение true. Импорт будет полностью невозможен, если выражение, созданное при импорте, использует функцию или оператор, которые отсутствуют локально.
import_not_null (boolean)
: Этот параметр управляет тем, включаются ли ограничения для столбца NOT NULL в определениях внешних таблиц, импортированных с внешнего сервера. Значение по умолчанию - true.
Обратите внимание, что ограничения, отличные от NOT NULL, никогда не будут импортированы из удаленных таблиц. Хотя PostgreSQL поддерживает ограничения проверки на внешних таблицах, нет возможности автоматически их импортировать из-за риска того, что выражение ограничения может быть оценено иначе на локальном и удаленном серверах. Любая такая несогласованность в поведении ограничения проверки может привести к труднообнаруживаемым ошибкам оптимизации запросов. Поэтому, если необходимо импортировать ограничения проверки, придется сделать это вручную, и следует тщательно проверить семантику каждого из них. Для получения более подробной информации о работе ограничений проверки на внешних таблицах см. раздел CREATE FOREIGN TABLE.
Таблицы или внешние таблицы, которые являются разделами другой таблицы, импортируются только тогда, когда они явно указаны в LIMIT TO предложении. В противном случае они автоматически исключаются из IMPORT FOREIGN SCHEMA. Поскольку все данные могут быть доступны через разнесенную таблицу, которая является корнем иерархии разбиения, импорт только разнесенных таблиц должен позволить получить доступ ко всем данным без создания дополнительных объектов.
Параметры управления подключением
По умолчанию все подключения, которые postgres_fdw устанавливает к внешним серверам, остаются открытыми в локальной сессии для повторного использования.
keep_connections (boolean)
: Этот параметр контролирует, сохраняет ли postgres_fdw соединения с внешним сервером открытыми, чтобы последующие запросы могли их повторно использовать. Он может быть указан только для внешнего сервера. По умолчанию используется значение on. Если установлено значение off, все соединения с этим внешним сервером будут удалены в конце каждой транзакции.
Функции
postgres_fdw_get_connections(OUT server_name text, OUT valid boolean) returns setof record
: Эта функция возвращает имена сторонних серверов всех открытых соединений, которые postgres_fdw установил из локальной сессии к сторонним серверам. Она также возвращает, является ли каждое соединение допустимым или нет. Возвращается false, если соединение со сторонним сервером используется в текущей локальной транзакции, но его сторонний сервер или сопоставление пользователей изменено или удалено (Примечание: имя сервера недопустимого соединения будет NULL, если сервер удален), а затем такое недопустимое соединение будет закрыто в конце этой транзакции. В противном случае возвращается true. Если открытых соединений нет, то запись не возвращается. Пример использования функции:
postgres=# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
server_name | valid
-------------+-------
loopback1 | t
loopback2 | f
postgres_fdw_disconnect(server_name text) returns boolean
: Эта функция разрывает открытые соединения, установленные postgres_fdw от локальной сессии до внешнего сервера с указанным именем. Обратите внимание, что может быть несколько подключений к данному серверу с использованием различных сопоставлений пользователей. Если эти подключения используются в текущей локальной транзакции, они не отключаются и выводятся сообщения предупреждения. Эта функция возвращает true, если она хотя бы одно подключение, иначе false. Если внешний сервер с заданным именем не найден, выдается сообщение об ошибке. Пример использования функции:
postgres=# SELECT postgres_fdw_disconnect('loopback1');
postgres_fdw_disconnect
-------------------------
t
postgres_fdw_disconnect_all() returns boolean
: Эта функция разрывает все открытые соединения, установленные postgres_fdw из локальной сессии со сторонними серверами. Если соединения используются в текущей локальной транзакции, они не отключаются и выводятся предупреждающие сообщения. Эта функция возвращает true, если она отключает хотя бы одно соединение, иначе false. Пример использования функции:
postgres=# SELECT postgres_fdw_disconnect_all();
postgres_fdw_disconnect_all
-----------------------------
t
Управление подключениями
postgres_fdw устанавливает соединение со сторонним сервером во время первого запроса, который использует внешнюю таблицу, связанную со сторонним сервером. По умолчанию это соединение сохраняется и повторно используется для последующих запросов в одном сеансе. Это поведение можно контролировать с помощью опции keep_connections для стороннего сервера. Если для доступа к стороннему серверу используются несколько идентификаторов пользователей (отображения пользователей), устанавливается соединение для каждого отображения пользователя.
При изменении определения или удалении внешнего сервера или сопоставления пользователей соответствующие подключения закрываются. Но обратите внимание, что если какие-либо соединения используются в текущей локальной транзакции, они сохраняются до конца транзакции. Закрытые соединения будут восстановлены при необходимости будущими запросами, использующими внешнюю таблицу.
После установления соединения с внешним сервером оно по умолчанию сохраняется до тех пор, пока не завершится локальная или соответствующая удаленная сессия. Чтобы явно отключить соединение, можно отключить параметр keep_connections для стороннего сервера или использовать функции postgres_fdw_disconnect и postgres_fdw_disconnect_all. Например, это полезно для закрытия соединений, которые больше не нужны, тем самым освобождая соединения на стороннем сервере.
Управление транзакциями
Во время выполнения запроса, который ссылается на любую удаленную таблицу на внешнем сервере, postgres_fdw открывает транзакцию на удаленном сервере, если она еще не открыта в соответствии с текущей локальной транзакцией. Удаленная транзакция фиксируется или откатывается при фиксации или отмене локальной транзакции. Точки сохранения аналогично управляются путем создания соответствующих удаленных точек сохранения.
Удаленная транзакция использует уровень изоляции SERIALIZABLE, когда у локальной транзакции есть уровень изоляции SERIALIZABLE; в противном случае используется уровень изоляции REPEATABLE READ. Этот выбор гарантирует, что если запрос выполняет несколько сканирований таблиц на удаленном сервере, он получит согласованные результаты для всех сканирований. Следствием этого является то, что последовательные запросы в рамках одной транзакции будут видеть одни и те же данные с удаленного сервера, даже если происходят одновременные обновления на удаленном сервере из-за других действий. Такое поведение было бы ожидаемо в любом случае, если локальная транзакция использует уровень изоляции SERIALIZABLE или REPEATABLE READ, но может быть неожиданным для локальной транзакции READ COMMITTED. В будущих версиях PostgreSQL эти правила могут быть изменены.
Примечание: в настоящее время не поддерживается подготовка удаленной транзакции для двухфазного подтверждения с помощью postgres_fdw.
Оптимизация удаленных запросов
postgres_fdw пытается оптимизировать удаленные запросы для уменьшения объема передаваемых данных от внешних серверов. Это делается путем отправки условий запроса WHERE к удаленному серверу для выполнения и путем отказа от извлечения столбцов таблицы, которые не требуются для текущего запроса. Чтобы уменьшить риск неправильного выполнения запросов, условия WHERE не отправляются на удаленный сервер, если они используют только встроенные типы данных, операторы и функции или принадлежат расширению, которое указано в параметре extensions внешнего сервера. Операторы и функции в таких условиях также должны быть IMMUTABLE. Для запроса UPDATE или DELETE postgres_fdw пытается оптимизировать выполнение запроса, отправляя весь запрос на удаленный сервер, если нет условий запроса WHERE, которые нельзя отправить на удаленный сервер, нет локальных соединений для этого запроса, нет триггеров уровня строки или хранимых генерируемых столбцов на целевой таблице и нет ограничений CHECK OPTION из родительских представлений. В UPDATE, выражения для назначения целевым столбцам должны использовать только встроенные типы данных, IMMUTABLE-операторы или IMMUTABLE-функции, чтобы уменьшить риск неправильного выполнения запроса.
Когда postgres_fdw сталкивается со слиянием между внешними таблицами на одном внешнем сервере, он отправляет все соединение на внешний сервер, если нет причин полагать, что будет более эффективно извлекать строки из каждой таблицы отдельно, либо если ссылки на таблицу, участвующие в этом процессе, подпадают под разные сопоставления пользователей. При отправке условий JOIN принимаются те же меры предосторожности, что и упомянутые выше для условий WHERE.
Запрос, который фактически отправляется на удаленный сервер для выполнения, может быть проверен с использованием EXPLAIN VERBOSE.
Среда выполнения удаленного запроса
В удаленных сеансах, открытых с помощью postgres_fdw, параметр search_path установлен только на pg_catalog, так что без квалификации схемы видны только встроенные объекты. Это не проблема для запросов, генерируемых самим postgres_fdw, потому что он всегда предоставляет такую квалификацию. Однако это может представлять опасность для функций, которые выполняются на удаленном сервере через триггеры или правила на удаленных таблицах. Например, если удаленная таблица является представлением, любые функции, используемые в этом представлении, будут выполняться с ограниченным путем поиска. Рекомендуется квалифицировать схему всех имен в таких функциях или прикрепить параметры SET search_path (см. CREATE FUNCTION) к таким функциям для установления их ожидаемой среды пути поиска.
postgres_fdw также устанавливает параметры удаленной сессии для различных параметров:
- часовой пояс установлен на
UTC; - стиль даты установлен на
ISO; - стиль интервала установлен на
postgres; - extra_float_digits установлен на
3для удаленных серверов версии 9.0 и новее и установлен на2для более старых версий.
Эти параметры менее вероятно будут проблематичными, чем search_path, но могут быть обработаны с помощью параметров функции SET, если возникнет необходимость.
Не рекомендуется переопределять это поведение путем изменения настроек этих параметров на уровне сеанса; это, скорее всего, приведет к postgres_fdw повреждению.
Совместимость между версиями
postgres_fdw может использоваться с удаленными серверами, начиная с версии PostgreSQL 8.3. Возможность только для чтения доступна, начиная с версии 8.1. Однако ограничение заключается в том, что postgres_fdw обычно предполагает, что неизменяемые встроенные функции и операторы безопасны для отправки на удаленный сервер для выполнения, если они появляются в предложении WHERE для внешней таблицы. Таким образом, встроенная функция, добавленная после выпуска удаленного сервера, может быть отправлена ему для выполнения, что приведет к ошибке "функция не существует" или аналогичной ошибке. Этот тип сбоя можно обойти, переписав запрос, например, заключив ссылку на внешнюю таблицу в подзапрос SELECT с помощью OFFSET 0 в качестве оптимизационного барьера и разместив проблемную функцию или оператор вне подзапроса SELECT.
События ожидания
postgres_fdw может сообщать о следующих событиях ожидания под типом события ожидания Extension:
PostgresFdwCleanupResult
: Ожидание прерывания транзакции на удаленном сервере.
PostgresFdwConnect
: Ожидание установления соединения с удаленным сервером.
PostgresFdwGetResult
: Ожидание получения результатов запроса от удаленного сервера.
Параметры конфигурации
postgres_fdw.application_name (string)
: Задает значение параметра конфигурации application_name, используемое при установлении соединения с внешним сервером postgres_fdw. Это заменяет параметр application_name объекта сервера. Обратите внимание, что изменение этого параметра не влияет ни на какие существующие подключения до тех пор, пока они не будут восстановлены.
postgres_fdw.application_name может быть любой строкой любой длины и содержать даже не-ASCII символы. Однако когда она передается и используется как application_name на удаленном сервере, обратите внимание, что она будет усечена до менее чем NAMEDATALEN символов. Все символы, кроме печатных символов ASCII, заменяются на шестнадцатеричные экранированные последовательности в стиле C. См. application_name для получения дополнительной информации.
% символы начинаются с «последовательностей escape», которые заменяются информацией о состоянии, как описано ниже. Неизвестные esc-последовательности игнорируются. Другие символы копируются прямо в имя приложения. Обратите внимание, что после % и перед опцией нельзя указать знак плюс/минус или числовую константу для выравнивания и заполнения.
| Побег | Эффект |
|---|---|
%a | Имя приложения на локальном сервере |
%c | Идентификатор сеанса на локальном сервере (см. log_line_prefix в разделе «Отчетность и ведение журнала ошибок» для подробностей) |
%C | Имя кластера на локальном сервере (см. cluster_name в разделе «Отчетность и ведение журнала ошибок» для подробностей) |
%u | Имя пользователя на локальном сервере |
%d | Имя базы данных на локальном сервере |
%p | Идентификатор процесса бэкенда на локальном сервере |
%% | Буквально% |
Например, предположим, что пользователь local_user устанавливает соединение с базы данных local_db до foreign_db как пользователь foreign_user, настройка 'db=%d, user=%u' заменяется на 'db=local_db, user=local_user'.
Примеры
Вот пример создания внешней таблицы с помощью 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 integer NOT NULL,
data text
)
SERVER foreign_server
OPTIONS (schema_name 'some_schema', table_name 'some_table');
Важно, чтобы типы данных и другие свойства столбцов, объявленных в CREATE FOREIGN TABLE, соответствовали фактической удаленной таблице. Имена столбцов также должны совпадать, если только параметры column_name не добавлены к отдельным столбцам, чтобы показать, как они называются в удаленной таблице. Во многих случаях использование IMPORT FOREIGN SCHEMA предпочтительнее создания определений внешних таблиц вручную.