Внешние данные
Обзор
16
Авторские права
© Postgres Professional, 2017–2024
Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк
Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Назначение оберток сторонних данных
Настройка доступа к внешним данным
Примеры: postgres_fdw и file_fdw
Другие доступные обертки
3
Назначение
Представление внешних данных как обычных таблиц
стандарт ISO/IEC 9075-9 (SQL/MED)
поддерживаются
SELECT, INSERT, UPDATE, DELETE, TRUNCATE, COPY
триггеры
обычное разграничение доступа (GRANT, REVOKE)
Способ миграции данных из других СУБД
Механизм для шардинга
вместе с секционированием: Postgres Pro Shardman
В стандарте ISO/IEC 9075-9 (SQL/MED) определяется, как базы данных
SQL должны работать с внешними источниками с учетом доступа как
на чтение, так и на запись.
Внешние данные представлены в PostgreSQL как таблицы, которые
называются внешними или сторонними. С ними можно работать при
помощи обычных команд DML: INSERT, UPDATE, DELETE, SELECT,
TRUNCATE, а также COPY. На внешние таблицы можно создавать
триггеры, а для разграничения прав используются команды GRANT
и REVOKE.
Основное отличие внешних таблиц от обычных в том, что данные не
хранятся в самой БД PostgreSQL, а загружаются из внешней системы
(отправляются во внешнюю систему) при выполнении запросов.
Обертки сторонних данных могут быть весьма полезны как при
одновременной работе с данными из разных источников, так и при
миграции данных в PostgreSQL из других СУБД.
Поддержка внешних данных активно развивается, поскольку (вместе
с секционированием) является одним из компонентов для реализации
шардинга: секции основной таблицы представляются внешними
таблицами, физически находящимися на отдельных серверах.
Данные из сторонних таблиц можно сохранять при логическом
резервировании (используя ключ --include-foreign-data утилиты
pg_dump), однако при восстановлении из такой копии нужно учитывать
доступность внешних серверов и их возможности по записи данных.
4
Компоненты
1. Обертка сторонних данных
каков тип внешнего источника данных?
postgres_fdw: внешний сервер PostgreSQL
Чтобы настроить доступ к внешним данным, нужно создать несколько
объектов базы данных.
Все начинается с обертки сторонних данных (foreign data wrapper,
FDW). Обертка определяет тип внешнего источника данных. Например,
postgres_fdw реализует доступ к базам данных PostgreSQL, а file_fdw —
доступ к файлам операционной системы.
6
Компоненты
1. Обертка сторонних данных
каков тип внешнего источника данных?
2. Внешний сервер
как подключиться к внешнему источнику?
postgres_fdw: узел, порт, база данных
Следующий объект — внешний сервер. Сервер определяет, как
подключаться к внешнему источнику данных.
Например, если источником является СУБД, то надо знать узел, порт,
имя базы данных. (Но не имя пользователя и пароль. Эта информация
появится чуть позже.)
Если требуется подключение к нескольким внешним источникам (одного
типа, который определен оберткой), то для каждого из них нужно
создать отдельный сервер.
8
Компоненты
1. Обертка сторонних данных
каков тип внешнего источника данных?
2. Внешний сервер
как подключиться к внешнему источнику?
3. Сопоставление ролей
как локальные роли связаны с разграничением доступа, используемым
внешним источником?
postgres_fdw: и там, и там — обычные роли PostgreSQL
Далее следует сопоставление ролей.
Внешний источник может выполнять аутентификацию при подключении.
В таких случаях необходимо определить, какие локальные роли могут
подключаться к каким учетным записям на внешнем сервере.
Если используется аутентификация по паролю, то здесь же указывается
и пароль.
Суперпользователь может разрешить обычным пользователям
использовать подключение без пароля, установив параметр
password_required в значение false.
10
Компоненты
1. Обертка сторонних данных
каков тип внешнего источника данных?
2. Внешний сервер
как подключиться к внешнему источнику?
3. Сопоставление ролей
как локальные роли связаны с ролями внешнего источника?
4. Внешние таблицы
какова структура внешних данных?
postgres_fdw: данные в обычных таблицах
И, наконец, можно приступить к созданию внешних таблиц. Внешняя
таблица определяет, как представить данные из внешнего источника
в виде обычной реляционной таблицы.
Для внешних таблиц можно добавлять ограничения целостности
NOT NULL и CHECK. Эти ограничения не будут применяться при
выполнении команд, ведь в любом случае PostgreSQL не сможет
обеспечить соблюдение ограничений на внешнем источнике.
Но планировщик может использовать информацию об ограничениях
при построении плана выполнения запроса.
Обертка postgres_fdw для выборки данных использует пакетный режим,
записи передаются пакетами, размер которых задается параметром
сервера fetch_size (по умолчанию 100 строк). Вставка (с помощью
команд INSERT и COPY FROM) также использует пакетный режим,
за количество строк здесь отвечает параметр сервера batch_size.
Для изменения и удаления строк пакетный режим пока не реализован.
12
Соединения и транзакции
Удаленное соединение
открывается при первом обращении к внешним данным
по умолчанию остается открытым до конца сеанса
автоматически восстанавливается при разрыве
Удаленная транзакция
фиксируется или прерывается автоматически, когда фиксируется
или прерывается локальная транзакция
Repeatable Read для локальных транзакций уровней Read Committed
и Repeatable Read
Serializable для локальных транзакций Serializable
согласованность не гарантируется (нет двухфазной фиксации)
При работе с другой базой данных через postgres_fdw важно понимать,
как происходит управление соединениями и транзакциями.
По умолчанию обертка postgres_fdw автоматически открывает
соединение при первом обращении к внешнему серверу (одно на
каждую роль) и оставляет его открытым до конца локального сеанса.
Если обертка обнаружит, что соединение разорвано, она автоматически
попытается установить новое подключение.
Удаленные транзакции «привязаны» к локальным:
●
расширение автоматически начинает удаленную транзакцию, когда
локальная обращается к внешним данным;
●
удаленная транзакция автоматически завершается с завершением
локальной (фиксацией или обрывом).
Тем не менее, локальная и удаленная транзакции — это разные
транзакции, а не одна глобальная. Расширение postgres_fdw пока
не позволяет использовать двухфазную фиксацию, поэтому
согласованность не гарантируется. Например, локальная транзакция
может быть зафиксирована, а удаленная — нет из-за сетевого сбоя.
При фиксации транзакции, в которой участвуют несколько внешних
серверов, подключенных через postgres_fdw, удаленные транзакции
могут завершаться асинхронно, а не последовательно одна за другой.
14
Сравнение с dblink
postgres_fdw dblink
доступные команды SQL SELECT, INSERT, любые
UPDATE, DELETE,
TRUNCATE, COPY
совмещение в запросе да сложно
локальных и внешних данных
управление соединением автоматическое ручное
управление транзакциями автоматическое ручное
глобальные транзакции нет нет
стандартизованный способ да нет
В теме «Фоновые процессы» рассматривалось расширение dblink,
которое предназначено для выполнения запросов на удаленном
сервере PostgreSQL и предоставляет возможности, схожие
с возможностями postgres_fdw.
Обертка postgres_fdw позволяет выполнять ограниченный набор
команд SQL, но в большинстве случаев более удобна: она сама
управляет соединениями и транзакциями («привязывая» их
к локальному соединению и транзакции). Обертка позволяет
использовать в одном запросе как локальные, так и внешние данные;
при этом автоматически формируется распределенный план
выполнения запроса. Синтаксис доступа к данным определяется
стандартом SQL.
Зато расширение dblink позволяет выполнять любые команды SQL.
В некоторых случаях может быть нужным управлять соединениями
и транзакциями вручную.
Однако оба способа не предоставляют глобальных (распределенных)
транзакций.
Таким образом, для доступа к внешним данным PostgreSQL в
большинстве случаев следует выбирать postgres_fdw. Расширение
dblink стоит использовать, если нужны возможности, которые не
предоставляет обертка сторонних данных.
15
Расширение file_fdw
1. Обертка сторонних данных
тип внешнего источника — текстовый файл с разделителями
2. Внешний сервер
как подключиться — не требуется дополнительной информации
3. Сопоставление ролей
не имеет смысла, т. к. во внешнем источнике нет ролей
4. Внешние таблицы
структура данных — описание полей файла в виде столбцов таблицы
Еще одну обертку сторонних данных предоставляет расширение
file_fdw, предназначенное для доступа к файлам в файловой системе
сервера PostgreSQL.
Доступ возможен только на чтение и выполняется с помощью того же
механизма, что и SQL-команда COPY.
17
Другие доступные обертки
Базы данных
ODBC, JDBC
различные реляционные и NoSQL СУБД
Файлы различных форматов
Геоданные и научные данные
Веб-ресурсы
стандартные протоколы (RSS, IMAP, WWW...)
для различных сайтов
Можно написать собственную обертку
на C или Python (с помощью расширения Multicorn)
Рассмотренные обертки сторонних данных, file_fdw и postgres_fdw,
поставляются вместе с PostgreSQL в виде contrib-модулей.
Помимо этого существует множество других оберток, которые
предоставляют доступ ко многим популярным базам данных и не
только. Информацию о них можно получить:
●
на страничке wiki: https://wiki.postgresql.org/wiki/Fdw;
●
на сайте PGXN: http://pgxn.org/tag/fdw/.
Если нужную обертку не удалось найти, то ее можно написать
самостоятельно. Для этого потребуется язык C:
Другой вариант — воспользоваться расширением Multicorn
(https://multicorn.org/), которое позволяет написать обертку на языке
Python.
18
Итоги
Обертки сторонних данных — стандартный способ работы
с данными любых внешних источников
доступ к PostgreSQL и файлам «из коробки»
есть реализации оберток для огромного числа систем
Альтернатива — расширение dblink для выполнения
произвольных запросов на удаленном сервере PostgreSQL
19
Практика
1. Поставщик информирует магазин о новинках книжного
рынка, предоставляя CSV-файл определенного формата.
Загрузите информацию из файла new_books.csv,
расположенного в домашнем каталоге пользователя student,
в таблицы базы данных. Формат файла описан
в комментариях к слайду.
Учтите, что авторы, указанные в файле, могут уже
существовать в базе данных, но в файле могут быть
допущены опечатки.
2. Проверьте в приложении, что новые книги успешно
переданы на логическую реплику, и для них работает
полнотекстовый поиск.
1. CSV-файл содержит следующие поля:
●
фамилия автора;
●
имя автора;
●
отчество автора;
●
название книги;
●
формат издания;
●
количество страниц;
●
ISBN;
●
аннотация;
●
издательство;
●
год выпуска;
●
гарнитура;
●
номер издания;
●
серия;
●
имя файла с обложкой (файл находится в каталоге covers).
Если у книги несколько авторов, то они следуют в файле в том порядке,
в котором указаны в выходных сведениях. При этом для второго
и следующих авторов все поля, относящиеся к книге — пустые.
Воспользуйтесь расширением file_fdw, указав для файла формат CSV.
Для борьбы с опечатками вспомните про расширение pg_trgm,
с которым вы познакомились в практике к теме «Классы операторов».
2. Логическая реплика с подпиской на изменения книг настраивалась
в задании к теме «Логическая репликация».
20
Практика+
1. В двух разных базах данных находятся две таблицы
с одинаковым набором столбцов. Выведите строки, которые
присутствуют в первой таблице, но отсутствуют во второй.
Решите задачу с помощью postgres_fdw и с помощью dblink
и сравните два способа.
2. Как можно проверить, какой уровень изоляции использует
обертка postgres_fdw и как она управляет соединениями
и транзакциями?
1. Расширение dblink демонстрировалось в теме «Фоновые процессы».
2. Настройте на втором сервере журнал сообщений таким образом,
чтобы в него записывалась информация:
●
о подключениях (log_connections);
●
отключениях (log_disconnections);
●
выполняемых командах (log_statement).
Обратитесь к таблице на втором сервере с помощью postgres_fdw
и проверьте, что попало в журнал сообщений.
