如何利用PostgreSQL实现海量数据无限空间存储 原创 曲艺伟、彭智 京东智联云开发者 昨天
今年的两会和政府工作报告中多次强调“加强新型基础设施建设，发展新一代信息网络，拓展5G应用”等拓展新基建的措施，年初爆发的新冠肺炎疫情也让“新基建”初露锋芒，互联网+、5G、IoT、人工智能等新基建技术正在飞速发展。无论是最近十多年数字经济宠儿的互联网大数据技术，还是未来踌躇满志亟待发展的5G物联网技术，都离不开海量数据的存储与计算。随着智能技术的推进和发展，如何在保证海量数据计算和存储的安全性问题时，能高效利用数据库计算能力计算热数据而又可以无限存储冷数据成为亟待解决的难题。
京东智联云云数据库PostgreSQL是京东智联云数据库团队历时 1 年多精心研发的产品，该产品凝聚了京东智联云数据库团队多年来的PostgreSQL使用经验，能够提供一个稳定、可靠的数据存储服务。云数据库PostgreSQL易于部署、管理和扩展，默认支持主从热备架构，提供数据备份、故障恢复、监控等全套解决方案，彻底解决数据库运维的烦恼。同时，京东智联云“云数据库 PostgreSQL”通过使用网络隔离、白名单限制外网访问等方式，为数据库服务提供了高级别的安全性需求，帮助用户省去绝大部分复杂而繁琐的管理工作，从而使用户能够更专注于业务流程的开发与建设。云数据库PostgreSQL目前已上线支持9.6、10.6、11.2、12.2版本。
为了实现冷热数据分离，京东智联云基于云数据库PostgreSQL推出自研插件s3_fdw，该插件可实现云数据库PostgreSQL和对象存储服务OSS双向打通。用户海量数据可无限存储在OSS上，实时业务数据存储在PostgreSQL做计算，在PostgreSQL计算完成后通过s3_fdw插件转存在OSS上。
目前，s3_fdw插件已广泛应用于京东物流相关业务。快递配送员每天从各个快递站点配送到用户地区会产生大量的轨迹数据，而站点负责人只需要了解配送员当天的配送路线和配送效率，定期对配送员历史轨迹和配送效率打分。在技术实现上，配送员每天会产生大量轨迹数据，最高达到TB级别。如果每天的轨迹都存在数据库中很快就会达到存储上限，而站长仅需要分析当天的数据作为热数据，历史数据仅作为定期分析场景的参考。因此，我们可以通过云数据库PostgreSQL实现当天配送员轨迹数据的存储和分析，再通过s3_fdw把历史轨迹数据定期同步到OSS上，实现冷热数据分离。
使用s3_fdw插件打通云数据库PostgreSQL与OSS之间的数据流转，无需复杂的程序操作，用户仅需要几行代码，即可轻松实现。下面我们就来展示在PostgreSQL中使用s3_fdw读写OSS外部表的操作过程：
准备数据
postgres=# CREATE TABLE local_table(id integer, name character varying, password character varying);
CREATE TABLE
postgres=# insert into local_table select generate_series(1,40000000), md5(random()::text), md5(random()::text);
INSERT 0 40000000
创建插件
postgres=# create extension s3_fdw;
CREATE EXTENSION
postgres=# CREATE SERVER s3_fdw_server FOREIGN DATA WRAPPER s3_fdw options(host 's3-internal.cn-north-1.jdcloud-oss.com', bucket 'postgresql');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR CURRENT_USER SERVER s3_fdw_server OPTIONS (access_key_id 'xxxxxx', secret_access_key 'xxxxxx');
CREATE USER MAPPING
创建外部表
postgres=# CREATE FOREIGN TABLE local_table_oss_1_10000000(id integer, name character varying, password character varying) SERVER s3_fdw_server OPTIONS(dir 's3_fdw_local_table/1_10000000/', format 'csv');
CREATE FOREIGN TABLE
postgres=# CREATE FOREIGN TABLE local_table_oss_10000001_20000000(id integer, name character varying, password character varying) SERVER s3_fdw_server OPTIONS(dir 's3_fdw_local_table/10000001_20000000/', format 'csv');
CREATE FOREIGN TABLE
postgres=# CREATE FOREIGN TABLE local_table_oss_20000001_30000000(id integer, name character varying, password character varying) SERVER s3_fdw_server OPTIONS(dir 's3_fdw_local_table/20000001_30000000/', format 'csv');
CREATE FOREIGN TABLE
postgres=# CREATE FOREIGN TABLE local_table_oss_30000001_40000000(id integer, name character varying, password character varying) SERVER s3_fdw_server OPTIONS(dir 's3_fdw_local_table/30000001_40000000/', format 'csv');
CREATE FOREIGN TABLE
开启4个客户端，并行地将云PostgreSQL实例的数据导入OSS
postgres=# insert into local_table_oss_1_10000000 select * from local_table where id <= 10000000;
postgres=# insert into local_table_oss_10000001_20000000 select * from local_table where id >= 10000001 and id <= 20000000;
postgres=# insert into local_table_oss_20000001_30000000 select * from local_table where id >= 20000001 and id <= 30000000;
postgres=# insert into local_table_oss_30000001_40000000 select * from local_table where id >= 30000001 and id <= 40000000;
直接读取OSS数据源
postgres=# select * from local_table_oss_1_10000000 limit 5;
id | name | password
----+----------------------------------+----------------------------------
1 | b6c4d3e0efff1ff051ed7989ade43287 | 2859055a8d51b2f8888993887340fe7d
2 | f718eb7452c59bae2ee06dd88eae1488 | bd21f8468c8d2f5d7bb756a55203d204
3 | 95735e3472903502f0a08dc895220ff9 | d06704faad7a247d29d6257af85d2906
4 | 3b690bb8912fe96567d04287fc9fa701 | d04ad214f9dd1d22cb680b72a0a9d0bb
5 | 849842e47625c271de0221adc55608a6 | 8e961dcffcef418200f623c1b5f34d7f
(5 rows)
本地创建新表
postgres=# create table local_table_2 (like local_table);
CREATE TABLE
postgres=# select * from local_table_2;
id | name | password
----+------+----------
(0 rows)
开启4个客户端，并行地将数据从OSS上load到云PostgreSQL实例
postgres=# insert into local_table_2 select * from local_table_oss_1_10000000;
postgres=# insert into local_table_2 select * from local_table_oss_10000001_20000000;
postgres=# insert into local_table_2 select * from local_table_oss_20000001_30000000;
postgres=# insert into local_table_2 select * from local_table_oss_30000001_40000000;
数据一致性校验
postgres=# select sum(hashtext(t.*::text)) from local_table t;
sum
10851381716282
(1 row)
postgres=# select sum(hashtext(t.*::text)) from local_table_2 t;
sum
10851381716282
(1 row)
<左右滑动以查看完整代码>
详细操作可点击链接查看：
https://docs.jdcloud.com/cn/rds/s3_fdw
以上可以看出，利用s3_fdw通过PostgreSQL对OSS外部表进行读写，可以很方便地实现对数据的清洗、过滤、导出。通过改变上文中的"insert into … select …” 语句中的select子句，可以轻松达到数据处理的目的。除此之外，数据从PostgreSQL流转到OSS后，另一端并不一定是PostgreSQL才能load数据，很重要的一点是：京东智联云数据仓库JDW，同样提供了类似s3_fdw的插件，可以将云PostgreSQL导出的数据轻松load进云JDW实例，进行数据分析。
整体架构图如下所示：
通过上述内容，想必大家对云数据库PostgreSQL到OSS之间的数据流转有了进一步的了解。通过在OSS上合理控制目录结构并使用s3_fdw写入数据，可以轻松将云PostgreSQL数据导入OSS中，进一步地，更可以将OSS数据导入云数据仓库JDW，有效覆盖OLTP和OLAP一体化的场景。还在为海量数据存储与计算烦恼么？赶快试试京东智联云自研的s3_fdw插件，轻松搞定多样化数据应用场景。
点击"阅读原文"，了解京东云数据库 PostgreSQL
