0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@zumax

Oracle DBのテーブルをAurora PostgreSQLからFDW経由で参照してみた

0
Posted at

背景・目的

OracleとPostgreSQLを併用する環境で、Oracle上に構築したマスターデータを定期的にPostgreSQL側に取り込みたくなりました。
本記事では、PostgreSQLのFDW (Foreign Data Wrapper) 機能を使って、Oracleのテーブルを直接参照し、マテリアライズドビューとして取り込む方法を検証してみます。

まとめ

下記に特徴を整理します。

特徴 説明
FDW Foreign Data Wrapperの略。PostgreSQLから外部データソースにアクセスするための標準的な仕組み
対応バージョン（Oracleの場合） oracle_fdw 拡張機能は、PostgreSQL 12.7 (Amazon Aurora release 4.2) 以上
特徴 ・透過的なデータアクセス
・多様なデータソース対応
・クエリ最適化
・セキュリティ
・PostgreSQL標準機能
PostgreSQLのマテリアライズドビュー ・フルリフレッシュのみ（差分リフレッシュがない）
・外部テーブルが必須
PostgreSQLのマテリアライズドビューのリフレッシュ リフレッシュ中にSELECTが待たされるので、ユニークインデックスを付与し、リフレッシュはCONCURRENTLYを指定すること

概要

下記を基にFDWを整理します。

  • FDW (Foreign Data Wrapper) とはPostgreSQLから外部データソースにアクセスするための標準的な仕組み
  • oracle_fdw 拡張機能は、PostgreSQL 12.7 (Amazon Aurora release 4.2) 以上のバージョンでサポートされている

FDWの特徴

透過的なデータアクセス

  • 外部DBのテーブルを、PostgreSQL内の普通のテーブルのように扱える
  • 複雑な接続処理を意識せずにSQLで操作可能
  • PostgreSQLネイティブの統合機能

多様なデータソース対応

  • oracle_fdw
    • Oracle Database
  • mysql_fdw
    • MySQL
  • postgres_fdw
    • 他のPostgreSQL
  • tds_fdw
    • SQL Server
  • その他、ファイルやログにもアクセス可能

クエリ最適化

  • プッシュダウン最適化
    • WHERE句やフィルタリング、ソートを外部DB側で実行
  • ネットワーク転送量を削減
  • パフォーマンス向上

セキュリティ

  • ユーザーごとに接続情報を管理
  • 暗号化通信対応（Oracle側の設定に依存）
  • 権限制御が可能

PostgreSQL標準機能

  • 追加ソフトウェア不要
  • 拡張機能として簡単にインストール
  • Aurora PostgreSQLでも利用可能

実践

Oracleの構築

  1. 以前、DMS用に構築したOracle on EC2があるのでこちらを再利用します

Auroraの構築

  1. Auroraを別のサブネット、別SGに構築します。10分程かかります

Auroraでoracle_fdwを設定

psqlのインストール

1.EC2にログインします
2.PostgreSQLクライアントをインストールします。リポジトリに追加してからインストールします

sudo tee /etc/yum.repos.d/pgdg.repo<<EOF
[pgdg15]
name=PostgreSQL 15 for RHEL/CentOS 7 - x86_64
baseurl=https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-7-x86_64
enabled=1
gpgcheck=0
EOF

sudo yum install -y postgresql15
$ psql --version
psql (PostgreSQL) 15.17
$

oracle_fdw拡張機能の有効化

1.Auroraへ接続します

$ psql -h <Aurora-Endpoint> \
>      -U postgres -d postgres
Password for user postgres:
psql (15.17, server 15.15)
SSL connection (protocol: TLSv1.2, cipher: AES128-SHA256, compression: off)
Type "help" for help.

=>

2.設定前に利用可能な拡張リストを確認します

postgres=> SELECT * FROM pg_available_extensions WHERE name LIKE '%fdw%';
     name     | default_version | installed_version |                                      comment
--------------+-----------------+-------------------+-----------------------------------------------------------------------------------
 postgres_fdw | 1.1             |                   | foreign-data wrapper for remote PostgreSQL servers
 mysql_fdw    | 1.2             |                   | Foreign data wrapper for querying a MySQL server
 oracle_fdw   | 1.2             |                   | foreign data wrapper for Oracle access
 tds_fdw      | 2.0.4           |                   | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)
 log_fdw      | 1.3             |                   | foreign-data wrapper for Postgres log file access
