Расширяемость
Внешние данные
12
Авторские права
© Postgres Professional, 2020 год.
Авторы: Егор Рогов, Павел Лузанов
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Назначение оберток сторонних данных
Настройка доступа к внешним данным
Примеры: postgres_fdw и file_fdw
Другие доступные обертки
3
Назначение
Представление внешних данных как обычных таблиц
стандарт ISO/IEC 9075-9 (SQL/MED)
поддерживаются SELECT, INSERT, UPDATE, DELETE
триггеры
обычное разграничение доступа (GRANT, REVOKE)
Способ миграции данных из других СУБД
В будущем — механизм для шардинга
вместе с секционированием
В стандарте ISO/IEC 9075-9 (SQL/MED) определяется, каким образом
базы данных SQL должны работать с внешними источниками. При этом
доступ к внешним данным возможен как на чтение, так и на запись.
Внешние данные представлены в PostgreSQL как таблицы, которые
называются внешними или сторонними. С ними можно работать при
помощи обычных команд DML: INSERT, UPDATE, DELETE, SELECT. На
внешние таблицы можно создавать триггеры. А для разграничения прав
используются команды GRANT, REVOKE.
Основное отличие внешних таблиц от обычных в том, что данные
физически не хранятся в БД PostgreSQL, а загружаются из внешней
системы (отправляются во внешнюю систему) при выполнении
запросов.
Обертки сторонних данных могут быть весьма полезны и при миграции
данных в PostgreSQL из других СУБД.
Поддержка внешних данных активно развивается, поскольку (вместе
с секционированием) является одним их компонентов для реализации
шардинга. Идея в том, чтобы секции основной таблицы были
представлены внешними таблицами, физически находящимися на
других серверах. Это можно сделать уже сейчас, но пока не хватает
поддержки двухфазной фиксации, параллельного доступа к секциями
и других составляющих.
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
Далее следует сопоставление ролей.
Внешний источник может выполнять аутентификацию при подключении.
В таких случаях необходимо определить, какие локальные роли могут
подключаться к каким учетным записям на внешнем сервере.
Если используется аутентификация по паролю, то здесь же указывается
и пароль.
10
Компоненты
1. Обертка сторонних данных
каков тип внешнего источника данных?
2. Внешний сервер
как подключиться к внешнему источнику?
3. Сопоставление ролей
как локальные роли связаны с ролями внешнего источника?
4. Внешние таблицы
какова структура внешних данных?
postgres_fdw: данные в обычных таблицах
И, наконец, можно приступить к созданию внешних таблиц. Внешняя
таблица определяет, как представить данные из внешнего источника
в виде обычной реляционной таблицы.
Для внешних таблиц можно добавлять ограничения целостности NOT
NULL и CHECK. Эти ограничения не будут применяться при
выполнении команд, ведь в любом случае PostgreSQL не сможет
обеспечить соблюдение ограничений на внешнем источнике. Но
планировщик может использовать информацию об ограничениях при
построении плана выполнения запроса.
12
Соединения и транзакции
Управление соединениями
открывается при первом обращении к внешним данным
остается открытым до конца сеанса
Управление удаленными транзакциями
фиксируется или прерывается автоматически, когда фиксируется
или прерывается локальная транзакция
Repeatable Read для локальных транзакций уровней Read Committed
и Repeatable Read
Serializable для локальных транзакций Serializable
согласованность не гарантируется (нет двухфазной фиксации)
При работе с внешней СУБД важно понимать, как происходит
управление соединениями и транзакциями.
Расширение postgres_fdw открывает соединение автоматически при
первом обращении к внешним данным (в рамках одного внешнего
сервера — одно на каждую роль), и соединение остается открытым до
конца локального сеанса. С этим надо быть аккуратным, чтобы не
исчерпать ограничение числа подключений.
Удаленные транзакции «привязаны» к локальным:
- расширение автоматически начинает удаленную транзакцию, когда
локальная обращается к внешним данным;
- удаленная транзакция автоматически завершается с завершением
локальной (фиксацией или обрывом).
Тем не менее, локальная и удаленная транзакции — это разные
транзакции, а не одна глобальная. Расширение postgres_fdw пока не
позволяет использовать двухфазную фиксацию, поэтому
согласованность не гарантируется. Например, локальная транзакция
может быть зафиксирована, а удаленная — нет из-за сетевого сбоя.
13
Сравнение с dblink
postgres_fdw dblink
доступные команды SQL SELECT, INSERT, любые
UPDATE, DELETE
совмещение в запросе да сложно
локальных и внешних данных
управление соединением автоматическое ручное
управление транзакциями автоматическое ручное
глобальные транзакции нет нет
стандартизованный способ да нет
В теме «Фоновые процессы» рассматривалось расширение dblink,
которое предназначено для выполнения запросов на удаленном
сервере PostgreSQL и предоставляет возможности, схожие
с возможностями postgres_fdw.
Обертка postgres_fdw позволяет выполнять ограниченный набор
команд SQL, но в большинстве случаев более удобна: она сама
управляет соединениями и транзакциями («привязывая» их
к локальному соединению и транзакции). Она позволяет использовать
в одном запросе как локальные данные, так и внешние; при этом
автоматически формируется распределенный план выполнения
запроса. Синтаксис доступа к данным определяется стандартом SQL.
Зато расширение dblink позволяет выполнять любые команды SQL.
В некоторых случаях может оказаться важным управление
соединениями и транзакциями вручную.
Оба способа не предоставляют глобальных (распределенных)
транзакций.
Таким образом, для доступа к внешним данным в большинстве случаев
следует выбрать postgres_fdw. Расширение dblink стоит использовать,
если нужно сделать что-то, не укладывающееся в рамки оберток
сторонних данных.
14
Расширение file_fdw
1. Обертка сторонних данных
тип внешнего источника — текстовый файл с разделителями
2. Внешний сервер
как подключиться — не требуется дополнительной информации
3. Сопоставление ролей
не имеет смысла, т. к. во внешнем источнике нет ролей
4. Внешние таблицы
структура данных — описание полей файла в виде столбцов таблицы
Другой пример обертки сторонних данных дает расширение file_fdw,
предназначенное для доступа к файлам в ФС сервера PostgreSQL.
Доступ возможен только на чтение и выполняется с помощью того же
механизма, что и SQL-команда COPY.
16
Другие доступные обертки
Базы данных
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.
17
Итоги
Обертки сторонних данных — стандартный способ работы
с данными любых внешних источников
доступ к PostgreSQL и файлам «из коробки»
есть реализации оберток для огромного числа систем
Альтернатива — расширение dblink для выполнения
произвольных запросов на удаленном сервере PostgreSQL
18
Практика
1. Поставщик информирует магазин о новинках книжного
рынка, предоставляя CSV-файл определенного формата.
Загрузите информацию из файла 2020.csv, расположенного
в домашнем каталоге пользователя student, в таблицы базы
данных. Формат файла описан в комментариях к слайду.
Учтите, что авторы, указанные в файле, могут уже
существовать в базе данных, но в файле могут быть
допущены опечатки.
2. Проверьте в приложении, что новые книги успешно
переданы на логическую реплику, и для них работает
полнотекстовый поиск.
1. CSV-файл содержит следующие поля:
- фамилия автора;
- имя автора;
- отчество автора;
- название книги;
- формат издания;
- количество страниц;
- ISBN;
- аннотация;
- издательство;
- год выпуска;
- гарнитура;
- номер издания;
- серия;
- имя файла с обложкой (файл находится в каталоге covers).
Если у книги несколько авторов, то они следуют в файле в том порядке,
в котором указаны в выходных сведениях. При этом для второго и
следующих авторов все поля, относящиеся к книге — пустые.
Воспользуйтесь расширением file_fdw, указав для файла формат CSV.
Для борьбы с опечатками вспомните про расширение pg_trgm,
с которым вы познакомились в практике к теме «Классы операторов».
2. Логическая реплика с подпиской на изменения книг настраивалась
в задании к теме «Логическая репликация».
19
Практика
1. В двух разных базах данных находятся две таблицы
с одинаковым набором столбцов. Выведите строки, которые
присутствуют в первой таблице, но отсутствуют во второй.
Решите задачу с помощью postgres_fdw и с помощью dblink
и сравните два способа.
2. Как можно проверить, какой уровень изоляции использует
обертка postgres_fdw и как она управляет соединениями и
транзакциями?
1. Расширение dblink демонстрировалось в теме «Фоновые процессы».
2. Настройте на втором сервере журнал сообщений таким образом,
чтобы в него записывалась информация
- о подключениях (log_connections);
- об отключениях (log_disconnections);
- о выполняемых командах (log_statement).
Обратитесь к таблице на втором сервере с помощью postgres_fdw
и проверьте, что попало в журнал сообщений.
