查了不少资料,也尝试了一些,最后采用的办法如下:

将系统的数据库从MySQL 5.5迁移到PostgreSQL 9.1

  1. 导出mysql表定义(无数据)
    mysqldump –no-data [dbname] >dbdef.sql

  2. 使用mysql2postgres把脚本转换为pgsql

 

3.
上面生成的脚本还不一定很完美,可以尝试导入pgsql,调试错误并手动修改之。我遇到的问题就只有一个,mysql列定义中的zerofill需要手工去掉。一些unsinged定义会生成constraint,如果不需要可以去掉。另外,trigger都有问题,只能后面手工重建

环境

  1. 导出mysql数据:
    mysqldump -v -nt –complete-insert=TRUE –compact –no-create-info
    –skip-quote-names [dbname] >dbdata.sql
    老一些版本的pgsql如果不支持批量插入的话还需要加上–extended-insert=FALSE,这个性能损失巨大。

  2. 转义符
    mysql默认字符串里的’\’是转义符,而pgsql默认不是,修改postgresql.conf:
    backslash_quote = on
    escape_string_warning = off
    standard_conforming_strings = off
    数据导入完成后可以改回默认值。

  3. pgsql里导入表定义和数据
    psql -d [dbname] <dbdef.sql
    psql -d [dbname] <dbdata.sql

  4. 重建trigger

  5. 自增主键(字段)的处理
    由于导入数据时此字段都是有值的,所以pgsql里面seq并不会增加,可以用如下语句设置自增列的当前值:
    SELECT setval(‘sample_id_seq’,max(id)) from sample;

Windows Server 2003 x64 简体中文, MySQL 5.5 (UTF8编码), PostgreSQL
9.1.4-1 (UTF8编码)

最后,如果数据量大,导入时考虑性能可以先把主键、索引、约束都去掉,导入完成后再加上。另外,psql客户端从管道导入数据似乎不够快,可以用tcp方式psql
-h localhost ,还有一些为大数据量导入优化的参数,大概列一下:

Spring 3.0.7, Struts 2.3.4, Hibernate 3.5.5

autovacuum = off

 

wal_level = minimal

从MySQL迁移到PostgreSQL

archive_mode = off

 

full_page_writes = off

* DDL定义中的各种写法区别

fsync = off

