【PostgreSQL】oracle_fdwで外部データにアクセスする

今回は、こういった悩みを解決していきます。

昨今、製造業界では「スマートファクトリー」や「DX」に注目が集まっています。

その中で避けては通れない社内データの一元化による見える化や分析業務があります。

企業で活用しているデータベースは、それぞれの業務に応じて導入してきたシステムの数だけあります。

生産管理には生産管理システム、在庫には在庫管理システムと点在しているはずです。

それらのデータとIotで取得した設備データやセンサー情報などを組み合わせて、新たな分析基盤の構築と活用が進められています。

本記事では下記の内容についてまとめています。

外部データにアクセスするには

PostgreSQLには、外部データラッパー（FDW）という外部のデータにアクセスする仕組みがあります。

今回紹介するのはその中の一つであるOracle_fdwです。

外部のOracleデータベースへアクセスすることができます。

外部のPostgreSQLへアクセスする方法は下の記事でまとめています。

私自身、Oracle_fdwを使って外部のデータにアクセスするまでにかなり手こずったので、備忘録として手順を残しておきたいと思います。

ローカルのpostgreSQLから実行していますが、postgreSQLがインストールされているサーバーからでも手順は同じです。

条件はpostgreSQLがインストールされているPCが外部のOracleへアクセスできるネットワーク上にあることです。

oracle_fdwで外部データにアクセスする方法

それでは具体的な手順を紹介していきます。

①Oracle_fdwのインストール

1. 以下のgithubのリリースページより、`oracle_fdw-2.5.0-pg15-win64.zip`をダウンロードする

2. zipファイルを展開して、debug_symbolsとlibとshareの3つのディレクトリ内にあるファイルを以下のフォルダ直下にあるdebug_symbolsとlibとshareのフォルダにコピーする。
※PostgreSQLをインストール時に作成されたフォルダ

C:\Program Files\PostgreSQL\15

②psqlを使ってPostgreSQLに接続する（A5:SQL mk-2でも可）

A5:SQL mk-2を使ってPostgreSQLにアクセスする方法は下の記事でまとめています。

PostgreSQLをインストールして、A5:SQL MK-2から接続する方法

1. PostgreSQLに接続する

psql -h localhost -p 5432 -U postgres -d postgres

2. ログイン用パスワードの入力待ちユーザー postgres のパスワード:になるのでパスワードを入力する

3. 正しくログインできるとpostgres=#と表示される

③oracle_fdwのエクステンションの作成

1. Oracle Foreign Data Wrapperの作成

CREATE EXTENSION oracle_fdw

2. 外部データラッパーのリストを確認する（psqlで実行）

\dew

                              外部データラッパ一覧
        名前    |  所有者  |      ハンドラ      |     バリデーター
    ------------+----------+--------------------+----------------------
     oracle_fdw | postgres | oracle_fdw_handler | oracle_fdw_validator
    (1 行)

④外部サーバーの作成

1. Oracleデータベースへの接続情報を外部サーバー（foreign）として定義する

　接続先のIPアドレス、ポート番号、サービス名を入力します

CREATE SERVER foreign FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '192.168.×××.×××:××××/××××××')

2. 外部サーバーのリストを確認する（psqlで実行）

\des+

                                                       外部サーバー一覧
    名前  |  所有者  | 外部データラッパ | アクセス権限 | タイプ | バージョン |            FDW オプション             | 説明
   -------+----------+------------------+--------------+--------+------------+---------------------------------------+------
    foreign | postgres | oracle_fdw       |              |        |            | (dbserver '192.168.×××.×××:××××/××××') |
   (1 行)

3. postgre側のローカルユーザー(postgres)が、定義された外部サーバー（foreign）を使用できるように権限を付与します

GRANT USAGE ON FOREIGN SERVER foreign TO postgres

⑤ユーザーマップの作成

1. postgre側のローカルユーザー(postgres)とOracle側のリモートユーザー(foreign_user)を紐づけるため、外部サーバー(foreign)上にユーザーマップを作成する

CREATE USER MAPPING FOR postgres SERVER foreign OPTIONS (USER 'foreign_user', PASSWORD 'foreign_pass')

2. ユーザーマップのリストを確認する（psqlで実行）

\deu+

                         ユーザーマッピング一覧
    サーバー | ユーザー名 |              FDW オプション
   ----------+------------+------------------------------------------
    foreign | postgres   | ("user" 'foreign_user', password 'foreign_pass')
   (1 行)

⑥外部テーブルの作成

1. Oracleデータベース上のテーブルやビューに対応する外部テーブル(foreign_data)を作成する

CREATE FOREIGN TABLE foreign_data (id int, name text) SERVER foreign OPTIONS (SCHEMA 'public', TABLE 'oracle_table)

2. 外部テーブルのテーブル定義を確認する（psqlで実行）

\det+

                                        外部テーブル一覧
    スキーマ |    テーブル    | サーバー |               FDW オプション                | 説明
   ----------+----------------+----------+---------------------------------------------+------
    public   | foreign_data | foreign    | (schema 'public', "table" 'oracle_table') |
   (1 行)

⑦外部テーブルの一括作成

1. postgre側に新規でスキーマを作成する

CREATE SCHEMA foreign_schema

2. Oracleデータベースの指定されたスキーマに含まれるすべてのテーブルを対象に、外部テーブルを作成する

IMPORT FOREIGN SCHEMA "public" FROM SERVER foreign INTO foreign_schema

⑧外部テーブルの削除

1. 外部テーブルを削除する

DROP FOREIGN TABLE adams_vbi_stec

まとめ

PostgreSQLからOracleデータベースへのアクセス方法について紹介しました。

私自身、外部のデータベースへのアクセス方法を調べてOracle_fdwを使おうと思ったとき、最も序盤のインストールでつまずきました。

オンプレミスの環境ということもあり、参考になる記事が少なかったため、今回は手順を残すことにしました。

システム間のデータ連携をする場面は増えつつあるので、今後どなたかの役に立てたらうれしく思います。