Уровень 2.0

Предусловие: изучена глава «Репликация».

В этой главе рассматриваются механизмы доступа к внешним источникам данных через Foreign Data Wrappers, расширения postgres_fdw, file_fdw и dblink.

Внешние источники данных

PostgreSQL позволяет подключаться к внешним источникам данных — данным, не принадлежащим текущей базе данных экземпляра. Под внешними источниками понимаются:

• другие БД того же экземпляра PostgreSQL;
• БД других экземпляров PostgreSQL;
• БД, обслуживаемые СУБД других производителей, таких как Oracle RDBMS и MS SQL Server;
• нереляционные БД;
• структурированные текстовые файлы.

С СУБД Pangolin поставляются пять расширений, обеспечивающих доступ к внешним источникам данных:

• Foreign Data Wrappers (FDW) — стандартизованный механизм, позволяющий представлять внешние данные в виде внешних таблиц FOREIGN TABLE:

  • postgres_fdw — подключение к удаленным серверам PostgreSQL;
  • oracle_fdw — подключение к Oracle RDBMS;
  • tds_fdw — подключение к MS SQL Server и Sybase;
  • file_fdw — доступ к структурированным текстовым файлам файловой системы сервера.

• dblink — расширение для подключения к другим базам данных PostgreSQL, не использующее инфраструктуру FDW, но позволяющее выполнять произвольные запросы и команды на удаленном сервере.

Список доступных в системе расширений можно получить запросом к представлению pg_available_extensions.

postgres@repbase=# SELECT name, comment FROM pg_available_extensions where name ~ 'fdw' OR name = 'dblink';

         name | comment
--------------+-----------------------------------------------------------------------------------
 postgres_fdw | foreign-data wrapper for remote PostgreSQL servers
 tds_fdw      | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)
 dblink       | connect to other PostgreSQL databases from within a database
 file_fdw     | foreign-data wrapper for flat file access
 oracle_fdw   | foreign data wrapper for Oracle access
(5 rows)

Foreign Data Wrappers (FDW)

Foreign Data Wrappers (FDW) — стандартизованная инфраструктура PostgreSQL, позволяющая обращаться к внешним источникам данных через механизм внешних таблиц FOREIGN TABLE. Все расширения FDW строятся на единой основе и используют одинаковый набор серверных объектов.

Ключевое преимущество инфраструктуры FDW — соответствие стандарту SQL: запросы к внешним таблицам выполняются так же, как и к обычным. Однако обертка может быть ограничена с помощью опций настройки — например, можно включать и выключать импорт ограничений целостности.

Альтернативой FDW является расширение dblink, которое не следует этому стандарту, но предоставляет широкие возможности для выполнения произвольных запросов и команд на удаленном PostgreSQL-сервере.

Объекты инфраструктуры FDW

Для работы с внешними данными через FDW необходимо создать три серверных объекта. Каждый объект отвечает за определенный аспект подключения:

• Серверный объект (FOREIGN SERVER) — описывает параметры подключения к внешнему источнику: сетевой адрес хоста, номер порта TCP, имя базы данных.
• Карта сопоставления пользователей (USER MAPPING) — связывает локальную роль PostgreSQL с учетными данными на удаленном сервере (имя пользователя, пароль).
• Внешняя таблица (FOREIGN TABLE) — представляет данные удаленного источника в виде обычной таблицы, доступной для SQL-запросов.

warning

Не путайте карту отображений пользователей FDW с файлом конфигурации pg_ident.conf, задающим карту отображений пользователей при внешней аутентификации.

Шаги настройки FDW

Инфраструктура FDW стандартна, и для использования любого способа доступа к внешним данным приходится выполнять приблизительно одинаковые действия. Порядок настройки:

1. Установить расширение. Командой CREATE EXTENSION подключается соответствующее FDW-расширение (например, postgres_fdw).
2. Создать серверный объект. Командой CREATE SERVER создается объект, описывающий параметры подключения к внешнему источнику (хост, порт, имя базы данных).
3. Создать карту сопоставления пользователей. Командой CREATE USER MAPPING сопоставляются роли в локальном экземпляре и удаленном сервере.
4. Создать внешнюю таблицу. Командой CREATE FOREIGN TABLE или IMPORT FOREIGN SCHEMA создается таблица, представляющая внешние данные.

примечание

