阿里云rds PG, PPAS PostgreSQL 同实例,跨库数据传输、访问(postgres_fdw 外部表)-阿里云开发者社区

开发者社区> 数据库> 正文

阿里云rds PG, PPAS PostgreSQL 同实例,跨库数据传输、访问(postgres_fdw 外部表)

简介: 标签 PostgreSQL , 阿里云rds , pg , ppas , 跨库查询 , 外部表 , postgres_fdw 背景 如果你使用pg或ppas作为实时数仓,并且有跨库数据传输(ods, dw, dm 分层结构)的需求,可以使用postgres_fdw外部表实现,不需要使用ETL工具对数据进行抽取和导入这种无用功操作。 postgres_fdw是PG的一个外部表插件,可读,

标签

PostgreSQL , 阿里云rds , pg , ppas , 跨库查询 , 外部表 , postgres_fdw


背景

如果你使用pg或ppas作为实时数仓,并且有跨库数据传输(ods, dw, dm 分层结构)的需求,可以使用postgres_fdw外部表实现,不需要使用ETL工具对数据进行抽取和导入这种无用功操作。

postgres_fdw是PG的一个外部表插件,可读,可写。同时支持条件下推,性能很不错。

实际上PG的FDW是一个模块,不仅支持PG外部表,还能够支持mongo, oracle, mysql, mssql等等几乎地球上所有的外部数据源(例如阿里的OSS数据源),因此你可以在PG数据库中直接访问这些数据源的数据,就如同访问PG的本地表一样方便。

例子

以阿里云RDS pg为例

1、在本地库创建插件

postgres=# create extension postgres_fdw;    
CREATE EXTENSION    

2、得到RDS当前数据库端口:

postgres=# show port;    
 port     
------    
 1921    
(1 row)    

3、在本地库创建外部库的server,取个名字,例如foreign_server,定义外部库的连接

CREATE SERVER foreign_server    
        FOREIGN DATA WRAPPER postgres_fdw    
        OPTIONS (host '127.0.0.1', port '1921', dbname 'digoal');    

4、在目标库(远端库),创建测试表

digoal=# create table test1 (id int, info text, crt_time timestamp);    
CREATE TABLE    
digoal=# create table test2 (id int, info text, crt_time timestamp);    
CREATE TABLE    
digoal=# create table test3 (id int, info text, crt_time timestamp);    
CREATE TABLE    
digoal=# insert into test1 select generate_series(1,1000000), md5(random()::text), now();    
INSERT 0 1000000    
digoal=# insert into test2 select generate_series(1,1000000), md5(random()::text), now();    
INSERT 0 1000000    
digoal=# insert into test3 select generate_series(1,1000000), md5(random()::text), now();    
INSERT 0 1000000    

5、在目标库,创建数据库用户

digoal=# create role r1 login encrypted password '1234';    
CREATE ROLE    

6、将需要被访问的表的权限赋予给这个用户

digoal=# grant all on table test1 to r1;    
GRANT    
digoal=# grant all on table test2 to r1;    
GRANT    
digoal=# grant all on table test3 to r1;    
GRANT    

7、在本地库,创建认证映射关系

例如本地用户postgres通过r1用户连接foreign_server 外部server.

CREATE USER MAPPING FOR postgres    
        SERVER foreign_server    
        OPTIONS (user 'r1', password '1234');    

8、在本地库创建外部表

CREATE FOREIGN TABLE ft_test1 (    
        id int,    
        info text,    
	crt_time timestamp    
)    
        SERVER foreign_server    
        OPTIONS (schema_name 'public', table_name 'test1');    

9、在本地库,导入远程表到本地,性能

postgres=# create table lt_test1 (id int, info text, crt_time timestamp);    
CREATE TABLE    
postgres=# \timing    
Timing is on.    
postgres=# insert into lt_test1 select * from ft_test1;    
INSERT 0 1000000    
Time: 3102.742 ms (00:03.103)    

支持条件下推

postgres=# explain verbose select * from ft_test1 where id=111;    
                                  QUERY PLAN                                      
------------------------------------------------------------------------------    
 Foreign Scan on public.ft_test1  (cost=100.00..103.04 rows=6 width=44)    
   Output: id, info, crt_time    
   Remote SQL: SELECT id, info, crt_time FROM public.test1 WHERE ((id = 111))    
(3 rows)    
    
Time: 1.199 ms    

10、将本地数据写入远端

postgres=# explain verbose insert into ft_test1 select * from lt_test1;    
                                   QUERY PLAN                                       
--------------------------------------------------------------------------------    
 Insert on public.ft_test1  (cost=0.00..322.76 rows=1000000 width=45)    
   Remote SQL: INSERT INTO public.test1(id, info, crt_time) VALUES ($1, $2, $3)    
   ->  Seq Scan on public.lt_test1  (cost=0.00..322.76 rows=1000000 width=45)    
         Output: lt_test1.id, lt_test1.info, lt_test1.crt_time    
(4 rows)    
    
postgres=# insert into ft_test1 select * from lt_test1;    
INSERT 0 1000000    
Time: 44294.740 ms (00:44.295)    

11、如果要一次创建远端某个SCHEMA下的所有表到本地的某个SCHEMA中作为外部表,可以使用import语法。

首先在本地创建一个SCHEMA,用于存放远端schema的表的外部表。

create schema ft;  

使用import语法将远端public schema下的所有表,定义到本地的ft schema中

postgres=# import foreign schema public from server foreign_server INTO ft;    
IMPORT FOREIGN SCHEMA    
postgres=# \det ft.*    
     List of foreign tables    
 Schema | Table |     Server         
--------+-------+----------------    
 ft     | test1 | foreign_server    
 ft     | test2 | foreign_server    
 ft     | test3 | foreign_server    
(3 rows)    

参考

https://www.postgresql.org/docs/11/postgres-fdw.html

 

免费领取阿里云RDS PostgreSQL实例、ECS虚拟机

版权声明:如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件至:developerteam@list.alibaba-inc.com 进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章