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 или выше.

Установка#

Установка расширения производится инсталлятором (документ «Руководство по установке», раздел «Установка»).

В случае ручной установки, выполните действия:

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

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

  2. Добавьте в систему библиотеки клиента:

    1. Добавьте строку /usr/lib/oracle/19.18/client64/lib/ в файл /etc/ld.so.conf:

    sudo vi /etc/ld.so.conf

    1. Сохраните файл и выйдите из редактора.

    2. Создайте связки и кеш динамических библиотек:

      sudo ldconfig -N

  3. Выполните настройку необходимых переменных окружения:

    • для использования расширения достаточно переменной окружения 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

    • для обновления версии 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».

  4. Перезагрузите сервер базы данных для применения переменных среды. Пример для инсталляции standalone:

    pg_ctl restart

    После выполнения описанных действий необходимые библиотеки и файлы клиента Oracle появятся в системе.

  5. Добавьте расширение в БД (необходимы права суперпользователя):

    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

  6. Дайте права на использование расширения oracle_fdw для необходимой роли:

    GRANT USAGE ON FOREIGN DATA WRAPPER oracle_fdw TO <role_name>;

  7. При необходимости обновите расширение:

    ALTER EXTENSION oracle_fdw UPDATE;

Настройка#

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

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

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

  1. Создать «сервер» – атрибут, который содержит параметры подключения к серверу базы данных Oracle:

    CREATE SERVER ora_db FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//oracle_server:1521/oracle_db');

    где:

    • ora_db – произвольное имя сервера;

    • dbserver – параметр подключения к серверу баз данных Oracle.

  2. Создать сопоставление пользователя на внешнем сервере для подключения к созданному серверу ora_db:

    CREATE USER MAPPING FOR pguser SERVER ora_db OPTIONS (user 'ora_user');

    где:

    • pguser – имя пользователя в базе данных Pangolin, который сможет пользоваться внешними данными;

    • ora_db – имя созданного сервера внешних данных;

    • ora_user – имя пользователя в базе данных Oracle, который имеет право на чтение внешних данных.

  3. Выдать права для использования стороннего сервера Oracle для пользователя, которому было создано сопоставление (USER MAPPING):

    GRANT USAGE ON FOREIGN SERVER ora_db TO pguser;

    где:

    • ora_db – имя созданного сервера внешних данных;

    • pguser – имя пользователя в базе данных Pangolin, которому предоставляются права на использование созданного сервера внешних данных.

  4. Добавить пароль для пользователя базы данных 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_fdw описан в подразделе «Функциональность FOREIGN DATA WRAPPER для БД Oracle» документа «Руководство администратора», раздел «Сценарии администрирования».

    Внимание!

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

  5. Вывод содержимого целевой тестовой таблицы 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.

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

    SQL> DESCRIBE SCOPE_TEST1;

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

  7. Создать внешнюю таблицу по отношению к существующей таблицы в базе данных 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.

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

    SELECT * FROM ext.ora_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)

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

    \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')

  10. Удалить внешнюю таблицу:

    DROP FOREIGN TABLE ext.ora_scope_test1;

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

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

  2. Дополнительно поставляемый модуль oracle_fdw: https://github.com/laurenz/oracle_fdw.