oracle_fdw. Оболочка сторонних данных для работы с СУБД Oracle

Версия: 2.5.0.

В исходном дистрибутиве установлено по умолчанию: нет.

Связанные компоненты: отсутствуют.

Схема размещения: ext.

Расширение представляет собой внешнюю оболочку данных (fdw – foreign-data wrapper) для простого и эффективного доступа к базам данных Oracle из СУБД Pangolin. Возможности включают отображение условий WHERE и требуемых столбцов, а также всестороннюю поддержку EXPLAIN. Расширение позволяет получать доступ к таблицам и представлениям Oracle (включая материализованные представления) через сторонние таблицы.

Доработка

Доработка: Добавлена совместимость с защищенным шифрованным хранилищем паролей pg_auth_config.

Версия: 4.5.0.

Ограничения

Расширение появилось в версии 4.5.0 СУБД Pangolin, поэтому для использования расширения необходимо обновить версию СУБД Pangolin до 4.5.0 или выше.

Установка

Для установки расширения выполните действия:

Установите oracle client версии не ниже 11.2:

sudo yum localinstall oracle-instantclient19.18-basic-19.18.0.0.0-2.x86_64.rpm

Добавьте в систему библиотеки клиента:
1. Добавьте строку /usr/lib/oracle/19.18/client64/lib/ в файл /etc/ld.so.conf:
```
sudo vi /etc/ld.so.conf
```
2. Сохраните файл и выйдите из редактора.
3. Создайте связки и кеш динамических библиотек:
```
sudo ldconfig -N
```
Выполните настройку необходимых переменных окружения:
- для использования расширения достаточно переменной окружения LD_LIBRARY_PATH присвоить значение, равное пути, где расположены библиотеки oracle client для установленной версии, например, /usr/lib/oracle/19.18/client64/lib.
```
vim ~/.bash_profile
# добавить путь к переменной
export LD_LIBRARY_PATH=/usr/pangolin-X.X.X/lib:/usr/lib/oracle/19.18/client64/lib
export ORACLE_HOME="/usr/lib/oracle/19.14/client64"
export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"
```
- для обновления версии Pangolin необходимо создать символьную ссылку на библиотеку libclntsh.so.18.1 в каталоге системных библиотек /usr/lib64:
```
ln -s /usr/lib/oracle/19.18/client64/lib/libclntsh.so.18.1 /usr/lib64/libclntsh.so.18.1
```
Примечание:

Переменные среды необходимо устанавливать для оболочки, в которой запускается сервер PostgreSQL.

Порядок установки переменной LD_LIBRARY_PATH описан в документе «Руководство по системному администрированию», раздел «Порядок установки переменной LD_LIBRARY_PATH в окружении Patroni».
Перезагрузите сервер базы данных для применения переменных среды. Пример для инсталляции standalone:
```
pg_ctl restart
```
После выполнения описанных действий необходимые библиотеки и файлы клиента Oracle появятся в системе.
Добавьте расширение в БД (необходимы права суперпользователя):
```
CREATE EXTENSION oracle_fdw SCHEMA ext;
```
Примечание:

В случае получения ошибки следующего вида проверьте правильность установки переменных среды (п. 2 – 4).
```
ERROR:  could not load library "{$PGHOME}/lib/oracle_fdw.so": libclntsh.so.18.1: cannot open shared object file: No such file or directory
```
Дайте права на использование расширения oracle_fdw для необходимой роли:
```
GRANT USAGE ON FOREIGN DATA WRAPPER oracle_fdw TO <role_name>;
```
При необходимости обновите расширение:
```
ALTER EXTENSION oracle_fdw UPDATE;
```

Настройка

Настройка не требуется.

Использование модуля

Пример использования расширения oracle_fdw описан в подразделе «Функциональность FOREIGN DATA WRAPPER для БД Oracle» документа «Руководство администратора», раздел «Сценарии администрирования».

Создать «сервер» – атрибут, который содержит параметры подключения к серверу базы данных Oracle:
```
CREATE SERVER ora_db FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//oracle_server:1521/oracle_db');
```
где:
- ora_db – произвольное имя сервера;
- dbserver – параметр подключения к серверу баз данных Oracle.
Создать сопоставление пользователя на внешнем сервере для подключения к созданному серверу ora_db:
```
CREATE USER MAPPING FOR pguser SERVER ora_db OPTIONS (user 'ora_user');
```
где:
- pguser – имя пользователя в базе данных Pangolin, который сможет пользоваться внешними данными;
- ora_db – имя созданного сервера внешних данных;
- ora_user – имя пользователя в базе данных Oracle, который имеет право на чтение внешних данных.
Выдать права для использования стороннего сервера Oracle для пользователя, которому было создано сопоставление (USER MAPPING):
```
GRANT USAGE ON FOREIGN SERVER ora_db TO pguser;
```
где:
- ora_db – имя созданного сервера внешних данных;
- pguser – имя пользователя в базе данных Pangolin, которому предоставляются права на использование созданного сервера внешних данных.
Добавить пароль для пользователя базы данных Oracle в шифрованное хранилище. Приведены два способа:
- функция add_auth_record_to_storage требует явного ввода пароля в строке запуска, что небезопасно:
```
SELECT add_auth_record_to_storage('FQDN_hostname-OR-IPaddress', 1521, 'oracle_db', 'ora_user', '<пароль>');
```
  Пример вывода результата:
