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

# 概要

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



# 対象読者
oracle_fdwをdockerコンテナ上に構築したい方


# 配置図
```plantuml
@startuml
left to right direction
node Windows10{
node Docker_B {

  database OracleDB as "OracleDB"{
      
  card "OracleDB\nログイン情報 \n User:system \n PASS:pass\n IP=192.168.0.1(変化する)  \n port=1521\nサービス名=FREE"

  }

}

node Docker_A {
    database PostgreSQL{
        component oracle_fdw
    }
    
  }
component client_app as "A5SQL or psqlコマンド"
}

client_app --> PostgreSQL :Oracleのテーブル\n取得or追加or更新する

PostgreSQL --> OracleDB:oracle_fdwを使って接続 

@enduml

```


# 環境

名称|バージョン|説明
---|----|------
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

https://github.com/laurenz/oracle_fdw/releases/tag/ORACLE_FDW_2_5_0

・Source code(zip)

### 上でダウンロードしたzipファイルを配置する
docker/postgresql/sdkフォルダにzipファイルを配置する





### Dockerfileを作成
#### 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
```diff_yaml: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を有効化する
```sql
-- 1.oracle_fdwをロード
CREATE EXTENSION oracle_fdw;
-- 1.pg_extensionにoracle_fdwが追加されていることを確認
select * from pg_catalog.pg_extension;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/15962f57-f8bd-caef-e4df-0527fada060c.png)



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

```sql
-- 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;

```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/40fd173e-998f-4787-8446-30288d3497f5.png)


### 3.ユーザーマッピング Oracle側のユーザとパスワードを登録
```sql
-- 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;

```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/11f71e55-0466-7470-179f-c626a7593864.png)

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

```sql
-- 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;

```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/e089223e-a4a5-2612-b4d0-a38fb69f9b39.png)



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

# 参考

github差分

https://github.com/RYA234/SQL_Puzzle_Learning/commit/20ba97a8269fd8fe010b898e99481f4c806bca9c