PostgreSQL跨库操作（dblink、postgres_fdw、mysql_fdw）

PostgreSQL跨库操作（dblink、postgres_fdw）

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

PostgreSQL跨库背景信息

阿里云RDS for PostgreSQL云盘版实例开放dblink和postgres_fdw插件，支持相同VPC内实例（包括自建PostgreSQL数据库）间的跨库操作。如果要访问VPC外部的其他实例，可以通过相同VPC内ECS的端口跳转实现。

PostgreSQL跨库注意事项

PostgreSQL云盘版的dblink和postgres_fdw插件进行跨库操作的注意事项如下：

相同VPC内的ECS/RDS PostgreSQL实例可以直接跨库操作。
RDS PostgreSQL实例可以通过本VPC内的ECS实例进行端口跳转，实现跨库操作。
自建PostgreSQL实例可以通过oracle_fdw或mysql_fdw连接VPC外部的Oracle实例或MySQL实例。
连接自身跨库操作时，host请填写localhost，port请填写show port命令返回的本地端口。

一、使用dblink

1、新建dblink插件。

create extension dblink;

2、创建dblink连接。

postgres=> select dblink_connect('<连接名称>', 
'host=<同一VPC下的另一RDS的内网域名> port=<同一VPC下的另一RDS的内网监听端口> 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 https://www.postgresql.org/docs/12/dblink.html。

二、使用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下的另一RDS的内网域名>,
        port '<同一VPC下的另一RDS的内网监听端口>', 
        dbname '<同一VPC下的另一RDS的库名>'
        );  

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 https://www.postgresql.org/docs/12/postgres-fdw.html。

来源 ：https://help.aliyun.com/document_detail/142422.html

三、读写MySQL数据（mysql_fdw）

RDS PostgreSQL提供mysql_fdw插件，可以读写RDS MySQL实例或自建MySQL数据库里的数据。

前提条件

实例为RDS PostgreSQL 13、12、11、10云盘版。
PostgreSQL和MySQL数据库网络互通。通过设置白名单、设置本地防火墙等保证网络互通且使用的连接地址正确。

背景信息

PostgreSQL从9.6开始就支持并行计算，到11的时候并行计算性能得到巨大提升，10亿数据量的join查询可以实现秒级完成。所以很多用户会使用PostgreSQL作为小的数据仓库使用，同时又能提供高并发访问。未来推出的PostgreSQL 13还将支持列存储引擎，分析能力还会有巨大的提升。

使用mysql_fdw插件能够将PostgreSQL和MySQL连接，同步MySQL数据进行数据分析。

使用mysql_fdw

操作步骤

1、新建mysql_fdw插件。

postgres=> create extension mysql_fdw;  
CREATE EXTENSION  

2、创建MySQL服务器定义。

postgres=> CREATE SERVER <server名称>  
postgres->      FOREIGN DATA WRAPPER mysql_fdw
postgres->      OPTIONS (host '<连接地址>', port '<连接端口>');  
CREATE SERVER  

示例

postgres=> CREATE SERVER mysql_server  
postgres->      FOREIGN DATA WRAPPER mysql_fdw
postgres->      OPTIONS (host 'rm-xxx.mysql.rds.aliyuncs.com', port '3306');  
CREATE SERVER  

创建用户映射，将MySQL服务器定义映射到PostgreSQL的某个用户上，将来使用这个用户访问MySQL的数据。

postgres=> CREATE USER MAPPING FOR <PostgreSQL用户名>   
SERVER <server名称>  
OPTIONS (username '<MySQL用户名>', password '<MySQL用户对应密码>');  
CREATE USER MAPPING  

示例

postgres=> CREATE USER MAPPING FOR pgtest 
SERVER mysql_server  
OPTIONS (username 'mysqltest', password 'Test1234!');  
CREATE USER MAPPING  

使用上一步骤的PostgreSQL用户创建MySQL的外部表。

说明
外部表的字段名要与MySQL数据库中表的字段名相同，同时可以仅创建您想要查询的字段。例如MySQL数据库中的表有3个字段ID、NAME、AGE，您可以仅创建其中2个字段ID、NAME。

postgres=> CREATE FOREIGN TABLE <表名> (<字段名> <数据类型>,<字段名> <数据类型>...) server <server名称> options (dbname '<MySQL数据库名>', table_name '<MySQL表名>');  
CREATE FOREIGN TABLE  

示例

postgres=> CREATE FOREIGN TABLE ft_test (id1 int, name1 text) 
server mysql_server options (
dbname 'test123', 
table_name 'test'
);  
CREATE FOREIGN TABLE  

测试读写

您可以通过外部表读写MySQL数据。

说明 MySQL对应的表必须有主键才可以写入数据，否则会报如下错误：

ERROR:  first column of remote table must be unique for INSERT/UPDATE/DELETE operation.

postgres=> select * from ft_test ;  

postgres=> insert into ft_test values (2,'abc');  
INSERT 0 1  

postgres=> insert into ft_test select generate_series(3,100),'abc';  
INSERT 0 98  
postgres=> select count(*) from ft_test ;  
 count   
-------  
    99  
(1 row)  

检查执行计划，即PostgreSQL查询MySQL数据的请求在MySQL中是如何执行的。

postgres=> explain verbose select count(*) from ft_test ;  
                                  QUERY PLAN                                     
-------------------------------------------------------------------------------  
 Aggregate  (cost=1027.50..1027.51 rows=1 width=8)  
   Output: count(*)  
   ->  Foreign Scan on public.ft_test  (cost=25.00..1025.00 rows=1000 width=0)  
         Output: id, info  
         Remote server startup cost: 25  
         Remote query: SELECT NULL FROM `test123`.`test`  
(6 rows)  

postgres=> explain verbose select id from ft_test where id=2;  
                               QUERY PLAN                                  
-------------------------------------------------------------------------  
 Foreign Scan on public.ft_test  (cost=25.00..1025.00 rows=1000 width=4)  
   Output: id  
   Remote server startup cost: 25  
   Remote query: SELECT `id` FROM `test123`.`test` WHERE ((`id` = 2))  
(4 rows)  

来源 ：https://help.aliyun.com/document_detail/143613.html