背景・目的
PostgreSQLにpostgres_fdwという機能があることを知りました。
簡単に整理します。
まとめ
下記に特徴をまとめます。
| 特徴 | 説明 |
|---|---|
| postgres_fdwとは | 外部のPostgreSQLサーバーに保存されているデータにアクセスするための外部データラッパー このモジュールが提供する機能は、従来の dblink モジュールの機能と大幅に重複している postgres_fdw はリモートテーブルへのアクセスにおいて、より透過的で標準準拠の構文を提供し、多くの場合、より優れたパフォーマンスを実現する |
| 作成手順 | 1.CREATE EXTENSION を使用して postgres_fdw 拡張機能をインストール 2. CREATE SERVER を使用して、接続する各リモートデータベースを表す外部サーバーオブジェクトを作成 3. CREATE USER MAPPING を使用して、各外部サーバーへのアクセスを許可するデータベースユーザーごとにユーザーマッピングを作成 4. CREATE FOREIGN TABLE または IMPORT FOREIGN SCHEMA を使用して、アクセスする各リモートテーブルごとに外部テーブルを作成 |
| できること | ・外部テーブルから SELECT を実行するだけで、その基になるリモートテーブルに格納されているデータにアクセスできるようになる ・INSERT、UPDATE、DELETE、COPY、または TRUNCATE を使用してリモートテーブルを変更することも |
| 制約事項 | ・SELECT、UPDATE、DELETE、または TRUNCATE で指定された ONLY オプションは、リモートテーブルへのアクセスや変更には影響しない ・postgres_fdw は現在、ON CONFLICT DO UPDATE 句を含む INSERT 文をサポートしていない |
| 注意点 | ・postgres_fdw は現在、必要に応じてデータ型の変換を比較的柔軟に実行するが、リモートサーバーがローカルサーバーとは異なるクエリ条件を解釈するため、型または照合順序が一致しない場合、予期せぬ意味上の異常が発生する可能性がある ・外部テーブルは、その基となるリモートテーブルよりも少ない列数、または異なる列順序で宣言できることに注意すること |
概要
下記を基に整理します。
- postgres_fdwは、外部のPostgreSQLサーバーに保存されているデータにアクセスするための外部データラッパー
- このモジュールが提供する機能は、従来の dblink モジュールの機能と大幅に重複している
- postgres_fdw はリモートテーブルへのアクセスにおいて、より透過的で標準準拠の構文を提供し、多くの場合、より優れたパフォーマンスを実現する
以下の準備が必要
- CREATE EXTENSION を使用して postgres_fdw 拡張機能をインストール
- CREATE SERVER を使用して、接続する各リモートデータベースを表す外部サーバーオブジェクトを作成
- ユーザー名とパスワード以外の接続情報は、サーバーオブジェクトのオプションとして指定する
- CREATE USER MAPPING を使用して、各外部サーバーへのアクセスを許可するデータベースユーザーごとにユーザーマッピングを作成
- ユーザーマッピングのユーザー名とパスワードのオプションとして使用するリモートユーザー名とパスワードを指定する
- CREATE FOREIGN TABLE または IMPORT FOREIGN SCHEMA を使用して、アクセスする各リモートテーブルごとに外部テーブルを作成
- 外部テーブルの列は、参照先のリモートテーブルと一致している必要がある
- ただし、外部テーブルオブジェクトのオプションとして正しいリモート名を指定すれば、リモートテーブルとは異なるテーブル名や列名を使用することも可能
操作と制約事項
- 外部テーブルから SELECT を実行するだけで、その基になるリモートテーブルに格納されているデータにアクセスできるようになる
- また、INSERT、UPDATE、DELETE、COPY、または TRUNCATE を使用してリモートテーブルを変更することもできます。(ユーザーマッピングで指定したリモートユーザーには、これらの操作を実行する権限が必要)
- SELECT、UPDATE、DELETE、または TRUNCATE で指定された ONLY オプションは、リモートテーブルへのアクセスや変更には影響しない
- postgres_fdw は現在、ON CONFLICT DO UPDATE 句を含む INSERT 文をサポートしていない
- ただし、一意のインデックス推論指定を省略すれば、ON CONFLICT DO NOTHING 句はサポートされる
- postgres_fdw はパーティションテーブルで実行される UPDATE 文による行の移動をサポートしていますが、移動された行を挿入するために選択されたリモートパーティションが、同じコマンド内の他の場所で更新される UPDATE ターゲットパーティションでもある場合は、現在のところ処理できない
- 一般的に、外部テーブルの列は、リモートテーブルの参照先列と全く同じデータ型、および該当する場合は照合順序で宣言することが推奨される
- postgres_fdw は現在、必要に応じてデータ型の変換を比較的柔軟に実行するが、リモートサーバーがローカルサーバーとは異なるクエリ条件を解釈するため、型または照合順序が一致しない場合、予期せぬ意味上の異常が発生する可能性がある
- 外部テーブルは、その基となるリモートテーブルよりも少ない列数、または異なる列順序で宣言できることに注意すること
- リモートテーブルとの列の一致は、位置ではなく名前で行われる
Foreign data wrappers
下記に外部データラッパー（FDW）が載っている。
これらのラッパーのほとんどはPostgreSQL Global Development Group（PGDG）によって公式にサポートされておらず、一部のプロジェクトはまだベータ版であることに注意が必要。
考察
次回は、実際に利用してみます。
参考