В примерах используется роль postgres для наглядности. В продуктивной среде создание FDW-объектов и работу с внешними таблицами следует выполнять от специализированных ролей, которым выдан доступ к FDW.

Для дальнейшей работы подготовим инфраструктуру: на сервере (порт 5432) создаем локальную базу данных localdb, на удаленном сервере (порт 6432) — базу externaldb с таблицей exttab.

postgres@repbase=# \c - - - 5432

You are now connected to database "repbase" as user "postgres" via socket in "/tmp" at port "5432".

postgres@repbase=# CREATE DATABASE localdb;

CREATE DATABASE

postgres@repbase=# \c - - - 6432

You are now connected to database "repbase" as user "postgres" via socket in "/tmp" at port "6432".

postgres@repbase=# CREATE DATABASE externaldb;

CREATE DATABASE

postgres@repbase=# \c externaldb

You are now connected to database "externaldb" as user "postgres".

postgres@externaldb=# CREATE TABLE exttab(id serial PRIMARY KEY, msg text);

CREATE TABLE

Вставим тестовые данные.

postgres@externaldb=# INSERT INTO exttab(msg) VALUES ('Первая строка'), ('Вторая');

INSERT 0 2

Возвращаемся на локальный сервер.

postgres@externaldb=# \c localdb - - 5432

You are now connected to database "localdb" as user "postgres" via socket in "/tmp" at port "5432".

Расширение postgres_fdw

Расширение postgres_fdw предоставляет доступ к удаленным серверам PostgreSQL через механизм внешних таблиц.

Создание расширения и серверного объекта

Перед началом работы необходимо зарегистрировать расширение postgres_fdw в базе данных с помощью команды CREATE EXTENSION. Это действие добавляет в систему необходимые типы данных, функции и обработчики, без которых команды для создания серверных объектов и внешних таблиц будут недоступны.

postgres@localdb=# CREATE EXTENSION postgres_fdw;

CREATE EXTENSION

Далее создается серверный объект, который описывает параметры подключения к удаленному экземпляру. Команда CREATE SERVER принимает опции для указания хоста, порта и имени базы данных.

postgres@localdb=# CREATE SERVER srv6432 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', port '6432', dbname 'externaldb');

CREATE SERVER

Созданные серверные объекты проверяются метакомандой \des. В примере использован вариант \des+ с подробным выводом.

postgres@localdb=# \x \des+ \x

Expanded display is on.
List of foreign servers
-[ RECORD 1 ]--------+-----------------------------------------------------
 Name                | srv6432
 Owner               | postgres
 Foreign-data wrapper| postgres_fdw
 Access privileges   |
 Type                |
 Version             |
 FDW options         | (host 'localhost', port '6432', dbname 'externaldb')
 Description         |

Expanded display is off.

Карта сопоставления пользователей

Карта сопоставления связывает локальную роль PostgreSQL с учетными данными на удаленном сервере. Создается командой CREATE USER MAPPING. Эта команда задает соответствие между локальной ролью и учетной записью на удаленном сервере, которая будет использоваться при подключении через серверный объект.

Создадим карту сопоставления для суперпользователя postgres.

postgres@localdb=# CREATE USER MAPPING FOR postgres SERVER srv6432 OPTIONS (user 'postgres', password 'postgres');

CREATE USER MAPPING

В примере локальный суперпользователь postgres сопоставлен с суперпользователем postgres удаленного сервера. Задавать пароль в явном виде не следует — для продуктивной среды следует использовать более безопасные методы аутентификации (например, аутентификацию по SSL-сертификатам). В демонстрационном примере пароль указан для простоты.

Проверить список отображений пользователей позволяет метакоманда \deu.

postgres@localdb=# \x \deu+ \x

Expanded display is on.
List of user mappings
-[ RECORD 1 ]-----------------------------------------
 Server        | srv6432
 User name     | postgres
 FDW options   | ("user" 'postgres', password 'postgres')

Expanded display is off.

Создание внешней таблицы

Внешняя таблица — это представление данных удаленного источника в виде обычной таблицы, доступной для SQL-запросов. Создание внешней таблицы завершает настройку инфраструктуры FDW и позволяет работать с внешними данными так же, как с локальными.

Внешняя таблица создается одним из двух способов:

