阿里数据库团队PostgreSQL实践PPT课件
2.3 PostgreSQL数据库关系操作实践
学院编号 CollegeID
文本
3
是
主键
学院名称 CollegeName
文本
40
是
否
学院介绍 CollegeIntro
文本
200
否
否
学院电话 CollegeTel
文本
30
否
否
8
数据库系统原理与开发
电数子据科库技原大理学-及陆应鑫用
二、使用数据库管理工具pgAdmin 4创建PostgreSQL数据库
备注
Note
文本
50
电数子据科库技原大理学-及陆应鑫用
必填字段 是 是 是 否 否 否
是否为键 代理键 外键 外键
否 否 否
5
数据库系统原理与开发
字段名称 学号 姓名 性别
学生表（STUDENT）
字段编码 StudentID StudentName
数据类型 文本 文本
字段大小 13 10
StudentGender
2.3 PostgreSQL数据库 关系操作实践
数据库系统原理与开发
电数子据科库技原大理学-及陆应鑫用
【本节学习目标】
• 掌握创建PostgreSQL关系数据库方法 • 掌握在PostgreSQL数据库中创建关系表方法 • 掌握在PostgreSQL数据库中定义关系表的主键、代
理键与外键方法 • 掌握在PostgreSQL数据库中定义关系表的实体完整
文本
2
出生日期
BirthDay
日期
短日期
专业
Major
文本
30
电数子据科库技原大理学-及陆应鑫用
必填字段 是 是 否
否
是否为键 主键 否 否
大数据之路 阿里巴巴大数据实践PPT
言风格，语言描述尽量简洁生动。
3 添加标题文字
此处添加详细文本描述，建议与标题相关并符合整体语
言风格，语言描述尽量简洁生动。
4 添加标题文字
此处添加详细文本描述，建议与标题相关并符合整体语
言风格，语言描述尽量简洁生动。
标题文本预设
标题文本预设 标题文本预设
标题文本预设
标题文本预设 标题文本预设 标题文本预设
1 添加标题文字 此处添加详细文本描述，建议与标题相关并符合整体语 言风格，语言描述尽量简洁生动。
2 添加标题文字
此处添加详细文本描述，建议与标题相关并符合整体语 言风格，语言描述尽量简洁生动。
3 添加标题文字
此处添加详细文本描述，建议与标题相关并符合整体语 言风格，语言描述尽量简洁生动。
03 02 01
&;我不害怕，我不害怕。我要奔向我心里的远方，亲手拥抱最美好的时光&;&;&;思绪渐渐拉回，却又在如今
目录/CONTENTS
01 大数据时代 02 互联网创新 03 技术新边疆 04 用户新物种
PART 01
大数据时代
标题数字等都可以通过点击和重新输入进行更改，顶部“开 始”面板中可以对字体、字号、颜色、行距等进行修改
******
添加标题内容
数据库系统原理与开发-PostgreSQL数据库SQL实践
否
否
取值唯一
4
数据库系统原理与开发
项目表（Project）
字段名称 字段编码
数据类型
项目编号 ProjectID 项目名称 ProjectName
Serial Varchar
所属部门 Department Char
估算工时 开始日期
结束日期
EstimateHours Int
StartDate
Date
SELECT ProjectName AS 项目名称, EmployeeName AS 员工姓名, FishedHours AS 实际工时 FROM Employee AS E, Project AS P, Assignment AS A WHERE E.EmployeeID =A.EmployeeID AND P.ProjectID=A.ProjectID ORDER BY P.ProjectID, A.EmployeeID;
10
性别
Gender
Char
2
所属部门 Department
Char
3
学历
Degree
Char
4
出生日期 Birthday
Date
联系电话 Phone
Char
11
邮箱
Varchar
20
数据库原理及应用
必填字段 是
是否为键 主键
是
否
否
默认值‘男’
否
外键
否
（‘本科’、‘研 究生’、‘其它’）
否
该视图的创建SQL语句如下：
CREATE VIEW ContactView AS SELECT EmployeeID AS 员工编号, EmployeeName AS 员工姓名, Phone AS 电话, Email AS 邮箱
数据库系统原理与开发-PostgreSQL数据库管理项目实践
5.6 PostgreSQL数据库管理项目实践【本节学习目标】•掌握数据库系统角色创建方法•掌握赋予数据库角色对象访问权限方法•掌握创建数据库用户及其角色赋予方法•掌握系统数据库备份方法•掌握系统数据库恢复方法一、项目案例——成绩管理系统本节结合一个“成绩管理系统”项目案例的数据库管理，学习PostgreSQL数据库管理操作，理解与掌握本章所学习的安全管理、数据库备份与恢复管理等技术方法。
成绩管理数据库GradeDB •学生信息表（Student）•教师信息表（Teacher）•课程信息表（ Course）•成绩记录表（Grade）二、数据库角色管理根据成绩管理系统需求，本系统设计三类数据库角色：学生（R_Student）、教师（R_Teacher）和教务人员（R_TAdmin）。
CREATE ROLE "R_Student" WITH --创建学生角色LOGINCONNECTION LIMIT -1;CREATE ROLE "R_Teacher" WITH --创建教师角色LOGINCONNECTION LIMIT -1;CREATE ROLE “R_TAdmin” WITH --创建教务人员角色LOGINCONNECTION LIMIT -1;角色创建SQL程序执行三、数据库角色权限设计与实现赋予各个角色的数据库对象访问权限SQL程序GRANT SELECT ON STUDENT TO "R_Student";GRANT SELECT ON TEACHER TO "R_Student";GRANT SELECT ON COURSE TO "R_Student";GRANT SELECT ON GRADE TO "R_Student";GRANT SELECT ON STUDENT TO "R_Teacher";GRANT SELECT ON TEACHER TO "R_Teacher";GRANT SELECT ON COURSE TO "R_Teacher";GRANT SELECT,INSERT,UPDATE,DELETE ON GRADE TO "R_Teacher"; GRANT SELECT,INSERT,UPDATE,DELETE ON STUDENT TO "R_TAdmin"; GRANT SELECT,INSERT,UPDATE,DELETE ON TEACHER TO "R_TAdmin"; GRANT SELECT,INSERT,UPDATE,DELETE ON COURSE TO "R_TAdmin"; GRANT SELECT,INSERT,UPDATE,DELETE ON GRADE TO "R_TAdmin";角色授权SQL程序执行四、数据库用户管理本系统创建一个学生用户（StudentUser）和一个教师用户（TeacherUser），并分别赋予学生角色（R_Student）和R_Teacher），初始密码为123456。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
() 阿里巴巴数据库团队
提纲
• IDB产品介绍 • PostgreSQL实践原因 • 实践步骤 • 实践总结
IDB 定位
淘宝
168 8
支付 宝
余额 宝
高德 地图
阿里 云
菜鸟 物流
天猫
速卖 通
口碑
芝麻 信用
UC
钉钉
…
用户服务层---数据库服务平台(iDB)
资源 申请
•PostgreSQL需要联合使用 with 查询, update returning, insert来进行选择性更新或者插入。
with data as (select #id#::integer id,#name#::varchar name,#status#::varchar status),
• Bitand(A,B) ：替换成运算符 & , A & B • sysdate： now, current_timestamp • NVL ： coalesce • DECODE：case when then else end
SQL改造
– Oracle Merge Into 插入或者更新的SQL改造
SQL改造
– 函数替换：将Oracle部分PG不支持的函数替换 为PG的等价函数。
• 分组合并：Oracle中wm_concat(xxx)转换成PG中的 string_agg(xxx,‘,’) over (partition by X oder by Y)来实 现。
• Oracle中的regexp_replace(xxx,reg)在PG需要加上第 三个参数 “,”
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.* )
SQL改造
– 常量参数需要进行强制类型转换，否则类型则 unknown，框架无法自动解析类型。
• select id, ‘new’ ::varchar status from t;
这样才能保证应用程序中获取到的status数据类型为 String。 不强制转换获取到的是Object 对象，会导致JavaBean或者 DO对象的属性值设置失败。
迁移方案。 • 应用改造： SQL改造，代码改造。 • 数据迁移：结构迁移，数据迁移。 • 回归与测试：功能回归、性能测试。 • 性能调优：针对上线后的性能问题进行分
析和优化。
SQL改造
– 时间间隔
• Oracle时间相减得到间隔，单位为天。PG时间相减 得到 interval值，需要转换为具体的时间值。
先后实现对Oracle,MySQL,Oceanbase,SqlServer,
PostgreSQL等数据库的查询，数据变更，结构变更 的支持。并且支持分库分表的操作。
2010年淘宝启动“去IOE”项目
PostgreSQL实践原因
• IDB帮助集团实现了去O，但是自己还在使 用Oracle。
• 2014年底，这台Oracle服务器已经集团仅
SQL改造
– 分页
• Oracle
SELECT * from ( SELECT rownum AS rn, t.* FROM t WHERE condition AND rownum <={end} ) where rn >= {start}
• PG
SELECT * from t where condition offset {start} limit {pagesize}
insert into my_user(id,name,status) select nextVal('seq_my_user'),,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 布尔值 char(1) ‘Y’/’N’
• 转换成 PG 类型 boolean
– Clob 大字段类型
• 转换成PG类型varchar
– 虚拟列
• 增加实际列或者使用视图–Oracle \u0000字符
• PG不允许存储，过滤掉再保存
剩的几台服务器之一，需要承担高昂的 License费用。
• 去O势在必行，用什么来替换它呢？
– MySQL? 造。
需要对应用和SQL做大量改
– 达梦? 功能与Oracle兼容，稳定性达不到要求
– PostgreSQL?
改造成本低，安全稳定
实践步骤
• 实践评估：可行性分析、工作量评估。 • 制定方案：确定迁移的重点和难点，制定
• Oracle中sysdate + interval xxxx unit(precision) 类型 的数据，PG中用now()+ ‘时间间隔字符串’ 的方式来 实现。
– Now() + ‘1 day’ – Now() + ‘14400’::interval
– 别名设置：
• 避免使用关键字role,data,label,type,name 作别名， 或者加上as 关键字
数据 访问
开发 设计
生产 变更
分库 分表
SQL 审核
流程 管控
权限 管控
基
础
技 术
AliSQL
OceanBase PostgreSQL MongoDB Oracle SQLServer ……
层
运
维 组 件
HA
监控 告警
性能 压测
自动化 调度
元数 据
备份 恢复
安装 配置
数据 质量
安全 审计
…
层
IDB是数据库团队自主研发的一个数据库服务产品， 是集团去O的重要实施平台。