postgres_fdwおよびdblink拡張を使用したPostgreSQLOCI Database with PostgreSQLを使用したOCI Databaseでのデータベース間アクセスの有効化

はじめに

Oracle Cloud Infrastructure Database with PostgreSQL (OCI Database with PostgreSQL)は、postgres_fdwやdblinkなどの拡張機能を使用したデータベース間接続をサポートしています。これらの拡張により、PostgreSQLデータベース間のシームレスなアクセスおよびデータ共有が可能になり、分析、フェデレーテッド問合せまたはレガシー統合シナリオに役立ちます。

dblinkとは?

DBlinkは、リモートのPostgreSQLデータベースでSQL文を直接実行できるPostgreSQL拡張機能です。結果セットを行として返すか、リモート・サーバーでコマンドを実行します。

postgres_fdwとは何ですか。

postgres_fdw (外部データ・ラッパー)は、データベースがリモートPostgreSQLサーバーの表に接続し、ローカル表であるかのように問合せできるようにするPostgreSQL拡張機能です。dblinkとは異なり、リモート表をローカル・オブジェクトとしてマップし、より手続き的な制御を提供します。

このチュートリアルでは、OCI Database with PostgreSQLインスタンスでのpostgres_fdwおよびdblink拡張の設定と、それぞれの例について説明します。

目的

To enable secure and reliable network communication between two OCI Database with PostgreSQL database systems located in different Virtual Cloud Networks (VCNs) and different regions.この設定により、データベース接続がVCNの境界を越えてシームレスに動作するようになります。これには、プライベート・ビューを使用したDNS解決のルーティングおよび設定のためのローカル・ピアリング・ゲートウェイ(LPG)の構成が含まれます。目標は、postgres_fdwやdblinkなどのデータベース拡張に対するVCN/リージョン間の接続をサポートすることです。

前提条件

  1. OCIコンソールからの拡張機能の有効化。

    1. dblink拡張子または postgres_fdw拡張子のいずれかに対して有効にする構成ファイルを作成します。

      イメージ

    2. 構成をデータベース・システムに適用します。

      イメージ

  2. データベース・レベルでの拡張の有効化。

    OCIコンソールで拡張を有効にした後、データベースに接続し、次のSQLコマンドを実行します。

     CREATE EXTENSION postgres_fdw;

 CREATE EXTENSION dblink;

タスク2: 異なるVCNs内のデータベース・システム間のネットワーク接続の構成

異なるVCNsにある2つのOCI Database with PostgreSQLデータベース・システム間の通信を有効にするには、次のステップに従います。

  1. ローカル・ピアリング・ゲートウェイ(LPG)の作成。

    各VCNにLPGを作成して、それらの間のピアリング接続を確立します。

  2. ルート表の構成。

    1. VCNごとに、LPGに関連付けられたルート表を更新して、ルート・ルールを含めます。

    2. 宛先CIDRブロックを他のターゲットVCNのアドレス範囲に設定し、対応するLPGとしてネクスト・ホップを指定します。

  3. プライベート・ビューを使用したDNS解決の設定。

    1. OCIコンソールに移動し、プライベート・ビューを検索して、両方のVCNsのDNS構成にアクセスします。

    2. 各VCNのプライベート・ビューを開き、ゾーン・エントリを作成します。

  4. 各データベース・システムのゾーン・レコードの作成。

    各VCNのプライベート・ビューで、他のVCNのデータベース・システムの完全修飾ドメイン名(FQDN)を表すゾーンを追加します。

  5. DNSレコードの追加。

    1. 新しいゾーン・エントリの横にある3つのドット・アイコンをクリックするか、「FQDN」リンクをクリックします。

    2. 「レコードの管理」を選択し、「レコードの追加」をクリックします。

    3. 「タイプA (IPv4アドレス)」を選択し、「TTL」を「500」に設定し、反対側のVCNでターゲット・データベース・システムのプライマリ・エンドポイントIPアドレスを入力します。

    4. 「レコードの追加」をクリックし、「変更の公開」をクリックします。

タスク3: 様々なVCNsおよび異なるリージョンのデータベース・システム間のネットワーク接続の構成

To enable communication between two OCI Database with PostgreSQL database systems located in different VCNs and different regions.

  1. Dynamic Routing Gateways (DRG)の作成。

    各リージョンにDRGを作成し、それぞれのVCNにアタッチします。

  2. ルート表の構成。

    1. VCNごとに、DRGに関連付けられたルート表を更新します。

    2. 他のVCNの宛先CIDRブロックを含むルート・ルールを追加し、アタッチされたDRGとしてネクスト・ホップを指定します。

  3. プライベート・ビューを使用したDNS解決の設定。

    1. OCIコンソールに移動し、プライベート・ビューを検索して、各VCNのDNS設定にアクセスします。

    2. 各VCNのプライベート・ビューを開き、ゾーン・エントリを作成します。

  4. 各データベース・システムのゾーン・レコードの作成。

    各VCNのプライベート・ビューで、他のVCNのデータベース・システムのFQDNを表すゾーンを追加します。

  5. DNSレコードの追加。

    1. 新しいゾーン・エントリの横にある3つのドット・アイコンをクリックするか、「FQDN」リンクをクリックします。

    2. 「レコードの管理」を選択し、「レコードの追加」をクリックします。

    3. 「タイプA (IPv4アドレス)」を選択し、「TTL」を「500」に設定して、反対のデータベース・システムの「プライマリ・エンドポイントIPアドレス」を入力します。

    4. 「レコードの追加」をクリックし、「変更の公開」をクリックします。

