クロスデータベース操作のための PostgreSQL 拡張 - ApsaraDB RDS

dblink や postgres_fdw などの PostgreSQL 拡張を使用して、テーブルに対するクロスデータベース操作を実行できます。

背景情報

Alibaba Cloud は、ApsaraDB RDS for PostgreSQL クラウドディスクインスタンスで dblink および postgres_fdw 拡張を有効にしています。これらの拡張は、セルフマネージド PostgreSQL データベースを含む、同一 VPC 内のインスタンス間のクロスデータベース操作をサポートします。購入するには、購入ページにアクセスしてください。

注意事項

クロスデータベース操作に dblink および postgres_fdw を使用する際は、以下の点に留意してください。

同一 VPC 内の ECS および ApsaraDB RDS for PostgreSQL インスタンスは、クロスデータベース操作を直接実行できます。
セルフマネージド PostgreSQL インスタンスは、oracle_fdw または mysql_fdw を使用して、VPC 外の Oracle インスタンスまたは MySQL インスタンスに接続できます。
同一インスタンス内の異なるデータベースに接続する場合：
- IPv6 対応インスタンスでの接続失敗を防ぐため、ホストは localhost ではなく 127.0.0.1 に設定してください。
- ポートを明示的に設定しないでください。ポート番号はメンテナンスや仕様変更時に変更される可能性があり、接続失敗につながる場合があります。ポートを省略すると、データベースは自動的に現在のポートを使用するため、接続が有効なままになります。
- ポートを明示的に設定する必要がある場合は、データベースに接続して SHOW PORT; SQL ステートメントを実行し、設定前に現在のポート番号を確認してください。
宛先データベースの IP アドレスホワイトリストに、ApsaraDB RDS for PostgreSQL インスタンスの VPC CIDR ブロック (たとえば 172.XX.XX.XX/16) を追加します。

説明
ApsaraDB RDS for PostgreSQL インスタンスの [データベース接続] ページで VPC CIDR ブロックを確認できます。

dblink

dblink 拡張を作成します。
```
create extension dblink;
```

dblink 接続を作成します。

postgres=> select dblink_connect('<connection_name>', 'host=<internal_endpoint_of_the_destination_instance> port=<listening_port_of_the_destination_instance> user=<destination_database_username> password=<password> dbname=<destination_database_name>');

postgres=> SELECT * FROM dblink('<connection_name>', '<sql_command>') as <table_name>(<column_name> <column_type>);

例

postgres=> select dblink_connect('a', 'host=pgm-bpxxxxx.pg.rds.aliyuncs.com port=3433 user=testuser2 password=passwd1234 dbname=postgres'); 


postgres=> select * from dblink('a','select * from products') as T(id int,name text,price numeric);  // 宛先データベースのテーブル

詳細については、「dblink ドキュメント」をご参照ください。

postgres_fdw

新しいデータベースを作成します。

postgres=> create database <database_name>;  // データベースを作成します。

postgres=> \c <database_name>  // 新しいデータベースに切り替えます。

例

postgres=> create database db1;  
CREATE DATABASE  

postgres=> \c db1

postgres_fdw 拡張を作成します。
```
db1=> create extension postgres_fdw;
```

宛先データベースの外部サーバーを作成します。

db1=> CREATE SERVER <server_name>                                                               
        FOREIGN DATA WRAPPER postgres_fdw  
        OPTIONS (host '<internal_endpoint_of_the_destination_instance>', port '<listening_port_of_the_destination_instance>', dbname '<destination_database_name>');  

db1=> CREATE USER MAPPING FOR <local_database_username>      
        SERVER <server_name> 
        OPTIONS (user '<destination_database_username>', password '<destination_database_password>');

例

db1=> CREATE SERVER foreign_server1                                                              
        FOREIGN DATA WRAPPER postgres_fdw  
        OPTIONS (host 'pgm-bpxxxxx.pg.rds.aliyuncs.com', port '3433', dbname 'postgres');  
CREATE SERVER  

db1=> CREATE USER MAPPING FOR testuser      
        SERVER foreign_server1  
        OPTIONS (user 'testuser2', password 'passwd1234');  
CREATE USER MAPPING

外部テーブルをインポートします。

db1=> import foreign schema public from server foreign_server1 into <schema_name>;  // 外部テーブルをインポートします。

db1=> select * from <schema_name>.<table_name>      // 宛先データベースのテーブル

例

db1=> import foreign schema public from server foreign_server1 into ft;  
IMPORT FOREIGN SCHEMA  

db1=> select * from ft.products;

詳細については、「postgres_fdw ドキュメント」をご参照ください。

よくある質問

Q: postgres_fdw を使用してパーティションテーブルをインポートするにはどうすればよいですか?

A: 宛先インスタンスでは、親パーティションテーブルの名前のみをインポートする必要があります。個々のパーティションをインポートする必要はありません。以下の例では、レンジパーティションテーブルを使用しています。

-- ソースインスタンス: ソースデータベース
CREATE TABLE sales (id int, p_name text, amount int, sale_date date) PARTITION BY RANGE (sale_date);
CREATE TABLE sales_2022_Q1 PARTITION OF sales FOR VALUES FROM ('2022-01-01') TO ('2022-03-31');
CREATE TABLE sales_2022_Q2 PARTITION OF sales FOR VALUES FROM ('2022-04-01') TO ('2022-06-30');
CREATE TABLE sales_2022_Q3 PARTITION OF sales FOR VALUES FROM ('2022-07-01') TO ('2022-09-30');
CREATE TABLE sales_2022_Q4 PARTITION OF sales FOR VALUES FROM ('2022-10-01') TO ('2022-12-31');

INSERT INTO sales VALUES (1,'prod_A',100,'2022-02-02');
INSERT INTO sales VALUES (2,'prod_B', 5,'2022-05-02');
INSERT INTO sales VALUES (3,'prod_C', 5,'2022-08-02');
INSERT INTO sales VALUES (4,'prod_D', 5,'2022-11-02');

-- 宛先インスタンスで実行します。親パーティションテーブルのみをインポートし、そのパーティションはインポートしません。
import FOREIGN SCHEMA public limit to (sales) from server pg_fdw_server into public;
select * from sales;

以下の結果が返されます。

输出结果

Q: pg_net と postgres_fdw の違いは何ですか?

A: ApsaraDB RDS for PostgreSQL は、pg_net および postgres_fdw 拡張の両方をサポートしています。これらは異なるプロトコルを使用し、ユースケースが異なります。pg_net 拡張は、HTTP/HTTPS 経由でリクエストを送信して RESTful API または Webhook を呼び出しますが、PostgreSQL プロトコルを使用するデータベースにはアクセスできません。postgres_fdw 拡張は、PostgreSQL プロトコル経由で他の PostgreSQL データベースに接続し、クロスデータベースクエリとデータアクセスを実行します。