oracle_fdwを使ってPostgresqlとOracle Databaseを繋ぐ（docker版）

概要

今回はoracle_fdwを使ってPostgresqlとOracle Databaseを繋いでいます。
両DBともDocker上に構築しています。

対象読者

oracle_fdwをdockerコンテナ上に構築したい方

配置図

環境

名称	バージョン	説明
Windows	10	クライアントのOS
sqlplus	23.6	Oracle Databaseのコマンドラインインターフェイス
Oracle	Database 23ai Free Release 23.0.0.0.0	データベース
PosgreSQL	1.4.0	--
Oracle_fdw	2.5.0	PostgreSQLのプラグイン Oracle Databaseとアクセスするためのプラグイン
Docker	??	コンテナ型仮想環境

フォルダ構成

構築に使ったフォルダ、ファイルは以下になります。

フォルダorファイル名	説明
./docker/postgresql/Dockerfile	Dockerイメージを作成するためのテキストファイル
./docker/postgresql/sdk	oracle公式から落としたzipファイルを置くフォルダ
./docker-compose.yml	コンテナ実行ファイル

手順

前提

・postgresqlのコンテナでsqlplusを使って、Oracle Databaseにアクセスできること
　参考記事
https://qiita.com/RYA234/items/e8782d96818fe193f34a

・Oracle Databaseが構築済みであること
　参考記事
https://qiita.com/RYA234/items/19abe4a44e9820c9efd3

Dockerコンテナ構築まで

oracle instantと oaracle_fdwをダウンロード

Oracle公式ページから　instantClient をインストールします。
https://www.oracle.com/jp/database/technologies/instant-client/linux-x86-64-downloads.html

・instantclient-basic-linux.x64-23.6.0.24.10.zip
・instantclient-sdk-linux.x64-23.6.0.24.10.zip
・instantclient-sqlplus-linux.x64-23.6.0.24.10.zip

・Source code(zip)

上でダウンロードしたzipファイルを配置する

docker/postgresql/sdkフォルダにzipファイルを配置する

Dockerfileを作成

Dockerfile

FROM postgres:14.0-bullseye

RUN apt-get update \
    && apt-get install -y build-essential git-core libv8-dev curl postgresql-server-dev-$PG_MAJOR unzip  make sudo libaio1 nmap iputils-ping net-tools\
    && rm -rf /var/lib/apt/lists/*

ENV UNAME=postgres
ENV GID=0
ENV UID=1000

RUN usermod -aG root  $UNAME

RUN echo "Defaults:$UNAME !env_reset" > /etc/sudoers && \
echo "$UNAME ALL=(ALL) NOPASSWD:ALL" >> /etc/sudoers

COPY ./sdk /tmp

USER $UNAME

# oracle instant clientの設定
# zipを解凍して　環境変数を通す
RUN unzip /tmp/instantclient-basic-linux.x64-23.6.0.24.10.zip -d /tmp
RUN unzip -o /tmp/instantclient-sdk-linux.x64-23.6.0.24.10.zip -d /tmp
RUN unzip -o /tmp/instantclient-sqlplus-linux.x64-23.6.0.24.10.zip -d /tmp

RUN sudo sh -c "echo /tmp/instantclient_23_6 > /etc/ld.so.conf.d/oracle-instantclient.conf"
RUN sudo ldconfig

ENV ORACLE_HOME=/tmp/instantclient_23_6
ENV LD_LIBRARY_PATH=/tmp/instantclient_23_6:$LD_LIBRARY_PATH
ENV PATH=/tmp/instantclient_23_6:$PATH
ENV NLS_LANG=Japanese_Japan.AL32UTF8
ENV ORACLE_SID=FREE
# oracle_fdwを解凍してビルドする
RUN unzip /tmp/oracle_fdw-ORACLE_FDW_2_5_0.zip -d /tmp

RUN cd /tmp/oracle_fdw-ORACLE_FDW_2_5_0 && make && sudo make install
EXPOSE 5432

docker_compose.yml

docker-compose.yml

services:
  postgresql:
    # image: postgres:11.7
    container_name: postgresql
    build: ./docker/postgresql 
    environment:
      TZ: 'Asia/Tokyo'
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      # PGDATA: /var/lib/postgresql/data/pgdata
    ports:
      - "5432:5432"
    volumes:
      - ./docker/postgresql/init:/docker-entrypoint-initdb.d
      - ./src:/src
      - db-data:/var/lib/postgresql/data
    # restart: always
    networks:
      external_11:
  oracle_db:
    image: oracle/database:23.5.0-free
    # build: ./docker/oracle
    container_name: oracle_test_db
    shm_size: 1g # 共有メモリを1GB以上にしないとコンテナ起動時にエラーが発生します。
    environment:
      - TZ=Asia/Tokyo
      - LANGUAGE=ja_JP.ja
      - LANG=ja_JP.UTF-8
      - NLS_LANG=Japanese_Japan.AL32UTF8 # sqlplusの日本語化でこの環境変数が必要になります。
      - ORACLE_PWD=pass # パスワードは適宜指定します。
    ports:
      - "1521:1521"
    volumes:
      - ./src:/opt/oracle/oradata
    networks:
      external_11:

volumes:
  db-data:
networks:
  external_11:
      driver: bridge
 

コマンド実行とIP確認

# ビルド
docker-compose build

# 起動
docker-compose up -d

# postgresqlコンテナに入る
docker-compose exec -it postgresql bash

# nmapでOracleのポートが開いていることを確認する
nmap 192.168.0.1

# 実行結果



# dockerコンテナ構築完了 SQL実行に移る。

SQL実行部分

windowsの時と同じですね。
IPだけは注意が必要です。
CREATE SERVER oracle_server FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.0.1:1521/FREE');

1.PostgresSQL側で　oracle_fdwを有効化する

-- 1.oracle_fdwをロード
CREATE EXTENSION oracle_fdw;
-- 1.pg_extensionにoracle_fdwが追加されていることを確認
select * from pg_catalog.pg_extension;

2.Oracle　DatabaseのIP PORT サービス名を登録

-- 2.Oracle　DatabaseのIP PORT サービス名を登録
-- 今回の場合はIP=192.168.0.1　PORT=1521　サービス名=FREE
CREATE SERVER oracle_server FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.0.1:1521/FREE');

-- 2.権限追加　確認の仕方わからない..........
GRANT USAGE ON FOREIGN SERVER oracle_server TO postgres;

-- 2.pg_foreign_serverにoracle_serverが作成されていることを確認
select * from pg_catalog.pg_foreign_server;

3.ユーザーマッピング Oracle側のユーザとパスワードを登録

-- 3.ユーザーマッピング Oracle側のユーザとパスワードを登録
CREATE USER MAPPING FOR postgres SERVER oracle_server OPTIONS ( USER 'system', PASSWORD 'pass');
-- 3.pg_user_mappingで確認
select * from pg_catalog.pg_user_mapping;

4.外部テーブルを作成する

-- 4.外部テーブルを作成
CREATE FOREIGN TABLE help(TOPIC varchar(50) , SEQ integer OPTIONS (key 'true'), INFO varchar(80)) SERVER oracle_server OPTIONS (SCHEMA 'SYSTEM', TABLE 'HELP');
 
--  4.作成した外部テーブルを確認
SELECT * FROM help;

感想

自分が以下２点で手間どりましたね。
・Oracle instantを導入するまで
・どのIPを参照するのか(最初localhostだと思ってました。)

参考

github差分