|
一、如何计划迁移数据库 将数据库从Oracle迁移至PostgreSQL数据库，需要考虑的有很多。但是有一点是不变的，或者说是目的：要保证数据库迁移后，系统功能能够正常使用，或对业务逻辑尽可能少的修改（修改业务逻辑可能会出现意想不到的连锁问题）。那么，就需要想办法将原有的Oracle数据库整体结构，尽可能的在PostgreSQL上还原，这样才能保证不必大量调整业务逻辑。 明确目的之后，可以具体分析系统现状了：该系统所使用的是Oracle数据库，但Orale数据库是一个总库，其他系统使用的表也在该库中，使用属主区分表。所以该系统除了自身使用的表之外，还使用了一部分公共表。这样一来，在迁移数据库之前，除了统计系统本身的表外，还要进行公共表解耦。 其次，如果要迁移到PostgreSQL数据库，那么建表语句就必须按照PostgreSQL的语法写，即调整建表脚本。除了建表脚本，原Oracle数据库还有大量的序列、索引、存储过程等，这些也需要移植过去。然后就是如何保证数据的完整性和时效性？ 总结以上的问题要点：
二、统计系统表及表功能 统计系统使用的表和表功能，主要是为了明确需要迁移的表，以及表所负责的功能（方便数据迁移时，确定数据导入的先后顺序）。这一步很简单，但是比较耗费时间，需要耐心和细心。最好能够一起统计表使用到的序列和索引等，方便之后的迁移操作。 三、解耦公共表 之所以需要解耦公共表，原因有二：
根据以上原因，最终采用的方案是：由表属主的系统，提供给我们这个迁移系统所需要的接口，我们由原来的直接查询其他系统的表，转为调用其他系统提供的接口来查询需要的数据。这么做的好处就是，将原来的公共表与本系统进行解耦，同时当该系统进行数据库迁移后（其他系统仍是Oracle数据库），还能够查询到需要的数据，不会影响其他系统运行。 需要注意的是：在进行表解耦之前，需要先统计该系统使用的外部表，包括本系统使用外部表的哪些关联字段、外部表在本系统中的作用等信息。然后根据这些信息，与其他系统负责人沟通并开发接口、联调接口等。 四、建立数据库 在各环境的数据库正式迁移之前，可以先在本地的PostgreSQL数据库，建立一个新的系统数据库用于本地系统功能和业务测试。当本地测试的差不多了，就可以把脚本提供给DBA，交由DBA去创建各环境的PostgreSQL数据库。至于怎么在本地安装PostgreSQL数据库，这里就不赘述了。 五、迁移表结构（包括序列、索引等） 迁移表结构涉及的东西比较多，可以参考以下的迁移步骤：
编写CREATE TABLE脚本的时候，除了需要注意建表的语法之外，还有Oracle和PostgreSQL两种数据库不同字段类型的替换。 例如： 1）varchar类型和character varying类型 Oracle中，定长的char类型最大长度是2000，变长的varchar类型长度最大是2000、varchar2类型最大长度是4000。 PostgreSQL中，定长的char类型和变长的varchar类型的最大长度都是1G（10485760），变长的text类型则没有长度限制。 在PostgreSQL中，text、char和varcahr的性能是没有区别的，大多数情况下使用text和varchar比较好；character是全称，char是别名（简称）；character varying是全称，varchar是别名（简称）;Oracle中较长的字段，可以用PostgreSQL中的character varying替代，长度是没问题的。 2）number类型和numeric类型 Oracle中，使用number类型来保存对精度要求高的数值，比如货币、金额等。对精度要求不高或没有要求的尽量不要用numeric类型，因为它的效率很低。 PostgreSQL中，与number类型对应的是numeric类型。 3）date类型和timestamp类型 Oracle中，使用date类型来保存时间数据，如创建时间、修改时间等。 PostgreSQL中，与date类型对应的是timestamp类型。 其他数据类型不再一一介绍，可以自行查阅资料来获取相关知识。 建表语句例子： - 删减了一部分关键字段，只保留了基本的字段，同表一起创建的还有该表使用的索引 CREATE TABLE cp_opr.user_role ( id varchar(20) COLLATE pg.'default' NOT NULL, role_id varchar(50) COLLATE pg.'default' NOT NULL, department_chinese_name varchar(255) COLLATE pg.'default' NOT NULL, user_name varchar(30) COLLATE pg.'default' NOT NULL, department_code varchar(30) COLLATE pg.'default', user_id varchar(30) COLLATE pg.'default' NOT NULL, state varchar(2) COLLATE pg.'default' NOT NULL, flag varchar(2) COLLATE pg.'default' DEFAULT '1'::varchar, manager varchar(30) COLLATE pg.'default', create_by varchar(100) COLLATE pg.'default' DEFAULT 'complaint'::varchar, create_date timestamp(0) without time zone NOT NULL DEFAULT now(), update_by varchar(100) COLLATE pg.'default', update_date timestamp(0) without time zone, CONSTRAINT user_role PRIMARY KEY (id) ); COMMENT ON TABLE cp_opr.user_role IS '用户角色表'; COMMENT ON COLUMN cp_opr.user_role.id IS 'ID'; COMMENT ON COLUMN cp_opr.user_role.role_id IS '角色ID'; COMMENT ON COLUMN cp_opr.user_role.department_chinese_name IS '机构名称'; COMMENT ON COLUMN cp_opr.user_role.user_name IS '用户名称'; COMMENT ON COLUMN cp_opr.user_role.department_code IS '机构'; COMMENT ON COLUMN cp_opr.user_role.user_id IS '用户ID'; COMMENT ON COLUMN cp_opr.user_role.state IS '状态'; COMMENT ON COLUMN cp_opr.user_role.flag IS '有效标志'; COMMENT ON COLUMN cp_opr.user_role.manager IS '角色的管理员'; COMMENT ON COLUMN cp_opr.user_role.create_by IS '创建人'; COMMENT ON COLUMN cp_opr.user_role.create_date IS '创建日期'; COMMENT ON COLUMN cp_opr.user_role.update_by IS '修改人'; COMMENT ON COLUMN cp_opr.user_role.update_date IS '修改日期'; -- 不能创建与表同名的索引 -- CREATE INDEX CREATE INDEX index_role_dep_code ON cp_opr.user_role USING btree(department_code) ; CREATE INDEX index_role_id ON cp_opr.user_role USING btree(role_id) ; CREATE INDEX index_role_user_id ON cp_opr.user_role USING btree(user_id) ; Oracle和PostgreSQl的序列创建方法大致上一样，但是会有一些细微的差别。需要注意的是，新的序列在创建的时候，高速缓存值最好和原序列的一致，否则可能会发生序列跳跃（Oracle的最低可为0，PostgreSQl的最低为1）。 -- 以下是三个序列的创建语句，当前设置的初始值为1，当迁移后需要查询原序列的当前值，并修改现在的初始值1 -- CREATE SEQUENCE create sequence cp_opr.sq_tauditdetail_id minvalue 1 maxvalue 9223372036854775807 start with 1 increment by 1 cache 10; -- CREATE SEQUENCE create sequence cp_opr.seq_complaint_email_log_id minvalue 1 maxvalue 9223372036854775807 start with 1 increment by 1 cache 10; -- CREATE SEQUENCE create sequence cp_opr.seq_t_complaint_mobilesms_log minvalue 1 maxvalue 9223372036854775807 start with 1 increment by 1 cache 10; 六、导入原有的表数据 导入Oracle数据库的历史数据，我们采用的方案是写批处理，然后跑批导数据。因为是本地和sit环境，所以数据量大的表可以适当删掉些无用的旧数据，以提升导入速度，方便测试，这里就不上批处理的代码了。 导入数据的时候，需要注意：数据导入的先后顺序，因为有的表数据通过逻辑外键关联，这些逻辑外键不允许为空。如果顺序乱了，则可能导入数据的时候报错。 七、改SQL语法 这一步主要改的是系统中的SQL语句，因为PostgreSQl的语法和Oracle的差异还是不小的，所以必须要进行调整。 调整的内容可以分为2部分：
语法需要注意的地方，主要是字段类型的转换，这是因为PostgreSQL的语法要求的。 字段类型的指定（转换）需要用“::”符号，例如： -- 不仅可以指定WHERE条件中的子类类型，还可以指定SELECT查询字段的类型 SELECT T.BRANCH_ID::VARCHAR FROM T_xxx T WHERE T.BRANCH_ID <> 1 ]]> AND T.PARENT_ID = 1 AND T.BRANCH_ID::VARCHAR <iterate prepend='in' conjunction=',' open='(' close=')'> #deplist[]# </iterate> -- INSERT语句用的最为频繁 INSERT INTO T_C_C (CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE, CALENDAR_ID, CALENDAR_DATE, IS_WORKING_DATE, YEAR, MONTH, DAY, QUARTER, WEEK) VALUES(#createdBy:VARCHAR#, now(), #updatedBy:VARCHAR#, now(), #calendarId:VARCHAR#, #calendarDate#::timestamp, #isWorkingDate:VARCHAR#, substr(to_char(#calendarDate#::timestamp,'YYYYQMMDD'),1,4), substr(to_char(#calendarDate#::timestamp,'YYYYQMMDD'),6,2), substr(to_char(#calendarDate#::timestamp,'YYYYQMMDD'),8,2), substr(to_char(#calendarDate#::timestamp,'YYYYQMMDD'),5,1), to_char(#calendarDate#::timestamp,'ww')); -- UPDATE语句中，不能够使用表别名 -- 特殊的时间加减法运算 select CURRENT_TIMESTAMP::TIMESTAMP + INTERVAL '5 day'; -- 分页查询语法 SELECT * FROM T_xxx LIMIT 20 OFFSET 0; -- 左右连接查询 需要使用LEFT OUTER JOIN 或 RIGHT OUTER JOIN SELECT * FROM T_A a LEFT OUTER JOIN T_B b WHERE a.id=b.id; -- 子查询必须要有别名 SELECT a.id,a.name,a.phone FROM(SELECT * FROM T_XXX WHERE ID in('1','2','5'))a; -- 插入空值NULL的时候，必须明确指定为NULL，而不能和Oracle一样是'' 以下列举一部分，更多的函数可以自行百度。 八、保证数据的时效性和完整性 数据的完整性，主要依靠的是：
测试导入后的数据是否完整也比较简单：
这个主要涉及的是，当生产环境数据库由Oracle切换到PostgreSQL的时候，需要一定的时间去导入原有的数据。那么导入数据的这段时间中，产生的新数据该如何导入新数据库？如果直接导入，会不会与原有的数据关联不上？除此之外，还会有什么意想不到的问题？ 比较遗憾的是，因为本次迁移的系统属于比较边缘的系统，所以最终采用的方案是：生产数据库切换的时候，将服务器关闭一段时间，阻止新数据的产生，直到旧数据迁移完毕再重启服务器。 除了本次迁移的系统，还有很多系统也需要迁移，如果那些系统迁移的时候遇到了这个问题，并有了更好的方案，再来更新。 九、其他 前几天这个切换生产数据库的项目正式上线了，还是在深夜进行切换的。之所以是深夜进行生产数据库的切换，主要考虑的问题是：因为我们系统的生产数据库在切换的时候，没有很好的关于切换时产生的新数据如何导入新数据库的解决方案，所以需要对这个问题进行一些妥协。 最后采用的方案是：将生产数据库切换时间定在夜里十点到凌晨三点（通过数据分析，发现这个时段产生的新数据比较少），凌晨三点后发布切换数据库的新版本系统。而在切换数据库时段产生的新数据，则由专门的人员进行手动记录案件，等到新版本发布之后，再将数据手动录入到系统中。 比较有趣的是，原计划需要用5个小时完成数据库的切换工作，实际上只用了大概3个小时就搞定了。之所以这么快，一方面是因为在正式的切换数据库之前，已经在预生产环境做过两次模拟切换了，大家对于切换流程都比较熟悉了。另一方面就是大家都困，都想早点回去睡....... 不过值得总结的是，在对生产数据表导完数据的时候，需要对系统中重要的数据表的数据量进行比较，也就是看看Oracle数据库的数据在导入PostgreSQL数据库之后有没有丢失数据。还有就是在数据量检测完成，发布版本之后，还需要导入一些新案件进行流程测试，检测是否有BUG出现。 虽然当天夜里已经对系统进行了一些测试，但是第二天白天拉取日志的时候，还是有一部分异常信息，主要就是一两条很隐蔽、难以测试的SQL语句类型转换异常。将这些问题处理之后，第二天下班之后又重新发了一个紧急版本。至此，数据库迁移的工作算是圆满结束了。 |
|
|
