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_fdw описана в подразделе «Расширение oracle_fdw» документа «Руководство по установке».

Пример установки вручную:

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

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

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

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

      sudo vi /etc/ld.so.conf

    • сохраните файл;

    • создать связки и кеш динамических библиотек

      sudo ldconfig -N

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

    echo "[ -n \"\$PATH\" ] && export PATH=\$PATH:/usr/lib/oracle/19.18/client64/bin || export PATH=/usr/lib/oracle/19.18/client64/bin" >> /etc/profile.d/ora2pg.sh
export PATH="$PATH:/usr/lib/oracle/19.18/client64/bin"
echo "export ORACLE_HOME=/usr/lib/oracle/19.18/client64" >> /etc/profile.d/ora2pg.sh
export ORACLE_HOME="/usr/lib/oracle/19.18/client64"
echo "export NLS_LANG=AMERICAN_AMERICA.AL32UTF8" >> /etc/profile.d/ora2pg.sh
export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"
echo "[ -n \"\$LD_LIBRARY_PATH\" ] && export LD_LIBRARY_PATH=\$LD_LIBRARY_PATH:/usr/lib/oracle/19.18/client64/lib || export LD_LIBRARY_PATH=/usr/lib/oracle/19.18/client64/lib" >> /etc/profile.d/ora2pg.sh
[ -n "$LD_LIBRARY_PATH" ] && export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/usr/lib/oracle/19.18/client64/lib" || export LD_LIBRARY_PATH=/usr/lib/oracle/19.18/client64/lib

    Примечание:

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

    Примечание:

    Порядок установки переменной LD_LIBRARY_PATH описан в документе «Руководство администратора», раздел «Порядок установки переменной LD_LIBRARY_PATH в окружении Pangolin Manager».

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

    pg_ctl restart

    Пример результата успешной перезагрузки:

    waiting for server to shut down.... done
server stopped
waiting for server to start....2023-02-27 14:15:43 MSK [7011]: [1-1] app=,user=,db=,client=,type=postmaster LOG:  pg_ouline: outlines disabled
2023-02-27 14:15:43 MSK [7011]: [2-1] app=,user=,db=,client=,type=postmaster LOG:  pg_ouline: outlines disabled
2023-02-27 14:15:43 MSK [7011]: [3-1] app=,user=,db=,client=,type=postmaster LOG:  redirecting log output to logging collector process
2023-02-27 14:15:43 MSK [7011]: [4-1] app=,user=,db=,client=,type=postmaster HINT:  Future log output will appear in directory "/pgerrorlogs/05".
 done
server started

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

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

    CREATE EXTENSION oracle_fdw SCHEMA ext;

    Примечание:

    В случае получения ошибки следующего вида проверьте правильность установки переменных среды (п. 2 – 4).

    ERROR:  could not load library "/usr/pgsql-se-05/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.