• CREATE FOREIGN TABLE — ручное описание структуры столбцов;
• IMPORT FOREIGN SCHEMA — автоматическое перенесение структуры удаленной таблицы.

warning

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

Импортируем схему public удаленной таблицы exttab из серверного объекта srv6432 в текущую базу.

postgres@localdb=# IMPORT FOREIGN SCHEMA public LIMIT TO (exttab) FROM SERVER srv6432 INTO public;

IMPORT FOREIGN SCHEMA

Проверим структуру созданной внешней таблицы метакомандой \d.

postgres@localdb=# \d exttab

                     Foreign table "public.exttab"
 Column  | Type    | Collation | Nullable | Default | FDW options
---------+---------+-----------+----------+---------+---------------------
 id      | integer |           | not null |         | (column_name 'id')
 msg     | text    |           |          |         | (column_name 'msg')
 Server: srv6432
 FDW options: (schema_name 'public', table_name 'exttab')

Вывод подтверждает, что таблица exttab создана как внешняя (Foreign table), привязана к серверу srv6432 и содержит два столбца: id (integer, not null) и msg (text). Опции FDW показывают соответствие схемы public и таблицы exttab удаленного источника.

Запросы к внешней таблице

Внешние таблицы соответствуют стандарту SQL — запросы к ним выполняются так же, как и к обычным таблицам. Поддерживаются операции SELECT, UPDATE и INSERT.

Выполним SELECT для проверки содержимого.

postgres@localdb=# SELECT * FROM exttab;

 id | msg
----+---------------
  1 | Первая строка
  2 | Вторая
(2 rows)

Обновим данные на удаленном сервере, преобразовав значения столбца msg в верхний регистр.

postgres@localdb=# UPDATE exttab SET msg = upper(msg);

UPDATE 2

Добавим новую строку в удаленную таблицу через внешнюю.

postgres@localdb=# INSERT INTO exttab VALUES (3,'ТРЕТЬЯ');

INSERT 0 1

Проверим результаты изменений — обновленные строки и добавленная новая.

postgres@localdb=# SELECT * FROM exttab;

 id |      msg
----+---------------
  1 | ПЕРВАЯ СТРОКА
  2 | ВТОРАЯ
  3 | ТРЕТЬЯ
(3 rows)

Расширение file_fdw

Расширение file_fdw предоставляет доступ к структурированным текстовым файлам файловой системы сервера через внешнюю таблицу, доступную только для чтения. Оно применяется, когда необходимо выполнять SQL-запросы к данным, хранящимся в файлах, без их предварительной загрузки в базу данных.

примечание

Текстовый файл должен быть отформатирован в соответствии с одним из форматов, поддерживаемых командой COPY FROM.

Команда COPY FROM загружает данные из текстовых файлов, копируя их в обычную таблицу базы данных. Запросы выполняются после копирования. Для работы с исходными файлами напрямую применяется file_fdw.

Для демонстрации работы file_fdw создадим тренировочный CSV-файл.

[postgres@srv-67-29 ~]$ cat << EOF > employees.csv
name,age,salary
John Doe,30,5000
Jane Smith,28,4500
Alice Johnson,,7000
EOF

[postgres@srv-67-29 ~]$ psql -d localdb -p 5432

Создание внешней таблицы для файла

Настройка file_fdw следует общим шагам FDW: установка расширения, создание серверного объекта.

postgres@localdb=# CREATE EXTENSION file_fdw;

CREATE EXTENSION

postgres@localdb=# CREATE SERVER srv_file_fdw FOREIGN DATA WRAPPER file_fdw;

CREATE SERVER

Создадим внешнюю таблицу для чтения файла employees.csv.

postgres@localdb=# CREATE FOREIGN TABLE emp_csv (name text, age integer, salary numeric(10,2))
                  SERVER srv_file_fdw OPTIONS (filename '/home/postgres/employees.csv', format 'csv', header 'true');

CREATE FOREIGN TABLE

После создания внешней таблицы к данным файла обращаются стандартными SQL-запросами.

postgres@localdb=# SELECT * FROM emp_csv;

     name      | age | salary
---------------+-----+---------
 John Doe      |  30 | 5000.00
 Jane Smith    |  28 | 4500.00
 Alice Johnson |     | 7000.00
(3 rows)

Аналогичным способом работают с любыми структурированными внешними файлами, например с логами PostgreSQL. Главное — правильно указать порядок полей, их типы и параметры формата файла.