(5 rows)

postgres=>

3.インストール済み拡張機能を確認します。まだoracle_fdwはインストールされていません

postgres=> \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

postgres=>

4.oracle_fdw拡張機能を有効化します

postgres=> CREATE EXTENSION oracle_fdw;
CREATE EXTENSION
postgres=>

5.インストール済み拡張機能を確認します。oracle_fdwがインストールされました

postgres=> \dx
                        List of installed extensions
    Name    | Version |   Schema   |              Description
------------+---------+------------+----------------------------------------
 oracle_fdw | 1.2     | public     | foreign data wrapper for Oracle access
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

postgres=>

6.FDWの一覧を確認します

postgres=> \dew
                   List of foreign-data wrappers
    Name    |  Owner   |      Handler       |      Validator
------------+----------+--------------------+----------------------
 oracle_fdw | rdsadmin | oracle_fdw_handler | oracle_fdw_validator
(1 row)

postgres=>

外部サーバの作成

1.接続情報を定義するため、外部サーバを作成します。これにより複数のユーザーが同じサーバー定義を共有が可能になります

postgres=> CREATE SERVER oracle_ec2
postgres-> FOREIGN DATA WRAPPER oracle_fdw
postgres-> OPTIONS (dbserver '//<Oracle-IP>:1521/ORCL');
CREATE SERVER
postgres=>

2.外部サーバ一覧を確認します

postgres=> \des
           List of foreign servers
    Name    |  Owner   | Foreign-data wrapper
------------+----------+----------------------
 oracle_ec2 | postgres | oracle_fdw
(1 row)

postgres=>

ユーザーマッピングを作成

1.事前に確認します。設定されていません

postgres=> \deu
List of user mappings
 Server | User name
--------+-----------
(0 rows)

postgres=>

2.ユーザーマッピングを作成します

postgres=> CREATE USER MAPPING FOR postgres
postgres-> SERVER oracle_ec2
postgres-> OPTIONS (user '<oracle-user>', password '<oracle-password>');
CREATE USER MAPPING
postgres=>

3.確認します。設定されました

postgres=> \deu
 List of user mappings
   Server   | User name
------------+-----------
 oracle_ec2 | postgres
(1 row)

postgres=>

Oracleにテストテーブルを作成

実際に連携する元になるテーブルを作成します

1.Oracleに接続します
2.連携用のマスターテーブルを作成します

CREATE TABLE test_master (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(100),
  created_date DATE
);

SQL> desc test_master
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(100)
 CREATED_DATE                                       DATE

SQL>

SQL> select count(1) from test_master
  2  ;

  COUNT(1)
----------
         0

SQL>

3.テストデータを投入します

SQL> INSERT INTO test_master VALUES (1, 'Test Data 1', SYSDATE);
INSERT INTO test_master VALUES (2, 'Test Data 2', SYSDATE);
1 row created.

SQL>

1 row created.

SQL> Commit;

Commit complete.

SQL>

4.登録されました

SQL> l
  1* SELECT * FROM test_master
SQL> /

        ID NAME                                                                                                 CREATED_D
---------- ---------------------------------------------------------------------------------------------------- ---------
         1 Test Data 1                                                                                          01-MAR-26
         2 Test Data 2                                                                                          01-MAR-26

SQL>

Auroraで外部テーブル作成

1.事前に外部テーブルの存在を確認します

postgres=> \det
 List of foreign tables
 Schema | Table | Server
--------+-------+--------
(0 rows)

postgres=>

2.外部テーブルを作成します。上記で作成したOracleのスキーマ（ユーザ）とテーブルを指定します