**
前者可用符号”`”(不含引号,在键盘上是Esc键下面的那个键对应的字符)来包裹住表名、列名等,后者不可以。

checkpoint_segments = 50 
checkpoint_timeout = 1h

* 转义字符 

maintenance_work_mem视内存情况尽量大点

MySQL  www.2cto.com           \

作者 RuralHunter

PostgreSQL    默认不支持惯用的\,而是用的英文单引号’

 

  1. 导出mysql表定义(无数据) mysqldump –no-data [dbname] dbdef.sql 2.
    使用mysql2postgres把脚本…

* 使用DB

MySQL中可以 use DB名;

PostgreSQL中不支持 use 关键词!

 

* 主键约束

二者没什么区别

 

* 自增长主键 

MySQL中写成这样

CREATE TABLE users (

    id INT(11) NOT NULL AUTO_INCREMENT,

    name VARCHAR(50) NOT NULL,

    PRIMARY KEY (id)

);

PostgreSQL中写成这样

CREATE TABLE users(

    id serial NOT NULL,

    name VARCHAR(50) NOT NULL,

    PRIMARY KEY (id )

);

注: PostgreSQL会对 serial数据类型的列隐式生成名为 表名_PK名_seq
的SEQUENCE,此例的SEQUENCE名为 users_id_seq 。

 

* 改变已有表的自增长序列 

01  www.2cto.com  

— 更安全有效的解决方案请参考<a
href=””
target=”_blank”
rel=”nofollow”>
假如有表a从MySQL迁移过来,其中有数据100条,若在建立此表时没有指定序列,PG会默认给此表的主键列一个

02

sequence——它是增长的,幅度为1。此时若用Hibernate来给表a添加数据会报错,说主键1已经存在!

03

所以可以在迁移表之后,将表的sequence作少许修改,让其从当前表的主键的最大值再加1来开始!即可解决

04

Hibernate添加数据时报错的问题

05

 

06

ALTER SEQUENCE “public”.”表名_主键名_seq” RESTART WITH (PK的最大值 +
1);

07

08

ALTER SEQUENCE 表名_主键名_seq RESTART WITH (PK的最大值 + 1);

09

 

10

e.g.

11

ALTER SEQUENCE file_types_id_seq” RESTART WITH 10;

12  www.2cto.com  

 

13

从上面stackoverflow.com网站上得到的更加简单有效的一句SQL语句如下:

14

SELECT pg_catalog.setval(pg_get_serial_sequence(‘table_name’,
‘id’), (SELECT MAX(id) FROM table_name)+1);

15

16

*/

 * 唯一键约束

MySQL            UNIQUE KEY name (name)

PostgreSQL     UNIQUE  (name)

* 内置SQL函数区别

MySQL格式化日期            DATE_FORMAT(CURRENT_TIMESTAMP,’%Y-%m-%d
%H:%i:%s’)

PostgreSQL格式化日期     to_char(CURRENT_TIMESTAMP,’yyyy-mm-dd
hh24:mi:ss’)

 

* 日期类型

MySQL            date    time    datetime

PostgreSQL    timestamp

 

* 布尔类型 

MySQL            木有,可用int(1)、枚举或者字符串的方式来模拟。

PostgreSQL    boolean

  www.2cto.com  

* 外键约束

在MySQL中创建带有外键的表写法如下:  

01

DROP TABLE IF EXISTS recipient_recipientgroup;

02

 

03

CREATE TABLE IF NOT EXISTS recipient_recipientgroup (

04

  id serial NOT NULL,

05

  recipient_id INTEGER DEFAULT NULL,

06

  recipient_group_id INTEGER DEFAULT NULL,

07

  PRIMARY KEY (id),

08

  KEY FK_recipient_recipientgroup_recipient (recipient_id),

09

  KEY FK_recipient_recipientgroup_recipient_group
(recipient_group_id),

10

  CONSTRAINT FK_recipient_recipientgroup_recipient FOREIGN KEY
(recipient_id) REFERENCES recipient (id),

11

  CONSTRAINT FK_recipient_recipientgroup_recipient_group FOREIGN KEY
(recipient_group_id) REFERENCES recipient_group (id)

12

);

在 PostgreSQL 中创建带有外键的表写法如下: (即在MySQL的写法上去掉 KEY …
这句!)

01

DROP TABLE IF EXISTS recipient_recipientgroup;

02

 

03

CREATE TABLE IF NOT EXISTS recipient_recipientgroup (

04  www.2cto.com  

  id serial NOT NULL,

05

  recipient_id INTEGER DEFAULT NULL,

06

  recipient_group_id INTEGER DEFAULT NULL,

07

  PRIMARY KEY (id),

08

—  KEY FK_recipient_recipientgroup_recipient (recipient_id),

09

—  KEY FK_recipient_recipientgroup_recipient_group
(recipient_group_id),

10

  CONSTRAINT FK_recipient_recipientgroup_recipient FOREIGN KEY
(recipient_id) REFERENCES recipient (id),

11

  CONSTRAINT FK_recipient_recipientgroup_recipient_group FOREIGN KEY
(recipient_group_id) REFERENCES recipient_group (id)

12

);

 

代码修改 

———- 

* 将SpringSide 3.3.4中提供的IdEntity类修改如下

01

/**

02

 * 参考SpringSide3,统一定义id的entity基类.

03

 *

04

 * 基类统一定义id的属性名称、数据类型、列名映射及生成策略.

05

 * 子类可重载getId()函数重定义id的列名映射和生成策略.

06

 */  www.2cto.com  

07

//JPA 基类的标识

08

@MappedSuperclass

09

public abstract class IdEntity {

10

 

11

    protected Long id;

12

 

13

    @Id

14

//    @GeneratedValue(strategy = GenerationType.AUTO)

15

    @GeneratedValue(strategy = GenerationType.IDENTITY)

16

    @Column(unique = true, nullable = false)

17

    public Long getId() {

18

        return this.id;

19

    }

20

21

    public void setId(Long id) {

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图