阿裡雲rds PG, PPAS PostgreSQL 同執行個體，跨庫資料傳輸、通路(postgres_fdw 外部表)

标簽

PostgreSQL , 阿裡雲rds , pg , ppas , 跨庫查詢 , 外部表 , postgres_fdw

https://github.com/digoal/blog/blob/master/201901/20190111_02.md#%E8%83%8C%E6%99%AF 背景

如果你使用pg或ppas作為實時數倉，并且有跨庫資料傳輸（ods, dw, dm 分層結構）的需求，可以使用postgres_fdw外部表實作，不需要使用ETL工具對資料進行抽取和導入這種無用功操作。

postgres_fdw是PG的一個外部表插件，可讀，可寫。同時支援條件下推，性能很不錯。

實際上PG的FDW是一個子產品，不僅支援PG外部表，還能夠支援mongo, oracle, mysql, mssql等等幾乎地球上所有的外部資料源（例如阿裡的OSS資料源），是以你可以在PG資料庫中直接通路這些資料源的資料，就如同通路PG的本地表一樣友善。

https://github.com/digoal/blog/blob/master/201901/20190111_02.md#%E4%BE%8B%E5%AD%90 例子

以阿裡雲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://github.com/digoal/blog/blob/master/201901/20190111_02.md#%E5%8F%82%E8%80%83 參考

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

https://github.com/digoal/blog/blob/master/201901/20190111_02.md#%E5%85%8D%E8%B4%B9%E9%A2%86%E5%8F%96%E9%98%BF%E9%87%8C%E4%BA%91rds-postgresql%E5%AE%9E%E4%BE%8Becs%E8%99%9A%E6%8B%9F%E6%9C%BA 免費領取阿裡雲RDS PostgreSQL執行個體、ECS虛拟機