从 Oracle 到 PostgreSQL ，乐动体育官方保险公司迁移实践

Oracle ACE-A，Oracle 10g OCM，SOUG（中国南方ORACLE用户组）联合发起人，ITPUB 专家。目前任职于某公司，担任技术经理职位，负责公司的软件基础设施的建设和维护工作。

PostgreSQL VS Oracle

站在开发和应用的角度看，乐动体育官方的LD90.VIP底层数据库搭建再好，但如果应用在上面跑不起来，跑的不好，新数据库平台就会变成一座鬼城。

今天，我分享的内容分四大部分，一是技术选型。第二、三是今天的重点，怎么让应用真正在新的数据库平台上面跑起来，这是要探究的一个问题。会涉及到一些比较细节的东西，尤其对于开发人员可能会更加关注一些，当然对于DBA来说，也是需要去注意的。

金融行业技术选型有个特点，当你向公司推荐一个数据库产品时，领导肯定会问个问题，同行业有没有在使用的？

我为什么会选择PostgreSQL？一个很重要的原因是不少同行在用，比如，平安科技。那抛开同行业，PostgreSQL自身有哪些优势？
我们做了PostgreSQL与Oracle的对比，就单体模式来说， PostgreSQL完全不输给Oracle，可以做到完全实时的同步，单体保持数据同步方面一点问题都没有。
分片模式， Oracle提供了一个Sharding模式。相对应的PostgreSQL有XC/XL解决方案。当然咯，这个方案也不是完美的，比如说GTM可能会成为性能的瓶颈点，很有可能会成为一个瓶颈。
另外，Oracle有个非常强大的功能RAC，这个在PostgreSQL里是没有的。不过PostgreSQL有读写分离的解决方案，在读多写少的场景下，能达到不错的吞吐量。

目前，不管是商业的还是开源的数据库，基本上逃不脱这四种架构。

看下来，PostgreSQL和Oracle是非常像的，包括架构以及数据安全方面，都与Oracle非常像。这也是我们选择PostgreSQL重要的原因。同时，PostgreSQL提供了丰富的可选架构，能满足各种不同场景。PostgreSQL有效遵循SQL标准，让应用迁移难度降低，PostgreSQL有丰富的可选组件，极易扩展等。

在这里顺便也说一下我的观点，现在都在说分布式，但分布式真的适合所有场景吗？我看未必。而且什么是分布式，我觉得这个问题也值得深入思考下。

让应用先跑起来

能不能让应用正确的跑起来，这是事关生存的问题。事关你新系统能不能在公司里面生存下来的问题，这个是非常关键的问题。

下面，涉及到很多往PostgreSQL迁应用时遇到的问题，总结如下：

1、 字符集问题：

PostgreSQL服务端是不支持GBK的，我们用UTF8。PostgreSQL还有个编码EUC CN，这个我们之前测过很多次，有很多生僻字是无法编码的。 比如“瑄”在EUC_CN下就无法编码。因此，不推荐使用EUC_CN。

2、多行注释问题：

/* some comments
/* other comments
/*****************/
• 上述注释在Oracle中是合法的
• 在POSTGRESQL中是非法的
• – 合法的PostgreSQL注释格式：
• – This is a standard SQL comment
• / multiline comment
• * with nesting: / nested comment */
• */
• 可以使用PLY(Python-Lex-Yacc)将注释自动改写掉
3、NUMERIC类型问题：
• 上述声明在Oracle中是合法的
• 但在POSTGRESQL中是非法的
• POSTGRESQL不支持负值的scale
• 也不支持scale大于precision
• 负值scale的解决方法：
{ 使用触发器，在触发器中调用round函数 }
SELECT round(123.6, -2);
round