postgres=> CREATE FOREIGN TABLE fdw_test_master (
postgres(>   id INTEGER,
postgres(>   name VARCHAR(100),
postgres(>   created_date TIMESTAMP
postgres(> ) SERVER oracle_ec2
postgres-> OPTIONS (schema '<ORACLE-SCHEMA>', table 'TEST_MASTER');
CREATE FOREIGN TABLE
postgres=>

3.作成後の確認をします。見えました

postgres=> \det
        List of foreign tables
 Schema |      Table      |   Server
--------+-----------------+------------
 public | fdw_test_master | oracle_ec2
(1 row)

postgres=>

4.データを取得してみます。上記のOracleで登録した内容が見えました

postgres=> SELECT * FROM fdw_test_master;
 id |    name     |    created_date
----+-------------+---------------------
  1 | Test Data 1 | 2026-03-01 11:58:18
  2 | Test Data 2 | 2026-03-01 11:58:19
(2 rows)

postgres=>

マテリアライズドビュー作成

外部テーブルのデータをPostgreSQL側にキャッシュするマテリアライズド・ビューを作成します

PostgreSQLでマテリアライズドビューを作成

1.事前に確認します

postgres=> \dm
Did not find any relations.
postgres=>

2.マテリアライズドビューを作成します

postgres=> CREATE MATERIALIZED VIEW mv_test_master AS
postgres-> SELECT * FROM fdw_test_master;
SELECT 2
postgres=>

3.マテリアライズドビューを確認します

postgres=> \dm
                   List of relations
 Schema |      Name      |       Type        |  Owner
--------+----------------+-------------------+----------
 public | mv_test_master | materialized view | postgres
(1 row)

postgres=>

4.定義を確認してみます

postgres=> SELECT
postgres->   schemaname,
postgres->   matviewname,
postgres->   hasindexes,
postgres->   ispopulated,
postgres->   definition
postgres-> FROM pg_matviews
postgres-> WHERE matviewname = 'mv_test_master';
 schemaname |  matviewname   | hasindexes | ispopulated |            definition
------------+----------------+------------+-------------+----------------------------------
 public     | mv_test_master | f          | t           |  SELECT fdw_test_master.id,     +
            |                |            |             |     fdw_test_master.name,       +
            |                |            |             |     fdw_test_master.created_date+
            |                |            |             |    FROM fdw_test_master;
(1 row)

postgres=>
postgres=>

5.データを確認します

postgres=> SELECT * FROM mv_test_master;
 id |    name     |    created_date
----+-------------+---------------------
  1 | Test Data 1 | 2026-03-01 11:58:18
  2 | Test Data 2 | 2026-03-01 11:58:19
(2 rows)

postgres=>

Oracleでデータを登録・更新

1.登録前の状態を確認します。この時点ではPostgreSQLと同じ内容です

SQL> l
  1* SELECT * FROM test_master
SQL> /

        ID NAME                                                                                                 CREATED_D
---------- ---------------------------------------------------------------------------------------------------- ---------
         1 Test Data 1                                                                                          01-MAR-26
         2 Test Data 2                                                                                          01-MAR-26

SQL>

2.データを登録・更新します

SQL> -- 新規データ追加
INSERT INTO test_master VALUES (3, 'Test Data 3', SYSDATE);
INSERT INTO test_master VALUES (4, 'Test Data 4', SYSDATE);

-- 既存データ更新
UPDATE test_master SET name = 'Updated Data 1' WHERE id = 1;

-- コミット
COMMIT;SQL>
1 row created.

SQL>
1 row created.

SQL> SQL> SQL>
1 row updated.

SQL> SQL> SQL>

Commit complete.

SQL>

3.データを確認します

SQL> SELECT * FROM test_master ORDER BY id;

        ID NAME                                                                                                 CREATED_D
---------- ---------------------------------------------------------------------------------------------------- ---------
         1 Updated Data 1                                                                                       01-MAR-26
         2 Test Data 2                                                                                          01-MAR-26
         3 Test Data 3                                                                                          01-MAR-26
         4 Test Data 4                                                                                          01-MAR-26

SQL>
SQL>

PostgreSQLでリフレッシュを実行

1.上記のOracleでデータを登録・更新された状態でマテリアライズドビューの確認をします（リフレッシュ前）。データは連携されていません

postgres=> SELECT * FROM mv_test_master;
 id |    name     |    created_date
----+-------------+---------------------
  1 | Test Data 1 | 2026-03-01 11:58:18
  2 | Test Data 2 | 2026-03-01 11:58:19
(2 rows)

postgres=>

2.外部テーブルを確認します。外部テーブルはOracleと同期が取れています

postgres=> SELECT * FROM fdw_test_master ORDER BY id;
 id |      name      |    created_date
----+----------------+---------------------
  1 | Updated Data 1 | 2026-03-01 11:58:18
  2 | Test Data 2    | 2026-03-01 11:58:19
  3 | Test Data 3    | 2026-03-01 12:17:51
  4 | Test Data 4    | 2026-03-01 12:17:51
(4 rows)

postgres=>

3.Refreshします

postgres=> REFRESH MATERIALIZED VIEW mv_test_master;
REFRESH MATERIALIZED VIEW
postgres=>

4.最新のデータに更新されました

postgres=> SELECT * FROM mv_test_master ORDER BY id;
 id |      name      |    created_date
----+----------------+---------------------
  1 | Updated Data 1 | 2026-03-01 11:58:18
  2 | Test Data 2    | 2026-03-01 11:58:19
  3 | Test Data 3    | 2026-03-01 12:17:51
  4 | Test Data 4    | 2026-03-01 12:17:51
(4 rows)

postgres=>

CONCURRENTLY オプション

  • 通常のREFRESHは全データを再取得し、リフレッシュ中は読み取り不可となります。SELECTすると待たされる（ブロックされる）ようです。Refresh完了後に、結果が返るらしいです
  • 上記を回避するために、CONCURRENTLYをつけることで、全データを再取得しても、リフレッシュ中も読み取り可能になるようです。ただしユニークインデックスが必要とのことです。確認してみます

ユニークインデックスがない状態

1.インデックスを確認します。hasindexesはf（false）です

postgres=> \d mv_test_master
                  Materialized view "public.mv_test_master"
    Column    |            Type             | Collation | Nullable | Default
--------------+-----------------------------+-----------+----------+---------
 id           | integer                     |           |          |
 name         | character varying(100)      |           |          |
 created_date | timestamp without time zone |           |          |

postgres=>


postgres=> SELECT matviewname, hasindexes FROM pg_matviews WHERE matviewname = 'mv_test_master';
  matviewname   | hasindexes
----------------+------------
 mv_test_master | f
(1 row)

postgres=>

2.ユニークインデックスを付ける前に、CONCURRENTLYをつけてリフレッシュします。エラーになりました

postgres=> REFRESH MATERIALIZED VIEW CONCURRENTLY mv_test_master;
ERROR:  cannot refresh materialized view "public.mv_test_master" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.
postgres=>

ユニークインデックスを付与した状態

1.ユニークインデックスを作成します

postgres=> CREATE UNIQUE INDEX idx_mv_test_master_id ON mv_test_master(id);
CREATE INDEX
postgres=>

2.作成後、確認します。idx_mv_test_master_idが追加されました

postgres=> \d mv_test_master
                  Materialized view "public.mv_test_master"
    Column    |            Type             | Collation | Nullable | Default
--------------+-----------------------------+-----------+----------+---------
 id           | integer                     |           |          |
 name         | character varying(100)      |           |          |
 created_date | timestamp without time zone |           |          |
Indexes:
    "idx_mv_test_master_id" UNIQUE, btree (id)

postgres=>

3.hasindexesがt（true）になりました

postgres=> SELECT matviewname, hasindexes FROM pg_matviews WHERE matviewname = 'mv_test_master';
  matviewname   | hasindexes
----------------+------------
 mv_test_master | t
(1 row)

postgres=>

4.CONCURRENTLYでリフレッシュテストをします。エラーになりませんでした

postgres=> REFRESH MATERIALIZED VIEW CONCURRENTLY mv_test_master;
REFRESH MATERIALIZED VIEW
postgres=>

考察

今回、FDWとマテリアライズドビューを組み合わせてOracleとPostgreSQLのデータ連携を試してみましたが、シンプルに実現できました。
特に、外部テーブルを作成するだけでOracleのデータが透過的に見えるのは、OracleのDB Linkに慣れていた私にとって理解しやすい仕組みでした。

一方で、PostgreSQLのマテリアライズドビューはOracleと比べると機能が限定的と感じました。

  • 差分リフレッシュがなく常にフルリフレッシュになる
  • 自動リフレッシュの仕組みがない点は、大規模データを扱う場合に課題となりそう
    • ただし、CONCURRENTLYオプションを使えば、リフレッシュ中もユーザーのクエリをブロックしない
    • ユニークインデックスが必要という制約はあるが、本番運用では必須の機能だと感じた

参考

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?