adsas 数据库是用于广告买量数据分析;在17年由 Oracle 迁移到 PostgreSQL。现把之前的迁移笔记整理下。本次迁移表91个;存储过程21个;数据库大小2G。
1. 准备PostgreSQL数据库
安装PostgreSQL数据库 参考 PostgreSQL简单安装手册 ;
在安装PostgreSQL数据库之前;首先要对数据库硬件要做基准评测;会影响是否能取代Oracle服务器。安装完成之后;需要创建同名 数据库/用户/schema。
2. 配置Ora2Pg迁移工具
Ora2Pg是一个免费的工具,用于将Oracle数据库迁移到PostgreSQL兼容的模式。它连接您的Oracle数据库,自动扫描并提取它的结构或数据,然后生成可以装载到PostgreSQL数据库的SQL脚本。Ora2Pg可以从逆向工程Oracle数据库到大型企业数据库迁移,或者简单地将一些Oracle数据复制到PostgreSQL数据库中。它非常容易使用,并且不需要任何Oracle数据库知识,而不需要提供连接到Oracle数据库所需的参数。
3. 利用Ora2Pg导出数据并导入PostgreSQL
[postgres@oracle166 config]$ tnsping ora165 TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 12-OCT-2018 16:45:22 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ******)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl))) OK (10 msec)
ORACLE_DSN dbi:Oracle:ora165 ORACLE_USER adsas ORACLE_PWD easouadsas166 LOGFILE /home/postgres/log/Ora2Pg.log SCHEMA adsas TYPE TABLE SEQUENCE COPY PG_NUMERIC_TYPE 0 PG_INTEGER_TYPE 1 DEFAULT_NUMERIC float SKIP fkeys pkeys ukeys indexes checks NLS_LANG AMERICAN_AMERICA.UTF8 OUTPUT adsas.sql
[postgres@oracle166 config]$ ora2pg -c ora2pg.conf
[postgres@oracle166 ]$ psql adsas adsas psql (9.6.0) Type "help" for help. adsas=> i /home/postgres/config/adsas.sql
4. PostgreSQL创建oracle兼容函数(Orafce)
在改写存储过程/函数之前;先安装Oracle兼容函数(Orafce);它会让你觉得PostgreSQL跟Oracle写法没什么区别;PostgreSQL是和Oracle最接近的企业数据库,包括数据类型,功能,架构和语法等几个方面。甚至大多数的日常应用的性能也不会输给Oracle。但是Oracle有些函数或者包,默认PostgreSQL是没有的,需要安装orafce包来实现这些兼容性。
现在orafce已经包含了如下内容。
1. 类型 date, varchar2 and nvarchar2 2. 函数 concat, nvl, nvl2, lnnvl, decode, bitand, nanvl, sinh, cosh, tanh and oracle.substr 3. dual 表 4. package : dbms_output utl_file dbms_pipe dbms_alert PLVdate PLVstr and PLVchr PLVsubst DBMS_utility PLVlex DBMS_ASSERT PLUnit DBMS_random
5. 安装PostgreSQL中调试工具plpgsql存储过程
这个我感觉没多大用;可以忽略!
6. 存储过程/函数改写过程
A/G
7. Oracle迁移PG代码优化
经存储过程/函数改写完成;代码要跟Oracle等同;执行时长跟Oracle上面的时长是否差不多;发现在Oracle上面执行 12s;而改写的代码需要 256s;相差21倍之多。这差距不是一般的大。同样的代码可能在Oracle执行效果要好;这跟数据库底层代码有关。
7.1 insert ... on conflict 妙用
经核查;其中有条SQL花了将近240s的时间。
INSERT INTO TBL_AD_CLICK_LOG SELECT * FROM TMP_AD_CLICK_LOG WHERE (ES_APP_ID, IDFA_SUM) NOT IN (SELECT ES_APP_ID, IDFA_SUM FROM TBL_AD_CLICK_LOG);
表TBL_AD_CLICK_LOG: 存放所有已排重的点击数;数据量有40万条。
表TMP_AD_CLICK_LOG: 存放当天已排重点击数;
该SQL目的就是避免重复的 idfa_sum 在插入表 TBL_AD_CLICK_LOG;以达到去重的效果。该SQL在Oracle上运行时长1s左右。然而在PG上运行时长将近240s。效率差。与这SQL等价且高效的SQL。
INSERT INTO TBL_AD_CLICK_LOG SELECT * FROM TMP_AD_CLICK_LOG on conflict (ES_APP_ID, IDFA_SUM) do nothing;
使用该SQL之前需创建唯一索引;
CREATE UNIQUE INDEX IND_AD_CLICK_IDFA ON TBL_AD_CLICK_LOG(ES_APP_ID, IDFA_SUM);
优化后;在PostgreSQL执行一次调度不到5s;相比Oracle的执行一次调度需要12s还短。
7.2 高效删除重复数据
DELETE FROM TBL_AD_CLICK_LOG WHERE ctid NOT IN (SELECT MIN(ctid) FROM TBL_AD_CLICK_LOG GROUP BY ES_APP_ID, IDFA_SUM);
优化的目的:用最小的时间达到相同效果。
delete from tbl_ad_click_log where ctid = any(array(select citd from (select "row_number"() over(partition by es_app_id, idfa_sum order by id) as rn,ctid from tbl_ad_click_log) as ad where ad.rn > 1));