digoal
2019-01-11
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
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