100
(1 row)
scale大于precision的解决方法：
{ NUMBER(2,3) => NUMERIC(3,3) }
{ 增加CHECK （col < 0.1） }
DECLARE
c NUMBER := 1;
pi NUMBER := 3.142;
r NUMBER := 10;
BEGIN
FOR i IN 1…10000 LOOP
c := pi * (r * r) + (mod(r, c) * pi + i);
END LOOP;
END;
/
上述代码在Oracle中是没有任何问题
DO $$DECLAREcNUMERIC:=1;piNUMERIC:=3.142;rNUMERIC:=10;BEGINFORiIN1..10000LOOPc:=pi\ast (r\ast r)+(mod(r,c)\ast pi+i);ENDLOOP;END;$$ LANGUAGE plpgsql;
ERROR: value overflows numeric format
解决方法: 显式指定NUMERIC的精度
DO $$DECLAREcNUMERIC(32,2):=1;piNUMERIC:=3.142;rNUMERIC:=10;BEGINFORiIN1..10000LOOPc:=pi\ast (r\ast r)+(mod(r,c)\ast pi+i);ENDLOOP;END;$$ LANGUAGE plpgsql;
4、VARCHAR类型问题
上面是经常碰到的VARCHAR问题，值已经超出了目标长度，肯定会报错。但在PostgreSQL里面不一样，会截断但不报错，这要特别注意，因为没有报错，你的应用如果没有注意到这个问题，很有可能你的数据就丢失了，计算结果就出错了，那这个就是很严重的问题了。

5、CHAR类型问题
在PostgreSQL中，CHAR类型的长度是实际有效字符的长度，这个和Oracle很不一样。这个在应用中如果不注意的话，就会报很多错误。而且,有时候查起来非常困难。当然我们也可以通过重载函数的方式来模拟Oracle中的行为。

6、SEQUENCE最大值问题

•POSTGRESQL的SEQUENCE最大值：9223372036854775807（bigint）

•而Oracle中的SEQUENCE最大值可达28位十进制值•一般情况下POSTGRESQL的SEQUENCE是足够的

•但可能也存在一些特殊情况

{LISCODE.SEQ_YBTBATTRANS_ID ‘10000000000000072561’ }
{ 该值明显已超出最大值}
{ 使用NUMERIC类型，配合触发器使用}
通常情况下PostgreSQL中SEQUENCE足够使用。但上面这个值已经超出最大值，目前，我的解决办法是把它用NUMERIC类型配合触发器使用，用触发器模拟序列类型，如果你是频繁插入，性能下降会非常严重，这是需要注意的问题。

7、类型转换

CREATE TABLE t1 (id VARCHAR(32));

SELECT * FROM t1 WHERE id = 27;

ERROR: operator does not exist: character varying = integer
LINE 1: SELECT * FROM t1 WHERE id = 27;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
CREATE CAST (varchar AS integer)
WITH INOUT
AS IMPLICIT;

SELECT * FROM t1 WHERE id = 27;

id

(0 rows)

EXPLAIN verbose SELECT * FROM t1 WHERE id = 27;

                    QUERY PLAN                        

---

Seq Scan on public.t1 (cost=0.00…22.95 rows=4 width=82)
Output: id
Filter: ((t1.id)::integer = 27)
(3 rows)
• 善用CAST

• 根据自己的需求，绘制类型转换矩阵

如果对Oracle熟悉，就会知道Oracle是由明确的类型转换矩阵的，在PostgreSQL里，这方面就差一点。但PostgreSQL提供了自定义创建CAST的特性。在我们实际迁移过程当中，如果你能够把CAST利用好是能解决很大一部分问题的。

7、操作符重载
比如说SELECT 3/5是个雷，除了它本身3对应的是INT4，5对应的也是INT4，所以它本身是一个非整型数字，但是，它也是会被截断掉。进行一个重载就可以解决，我们重新定义一个函数，可以用系统的numeric div，这样用户不需要做任何的修改就可以达到跟Oracle一样的效果。

总结一下，操作符重载是PostgreSQL提供的一个非常好的特性，善用操作符重载可以解决一些兼容性问题，以及前面说的CAST是可以解决很多监管系统问题的，在这个过程中也是有很多问题需要注意的。

第一、POSTGRESQL本身它设置了很多类型转换和操作符，这个一定要考虑是否有冲突。

第二、类型转化的操作也需要相互配合，因为在调用操作符时，是要判断类型转换是否需要自己去做的，所以，这两个是需要密切配合才能完美的使用好。

8、子查询

Oracle当中子查询不需要别名alias，但在PostgreSQL当中是不行的。
9、SELECT表达式别名问题

下种这种语句在Oracle里面没有任何问题，但在PostgreSQL就会出问题。