背景

PostgreSQL数据库目前被广泛应用于企业的在线业务，这款数据库也被业界誉为“最先进的开源数据库”。 本文介绍了AnalyticDB PostgreSQL如何能够使用DMS进行业务调度，完成面向RDS PG的实现定时调度的数据EL链路，开发并让企业可以同时享受PostgreSQL在OLTP & OLAP的场景下的全面能力。

工具介绍

本文使用了dms来实现了整个链路的调度， 使用了oss介质作为中间态的存储，将数据从PostgreSQL 加载到OSS后，落入ADB PG Serverless版本中，实现T+1的数据分析。

优势：

1. 数据基于oss低成本存储实现归档，永不删除
2. 数据从RDS（可以是PG也可以是MySQL），T+1加载到ADB PG Serverless版本中做高性能分析
3. 基于DMS配置自动调度框架，白屏化，低代码。

约束：

1. RDS源表中的数据，需要能够通过条件来增量归档。下面的例子是通过t_src表中的c date列，实现按天归档。

方案框架：

step by step流程 （大约20~30分钟可完成）

资源准备

注意：本方案对 rds 的pg14版本暂不支持； 支持pg9.4 到13 的版本；

1. 已有一台开通的rds pg； （确认您的版本在pg9~pg13之间）若无RDS PG，可购买创建；
2. 开通adb pg serverless版本，（疫情期间，我们提供了疫情礼包，包含计算引擎）； 在创建好实例之后，请先进行初始设置，需要设置实例账号；
3. 开通oss，并建好对应的bucket，有对应的access key，secret可以。

1. 若您没有oss，可免费开启该服务： https://www.aliyun.com/product/oss
2. 当oss服务已开启，可登录oss控制台 ： https://oss.console.aliyun.com/bucket
3. 需要创建一个的oss bucket，需要保证该bucket和RDS PG 和 ADBPG在同一个region内；

1. 建立bucket以后： 点击进入bucket，记录你的bucket信息；包括 bucket_host, bucket_name，获取方式如下；

1. 怎么获取access_key 和 secret_key，在控制台右上角点击自己的头像，去到AccessKey管理

进入后可查看secret，记录好自己的两个关键key；

执行流程

第一步：数据和服务准备

理论上用户在RDSPG上已经存在着业务数据，本文为了方便理解，创建了样本数据来供测试流程使用；

1. 登录rds pg，进入选择数据库，进入SQL编辑页面， 创建oss_fdw插件。

1. 通过dms登录rds pg，运行SQL： create extension if not exists oss_fdw;  
2. 创建一个表作为源表，运行SQL：

 

   create table t_src (a int, b int, c date);

   insert into t_src select generate_series(1, 1000), 1, now();

   select * from t_src;

1. 为RDS PG创建一个外表写入

-- 导入外表插件

create extension if not exists oss_fdw;  

-- 创建外表服务；

create  SERVER ossserver FOREIGN DATA WRAPPER oss_fdw OPTIONS

    (host '[bucket_host]' , id '[access_key]', key '[secret_key]',bucket '[bucket_name]');

2. 开通adb pg serverless版本，进入数据库， 在SQL编辑页面进行创建oss_fdw插件，创建目标表，并配置oss 外表。登录adb pg，运行SQL：

--添加外表访问插件

create extension if not exists oss_fdw;  

-- 和rdspg侧保持表的定义相同，创建一张表； 注意serverless版本暂不支持主键；

create table t_target (a int, b int, c date);

-- 导入外表访问插件

create extension if not exists oss_fdw;  

-- 创建外表访问链接服务

create server oss_serv

   foreign data wrapper oss_fdw

   options (

       endpoint '[bucket_host]',

       bucket '[bucket_name]');

create user mapping for public

   server oss_serv

   options (

     id '[access_key]',

     key '[secret_key]');

3. 通过dms配置自动归档和加载任务流

第二步： 配置ETL任务；

1. RDS PG 抽取任务配置

1. 新建任务编排。 在DMS页面上，集成与开发(DTS)”-> 任务编排->新建任务流 ， 设置任务流名称“rdspg数据导入oss”

1. 新建RDS PG归档流程： 选择新建任务流，数据加工 -> 单实例SQL，拖入面板

1. 命名该实例，点击 ，改名为’rds数据抽取‘
2. 点击实例设置按钮 进入配置，并将如下SQL贴入，此段为将数据从RDS PG数据写入OSS存储介质中；

drop FOREIGN TABLE if exists oss_${mydate};

CREATE FOREIGN TABLE if not exists oss_${mydate}

   (a int,

    b int,

    c date)

    SERVER ossserver

    OPTIONS ( dir 'rds/t3/${mydate}/', delimiter '|' ,

        format 'csv', encoding 'utf8');

       

insert into oss_${mydate} select * from t_src where c >= '${mydate}';

1. 填入该任务关联的数据库名称； （注意搜索方法为，回到SQL窗口，切换到需要的数据库，在头部有数据库对应的已public开头的库名，点击右侧进行复制； ）

1. 返回任务配置页，将刚刚复制的库名放入搜索框后，选中结果

1. 点击“变量设置”一栏，配置日期变量mydate, 时间格式变为 yyyyMMdd； 注意：这个变量在后面的SQL中要用到，用来根据日期做增量归档。

2. 配置ADB PG的加载任务

1. 新建ADB PG加载任务，数据加供 -> 单实例SQL，将其拖拽至面板，改名为“ADBPG数据加载”

1. 点击内容设置 ，首先选取对应的ADBPG数据库实例（方法如上）

1. 编辑对应SQL，在ADBPG侧进行数据从OSS抽取

CREATE FOREIGN TABLE if not EXISTS  oss_${mydate}(  

    a int ,

    b int ,

    c date

) server oss_serv

   options (

       dir 'rds/t3/${mydate}/',

       format 'csv',

       delimiter '|',

       encoding 'utf8');

insert into t_target select * from oss_${mydate};

1. 配置日期变量mydate，作用同上。

3. 任务调度和执行时间配置

1. 配置调度流程，先跑RDS PG任务，再跑ADB PG，选中RDS PG框下面圆点，拖到ADB PG上。

1. 找到调度配置，开启调度， 并配置调度策略。配置调度页。选择你希望的任务调度周期，每次调度会对数据进行rdspg侧的导出和adbpg的导入，保证数据的同步；

4. 测试和发布

1. 点击“试运行”，测试无误后，点击“发布”

总结

该产品能力支持用户快速完成一套免费的数据ETL链路，支持用户将现有的RDS PG的数据落入OSS 中，并加载至ADBPG中进行分析； 也可以落在OSS上后，以外表的形式进行联邦分析；

用法： 对于海量分区数据的分析，在ADBPG中有明显的加速效果； 对于TB~PB级的数据，可做到秒级查询，汇总并对接主流的BI产品，目前AnalyticDB PostgreSQL被广泛应用在企业的数字化转型，实现云端数据平台部署；

参考文档

【1】dms案例，https://www.alibabacloud.com/help/zh/data-management-service/latest/regularly-back-up-data-from-relational-databases-to-oss-in-dms

【2】rds pg外表使用说明 https://help.aliyun.com/document_detail/164815.html

【3】adb pg外表使用说明 https://help.aliyun.com/document_detail/164815.html