ApsaraDB RDS：跨庫操作（dblink、postgres_fdw）

注意事項

PostgreSQL雲端硬碟版的dblink和postgres_fdw外掛程式進行跨庫操作的注意事項如下：

• 相同VPC內的ECS/RDS PostgreSQL執行個體可以直接跨庫操作。

• 自建PostgreSQL執行個體可以通過oracle_fdw或mysql_fdw串連VPC外部的Oracle執行個體或MySQL執行個體。

• 同一執行個體中的不同資料庫之間進行跨庫串連時：

  • 建議host顯式設定為127.0.0.1而非localhost，以避免因執行個體開啟IPV6導致跨庫串連失效。

  • 不建議顯式設定Port，因為阿里雲營運操作或者使用者執行變更配置等操作可能導致Port變化，從而導致串連失敗。不顯式設定Port能夠在串連時預設使用資料庫當前Port參數，從而保證串連有效性。

  • 如果需要顯式設定Port，則請串連資料庫執行SQL語句SHOW PORT;查詢後再設定。

• 需要將RDS PostgreSQL的專用網路網段（例如172.XX.XX.XX/16）添加到目標資料庫的白名單中，允許RDS PostgreSQL訪問。

  說明

  您可以在RDS PostgreSQL執行個體的資料庫連接中查看專用網路網段。

使用dblink

1. 建立dblink外掛程式。

   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 <server名稱>                                                              
           FOREIGN DATA WRAPPER postgres_fdw  
           OPTIONS (host '<同一VPC下的遠端目標資料庫所在執行個體的內網網域名稱>,port '<同一VPC下的遠端目標資料庫所在執行個體的內網監聽連接埠>', dbname '<同一VPC下的遠端目標資料庫的名稱>');  
   
   db1=> CREATE USER MAPPING FOR <本機資料庫使用者名稱>      
           SERVER <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 <SCHEMA名稱>;  //匯入外部表格
   
   db1=> select * from <SCHEMA名稱>.<表名>      //遠端目標資料庫的表

   樣本

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

   

更多詳情請參見postgres_fdw。

常見問題

問題：通過postgres_fdw訪問外部表格時，如果訪問的表是一個分區表，如何匯入外表？

解決方案：在目標執行個體上執行，只需要匯入分區表名字即可，無需匯入partition表。

以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');

-- 目標執行個體上執行，只需要匯入分區表名字即可，無需匯入partition表。
import FOREIGN SCHEMA public limit to (sales) from server pg_fdw_server into public;
select * from sales;

返回結果如下：