oracle_fdw

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

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', '<пароль>');
     Пример вывода результата:

     Раскрыть type=sql
     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
     По запросу утилиты ввести интерактивно дважды пароль пользователя:

     Раскрыть type=bash
     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;
   Пример вывода результата запроса:

   Раскрыть type=sql
    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
   Пример вывода результата запроса:

   Раскрыть type=sql
                                             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;

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