# Занятие 20: Внешние данные / Обзор > Исходная страница в нативном Markdown: Внешние данные / Обзор --- Индекс LLMS: [llms.txt](/ru/llms.txt) --- > [!NOTE] > Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. > **HTML source:** [dev2_20_fdw_overview.html](https://edu.postgrespro.ru/16/dev2-16/dev2_20_fdw_overview.html) > **Archive:** [DEV2-handouts-16.zip](https://edu.postgrespro.ru/16/DEV2-handouts-16.zip) > **Страниц PDF:** 27 ## Page 1: Внешние данные / Обзор {#source-page-001} ### Авторские права © Postgres Professional, 2017–2024 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал) ### Использование материалов курса Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса. ### Обратная связь Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru ### Отказ от ответственности Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения. ## Page 2: Темы {#source-page-002} - Назначение оберток сторонних данных - Настройка доступа к внешним данным - Примеры: postgres\_fdw и file\_fdw - Другие доступные обертки ## Page 3: Назначение {#source-page-003} **Представление внешних данных как обычных таблиц** - стандарт 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), однако при восстановлении из такой копии нужно учитывать доступность внешних серверов и их возможности по записи данных. ## Page 4: Компоненты {#source-page-004} **1. Обертка сторонних данных** - каков тип внешнего источника данных? - postgres\_fdw: внешний сервер PostgreSQL ### Комментарии преподавателя Чтобы настроить доступ к внешним данным, нужно создать несколько объектов базы данных. Все начинается с обертки сторонних данных (foreign data wrapper, FDW). Обертка определяет тип внешнего источника данных. Например, postgres\_fdw реализует доступ к базам данных PostgreSQL, а file\_fdw — доступ к файлам операционной системы. ## Page 5: Обертка сторонних данных {#source-page-005} ```sql => CREATE DATABASE fdw_overview; ``` ```sql CREATE DATABASE ``` ```sql => \c fdw_overview ``` ```text You are now connected to database "fdw_overview" as user "student". ``` Обертка создается при создании расширения: ```sql => CREATE EXTENSION postgres_fdw; ``` ```sql CREATE EXTENSION ``` ```text => \dew ``` ```text List of foreign-data wrappers Name | Owner | Handler | Validator --------------+---------+----------------------+------------------------ postgres_fdw | student | postgres_fdw_handler | postgres_fdw_validator (1 row) ``` ## Page 6: Компоненты {#source-page-006} **1. Обертка сторонних данных** - каков тип внешнего источника данных? - 2. Внешний сервер - как подключиться к внешнему источнику? - postgres\_fdw: узел, порт, база данных ### Комментарии преподавателя Следующий объект — внешний сервер. Сервер определяет, как подключаться к внешнему источнику данных. Например, если источником является СУБД, то надо знать узел, порт, имя базы данных. (Но не имя пользователя и пароль. Эта информация появится чуть позже.) Если требуется подключение к нескольким внешним источникам (одного типа, который определен оберткой), то для каждого из них нужно создать отдельный сервер. ## Page 7: Внешний сервер {#source-page-007} В качестве внешнего сервера выберем базу данных книжного магазина на нашем же локальном сервере. ```sql => CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS ( host 'localhost', port '5432', dbname 'bookstore2' ); ``` ```sql CREATE SERVER ``` Обратите внимание, что в параметрах сервера не указывается роль. ```text => \x \des+ \x ``` ```text Expanded display is on. List of foreign servers -[ RECORD 1 ]--------+----------------------------------------------------- Name | remote_server Owner | student Foreign-data wrapper | postgres_fdw Access privileges | Type | Version | FDW options | (host 'localhost', port '5432', dbname 'bookstore2') Description | ``` ```text Expanded display is off. ``` ## Page 8: Компоненты {#source-page-008} **1. Обертка сторонних данных** - каков тип внешнего источника данных? - 2. Внешний сервер - как подключиться к внешнему источнику? - 3. Сопоставление ролей - как локальные роли связаны с разграничением доступа, используемым - внешним источником? - postgres\_fdw: и там, и там — обычные роли PostgreSQL ### Комментарии преподавателя Далее следует сопоставление ролей. Внешний источник может выполнять аутентификацию при подключении. В таких случаях необходимо определить, какие локальные роли могут подключаться к каким учетным записям на внешнем сервере. Если используется аутентификация по паролю, то здесь же указывается и пароль. Суперпользователь может разрешить обычным пользователям использовать подключение без пароля, установив параметр password\_required в значение false. ## Page 9: Сопоставление ролей {#source-page-009} Настроим соответствие: пусть локальная роль student подключается к внешнему серверу как postgres. ```sql => CREATE USER MAPPING FOR student SERVER remote_server OPTIONS ( user 'postgres', password 'postgres' ); ``` ```sql CREATE USER MAPPING ``` ```text => \deu+ ``` ```text List of user mappings Server | User name | FDW options ---------------+-----------+------------------------------------------ remote_server | student | ("user" 'postgres', password 'postgres') (1 row) ``` Разумеется, можно настраивать несколько соответствий для разных ролей. ## Page 10: Компоненты {#source-page-010} **1. Обертка сторонних данных** - каков тип внешнего источника данных? - 2. Внешний сервер - как подключиться к внешнему источнику? - 3. Сопоставление ролей - как локальные роли связаны с ролями внешнего источника? 4. Внешние таблицы - какова структура внешних данных? - postgres\_fdw: данные в обычных таблицах ### Комментарии преподавателя И, наконец, можно приступить к созданию внешних таблиц. Внешняя таблица определяет, как представить данные из внешнего источника в виде обычной реляционной таблицы. Для внешних таблиц можно добавлять ограничения целостности NOT NULL и CHECK. Эти ограничения не будут применяться при выполнении команд, ведь в любом случае PostgreSQL не сможет обеспечить соблюдение ограничений на внешнем источнике. Но планировщик может использовать информацию об ограничениях при построении плана выполнения запроса. Обертка postgres\_fdw для выборки данных использует пакетный режим, записи передаются пакетами, размер которых задается параметром сервера fetch\_size (по умолчанию 100 строк). Вставка (с помощью команд INSERT и COPY FROM) также использует пакетный режим, за количество строк здесь отвечает параметр сервера batch\_size. Для изменения и удаления строк пакетный режим пока не реализован. ## Page 11: Внешние таблицы {#source-page-011} Внешнюю таблицу можно создать явным образом, при необходимости указывая названия объектов, если они отличаются: ```sql => CREATE FOREIGN TABLE remote_users ( id integer OPTIONS (column_name 'user_id') NOT NULL, username text NOT NULL, email text NOT NULL ) SERVER remote_server OPTIONS ( schema_name 'public', table_name 'users' ); ``` ```sql CREATE FOREIGN TABLE ``` Можно указать только некоторые ограничения целостности, но в любом случае они не проверяются локально и просто отражают ограничения, накладываемые внешней системой. ```text => \det ``` ```text List of foreign tables Schema | Table | Server --------+--------------+--------------- public | remote_users | remote_server (1 row) ``` ```sql => SELECT * FROM remote_users WHERE id = 1; ``` ```text id | username | email ----+----------+----------------- 1 | alice | alice@localhost (1 row) ``` Как выполняется такой запрос? Иными словами, передается ли предикат внешнему серверу, чтобы он сам выбрал эффективный способ выполнения, или фильтрацией занимается локальный сервер? ```sql => EXPLAIN (verbose, costs off) SELECT * FROM remote_users WHERE id = 1; ``` ```sql QUERY PLAN --------------------------------------------------------------------------------------- Foreign Scan on public.remote_users Output: id, username, email Remote SQL: SELECT user_id, username, email FROM public.users WHERE ((user_id = 1)) (3 rows) ``` Планировщик умеет распределять работу между серверами. Также ничто не мешает использовать в одном запросе как внешние, так и локальные данные. ```sql => EXPLAIN (costs off) SELECT username FROM remote_users UNION ALL SELECT rolname FROM pg_roles; ``` ```sql QUERY PLAN ------------------------------------------ Append -> Subquery Scan on "*SELECT* 1" -> Foreign Scan on remote_users -> Subquery Scan on "*SELECT* 2" -> Seq Scan on pg_authid (5 rows) ``` Другой способ — не создавать внешние таблицы по одной, а импортировать внешнюю схему (всю или выборочно несколько таблиц), если обертка это позволяет. ```sql => CREATE SCHEMA bookstore2; ``` ```sql CREATE SCHEMA ``` ## Page 12: Page 12 {#source-page-012} ```sql => IMPORT FOREIGN SCHEMA public LIMIT TO (users, sessions) FROM SERVER remote_server INTO bookstore2; ``` ```text IMPORT FOREIGN SCHEMA ``` ```sql => SELECT * FROM bookstore2.users; ``` ```text user_id | username | email ---------+----------+----------------- 1 | alice | alice@localhost 2 | bob | bob@localhost (2 rows) ``` Обертка postgres\_fdw позволяет и изменять данные: ```sql => EXPLAIN (analyze, verbose, costs off) UPDATE remote_users SET email = 'alice@gmail.com' WHERE id = 1; ``` ```sql QUERY PLAN ------------------------------------------------------------------------------------------ --------- Update on public.remote_users (actual time=0.978..0.979 rows=0 loops=1) -> Foreign Update on public.remote_users (actual time=0.976..0.977 rows=1 loops=1) Remote SQL: UPDATE public.users SET email = 'alice@gmail.com'::text WHERE ((user_id = 1)) Planning Time: 0.514 ms Execution Time: 1.793 ms (5 rows) ``` ## Page 13: Соединения и транзакции {#source-page-013} **Удаленное соединение** - открывается при первом обращении к внешним данным - по умолчанию остается открытым до конца сеанса - автоматически восстанавливается при разрыве - Удаленная транзакция - фиксируется или прерывается автоматически, когда фиксируется - или прерывается локальная транзакция - Repeatable Read для локальных транзакций уровней Read Committed - и Repeatable Read - Serializable для локальных транзакций Serializable - согласованность не гарантируется (нет двухфазной фиксации) ### Комментарии преподавателя При работе с другой базой данных через postgres\_fdw важно понимать, как происходит управление соединениями и транзакциями. По умолчанию обертка postgres\_fdw автоматически открывает соединение при первом обращении к внешнему серверу (одно на каждую роль) и оставляет его открытым до конца локального сеанса. Если обертка обнаружит, что соединение разорвано, она автоматически попытается установить новое подключение. Удаленные транзакции «привязаны» к локальным: ● расширение автоматически начинает удаленную транзакцию, когда локальная обращается к внешним данным; ● удаленная транзакция автоматически завершается с завершением локальной (фиксацией или обрывом). Тем не менее, локальная и удаленная транзакции — это разные транзакции, а не одна глобальная. Расширение postgres\_fdw пока не позволяет использовать двухфазную фиксацию, поэтому согласованность не гарантируется. Например, локальная транзакция может быть зафиксирована, а удаленная — нет из-за сетевого сбоя. При фиксации транзакции, в которой участвуют несколько внешних серверов, подключенных через postgres\_fdw, удаленные транзакции могут завершаться асинхронно, а не последовательно одна за другой. ONNECTION-MANAGEMENT ## Page 14: Соединения и транзакции {#source-page-014} Когда выполнялся запрос к внешней таблице remote\_users, обертка открыла соединение и по умолчанию не закрывает его: ```sql => SELECT datname, pid FROM pg_stat_activity WHERE application_name = 'postgres_fdw'; ``` ```text datname | pid ------------+------- bookstore2 | 50818 (1 row) ``` Завершим обслуживающий процесс... ```sql => SELECT pg_terminate_backend(50818); ``` ```text pg_terminate_backend ---------------------- t (1 row) ``` ...и еще раз обратимся к внешней таблице: ```sql => SELECT * FROM remote_users; ``` ```text id | username | email ----+----------+----------------- 2 | bob | bob@localhost 1 | alice | alice@gmail.com (2 rows) ``` Нам удалось получить данные! Обертка автоматически восстановила подключение, соединение теперь обслуживается другим процессом: ```sql => SELECT datname, pid FROM pg_stat_activity WHERE application_name = 'postgres_fdw'; ``` ```text datname | pid ------------+------- bookstore2 | 51260 (1 row) ``` Поведение можно изменить, задав атрибут внешнего сервера ```text keep_connections 'off' ``` Тогда обертка будет открывать соединение при каждом обращении к внешней таблице и закрывать его после обращения. ## Page 15: Сравнение с dblink {#source-page-015} postgres\_fdw dblink - доступные команды SQL SELECT, INSERT, любые UPDATE, DELETE, TRUNCATE, COPY - совмещение в запросе да сложно локальных и внешних данных - управление соединением автоматическое ручное - управление транзакциями автоматическое ручное - глобальные транзакции нет нет - стандартизованный способ да нет ### Комментарии преподавателя В теме «Фоновые процессы» рассматривалось расширение dblink, которое предназначено для выполнения запросов на удаленном сервере PostgreSQL и предоставляет возможности, схожие с возможностями postgres\_fdw. Обертка postgres\_fdw позволяет выполнять ограниченный набор команд SQL, но в большинстве случаев более удобна: она сама управляет соединениями и транзакциями («привязывая» их к локальному соединению и транзакции). Обертка позволяет использовать в одном запросе как локальные, так и внешние данные; при этом автоматически формируется распределенный план выполнения запроса. Синтаксис доступа к данным определяется стандартом SQL. Зато расширение dblink позволяет выполнять любые команды SQL. В некоторых случаях может быть нужным управлять соединениями и транзакциями вручную. Однако оба способа не предоставляют глобальных (распределенных) транзакций. Таким образом, для доступа к внешним данным PostgreSQL в большинстве случаев следует выбирать postgres\_fdw. Расширение dblink стоит использовать, если нужны возможности, которые не предоставляет обертка сторонних данных. ## Page 16: Расширение file_fdw {#source-page-016} **1. Обертка сторонних данных** - тип внешнего источника — текстовый файл с разделителями 2. Внешний сервер - как подключиться — не требуется дополнительной информации - 3. Сопоставление ролей - не имеет смысла, т. к. во внешнем источнике нет ролей - 4. Внешние таблицы - структура данных — описание полей файла в виде столбцов таблицы ### Комментарии преподавателя Еще одну обертку сторонних данных предоставляет расширение file\_fdw, предназначенное для доступа к файлам в файловой системе сервера PostgreSQL. Доступ возможен только на чтение и выполняется с помощью того же механизма, что и SQL-команда COPY. ## Page 17: Расширение file_fdw {#source-page-017} Расширение file\_fdw позволяет обращаться к любому текстовому файлу и задействует тот же механизм, что и команда COPY. Поэтому для демонстрации мы выгрузим строки из имеющейся таблицы в файл, а затем прочитаем данные из файла. ```sql => COPY (SELECT * FROM remote_users) TO '/var/lib/postgresql/16/users.txt' WITH ( format 'text', delimiter '/' ); ``` ```text COPY 2 ``` ```sh student$ cat /var/lib/postgresql/16/users.txt ``` ```text 2/bob/bob@localhost 1/alice/alice@gmail.com ``` ```sql => CREATE EXTENSION file_fdw; ``` ```sql CREATE EXTENSION ``` ```sql => CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw; ``` ```sql CREATE SERVER ``` ```sql => CREATE FOREIGN TABLE file_users ( id integer, username text, email text ) SERVER file_server OPTIONS ( filename '/var/lib/postgresql/16/users.txt', format 'text', delimiter '/' ); ``` ```sql CREATE FOREIGN TABLE ``` ```sql => SELECT * FROM file_users; ``` ```text id | username | email ----+----------+----------------- 2 | bob | bob@localhost 1 | alice | alice@gmail.com (2 rows) ``` ## Page 18: Другие доступные обертки {#source-page-018} **Базы данных** - ODBC, JDBC - различные реляционные и NoSQL СУБД - Файлы различных форматов - Геоданные и научные данные - Веб-ресурсы - стандартные протоколы (RSS, IMAP, WWW...) - для различных сайтов - Можно написать собственную обертку - на C или Python (с помощью расширения Multicorn) ### Комментарии преподавателя Рассмотренные обертки сторонних данных, file\_fdw и postgres\_fdw, поставляются вместе с PostgreSQL в виде contrib-модулей. Помимо этого существует множество других оберток, которые предоставляют доступ ко многим популярным базам данных и не только. Информацию о них можно получить: ● на страничке wiki: ● на сайте PGXN: Если нужную обертку не удалось найти, то ее можно написать самостоятельно. Для этого потребуется язык C: Другой вариант — воспользоваться расширением Multicorn (, которое позволяет написать обертку на языке Python. ## Page 19: Итоги {#source-page-019} **Обертки сторонних данных — стандартный способ работы с данными любых внешних источников** - доступ к PostgreSQL и файлам «из коробки» - есть реализации оберток для огромного числа систем - Альтернатива — расширение dblink для выполнения произвольных запросов на удаленном сервере PostgreSQL ## Page 20: Практика {#source-page-020} 1. Поставщик информирует магазин о новинках книжного рынка, предоставляя CSV-файл определенного формата. Загрузите информацию из файла new\_books.csv, расположенного в домашнем каталоге пользователя student, в таблицы базы данных. Формат файла описан в комментариях к слайду. - Учтите, что авторы, указанные в файле, могут уже существовать в базе данных, но в файле могут быть допущены опечатки. 2. Проверьте в приложении, что новые книги успешно переданы на логическую реплику, и для них работает полнотекстовый поиск. ### Комментарии преподавателя 1. CSV-файл содержит следующие поля: ● фамилия автора; ● имя автора; ● отчество автора; ● название книги; ● формат издания; ● количество страниц; ● ISBN; ● аннотация; ● издательство; ● год выпуска; ● гарнитура; ● номер издания; ● серия; ● имя файла с обложкой (файл находится в каталоге covers). Если у книги несколько авторов, то они следуют в файле в том порядке, в котором указаны в выходных сведениях. При этом для второго и следующих авторов все поля, относящиеся к книге — пустые. Воспользуйтесь расширением file\_fdw, указав для файла формат CSV. Для борьбы с опечатками вспомните про расширение pg\_trgm, с которым вы познакомились в практике к теме «Классы операторов». 2. Логическая реплика с подпиской на изменения книг настраивалась в задании к теме «Логическая репликация». ## Page 21: 1. Загрузка новых поступлений {#source-page-021} ```sql => CREATE EXTENSION file_fdw; ``` ```sql CREATE EXTENSION ``` ```sql => CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw; ``` ```sql CREATE SERVER ``` ```sql => CREATE FOREIGN TABLE new_books ( last_name text, first_name text, middle_name text, title text, format text, pages integer, isbn text, abstract text, publisher text, year text, typeface text, edition integer, series text, cover_filename text ) SERVER file_server OPTIONS ( filename '/home/student/new_books.csv', format 'csv' ); ``` ```sql CREATE FOREIGN TABLE ``` Здесь предполагается, что несколько процедур загрузки книг не будут запускаться параллельно. Если такой гарантии нет, в коде надо отдельно предусмотреть эту возможность, устанавливая необходимые блокировки. Для сопоставления авторов используется нечеткий поиск с помощью триграмм. Оператор % отсекает совсем непохожих (предел похожести по умолчанию 0.3 слишком низок для нашего случая, поэтому увеличиваем его). Из оставшихся выбираем лучшее совпадение, упорядочивая кандидатов с помощью функции similarity. ```sql => CREATE EXTENSION pg_trgm; ``` ```sql CREATE EXTENSION ``` ```sql => SELECT set_limit(0.7); -- предел похожести для оператора % ``` ```text set_limit ----------- 0.7 (1 row) ``` ## Page 22: Page 22 {#source-page-022} ```sql => DO $$ DECLARE i new_books; book_id bigint; author_id bigint; author_cause text; seq_num integer; BEGIN FOR i IN SELECT * FROM new_books LOOP IF i.title IS NOT NULL THEN INSERT INTO books AS b( title, format, pages, additional, cover ) VALUES ( i.title, i.format::text::book_format, i.pages, jsonb_build_object( 'ISBN', i.isbn, 'Аннотация', i.abstract, 'Издательство', i.publisher, 'Год выпуска', i.year, 'Гарнитура', i.typeface, 'Издание', i.edition, 'Серия', i.series ), pg_read_binary_file( '/home/student/covers/'||i.cover_filename ) ) RETURNING b.book_id INTO book_id; seq_num := 1; RAISE NOTICE 'Книга: % (%)', i.title, book_id; END IF; ``` ```sql -- есть ли точное совпадение с имеющимся автором? SELECT a.author_id, 'точное совпадение' INTO author_id, author_cause FROM authors a WHERE a.last_name = i.last_name AND a.first_name = i.first_name AND a.middle_name = i.middle_name; -- если нет, то может найдется очень похожий? IF author_id IS NULL THEN SELECT a.author_id, 'НЕточное совпадение' INTO author_id, author_cause FROM authors a WHERE (a.last_name || a.first_name || a.middle_name) % (i.last_name || i.first_name || i.middle_name) ORDER BY similarity( (a.last_name || a.first_name || a.middle_name), (i.last_name || i.first_name || i.middle_name) ) DESC LIMIT 1; END IF; -- если и похожего нет, то считаем автора новым IF author_id IS NULL THEN INSERT INTO authors AS a(first_name,last_name,middle_name) VALUES (i.first_name, i.last_name, i.middle_name) RETURNING a.author_id, 'новый' INTO author_id, author_cause; END IF; RAISE NOTICE ' Автор: %, % (%)', i.last_name, author_cause, author_id; ``` ```sql INSERT INTO authorships(book_id, author_id, seq_num) VALUES (book_id, author_id, seq_num); seq_num := seq_num + 1; END LOOP; END; $$; ``` ## Page 23: Page 23 {#source-page-023} ```text NOTICE: Книга: PostgreSQL 16 изнутри (101) NOTICE: Автор: Рогов, новый (162) NOTICE: Книга: PostgreSQL 15 изнутри (102) NOTICE: Автор: Рогов, точное совпадение (162) NOTICE: Книга: Путеводитель по базам данных (103) NOTICE: Автор: Комаров, новый (163) NOTICE: Книга: PostGIS в действии (104) NOTICE: Автор: Обе, новый (164) NOTICE: Автор: Хсу, новый (165) NOTICE: Книга: Жемчужины разработки. Чему мы научились за 50 лет создания ПО (105) NOTICE: Автор: Вигерс, новый (166) NOTICE: Книга: Мониторинг PostgreSQL (106) NOTICE: Автор: Лесовский, новый (167) NOTICE: Книга: Думай о смысле. Будни переводчика IT-текстов (107) NOTICE: Автор: Чаплыгин, новый (168) NOTICE: Книга: Идеи, определившие облик информатики (108) NOTICE: Автор: Льюис, новый (169) DO ``` ```sql => DROP FOREIGN TABLE new_books; ``` ```sql DROP FOREIGN TABLE ``` ## Page 24: Практика+ {#source-page-024} 1. В двух разных базах данных находятся две таблицы с одинаковым набором столбцов. Выведите строки, которые присутствуют в первой таблице, но отсутствуют во второй. Решите задачу с помощью postgres\_fdw и с помощью dblink и сравните два способа. 2. Как можно проверить, какой уровень изоляции использует обертка postgres\_fdw и как она управляет соединениями и транзакциями? ### Комментарии преподавателя 1. Расширение dblink демонстрировалось в теме «Фоновые процессы». 2. Настройте на втором сервере журнал сообщений таким образом, чтобы в него записывалась информация: ● о подключениях (log\_connections); ● отключениях (log\_disconnections); ● выполняемых командах (log\_statement). Обратитесь к таблице на втором сервере с помощью postgres\_fdw и проверьте, что попало в журнал сообщений. ## Page 25: 1. Сравнение таблиц в разных базах {#source-page-025} Таблица в первой базе данных: ```sql => CREATE DATABASE fdw_overview; ``` ```sql CREATE DATABASE ``` ```sql => \c fdw_overview ``` ```text You are now connected to database "fdw_overview" as user "student". ``` ```sql => CREATE TABLE test ( s text ); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO test VALUES ('foo'),('bar'),('baz'); ``` ```sql INSERT 0 3 ``` И во второй (расположим ее на втором сервере в БД postgres): ```sh student$ psql -p 5433 -U postgres -d postgres ``` ```sql => CREATE TABLE test ( s text ); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO test VALUES ('foo'),('bar'); ``` ```sql INSERT 0 2 ``` Создаем стороннюю таблицу для доступа к таблице во второй базе данных: ```sql => CREATE EXTENSION postgres_fdw; ``` ```sql CREATE EXTENSION ``` ```sql => CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS ( host 'localhost', port '5433', dbname 'postgres' ); ``` ```sql CREATE SERVER ``` На втором сервере используем роль postgres: ```sql => CREATE USER MAPPING FOR student SERVER remote_server OPTIONS ( user 'postgres' -- пароль не нужен, аутентификация trust ); ``` ```sql CREATE USER MAPPING ``` ```sql => CREATE FOREIGN TABLE test2 ( s text ) SERVER remote_server OPTIONS ( schema_name 'public', table_name 'test' ); ``` ```sql CREATE FOREIGN TABLE ``` Сравниваем: ```sql => SELECT * FROM test EXCEPT SELECT * FROM test2; ``` ## Page 26: Page 26 {#source-page-026} ```text s ----- baz (1 row) ``` С помощью расширения dblink задачу можно решить следующим образом: ```sql => CREATE EXTENSION dblink; ``` ```sql CREATE EXTENSION ``` ```sql => SELECT * FROM test EXCEPT SELECT * FROM dblink( 'host=localhost port=5433 dbname=postgres user=postgres', 'SELECT * FROM test' ) AS (s text); ``` ```text s ----- baz (1 row) ``` В этом случае подготовительные действия практически не требуются, но сам запрос выглядит сложнее. ### 2. Проверка работы postgres\\\_fdw На втором сервере настроим журнал сообщений так, чтобы в него попадала информация о подключениях и отключениях, а также о выполняемых командах. ```sql => ALTER SYSTEM SET log_connections = on; ``` ```sql ALTER SYSTEM ``` ```sql => ALTER SYSTEM SET log_disconnections = on; ``` ```sql ALTER SYSTEM ``` ```sql => ALTER SYSTEM SET log_statement = 'all'; ``` ```sql ALTER SYSTEM ``` ```sql => SELECT pg_reload_conf(); ``` ```text pg_reload_conf ---------------- t (1 row) ``` Начнем новое соединение и локальную транзакцию Read Committed. ```sql => \q ``` ```sh student$ psql fdw_overview ``` ```text => BEGIN; ``` ```text BEGIN ``` Обращаемся к сторонней таблице: ```sql => SELECT * FROM test2; ``` ```text s ----- foo bar (2 rows) ``` В журнале второго сервера видим: установлено новое соединение; в нем выставлены некоторые параметры; начата транзакция с уровнем изоляции Repeatable Read; с помощью курсора прочитаны записи из таблицы test. ```sh student$ sudo tail -n 12 /var/log/postgresql/postgresql-16-replica.log ``` ```text 2026-04-10 16:46:28.865 MSK [113360] [unknown]@[unknown] LOG: connection received: ``` ## Page 27: Page 27 {#source-page-027} ```sql host=127.0.0.1 port=39468 2026-04-10 16:46:28.876 MSK [113360] postgres@postgres LOG: connection authorized: user=postgres database=postgres application_name=postgres_fdw SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256) 2026-04-10 16:46:28.878 MSK [113360] postgres@postgres LOG: statement: SET search_path = pg_catalog 2026-04-10 16:46:28.879 MSK [113360] postgres@postgres LOG: statement: SET timezone = 'GMT' 2026-04-10 16:46:28.879 MSK [113360] postgres@postgres LOG: statement: SET datestyle = ISO 2026-04-10 16:46:28.879 MSK [113360] postgres@postgres LOG: statement: SET intervalstyle = postgres 2026-04-10 16:46:28.879 MSK [113360] postgres@postgres LOG: statement: SET extra_float_digits = 3 2026-04-10 16:46:28.879 MSK [113360] postgres@postgres LOG: statement: START TRANSACTION ISOLATION LEVEL REPEATABLE READ 2026-04-10 16:46:28.880 MSK [113360] postgres@postgres LOG: execute : DECLARE c1 CURSOR FOR SELECT s FROM public.test 2026-04-10 16:46:28.881 MSK [113360] postgres@postgres LOG: statement: FETCH 100 FROM c1 2026-04-10 16:46:28.881 MSK [113360] postgres@postgres LOG: statement: CLOSE c1 ``` Завершаем локальную транзакцию. ```text => COMMIT; ``` ```text COMMIT ``` В журнале видим, что удаленная транзакция также завершена: ```sh student$ sudo tail -n 1 /var/log/postgresql/postgresql-16-replica.log ``` ```text 2026-04-10 16:46:29.338 MSK [113360] postgres@postgres LOG: statement: COMMIT TRANSACTION ``` Завершаем сеанс. ```sql => \q ``` В этот момент завершается и соединение с удаленным узлом: ```sh student$ sudo tail -n 2 /var/log/postgresql/postgresql-16-replica.log ``` ```sql 2026-04-10 16:46:29.536 MSK [113360] postgres@postgres LOG: could not receive data from client: Connection reset by peer 2026-04-10 16:46:29.536 MSK [113360] postgres@postgres LOG: disconnection: session time: 0:00:00.671 user=postgres database=postgres host=127.0.0.1 port=39468 ``` Журнал сообщений можно использовать, чтобы разобраться, как работа внешних средств выглядит с точки зрения СУБД.