华为云rds-PostgreSQL增强版替换Oracle常见问题以及解决方案

举报
PostgreSQL 发表于 2019/09/03 17:34:58 2019/09/03
【摘要】 华为云rds-PostgreSQL增强版提供兼容oracle功能，目前已经做到兼容oracle 90%的语法，在使用PostgreSQL增强版替换oracle的过程中，可能遇到一些问题，这里总结一下，并给出解决方案。1.1 JDBC驱动 基于oracle数据库的应用本身使用oracle的JDBC驱动，换成postgresql增强版需要将JDBC驱动换成postgresql的驱...

华为云rds-PostgreSQL增强版提供兼容oracle功能，目前已经做到兼容oracle 90%的语法，在使用PostgreSQL增强版替换oracle的过程中，可能遇到一些问题，这里总结一下，并给出解决方案。

1.1      JDBC驱动

    基于oracle数据库的应用本身使用oracle的JDBC驱动，换成postgresql增强版需要将JDBC驱动换成postgresql的驱动

1.2      事务处理

Oracle数据库，在同一个事务中的多个语句，如某个语句执行出错，该语句不影响其他语句的执行，如事务提交，则执行成功语句会持久化到DB中。PG数据库，在同一个事务中的多个语句，如某个SQL语句执行出错，则就算在其后执行commit，事务也会回滚。如在该出错语句之后执行其他DML语句，则会报错。因此Oracle捕获异常后是重做异常sql，而PG是先回滚事务，然后重做整个事务。

1.3      查询结果预期有序一定要加order by

oracle数据库在数据量少，查询结果集小，并且数据没有更新时，结果集顺序相对稳定，但是不能绝对保证顺序，当结果集、数据量增大、数据频繁更新时，结果集一定会紊乱，PostgreSQL同理，在一定情况下PG这个情况更容易出现，因此查询结果有顺序的预期一定要加order by

1.4      数据库对象大小写

oracle数据字典默认大写，PG数据字典默认小写，因此创建和引用对象时不加引号，如果涉及特殊字符，PG创建数据库对象时要小写，这样才不区分SQL的大小写

例如：oracle

 

SQL>   drop table test;

create   table "TEST"(ID varchar(20));

insert   into test(id) values('a');

insert   into "TEST"(id) values('b');

Table   dropped.

 

例如：postgresql

test=>   drop table test;

DROP TABLE

test=> create   table "TEST"(ID varchar(20));

CREATE   TABLE

test=>   insert into test(id) values('a');

ERROR:  relation "test" does not exist

LINE 1:   insert into test(id) values('a');

                    ^

test=>   insert into "TEST"(id) values('b');

INSERT 0 1

 

1.5      列(别)名为关键字

Oracle和PG的数据字典关键字集合是有差异、有交集的。Oracle中比如text这样的关键字可以直接作为列的别名，比如：select xx text from t，目前我们已经做了很多关键字直接作为别名的兼容，但是目前还是没有完全支持，因此建议写sql或者改问题过程中逐步加as，比如select xx as text from t

 

1.6      sequence范围不一致

修改sequence maxvalue的值
PG范围为：1到9223372036854775807
oracle范围为：1到9.99...9 x 10125

 

1.7      同义词

PG中没有同义词，自动创建为视图，转换过来的视图名称与存在的表名相同，需要修改视图名称。

1.8      float类型精度

oracle支持float(126)，PG最大支持到float(53)，单在一般情况下PG的精度已经够用了，开发中遇到此类问题可以直接修改。

1.9      primary等关键字不能用做列名

oracle支持primary作为列名，PG中要用primary作为列名的话需要加上双引号，例如：

create   table t1(“primary” int);

建议最好不用用primary等关键字做为列名，可以修改为primary_key就没有问题了。

1.10   不能modify带有view的列

Oracle可以直接modify带有view的列，PG中必须删除view才可以modify表，建议用户先删除view再modify表，最后重建view

1.11   在view上创建primary key

Oracle可以在view上直接创建primary key等约束，PG中不能在view上创建约束，建议用户直接在表上创建约束。

1.12   Data类型差值不一致问题

Oracle执行以下语句返回结果

SQL>   select to_date('2019-06-06 11:41:07','yyyy-MM-dd HH24:mi:ss')-to_date('   2018-06-06 11:06:08','yyyy-MM-dd HH24:mi:ss') from dual;

