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

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

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

      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
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».

  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.

  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.