阿里数据库团队PostgreSQL实践

1、阿里数据库团队阿里数据库团队PostgreSQLPostgreSQL实践实践杨洋洋(阳帅)yang_阿里巴巴数据库团队提纲 IDB产品介绍 PostgreSQL实践原因 实践步骤 实践总结IDB 定位HA备份恢复性能压测自动化调度数据质量安全审计监控告警安装配置元数据用户服务层-数据库服务平台(iDB)资源申请数据访问开发设计生产变更分库分表SQL审核流程管控权限管控运维组件层淘宝天猫支付宝余额宝口碑芝麻信用钉钉1688速卖通高德地图UC阿里云菜鸟物流AliSQLMongoDBPostgreSQLOceanBaseOracle基础技术层SQLServerIDB是数据库团队自主研发的一个数据库服

2、务产品，是集团去O的重要实施平台。先后实现对Oracle,MySQL,Oceanbase,SqlServer,PostgreSQL等数据库的查询，数据变更，结构变更的支持。并且支持分库分表的操作。2010年淘宝启动“去IOE”项目PostgreSQL实践原因 IDB帮助集团实现了去O，但是自己还在使用Oracle。 2014年底，这台Oracle服务器已经集团仅剩的几台服务器之一，需要承担高昂的License费用。 去O势在必行，用什么来替换它呢？ MySQL?需要对应用和SQL做大量改造。 达梦? 功能与Oracle兼容，稳定性达不到要求 PostgreSQL?改造成本低，安全稳定实践步骤

3、实践评估：可行性分析、工作量评估。 制定方案：确定迁移的重点和难点，制定迁移方案。 应用改造： SQL改造，代码改造。 数据迁移：结构迁移，数据迁移。 回归与测试：功能回归、性能测试。 性能调优：针对上线后的性能问题进行分析和优化。SQL改造 时间间隔 Oracle时间相减得到间隔，单位为天。PG时间相减得到 interval值，需要转换为具体的时间值。 Oracle中sysdate + interval xxxx unit(precision) 类型的数据，PG中用now()+ 时间间隔字符串 的方式来实现。 Now() + 1 day Now() + 14400:interval 别名设置

4、： 避免使用关键字role,data,label,type,name 作别名，或者加上as 关键字SQL改造 常量参数需要进行强制类型转换，否则类型则unknown，框架无法自动解析类型。 select id, new :varchar status from t;这样才能保证应用程序中获取到的status数据类型为String。不强制转换获取到的是Object 对象，会导致JavaBean或者DO对象的属性值设置失败。SQL改造 函数替换：将Oracle部分PG不支持的函数替换为PG的等价函数。 分组合并：Oracle中wm_concat(xxx)转换成PG中的string_agg(xxx,

5、) over (partition by X oder by Y)来实现。 Oracle中的regexp_replace(xxx,reg)在PG需要加上第三个参数 “,” Bitand(A,B) ：替换成运算符 & , A & B sysdate： now, current_timestamp NVL ： coalesce DECODE：case when then else endSQL改造 Oracle Merge Into 插入或者更新的SQL改造 PostgreSQL需要联合使用 with 查询, update returning, insert来进行选择性更新或者插入