TO_DATE('2019-06-0611:41:07','YYYY-MM-DDHH24:MI:SS')-TO_DATE('2018-06-0611:06:08

--------------------------------------------------------------------------------

                                                                                                                                     365.024294

PG执行以下语句返回结果：

postgres=#   select to_date('2019-06-06 11:41:07','yyyy-MM-dd HH24:mi:ss')-to_date('   2018-06-06 11:06:08','yyyy-MM-dd HH24:mi:ss') from dual;

     ?column?     

-------------------

 365 days 00:34:59

(1 row)

PG解决方案，在结果上加上一个0，让数据不做转换

postgres=#   select to_date('2019-06-06 11:41:07','yyyy-MM-dd HH24:mi:ss')-to_date('   2018-06-06 11:06:08','yyyy-MM-dd HH24:mi:ss') + 0 from   dual;

     ?column?     

-------------------

 365.024293981481

(1 row)

 

1.13   使用pg_dump导出的线下库，在导入到PG线上库时报错“must member of role eas850pgnew”

原因分析：用户线下导出库是用用户eas850pgnew，查看了导出dump出的脚本，每个对象在创建完后都会有alter table xxx owner to eas850pgnew。正是这句报的错，因为用户使用了root账户创建了database，并导入到owner为root的database中，所有表默认owner都是root，而root不是超级权限，在执行alter table xxx owner to eas850pgnew时报错。

解决方法：

a.       使用root创建数据库test

create database test;

b.       连接上test库创建新用户eas850pgnew

create role eas850pgnew createdb createrole login password 'Perftest1!'

c.       使用新用户eas850pgnew创建目标数据库eas850pgnew_ora

crete database eas850pgnew_ora;

d.       使用pg_admin通过用户eas850pgnew导入库到目标库eas850pgnew_ora中

                       

1.14   时间格式不一致问题

可以按照用户使用的时间格式来设置nls_timestamp_format或则nls_date_format参数

1.15   PG增强版不能在postgres库中创建role问题

a.       连接数据库postgres，创建一个test库

b.       连接test库，在test库中执行创建用户操作

1.16   更改数据库中所有对象的owner

从oracle中迁移过来的数据库默认owner是root，需要将owner修改为用户自己新建的用户，修改方法如下：假设要将root下的test库中所有对象owner从root改为用户post

c.       用post账户登录test库执行：grant post to root;

d.       用root账户登录test库执行：reassign owned by root to post;

1.17   PG增强版中定长字符串长度不够会补充空格

例如：char(10)类型的列，在插入数据后，长度不够会补充空格，再如，以下存储过程中用到了定长字符串，在增强版上不能返回正确的值

 

CREATE OR   REPLACE FUNCTION dateadd(difftype character varying, incrementvalue numeric,   inputdate timestamp with time zone)

  RETURNS timestamp without time zone AS

$BODY$

DECLARE

   YEAR_CONST Char(15) := 'year'; 

   MONTH_CONST Char(15) := 'month'; 

   WEEK_CONST Char(15) := 'week'; 

   DAY_CONST Char(15) := 'day'; 

   HOUR_CONST Char(15) := 'hour'; 

   MIN_CONST Char(15) :='minute'; 

   SECOND_CONST Char(15) :='second'; 

   dateTemp timestamp; 

   intervals interval; 

BEGIN

   if inputdate is null then return null ;   end if;

   IF lower($1) = lower(YEAR_CONST) THEN 

       select cast(cast(incrementvalue as   character varying) || ' year' as interval) into intervals;  

   ELSEIF lower($1) = lower(MONTH_CONST)   THEN 

       select cast(cast(incrementvalue as   character varying) || ' month' as interval) into intervals; 

   ELSEIF lower($1) = lower(WEEK_CONST)   THEN 

       select cast(cast(incrementvalue as   character varying) || ' week' as interval) into intervals; 

   ELSEIF lower($1) = lower(DAY_CONST)   THEN 

       select cast(cast(incrementvalue as   character varying) || ' day' as interval) into intervals; 

   ELSEIF lower($1) = lower(HOUR_CONST)   THEN 

       select cast(cast(incrementvalue as   character varying) || ' hour' as interval) into intervals; 

   ELSEIF lower($1) = lower(MIN_CONST)   THEN 

       select cast(cast(incrementvalue as   character varying) || ' minute' as interval) into intervals; 

   ELSEIF lower($1) = lower(SECOND_CONST)   THEN 

       select cast(cast(incrementvalue as   character varying) || ' second' as interval) into intervals;    

   END IF;   

   dateTemp:= inputdate + intervals; 

   RETURN dateTemp; 

END; 

$BODY$

LANGUAGE   PLPGSQL;

 

在PG增强版上执行会返回NULL，原因是char(10)=’year’其实会在year后面补充6个空格，直到长度为10，所以在PG增强版上可以使用变长字符串来解决这个问题，如下：

CREATE OR   REPLACE FUNCTION dateadd(difftype character varying, incrementvalue numeric,   inputdate timestamp with time zone)

  RETURNS timestamp without time zone AS

$BODY$

DECLARE

   YEAR_CONST varchar(15) := 'year'; 

   MONTH_CONST varchar(15) := 'month'; 

   WEEK_CONST varchar(15) := 'week'; 

   DAY_CONST varchar(15) := 'day'; 

   HOUR_CONST varchar(15) := 'hour'; 

   MIN_CONST varchar(15) :='minute'; 

   SECOND_CONST varchar(15) :='second'; 

     dateTemp timestamp; 

   intervals interval; 

BEGIN

   if inputdate is null then return null ;   end if;

   IF lower($1) = lower(YEAR_CONST) THEN 

       select cast(cast(incrementvalue as   character varying) || ' year' as interval) into intervals;  

   ELSEIF lower($1) = lower(MONTH_CONST)   THEN 

       select cast(cast(incrementvalue as   character varying) || ' month' as interval) into intervals; 

   ELSEIF lower($1) = lower(WEEK_CONST)   THEN 

       select cast(cast(incrementvalue as   character varying) || ' week' as interval) into intervals; 

   ELSEIF lower($1) = lower(DAY_CONST)   THEN 

       select cast(cast(incrementvalue as   character varying) || ' day' as interval) into intervals; 

   ELSEIF lower($1) = lower(HOUR_CONST)   THEN 

       select cast(cast(incrementvalue as   character varying) || ' hour' as interval) into intervals; 

   ELSEIF lower($1) = lower(MIN_CONST)   THEN 

       select cast(cast(incrementvalue as   character varying) || ' minute' as interval) into intervals; 

   ELSEIF lower($1) = lower(SECOND_CONST)   THEN 

       select cast(cast(incrementvalue as   character varying) || ' second' as interval) into intervals;    

   END IF;   

   dateTemp:= inputdate + intervals; 

   RETURN dateTemp; 

END; 

$BODY$

LANGUAGE   PLPGSQL;

1.18   Date类型相减返回值为interval类型的问题

例如：PG增强版下执行以下语句：

glow=>   SELECT CAST('2019-06-02' AS DATE) - CAST('2019-06-01' AS DATE);

 ?column?

----------

 1 day

(1 row)

 

可以看到，执行结果是一个interval类型，而在PG社区版上执行结果是一个数字

glow=>   SELECT CAST('2019-06-02' AS DATE) - CAST('2019-06-01' AS DATE);

 ?column?

----------

        1

(1 row)

如果想要在PG增强版上得到和PG社区版一样的结果，可以做如下修改：

方案1：+ 0

glow=>   select cast('2019-08-08' as date) - cast('2019-08-01' as date) +0;

 ?column?

----------

        7

(1 row)

方案2：date加上双引号

glow=>   select cast('2019-08-08' as "date") - cast('2019-08-01' as   "date");

 ?column?

----------

        7

(1 row)

 

1.19   加密方式password_encryption=scram-sha-256导致用户无法连接

目前默认的加密方式为scram-sha-256，这种加密方式会导致部分用户客户端无法连接到服务器的问题（客户端不支持这种加密方式），一般会报如下错误：

An error   occurred while establishing the connection:

Long Message:

不支援 10 验证类型。请核对您已经组态   pg_hba.conf 文件包含客户端的IP位址或网路区段，以及驱动程序所支援的验证架构模式已被支援。

Details:

     Type: org.postgresql.util.PSQLException

 

解决方法：后台修改password_encryption=md5，然后在控制台上修改rds的root密码，密码设置可以和原来一样。

为什么要修改密码呢？

答：因为加密方式从scram-sha-256变为md5后，原来的密码用md5方式无法解开，所以需要修改密码，修改密码的过程即重新对密码进行md5加密过程。

1.20   PG增强版安装插件的方式

为了安全考虑，目前不允许roor以外的用户安装插件，并且在安装插件的时候不能使用社区版的create extension ‘插件名字’的方式，我们提供了插件安装接口

Root无权限直接用create extension安装插件

glow=> create   extension postgis;

ERROR:  permission denied to create extension   "postgis"

HINT:  Must be superuser to create this extension.

使用control_extension()来安装卸载插件

glow=> select   control_extension('create','postgis');

      control_extension

------------------------------

 create postgis successfully.

(1 row)

glow=> select control_extension('drop','postgis');

     control_extension

----------------------------

 drop postgis successfully.

(1 row)

 

 


【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
Oracle PostgreSQL
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入