postgres_fdw ユースケースと大まかなステップ dblink を使用して接続を作成する

リモート PostgreSQL データベースとの Aurora PostgreSQL 互換統合

このセクションでは、 postgres_fdw (外部データラッパー) 拡張機能または dblink機能を使用した Amazon Aurora PostgreSQL 互換エディションとリモート PostgreSQL データベースの統合について説明します。postgres_fdw モジュールは、リモート PostgreSQL ベースのデータベースを操作するためのフェデレーティッドクエリ機能を提供します。リモートデータベースは、Amazon EC2 またはオンプレミスで管理または自己管理できます。postgres_fdw 拡張機能は、現在サポートされているすべてのバージョンの Amazon Relational Database Service (Amazon RDS) for PostgreSQL および Aurora PostgreSQL 互換で使用できます。

postgres_fdw 拡張機能を使用すると、リモート PostgreSQL データベースからローカルテーブルであるかのようにデータにアクセスしてクエリを実行できます。postgres_fdw 拡張機能では、以下もサポートされています。

異なるバージョンを実行している外部 PostgreSQL サーバーからデータにアクセスするためのクロスバージョン互換性。
トランザクション管理。ローカルおよび外部の PostgreSQL サーバー間でオペレーションを実行するときに、データの整合性と整合性を確保するのに役立ちます。
分散トランザクションは、複数の外部 PostgreSQL サーバーでオペレーションを実行するときに、アトミック性 (ACID トランザクションのプロパティ) と分離を保証します。これにより、トランザクション内のすべてのオペレーションがコミットされるか、コミットされないかのいずれかが保証され、データの整合性と整合性が維持されます。

dblink モジュールはリモート PostgreSQL データベースを操作する方法を提供しますが、分散トランザクションやその他の高度な機能をサポートしていません。より高度な機能が必要な場合は、代わりに postgres_fdw拡張機能の使用を検討してください。postgres_fdw 拡張機能は、より多くの統合および最適化機能を提供します。

postgres_fdw ユースケースと大まかなステップ

Aurora PostgreSQL 互換のpostgres_fdw拡張機能の使用では、次のユースケースとシナリオがサポートされています。

フェデレーティッドクエリとデータ統合 - 単一の Aurora PostgreSQL 互換インスタンス内の複数の PostgreSQL データベースからのデータのクエリと結合
読み取りワークロードのオフロード - 外部 PostgreSQL サーバーのリードレプリカへの接続、読み取り負荷の高いワークロードのオフロード、クエリパフォーマンスの向上
クロスデータベースオペレーション - 複数の PostgreSQL データベースで INSERT、DELETE、UPDATE、 COPYオペレーションを実行し、クロスデータベースのデータ操作とメンテナンスタスクを可能にします。

を設定するにはpostgres_fdw、以下の大まかなステップを使用します。

PostgreSQL クライアントを使用して Aurora PostgreSQL 互換クラスターに接続し、postgres_fdw拡張機能を作成します。
```
CREATE EXTENSION postgres_fdw;
```
この拡張機能は、リモート PostgreSQL データベースに接続する機能を提供します。
CREATE SERVER コマンドmy_fdw_targetを使用して、という名前の外部サーバーを作成します。このサーバーは、接続先のリモート PostgreSQL データベースを表します。このサーバーのオプションとして、データベース名、ホスト名、SSL モードを指定します。
Aurora PostgreSQL 互換がリモート PostgreSQL データベースに接続できるように、必要なセキュリティグループとネットワーク設定が設定されていることを確認します。

リモートデータベースがオンプレミスでホストされている場合は、仮想プライベートネットワーク (VPN) または AWS Direct Connect 接続の設定が必要になる場合があります。

次のコマンドを実行してください。
```
CREATE SERVER my_fdw_target Foreign Data Wrapper postgres_fdw OPTIONS (DBNAME 'postgres', HOST 'SOURCE_HOSTNAME', SSLMODE 'require');
```
my_fdw_target サーバー上のユーザーのdbuserユーザーマッピングを作成します。このマッピングは、ローカル Aurora PostgreSQL 互換インスタンスのdbuserユーザーとパスワードを、リモートデータベースの対応するユーザーと関連付けます。
```
CREATE USER MAPPING FOR dbuser SERVER my_fdw_target OPTIONS (user 'DBUSER', password 'PASSWORD');
```
このステップは、リモートデータベースを認証してアクセスを提供するために必要です。
以前に設定したmy_fdw_targetサーバーとユーザーマッピングcustomer_fdwを使用して、という名前の外部テーブルを作成します。
```
CREATE FOREIGN TABLE customer_fdw( id int, name varchar, emailid varchar, projectname varchar, contactnumber bigint) server my_fdw_target OPTIONS( TABLE_NAME 'customers');
```
customer_fdw テーブルは、my_fdw_targetサーバーによって指定されたリモートデータベースのcustomersテーブルにマッピングされます。外部テーブルはリモートテーブルと同じ構造であるため、ローカルテーブルであるかのようにリモートデータを操作できます。

、、 SELECTクエリなどINSERTUPDATE、customer_fdw外部テーブルに対してさまざまなデータ操作オペレーションを実行できます。このスクリプトは、新しい行の挿入と既存の行の更新、レコードの削除、customer_fdw外部テーブルを介したリモートcustomersテーブルのテーブルの切り捨てを示しています。