Расширение dblink

Расширение dblink не следует стандарту FDW и не использует инфраструктуру внешних таблиц. В отличие от FDW, оно предоставляет функции для прямого выполнения SQL-запросов и произвольных команд на удаленном PostgreSQL-сервере.

Основные возможности расширения:

• управление соединениями;
• выполнение запросов;
• управление курсорами;
• проверка статуса удаленного сервера;
• посылка асинхронных сообщений.

Подключение и управление соединениями

Расширение dblink предоставляет множество функций для подключения и выполнения запросов и других действий в удаленной базе данных PostgreSQL.

Подключим расширение к базе localdb.

postgres@localdb=# CREATE EXTENSION dblink;

CREATE EXTENSION

Список доступных функций выводится с помощью метакоманды \dx+.

postgres@localdb=# \dx+ dblink

                               Objects in extension "dblink"
                                     Object description
-------------------------------------------------------------------------
 foreign-data wrapper dblink_fdw
 function dblink_build_sql_delete(text,int2vector,integer,text[])
 function dblink(text,text,boolean)
 ...
 type dblink_pkey_results
(43 rows)

Соединение с удаленным сервером открывается функцией dblink_connect().

postgres@localdb=# SELECT dblink_connect('conn2extdb','port=6432 dbname=externaldb');

 dblink_connect
----------------
 OK
(1 row)

Список открытых соединений возвращает dblink_get_connections().

postgres@localdb=# SELECT dblink_get_connections();

 dblink_get_connections
------------------------
 {conn2extdb}
(1 row)

Выполнение запросов

Выполнить запрос к удаленной БД позволяет функция dblink(). Эта функция возвращает набор структурированных строк (SETOF record).

Для вывода результирующих строк в виде таблицы задаются поля структуры и их типы данных. Указание достигается добавлением конструкции AS t(i integer, t text). Она сообщает PostgreSQL, что в возвращаемом наборе строки имеют два поля: первое — i — целочисленное, второе — t — текстовое. Количество и тип полей должны соответствовать структуре удаленной таблицы.

postgres@localdb=# SELECT * FROM dblink('conn2extdb','SELECT * FROM exttab') AS t(i integer, t text);

 i |       t
---+---------------
 1 | ПЕРВАЯ СТРОКА
 2 | ВТОРАЯ
 3 | ТРЕТЬЯ
(3 rows)

Все перегруженные варианты функции возвращают результат в виде набора строк. Сигнатуры всех вариантов dblink() проверяются метакомандой \df.

postgres@localdb=# \df dblink

                       List of functions
 Schema |  Name  |   Result data type    |  Argument data types   | Type
--------+--------+-----------------------+------------------------+-------
 public | dblink | SETOF record          | text                   | func
 public | dblink | SETOF record          | text, boolean          | func
 public | dblink | SETOF record          | text, text             | func
 public | dblink | SETOF record          | text, text, boolean    | func
(4 rows)

Завершение соединения

После выполнения запросов в удаленной БД не следует забывать закрывать удаленные сессии. Каждая открытая сессия порождает на стороне удаленного сервера обслуживающий процесс, который, даже простаивая, расходует системные ресурсы. Закрыть удаленную сессию dblink позволяет функция dblink_disconnect().

postgres@localdb=# SELECT dblink_disconnect('conn2extdb');

 dblink_disconnect
-------------------
 OK
(1 row)

Убедимся, что список открытых соединений пуст.

postgres@localdb=# SELECT dblink_get_connections();

 dblink_get_connections
------------------------

(1 row)

Если расширение dblink больше не требуется, его следует удалить.

postgres@localdb=# DROP EXTENSION dblink;

DROP EXTENSION

Многие функции расширения dblink самостоятельно открывают соединение и после завершения автоматически закрывают его. Однако из-за затрат на процедуру открытия соединения в большинстве случаев эффективнее один раз открыть соединение, выполнить необходимые операции и закрыть его.

Итоги

