ApsaraDB RDS:データベース間の操作に dblink および postgres_fdw 拡張機能を使用する

使用上の注意

クラウドディスクを使用する RDS インスタンスでデータベース間の操作を実行する場合は、次の点に注意してください。

• セルフマネージド PostgreSQL インスタンスが ECS (Elastic Compute Service) インスタンス上にあり、ECS インスタンスと RDS インスタンスが同じ VPC 内にある場合は、データベース間の操作を直接実行できます。

• セルフマネージド PostgreSQL インスタンスを、異なる VPC 内にある Oracle または MySQL インスタンスに接続する場合は、oracle_fdw または mysql_fdw 拡張機能を使用できます。

• 同じ RDS インスタンス上のデータベース間でテーブルを管理する場合は、次の点に注意してください。

  • host パラメーターを localhost ではなく 127.0.0.1 に明示的に設定することをお勧めします。これは、IPv6 対応の RDS インスタンスで発生する接続エラーを防ぐのに役立ちます。

  • port パラメーターは、Alibaba Cloud の O&M 操作や仕様変更などの操作によってポート番号が変更される可能性があるため、明示的に設定しないことをお勧めします。これは、接続エラーを防ぐのに役立ちます。 port パラメーターを明示的に設定しない場合、データベースに接続するときに、データベースの port パラメーターの値がデフォルトで使用されます。これは、接続の有効性を保証します。

  • port パラメーターを明示的に設定する場合は、データベースに接続し、SHOW PORT; 文を実行してポートをクエリしてから、port パラメーターを設定することをお勧めします。

• RDS インスタンスが存在する VPC の CIDR ブロック (例: 172.XX.XX.XX/16) を、接続先のインスタンスの IP アドレス ホワイトリストに追加する必要があります。

  説明

  RDS インスタンスが存在する VPC の CIDR ブロックは、ApsaraDB RDS コンソールの [データベース接続] ページで確認できます。

dblink 拡張機能を使用する

1. 拡張機能を作成します。

   create extension dblink;

2. dblink 接続を作成します。

   postgres=> select dblink_connect('<接続名>', 'host=<同じ VPC 内の宛先データベースが存在するインスタンスの内部エンドポイント> port=<同じ VPC 内の宛先データベースが存在するインスタンスの内部リスニングポート> user=<宛先データベースにログインするユーザー名> password=<パスワード> dbname=<宛先データベースの名前>');
   
   postgres=> SELECT * FROM dblink('<接続名>', '<SQL 文>') as <テーブル名>(<列名> <列タイプ>); 

   例

   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 拡張機能を使用する

1. データベースを作成します。

   postgres=> create database <データベース名>; //データベースを作成します。
   
   postgres=> \c <データベース名> //データベースに切り替えます。

   例

   postgres=> create database db1;  
   CREATE DATABASE  
   
   postgres=> \c db1  

2. postgres_fdw 拡張機能を作成します。

   db1=> create extension postgres_fdw;

3. 宛先データベースへの接続に使用するサーバーオブジェクトを作成します。

   db1=> CREATE SERVER <サーバーオブジェクトの名前>                                                              
           FOREIGN DATA WRAPPER postgres_fdw  
           OPTIONS (host '<同じ VPC 内の宛先データベースが存在するインスタンスの内部エンドポイント>',port '<同じ VPC 内の宛先データベースが存在するインスタンスの内部リスニングポート>', dbname '<同じ VPC 内の宛先データベースの名前>');  
   
   db1=> CREATE USER MAPPING FOR <ソースデータベースにログインするために使用するユーザー名>      
           SERVER <サーバーオブジェクトの名前> 
           OPTIONS (user '<宛先データベースにログインするために使用するユーザー名>', 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  

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

   db1=> import foreign schema public from server foreign_server1 into <スキーマ名>; //外部テーブルをインポートします。
   
   db1=> select * from <スキーマ名>.<テーブル名>      //宛先データベースのテーブル。

   例

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

詳細については、「postgres_fdw」をご参照ください。

FAQ

postgres_fdw 拡張機能を使用してパーティション化された外部テーブルにアクセスする場合、テーブルをどのようにインポートしますか?

パーティションテーブルの名前のみを宛先インスタンスにインポートする必要があります。

次のコードは、「Range Partition」という名前のパーティションテーブルをインポートする方法の例を示しています。

-- ソースインスタンスのソースデータベース。
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');

-- パーティションテーブルの名前のみを宛先 RDS インスタンスにインポートします。
import FOREIGN SCHEMA public limit to (sales) from server pg_fdw_server into public;
select * from sales;

次の図は、出力例を示しています。