このトピックでは、PostgreSQLで提供されるdblink拡張機能とpostgres_fdw拡張機能を使用して、データベース間のテーブルを管理する方法について説明します。
背景情報
クラウドディスクを使用するApsaraDB RDS for PostgreSQLインスタンスは、dblinkおよびpostgres_fdw拡張機能をサポートしています。 拡張機能を使用して、同じ仮想プライベートクラウド (VPC) にあるインスタンス上のデータベース間でテーブルを管理できます。 インスタンスには、自己管理型PostgreSQLインスタンスが含まれます。
クラウドディスクを使用するRDSインスタンスを購入する場合は、ApsaraDB RDS購入ページに移動します。
使用上の注意
クラウドディスクを使用するRDSインスタンスでクロスデータベース操作を実行する場合は、次の項目に注意してください。
自己管理型PostgreSQLインスタンスがElastic Compute Service (ECS) インスタンスにあり、ECSインスタンスとRDSインスタンスが同じVPCにある場合、データベース間操作を直接実行できます。
自己管理型PostgreSQLインスタンスを別のVPCにあるOracleまたはMySQLインスタンスに接続する場合は、oracle_fdwまたはmysql_fdw拡張子を使用できます。
同じRDSインスタンス上のデータベース間でテーブルを管理する場合は、次の項目に注意してください。
hostパラメーターを
localhostではなく127.0.0.1に明示的に設定することを推奨します。 これにより、IPv6-enabled RDSインスタンスで発生する接続障害を防ぐことができます。Alibaba Cloud O&M操作や仕様の変更などの操作によりポート番号が変更される可能性があるため、portパラメーターを明示的に設定しないことを推奨します。 これは接続障害を防ぐのに役立ちます。 portパラメーターを明示的に設定しない場合、データベースに接続するときにデータベースのportパラメーターの値がデフォルトで使用されます。 これにより、接続の有効性が保証されます。
portパラメーターを明示的に設定する場合は、データベースに接続し、
SHOW PORT;ステートメントを実行してポートを照会し、portパラメーターを設定することをお勧めします。
RDSインスタンスが存在するVPCのCIDRブロック (
172.XX. XX.XX/16など) を、接続先インスタンスのIPアドレスホワイトリストに追加する必要があります。説明ApsaraDB RDSコンソールの [データベース接続] ページで、RDSインスタンスが存在するVPCのCIDRブロックを表示できます。
dblink拡張機能を使用する
エクステンションを作成します。
拡張dblinkを作成します。dblink接続を作成します。
postgres=> select dblink_connect('<接続名>' 、'host=<ターゲットデータベースが同じVPCにあるインスタンスの内部エンドポイント> port=<ターゲットデータベースが同じVPCにあるインスタンスの内部リスニングポート> user=<ターゲットデータベースにログオンするユーザー名> password=<Password> dbname=<ターゲットデータベースの名前> '); postgres=> SELECT * FROM dblink('<Connection name>', '<SQL文>') as <Table name>(<Column name> <Column type>);例
postgres=> select dblink_connect('a', 'host=pgm-bpxxxxx.pg.rds.aliyuncs.comポート=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=> データベースの作成 <データベース名>; // データベースを作成します。 postgres=> \c <データベース名> // データベースに切り替えます。例
postgres=> データベースdb1を作成します。データベースの作成 postgres=> \c db1postgres_fdw拡張子を作成します。
db1=> create extension postgres_fdw;ターゲットデータベースへの接続に使用されるサーバーオブジェクトを作成します。
db1=> CREATE SERVER <サーバーオブジェクトの名前> FOREIGN DATA WRAPPER postgres_fdw オプション (ホスト '<ターゲットデータベースが同じVPCにあるインスタンスの内部エンドポイント> 、ポート' <ターゲットデータベースが同じVPCにあるインスタンスの内部リスニングポート> '、dbname' <同じVPCにあるターゲットデータベースの名前> '); db1=> CREATE USER MAPPING FOR <ソースデータベースへのログインに使用されるユーザー名> SERVER <サーバーオブジェクトの名前> オプション (user '<ターゲットデータベースへのログオンに使用されるユーザー名>' 、password '<ターゲットデータベースへのログオンに使用されるパスワード>');例
を作成するdb1=> CREATE SERVER foreign_server1 FOREIGN DATA WRAPPER postgres_fdw オプション (ホスト 'pgm -bpxxxxx.pg.rds.aliyuncs.com '、ポート '3433' 、dbname 'postgres'); サーバーの作成 db1=> テストユーザーのマッピングを作成する SERVER foreign_server1 オプション (ユーザー 'testuser2' 、パスワード 'passwd1234'); ユーザーマッピング外部テーブルをインポートします。
db1=> 外部スキーマpublicをサーバーforeign_server1から <schema name> にインポートします。// 外部テーブルをインポートします。 db1=> select * from <スキーマ名>.<テーブル名> // ターゲットデータベースのテーブル。例
db1=> 外部スキーマpublicをサーバーforeign_server1からftにインポートします。インポート外国のスキーマ db1=> select * from ft.products;
詳細については、「postgres_fdw」をご参照ください。
よくある質問
postgres_fdw拡張機能を使用してパーティション化された外部テーブルにアクセスする場合、テーブルをインポートするにはどうすればよいですか?
パーティションテーブルの名前をターゲットインスタンスにインポートするだけです。
次のコードは、Range Partitionという名前のパーティションテーブルをインポートする方法の例を示しています。
-- ソースインスタンスのソースデータベース。
CREATE TABLE sales (id int, p_name text, amount int, sale_date date)
作成テーブルsales_2022_Q1 ('2022-01-01 ') から ('2022-03-31') までの値の売上の一部。作成テーブルsales_2022_Q2 ('2022-04-01 ') から ('2022-06-30') までの値の売上の部分。作成テーブルsales_2022_Q3 ('2022-07-01 ') から ('2022-09-30') までの値の売上の部分。作成テーブルsales_2022_Q4 ('2022-10-01 ') から ('2022-12-31') までの値の売上の一部。売上高に挿入する (1、'prod_A '、100、'2022-02-02');
販売値に挿入 (2、'prod_B' 、5、'2022-05-02 ');
販売値に挿入 (3、'prod_C '、5、'2022-08-02');
販売値に挿入 (4、'prod_D' 、5、'2022-11-02 ');
-パーティションテーブルの名前のみをターゲットRDSインスタンスにインポートします。
サーバーpg_fdw_serverからパブリックにFOREIGN SCHEMAパブリック制限を (販売) にインポートします。select * from sales; 次の図は、サンプル出力を示しています。
