はじめに
Google CloudとOCIをクロスクラウド接続し、その接続経由で AlloyDB for PostgreSQL から Oracle Database(BaseDB)へ oracle_fdw を利用してリンクし、Oracle Databaseのテーブルを Materialized View(以下MView) として AlloyDB上に作成してみた話になります。
Google CloudとOCIはクロスクラウド接続で直接接続することが可能です。
なお、Google Cloud上のCloud SQLやAlloyDBはユーザーのVPCではなく Google Cloud のInternal VPCに配置されるため、クロスクラウド接続を行っただけでは直接接続できず、カスタムルートなどの設定が必要になります。
テスト構成
Google Cloud上のAlloyDBからクロスクラウド接続経由でOCIのOracle Database(BaseDB)へリンク(oracle_fdw)で接続しています。
AlloyDBからOracle Databaseへの接続
Google CloudとOCIの間のクロスクラウド接続が設定されていることが前提となります。
カスタムルート設定
AlloyDBのIPは 10.102.97.25 となります。
カスタムルートは 10.102.97.0/24 として設定します。
VPCネットワークピアリングは設定してあるため、OCI側のルート表を確認します。
AlloyDBとOracle Databsaeのリンク設定
AlloyDBにOracle fdw イクステンションをインストールし、Oracle Database側の ORA_TBL を外部テーブルとして設定します。
Extensionのインストールと確認
$ psql -h 10.102.97.25 -p 5432 -U postgres -d postgres
Password for user postgres:
psql (10.23, server 16.3)
WARNING: psql major version 10, server major version 16.
Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=> CREATE EXTENSION oracle_fdw;
CREATE EXTENSION
postgres=> \dew+
List of foreign-data wrappers
Name | Owner | Handler | Validator | Access privileges | FDW options | Description
------------+----------+--------------------+----------------------+-------------------+-------------+-----------------------------
oracle_fdw | postgres | oracle_fdw_handler | oracle_fdw_validator | | | Oracle foreign data wrapper
(1 row)
postgres=> SELECT name, default_version FROM pg_available_extensions WHERE name LIKE 'oracle%';
name | default_version
------------+-----------------
oracle_fdw | 1.2
(1 row)
postgres=> SELECT oracle_diag();
oracle_diag
--------------------------------------------------------------
oracle_fdw 2.6.0, PostgreSQL 16.3, Oracle client 21.10.0.0.0
(1 row)
外部サーバーとユーザマッピングの作成
postgres=> CREATE SERVER ora_server FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '192.168.1.25:1521/PDBTEST.testsub.testvpc.oraclevcn.com');
CREATE SERVER
postgres=> \des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
------------+----------+----------------------+-------------------+------+---------+----------------------------------------------------------------------+-------------
ora_server | postgres | oracle_fdw | | | | (dbserver '192.168.1.25:1521/PDBTEST.testsub.testvpc.oraclevcn.com') |
(1 row)
postgres=> CREATE USER MAPPING FOR postgres SERVER ora_server OPTIONS (user 'HR', password '********');
CREATE USER MAPPING
postgres=> \deu+
List of user mappings
Server | User name | FDW options
------------+-----------+------------------------------------------
ora_server | postgres | ("user" 'HR', password '********')
(1 row)
外部テーブルの作成
postgres=> CREATE FOREIGN TABLE f_ora_tbl(
postgres(> id integer OPTIONS (key 'true'),
postgres(> name varchar(128),
postgres(> update_time timestamp)
postgres-> SERVER ora_server OPTIONS (schema 'HR',table 'ORA_TBL');
CREATE FOREIGN TABLE
postgres=> \det+
List of foreign tables
Schema | Table | Server | FDW options | Description
--------+-----------+------------+----------------------------------+-------------
public | f_ora_tbl | ora_server | (schema 'HR', "table" 'ORA_TBL') |
(1 row)
postgres=> SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'f_ora_tbl' order by 1;
column_name | data_type
-------------+-----------------------------
id | integer
name | character varying
update_time | timestamp without time zone
(3 rows)
Oracle Database側のORA_TBL定義
SQL> desc ora_tbl
Name Null? Type
--------------- -------- ----------------
ID NOT NULL NUMBER
NAME VARCHAR2(128)
UPDATE_TIME TIMESTAMP(6)
MViewの作成
まずは現在の f_ora_tbl(Oracle側:ORA_TBL) の状態を確認します。
postgres=> select * from f_ora_tbl order by id;
id | name | update_time
----+----------+----------------------------
1 | asahide | 2024-09-28 11:12:42.26059
2 | hogehoge | 2024-09-28 11:26:01.636484
3 | mvtest | 2024-09-29 00:48:34.099975
4 | oratest | 2024-09-29 09:52:06
(4 rows)
f_ora_mvという名前のMViewをf_ora_tblという外部テーブルから作成します。
MView作成
postgres=> CREATE MATERIALIZED VIEW f_ora_mv as select * from f_ora_tbl;
SELECT 4
postgres=> commit;
WARNING: there is no transaction in progress
COMMIT
postgres=> select * from f_ora_mv order by id;
id | name | update_time
----+----------+----------------------------
1 | asahide | 2024-09-28 11:12:42.26059
2 | hogehoge | 2024-09-28 11:26:01.636484
3 | mvtest | 2024-09-29 00:48:34.099975
4 | oratest | 2024-09-29 09:52:06
(4 rows)
f_ora_tbl へ新しいデータを挿入します。
外部テーブルへのデータ挿入と確認
postgres=> insert into f_ora_tbl values(5,'mvtest2',current_timestamp);
INSERT 0 1
postgres=> commit;
WARNING: there is no transaction in progress
COMMIT
postgres=> select * from f_ora_tbl order by id;
id | name | update_time
----+----------+----------------------------
1 | asahide | 2024-09-28 11:12:42.26059
2 | hogehoge | 2024-09-28 11:26:01.636484
3 | mvtest | 2024-09-29 00:48:34.099975
4 | oratest | 2024-09-29 09:52:06
5 | mvtest2 | 2024-09-29 01:41:47.675034
(5 rows)
MViewの状態を確認します、MView側ではデータは反映されてません。
MViewの状態確認
postgres=> select * from f_ora_mv order by id;
id | name | update_time
----+----------+----------------------------
1 | asahide | 2024-09-28 11:12:42.26059
2 | hogehoge | 2024-09-28 11:26:01.636484
3 | mvtest | 2024-09-29 00:48:34.099975
4 | oratest | 2024-09-29 09:52:06
(4 rows)
リフレッシュを行うとデータが反映されます。
なお、現時点では pg_ivmはサポートされていないため、完全リフレッシュ(全データの更新)のみとなります。
Refreshの実行
postgres=> REFRESH MATERIALIZED VIEW f_ora_mv;
REFRESH MATERIALIZED VIEW
postgres=> select * from f_ora_mv order by 1;
id | name | update_time
----+----------+----------------------------
1 | asahide | 2024-09-28 11:12:42.26059
2 | hogehoge | 2024-09-28 11:26:01.636484
3 | mvtest | 2024-09-29 00:48:34.099975
4 | oratest | 2024-09-29 09:52:06
5 | mvtest2 | 2024-09-29 01:41:47.675034
(5 rows)
無事MView環境を構築することができました。
まとめ
集計データを利用したい場合に利用されるMViewですが、クロスクラウド接続でも利用できることが確認できました。
Oracle Databaseに入っているデータを AlloyDB から参照したい、という場合に利用しやすくなると想定されますね。
おまけ
今回は AlloyDB から Oracle Databaseにデータを入れて、それを確認する、という形でした。
ユースケースとしては Oracle Database側でデータ更新を行ってそれをAlloyDB側でリフレッシュして利用する、という形の方がスタンダードになると思いますので、その形も確認してみました。
Oracle Database側でのデータインサート
SQL> select * from ora_tbl order by id;
ID NAME UPDATE_TIME
---------- ---------- ------------------------------
1 asahide 28-SEP-24 11.12.42.260590 AM
2 hogehoge 28-SEP-24 11.26.01.636484 AM
3 mvtest 29-SEP-24 12.48.34.099975 AM
4 oratest 29-SEP-24 09.52.06.000000 AM
5 mvtest2 29-SEP-24 01.41.47.675034 AM
SQL> insert into ora_tbl values (6, 'oratest2', sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from ora_tbl order by id;
ID NAME UPDATE_TIME
---------- ---------- ------------------------------
1 asahide 28-SEP-24 11.12.42.260590 AM
2 hogehoge 28-SEP-24 11.26.01.636484 AM
3 mvtest 29-SEP-24 12.48.34.099975 AM
4 oratest 29-SEP-24 09.52.06.000000 AM
5 mvtest2 29-SEP-24 01.41.47.675034 AM
6 oratest2 29-SEP-24 10.43.50.000000 AM
6 rows selected.
AlloyDB側でリフレッシュを行うとデータが反映されていることが確認できますね。
タイムゾーンとかがずれているため時刻がずれてしまってますが。。
AlloyDB側でのリフレッシュ確認
postgres=> select * from f_ora_mv order by id;
id | name | update_time
----+----------+----------------------------
1 | asahide | 2024-09-28 11:12:42.26059
2 | hogehoge | 2024-09-28 11:26:01.636484
3 | mvtest | 2024-09-29 00:48:34.099975
4 | oratest | 2024-09-29 09:52:06
5 | mvtest2 | 2024-09-29 01:41:47.675034
(5 rows)
postgres=> REFRESH MATERIALIZED VIEW f_ora_mv;
REFRESH MATERIALIZED VIEW
postgres=> select * from f_ora_mv order by id;
id | name | update_time
----+----------+----------------------------
1 | asahide | 2024-09-28 11:12:42.26059
2 | hogehoge | 2024-09-28 11:26:01.636484
3 | mvtest | 2024-09-29 00:48:34.099975
4 | oratest | 2024-09-29 09:52:06
5 | mvtest2 | 2024-09-29 01:41:47.675034
6 | oratest2 | 2024-09-29 10:43:50
(6 rows)