タスク4: postgres_fdw拡張を例で構成

  1. 外部データ・ラッパー(FDW)サーバーを作成します。

    CREATE SERVERコマンドは、postgres_fdwラッパーを使用して、fdw_servという名前の外部サーバーを定義します。ソースPostgreSQLデータベースのホスト、データベース名、ポートなどの接続詳細を指定します。これは、ターゲットPostgreSQLインスタンス内のソース・データにアクセスするための論理参照として機能します。

     CREATE SERVER <fdw_servername> FOREIGN DATA WRAPPER <fdw_servername> OPTIONS (host 'primary.xxxxxx.FQDN_Remote_source_DBSystem.... oci.oraclecloud.com', dbname 'remote_source_dbname', port '5432');

  2. ユーザーのマップ。

    CREATE USER MAPPINGコマンドは、ターゲットのPostgreSQLユーザー(arvindya)をソース・データベース資格証明に関連付けます。これにより、ターゲット・サーバーが定義された外部サーバーに接続するときにターゲット認証が可能になります。これにより、実行された問合せターゲットがソース・サーバーで適切に認可されます。

     CREATE USER MAPPING FOR <user_name> SERVER <fdw_servername> OPTIONS (user <user_name>, password <'user_password'>);

  3. スキーマ・ソースのインポート。

    IMPORT FOREIGN SCHEMAコマンドは、ソース・サーバーのパブリック・スキーマからすべての表を外部表としてターゲット・データベースにインポートします。これらの表はローカルに表示されますが、ソース・データベースからリアルタイムでデータをフェッチします。

     IMPORT FOREIGN SCHEMA <schema_name> FROM SERVER <fdw_server_name> INTO public;

次に例を示します。

-- Create extension
CREATE EXTENSION postgres_fdw;

-- Create FDW Server
CREATE SERVER fdw_serv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
    host 'primary.xxxxxx.Remote Source DBSystem FQDN.....xxxxx.oci.oraclecloud.com',
    dbname 'source',
    port '5432'
);

-- User Mapping
CREATE USER MAPPING FOR arvindya SERVER fdw_serv OPTIONS (
    user '<user_name>',
    password '<user_password>'
);

-- Import Schema from source
IMPORT FOREIGN SCHEMA public FROM SERVER fdw_serv INTO public;

-- Validate Data
SELECT * FROM emp;

dblink_connectファンクションは、指定された接続文字列を使用して、ターゲットPostgreSQLデータベースからソースPostgreSQLデータベースへの接続を確立します。これには、データベース名、ホスト、ポート、ユーザー名、パスワードなどのパラメータが含まれます。接続は一意の名前(myconn)で識別され、後続のdblink操作に使用されます。接続に成功すると、リモート問合せの準備状況を示す「OK」が返されます。

SELECT dblink_connect ('Conn_name', 'dbname=<Remote_source_DBName> port=5432 host=primary.xxxxxx..Remote_source_DbSystem FQDN..xxxx.oci.oraclecloud.com user=<user_name> password=<'user_password'>);

次に例を示します。

CREATE DATABASE target;
\c target
CREATE EXTENSION dblink;

-- Establish Connection
SELECT dblink_connect ('myconn', 'dbname=source port=5432 host=primary.xxxxxx..Remote_source_DbSystem FQDN..xxxx.oci.oraclecloud.com user=<user_name> password=<user_password>');

-- Select from Source
SELECT * FROM dblink('myconn', 'SELECT * FROM testing') AS t(a int);

-- Execute SQL remotely
SELECT * FROM dblink_exec('myconn', 'create table dblinking_table (id int)');
SELECT * FROM dblink_exec('myconn', 'insert into dblinking_table values(generate_series(1,10))');
SELECT * FROM dblink_exec('myconn', 'delete from dblinking_table where id <= 5');

-- Final Validation
SELECT * FROM dblink('myconn', 'SELECT * FROM dblinking_table') AS t(a int);

確認

その他の学習リソース

docs.oracle.com/learnで他のラボを確認するか、Oracle Learning YouTubeチャネルで無料のラーニング・コンテンツにアクセスしてください。また、education.oracle.com/learning-explorerにアクセスして、Oracle Learning Explorerになります。

製品ドキュメントについては、Oracle Help Centerを参照してください。