INSERT INTO customer_fdw values ( 1, 'Test1', 'Test1@email.com', 'LMS1', '888888888');
INSERT INTO customer_fdw values ( 2, 'Test2', 'Test2@email.com', 'LMS2', '999999999');
INSERT INTO customer_fdw values ( 3, 'Test3', 'Test3@email.com', 'LMS3', '111111111');
UPDATE customer_fdw set contactnumber = '123456789' where id = 2;
DELETE FROM customer_fdw where id = 1;
TRUNCATE TABLE customer_fdw;

SQL クエリプランを検証するには、 EXPLAINステートメントを使用して、customer_fdwテーブルのクエリのSELECTクエリプランを分析します。
```
EXPLAIN select * from customer_fdw where id =1;
```
これは、クエリの実行方法と最適化方法を理解するのに役立ちます。EXPLAIN ステートメントの使用の詳細については、 AWS 「規範ガイダンス」のPostgreSQL クエリパフォーマンスの最適化」を参照してください。
リモートデータベースからローカルスキーマに複数のテーブルをインポートするには、 IMPORT FOREIGN SCHEMA コマンドを使用します。
```
CREATE SCHEMA public_fdw;
IMPORT FOREIGN SCHEMA public LIMIT TO (employees, departments)
    FROM SERVER my_fdw_target INTO public_fdw;
```
これにより、public_fdwスキーマ内の指定されたテーブルのローカル外部テーブルが作成されます。この例では、特定のテーブルは従業員と部門です。
特定のデータベースユーザーに FDW および関連する外部サーバーにアクセスして使用するために必要なアクセス許可を付与するには、次のコマンドを実行します。
```
GRANT USAGE ON FOREIGN SERVER my_fdw_target TO targetdbuser;
GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO targetdbuser;
```
このステップは、複数のユーザーが外部データラッパーによって容易になる外部テーブルにアクセスする必要がある場合に役立ちます。

外部テーブルを使用する場合は、次の制限に注意してください。

リモートソースからデータにアクセスすると、ネットワークレイテンシーによるデータ転送コストとパフォーマンスのオーバーヘッドが発生する可能性があります。パフォーマンスの問題は、Aurora PostgreSQL 互換インスタンスとリモートデータソース間の大幅なデータ転送を必要とする大規模なデータセットやクエリで顕著になる可能性があります。
ウィンドウ関数などの機能を含む複雑なクエリでは、再帰クエリが期待どおりに機能しないか、サポートされない可能性があります。
現在、パスワードの暗号化はサポートされていません。権限のあるユーザーのみが FDWs にアクセスしてリモートデータベースからデータを取得できるようにするコントロールを実装します。

次のテーブル作成スクリプトの試行に示すように、プライマリキー制約を外部テーブルで定義することはできません。


CREATE FOREIGN TABLE customer_fdw2( id int primary key, name varchar, emailid varchar, projectname varchar, contactnumber bigint) server my_fdw_target OPTIONS( TABLE_NAME 'customers');
Primary keys cannot be defined on Foreign table

INSERT ステートメントの ON CONFLICT句は、次の例に示すように、外部テーブルではサポートされていません。


INSERT INTO customer_fdw (id, name, emailid, projectname, contactnumber) VALUES
(1, 'test1', 'test@email.com', 'LMS', 11111111 ),
(3, 'test3', 'test3@email.com', 'LMS', 22222222 )
ON CONFLICT (id) DO UPDATE 
SET name = EXCLUDED.name;
On Conflict option doesnot work.

クリーンアップ

postgres_fdw 拡張機能、my_fdw_targetサーバー、ユーザーマッピング、外部テーブルの削除など、作成されたオブジェクトをクリーンアップするには、次のコマンドを実行します。


DROP FOREIGN TABLE customer_fdw;
DROP USER MAPPING for postgres;
DROP SERVER  my_fdw_target;
DROP EXTENSION postgres_fdw cascade;

dblink を使用して接続を作成する

dblink モジュール関数は、接続を作成し、リモート PostgreSQL データベースで SQL ステートメントを実行する代替方法を提供します。このdblinkソリューションは、リモートデータベースで 1 回限りのクエリまたはオペレーションを実行するための、よりシンプルで柔軟な方法です。大規模なデータ統合、パフォーマンスの最適化、データ整合性要件を含むより複雑なシナリオでは、を使用することをお勧めしますpostgres_fdw。

の使用dblinkには、以下の大まかなステップが含まれます。

dblink 拡張機能を作成します。
```
CREATE EXTENSION dblink;
```
この拡張機能は、リモート PostgreSQL データベースに接続する機能を提供します。
リモート PostgreSQL データベースへの接続を確立するには、 dblink_connect関数を使用します。
```
SELECT dblink_connect('myconn', 'dbname=postgres port=5432 host=SOURCE_HOSTNAME user=postgres password=postgres');
```
リモート PostgreSQL データベースに接続したら、 dblink関数を使用してリモートデータベースで SQL ステートメントを実行します。
```
SELECT  FROM dblink('myconn', 'SELECT col1, col2 FROM remote_table') AS remote_data(col1 int, col2 text);
```
このクエリは、 myconn接続を使用してリモートデータベースで SELECT * FROM remote_tableステートメントを実行します。クエリは、列 col1とを含むローカル一時テーブルに結果を取得しますcol2。
dblink_exec 関数を使用して、リモートデータベースDELETEで INSERT、UPDATE、などの非クエリステートメントを実行することもできます。
```
SELECT dblink_exec('myconn', 'INSERT INTO remote_table VALUES (1, ''value'')');
```

ブラウザで JavaScript が無効になっているか、使用できません。

AWS ドキュメントを使用するには、JavaScript を有効にする必要があります。手順については、使用するブラウザのヘルプページを参照してください。

ドキュメントの表記規則

Aurora PostgreSQL 互換の統合

異種データベース