6、。with data as (select #id#:integer id,#name#:varchar name,#status#:varchar status), upsert as (update my_user dst set name=, gmt_modified=now(), status=d.status from data d where is not null and d.id=dst.id returning dst.* )insert into my_user(id,name,status)select nextVal(seq_my_user),

7、,d.status from data d where not exists(select 1 from my_user dst where dst.id = d.id)SQL改造 整数参数传入空字符串的处理 使用 #id#:numeric时，程序传递空字符串作为参数，则会出现 invalid input syntax for type numeric错误 需要使用to_number(#param#) （EDB） Select * from t where status = new and pid = to_number(#param#)SQL改造 分页 Oracle SELECT

8、 * from (SELECT rownum AS rn, t.* FROM t WHERE condition AND rownum = start PGSELECT * from t where condition offset start limit pagesizeSQL改造 部分数据类型转换 Oracle 布尔值 char(1) Y/N 转换成 PG 类型 boolean Clob 大字段类型 转换成PG类型varchar 虚拟列 增加实际列或者使用视图 Oracle u0000字符 PG不允许存储，过滤掉再保存 修改字段类型时可以使用 USING 表达式 进行字段值转换 如alte

9、r table user alter column is_deleted type booleanUSING case is_deleted when Y then true else false end ; alter table user alter column is_deleted type booleanUSING is_deleted = Y;SQL改造 递归查询 START WITHCONNECT BY 转换成 WITH RECURSIVE 查询例如 select* fromemp start withempno=7 connectbymgr=priorempno;转换成PG的递

10、归SQL：with recursive r_emp r as (select a.id, , a.pid from emp a where id = 1001 union all select b.id, , b.pid from emp b inner join r on r.id = b.pid) select id, name from r应用程序改造 字段名的大小转换与映射问题。 Oracle 字段名默认大写，PG字段名默认小写。全部大写大小写混合区分大小写区分大小写SQL字段名USER_NAMEUser_Age“User_Id”“use_desc”Oracle

11、得到的元数据USER_NAMEUSER_AGEUser_Iduse_descPG得到的元数据user_nameuser_ageUser_Iduse_desc应用程序改造 Oracle 与 PG 元数据大小写差异解决办法 批量修改SQL文件，为字段加入大写别名（操作性差，工作量大） Select user_id “USER_ID” from t where ; 批量修改代码，将程序中获取数据值时将字段名小写。 （操作性差，工作量大） row.getInt(“user_id”);应用程序改造 Oracle PG 默认元数据大小写差异解决办法 持久层框架改造，修改数据库字段到JavaBean的映射方

12、法(Mapping)SQL：select user_name from t where user_id=?Oracle 元数据：USER_NAME程序获取数据：row.get(“USER_NAME”)PG 元数据：user_name，原来的程序无法获取到数据：SQL:user_name - PG:user_name - 框架转换：”USER_NAME” -原来的程序正常获取到数据。应用程序改造 持久层框架改造改造示例：rsmd ResultSet.getMetaData() for (int i = 0, n = rsmd.getColumnCount(); i n; i+) String c

13、olumnName = rsmd.getColumnName(i + 1); if (delegate.isUseColumnLabel() String columnLabel = rsmd.getColumnLabel(i + 1); if if ( (isPostgreSQLisPostgreSQL) ) if( if(columnLabel.equalscolumnLabel.equals( (columnLabel.toLowerCasecolumnLabel.toLowerCase()() /) /小写的字段名直接改大写。 columnNamecolumnName = = colu

14、mnLabel.toUpperCasecolumnLabel.toUpperCase();(); else else columnNamecolumnName = = columnLabelcolumnLabel; ; /”/”user_IDuser_ID” ” 大小写敏感字段保留 else columnName = columnLabel; 应用程序改造 游标/流式数据处理 需要开启事物 PG-JDBC驱动默认加载所有行，如果结果集大会造成应用(JAVA)内存溢出(OutOfMemory) 使用游标或者设置fetchSize需要开启事物应用程序改造 配合表数据类型的改造进行修改。 javaB

15、ean.setDeleted(“Y”.equals(row.getString(IS_DELETE) javaBean.setDeleted(row.getBoolean(IS_DELETE) 通知下游数据使用方进行程序改造。性能优化 模糊查询： 如果没有修改PG库的locale，使用 like abc% 查询时，默认会扫描所有行，即使有索引也不走索引，引发性能问题。原因是要查询的数据类型和索引的数据类型不匹配。 解决方式：重建索引，为索引列指定 pattern_ops 模式，如 varchar_pattern_ops 。create index idx_t_name on t(name va

16、rchar_pattern_ops);性能优化 避免长事物 读取大量数据需要使用事物来防止溢出，但是使用长事物可能造成性能问题。 长事物会导致vacuum进程无法回收已经删除数据的存储空间，新的数据写入只能使用新的数据块上，导致磁盘空间持续增长。 解决办法： 数据库上监控长事物 程序上排查长事物产生的原因并进行修复性能优化 受长事务影响膨胀的表的处理 小表可使用 vacuum full 来处理。 大表使用 pg_reorg 来进行在线空间收缩，不锁表，不影响业务。性能优化 分页排序优化 部分Oracle的复杂SQL使用到PG上会产生性能问题，多层子查询只在最外层排序分页的时候性能影响明显，尽量

17、在子查询里进行关联，过滤，分页。当表和子查询多时，表的join顺序没有Oracle优化得好，可能会走错索引，所以尽量避免子查询，使用join来做。数据迁移 确定迁移工具 制定迁移方案 制定迁移脚本，开始迁移 验证迁移后的数据正确性数据迁移 工具选择 MTK Ora2PG，DBConvert DSQL(分布式数据库SQL引擎) , DTS（阿里云的数据迁移服务），数据迁移 制定迁移方案：MTK + DSQL(分布式数据库SQL引擎) MTK迁移任务可以并行到表级别 DSQL迁移任务可以并行到行级别，解决大表迁移时间长的问题。 重要的数据使用触发器记录变更，全量迁移完成之后再做增量迁移数据迁移 制

18、定迁移脚本 元数据迁移 Schema, 表结构，视图，序列， 制定数据迁移任务脚本 大表单独使用任务优先迁移 如 任务一：runMTK.sh t1 & 任务二： runMTK.sh t2 & 小表批量迁移 如 任务三：runMTK.sh t7,t8,t9,t10 &数据迁移 先建立索引再进行数据迁移 建立索引比较耗时，每个索引创建时间与接近迁移一次数据时间一个带7个索引的样本表进行测试样本（21G/21G/）同步数据时间( (分钟) )创建索引时间( (分钟) )CPUCPU总耗时先数据后索引111306%(2H先索引后数据（多线程写） 33014%(70%)40迁移性能 我们进行了3轮演练，比较性能，进行方案改进开始迁移 400+G的数据，分两批迁移， 实时性要求不高的表先迁移 实时性，一致性要求高的表后迁移，200G数据耗时约