```
add_auth_record_to_storage 
----------------------------

(1 row)
```
- утилита pg_auth_ config с интерактивным вводом пароля:
```
pg_auth_config add --host <FQDN_hostname-OR-IPaddress> --port 1521 --database oracle_db --user ora_user
```
  По запросу утилиты ввести интерактивно дважды пароль пользователя:
```
enter password:
*******************
confirm password:
*******************
```
  В случае успешного завершения утилита выдает сообщение:
```
Going to add auth record for user: "ora_user", host: "<FQDN_hostname-OR-IPaddress>", port: "1521", database: "oracle_db"
new record added
```
Внимание!

При проверке хранилища паролей расширение не проверяет пароль пользователя БД Oracle.

Вывод содержимого целевой тестовой таблицы SCOPE_TEST1 в базе данных Oracle:

SQL> SELECT * FROM SCOPE_TEST1;

      COL1 COL2
---------- -------------------------------------------------
      4034 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
       734 GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
	    32 BUFFER_POOL DEFAULT FLASH_CACHE
    	74 POOL DEFAULT FLASH_CACHE
       297 GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
    	84 GROUPS POOL DEFAULT FLASH_CACHE
       532 BUFFER_POOL DEFAULT
       574 POOL DEFAULT
       297 GROUPS 1 BUFFER_POOL DEFAULT
       584 GROUPS POOL DEFAULT

10 rows selected.

Структура целевой тестовой таблицы SCOPE_TEST1 в базе данных Oracle:

SQL> DESCRIBE SCOPE_TEST1;

 Name						   Null?    Type
 ----------------------------- -------- ----------------------
 COL1						   NOT NULL NUMBER(19)
 COL2						   NOT NULL VARCHAR2(128 CHAR)

Создать внешнюю таблицу по отношению к существующей таблицы в базе данных Oracle (атрибуты и типы данных должны соответствовать):
```
CREATE FOREIGN TABLE ext.ora_scope_test1(col1 numeric(19,0), col2 varchar(128)) SERVER ora_db OPTIONS (SCHEMA 'PG_USER', TABLE 'SCOPE_TEST1');
```
где:
- ext.ora_scope_test1 – полное название внешней таблицы, создаваемой в базе данных Pangolin;
- col1, col2 – столбцы таблицы, которые должны соответствовать по названию и типу данных структуре внешней таблице в базе данных Oracle;
- ora_db – имя сервера внешних данных Oracle;
- PG_USER – имя схемы в базе данных Oracle;
- SCOPE_TEST1 – имя тестовой таблицы в базе данных Oracle.

Проверка работы расширения: можно обращаться к целевой таблице во внешней базе данных Oracle, как к обычной таблице PostgreSQL в СУБД Pangolin:

SELECT * FROM ext.ora_scope_test1;

Пример вывода результата запроса:

 col1 |                       col2                        
------+---------------------------------------------------
| FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
| GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
| BUFFER_POOL DEFAULT FLASH_CACHE
| POOL DEFAULT FLASH_CACHE
| GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
| GROUPS POOL DEFAULT FLASH_CACHE
| BUFFER_POOL DEFAULT
| POOL DEFAULT
| GROUPS 1 BUFFER_POOL DEFAULT 
| GROUPS POOL DEFAULT
(10 rows)

Созданная внешняя таблица присутствует в списке системного каталога:

\d+ ora_scope_test1

                                          Foreign table "ext.ora_scope_test1"
 Column |          Type          | Collation | Nullable | Default | FDW options | Storage  | Stats target | Description 
--------+------------------------+-----------+----------+---------+-------------+----------+--------------+-------------
 col1   | numeric(19,0)          |           |          |         |             | main     |              | 
 col2   | character varying(128) |           |          |         |             | extended |              | 
Server: ora_db
FDW options: (schema 'PG_USER', "table" 'SCOPE_TEST1')

Удалить внешнюю таблицу:
```
DROP FOREIGN TABLE ext.ora_scope_test1;
```

Ссылки на документацию

Описание расширения: Подраздел Функциональность FOREIGN DATA WRAPPER для БД Oracle документа «Сценарии администрирования».
Дополнительно поставляемый модуль oracle_fdw: https://github.com/laurenz/oracle_fdw.

Доработка​

Ограничения​

Установка​

Настройка​

Использование модуля​

Ссылки на документацию​