• PostgreSQL подключается к внешним данным через механизм FDW (Foreign Data Wrappers);
• FDW использует три серверных объекта: FOREIGN SERVER, USER MAPPING, FOREIGN TABLE;
• Объекты создаются в порядке: расширение (CREATE EXTENSION), серверный объект, карта сопоставления, внешняя таблица;
• Расширение postgres_fdw обеспечивает подключение к удаленным серверам PostgreSQL;
• Расширение file_fdw предоставляет доступ только для чтения к структурированным текстовым файлам;
• Внешняя таблица создается вручную CREATE FOREIGN TABLE или автоматически IMPORT FOREIGN SCHEMA;
• Расширение dblink позволяет выполнять SQL-запросы на удаленном PostgreSQL-сервере без инфраструктуры FDW;
• Соединения dblink управляются функциями dblink_connect() и dblink_disconnect();
• Незакрытые соединения dblink расходуют системные ресурсы на удаленном сервере.

Самопроверка

Вопрос 1

Создан серверный объект. Как в psql проверить его наличие?

\d\de\des\3des\deu\3deu

Вопрос 2

Какие серверные объекты инфраструктуры FDW используются для работы с внешними данными? Выберите все верные варианты.

FOREIGN SERVER — описывает параметры подключения к внешнему источнику

USER MAPPING — связывает локальную роль с учетными данными на удаленном сервере

FOREIGN TABLE — представляет данные удаленного источника в виде обычной таблицы

FOREIGN DATABASE — описывает внешнюю базу данных

FOREIGN CONNECTION — управляет соединением с внешним источником

Вопрос 3

Какие опции используются в команде CREATE SERVER для подключения к удаленному PostgreSQL через postgres_fdw?

host — сетевой адрес хоста, port — номер порта TCP, dbname — имя базы данныхserver — сетевой адрес хоста, port — номер порта TCP, database — имя базы данныхaddress — сетевой адрес хоста, port — номер порта TCP, schema — имя схемыhost — сетевой адрес хоста, connection — строка подключения, dbname — имя базы данных

Вопрос 4

Что делает команда CREATE USER MAPPING FOR postgres SERVER srv6432 OPTIONS (user 'postgres', password 'postgres')?

Связывает локальную роль postgres с учетными данными на удаленном сервере srv6432Создает нового пользователя postgres на удаленном сервере srv6432Назначает пароль postgres для локальной ролиУстанавливает соединение с сервером srv6432

Вопрос 5

Какие способы создания внешней таблицы существуют? Выберите все верные варианты.

CREATE FOREIGN TABLE

IMPORT FOREIGN SCHEMA

CREATE TABLE ... FROM SERVER

IMPORT TABLE

Вопрос 6

Что делает команда IMPORT FOREIGN SCHEMA public LIMIT TO (exttab) FROM SERVER srv6432 INTO public?

Импортирует структуру только таблицы exttab из схемы public сервера srv6432 в локальную схему publicИмпортирует все таблицы из локальной схемы public сервера srv6432Копирует данные таблицы exttab в схемы public с сервера srv6432 в локальную таблицуСоздает внешнюю таблицу exttab в локальной схеме public на сервере srv6432

Вопрос 7

Какой тип доступа к внешним файлам предоставляет расширение file_fdw?

Только для чтенияЧтение и записьТолько для записиЧтение, запись и удаление

Вопрос 8

Какие опции указываются в команде CREATE FOREIGN TABLE для file_fdw? Выберите все верные варианты.

filename — путь к файлу

format — формат файла (например, csv)

header — наличие заголовка в файле (true/false)

mode — режим доступа (read/write)

encoding — кодировка файла

Вопрос 9

Чем расширение dblink отличается от FDW?

dblink не следует стандарту FDW и не использует инфраструктуру внешних таблицdblink требует создания внешних таблиц для подключения к удаленным базамdblink не поддерживает подключение к PostgreSQLdblink может работать только с локальными базами данных

Вопрос 10

Какие возможности предоставляет расширение dblink? Выберите все верные варианты.

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

Выполнение SQL-запросов на удаленном сервере

Управление курсорами

Проверка статуса удаленного сервера

Посылка асинхронных сообщений

Создание внешних таблиц

Вопрос 11

Какая функция позволяет вывести список соединений dblink?

dblink_get_connections()dblink_get_connection()dblink_connections()dblink_connection()dblink()

Вопрос 12

Какая функция открывает именованное соединение с удаленным сервером через dblink?

dblink_connect('conn_name', 'connection_string')dblink_open('conn_name', 'connection_string')dblink_start('conn_name', 'connection_string')dblink_init('conn_name', 'connection_string')