全部产品
Search

搜索本产品

    云数据库 RDS：跨库操作（dblink、postgres_fdw）

    文档中心

    云数据库 RDS：跨库操作（dblink、postgres_fdw）

    更新时间：Nov 07, 2023

    使用PostgreSQL本身提供的扩展插件，例如dblink和postgres_fdw，可以跨库操作表。

    背景信息

    阿里云RDS for PostgreSQL云盘版实例开放dblink和postgres_fdw插件，支持相同VPC内实例（包括自建PostgreSQL数据库）间的跨库操作。

    购买PostgreSQL云盘版实例。

    注意事项

    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实例的数据库连接中查看专有网络网段。查看VPC网段

    使用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;

    返回结果如下：

    输出结果