从MySQL导出到PostgreSQL

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
云数据库 RDS SQL Server,基础系列 2核4GB
简介:


异构
数据库的导入、导出是一个大难题。从MySQL到PG目前没有特别完善的工具,还需要人工的参与。本文将简单介绍下如何利用一些工具,手工将MySQL导出数据到PostgreSQL。

导出、转换、导入


数据导出、导入的思路是:

  1. 导出结构

  2. 导出数据

  3. 对结构进行转换

  4. 对数据进行转换

  5. 导入结构

  6. 导入数据

这里的想法是将结构和数据分开处理,这样有很多好处。首先DDL的语句的差别相比DML要大一些,比如各种类型的转换、符号的变化等,分开之后可以针对性的处理;其次,DDL和DML分开更好做批量的处理;其他好处……

当前的开源转换工具,尚没有一个完善的。不同的工具分别做了些不同的转换,因此本文将会利用多个工具互相配合使用,可以达到较好的效果。主要用到的工具有:

  1. mysql-postgresql-converter

    单个python脚本,依赖较少。对文件內容、格式的转换,比较好的是enum类型改写等。

  2. my2pg.pl

    单个perl脚本,依赖较少,会对数据类型做不错的转换。

  3. SED、VIM, 文本编辑神器,你懂得~

导出


导出主要采用的是mysql自带的mysqldump,导出成sql文件的形式。这里只给出直接的例子,更多的参数请参考文档(mysqldump --help)。

导出结构

/usr/local/mysql/bin/mysqldump --skip-opt --compatible=postgresql --compact --no-data --default-character-set=utf8 --skip-dump-date -u cvusr -p -h mysqlhostaddress cvdb > schm.sql

  • --no-data,故名思议,就是不带数据

  • --compatible=postgresql, 尽量兼容PostgreSQL的语法的方式,至少能减少后面一部分工作量

  • --default-character-set=utf8,编码尽量与目标库保持一致

  • 这里有更多的说明

导出数据

/usr/local/mysql/bin/mysqldump --skip-opt --compatible=postgresql --compact --no-create-info --default-character-set=utf8 --skip-comments --skip-dump-date -u cvusr -p -h mysqlhost cvdb > data.sql

转换


数据的导出是比较容易的,麻烦的地方在于将数据文件转换成另一种数据库可以采用的方式。在这个过程中,至少需要处理以下几个问题,这里有详细的MySQL与PG的对比

在结构上

  1. 类型

    如mediumint、char等,其实PG是可以自定义类型,这个应该是细节的处理,现有工具已经能做的不错。my2pg.pl工具可以完成得较好

  2. 语义转换

    典型的,在MySQL中可以create table的时候对字段采用enum类型,在PG中就需要抽出来先建个enum数据类型。另外如UK、default value等。 这方面,mysql-postgresql-converter可以满足部分需求。

  3. 其他地方

在数据上:

  1. 格式

  2. '与"

  3. 默认值

  4. 其他地方

可以在PG上做如下设置:

backslash_quote = on

escape_string_warning = off

standard_conforming_strings = off

在mysqldump的时候,在建表语句中会有注释,但发现几个工具都去不掉,mysqldump的"--skip-comments"选项也没起作用,百思不得姐之后决定手动处理:

sed -i "s/ COMMENT '.*',/ ,/g" schm.dump

sed -i "s/ COMMENT '.*',/ ,/g" data.dump

自动转换

经过多次尝试,发现没有一个工具能够将导出的结构处理的比较完善,使得可以直接在PG中导入。开了脑洞后尝试工具的合作,主要是因为懒,不想手动改:

// 一些数据类型的修改,读者可以自行作个对比。目前my2pg.pl做得比其他工具要好一些。 (但其将blob类型转换成了text,需要注意,可以将脚本my2pg.pl中的“s/\w*blob$chareg/text/i; ”改为“s/\w*blob$chareg/bytea/i; ”

perl my2pg.pl  schm.dump  > my2pg-schm.dump

// enum类型改写等,这个是上面的工具没有处理的,还有一些其他的

python mysql-postgresql-converter/db_converter.py schm.dump > db_cvtr-my2pg-schm.dump

这里需要注意的是,这两个工具的顺序不能变,变了之后结果就不认识了。如果有兴趣,可以尝试下与其他工具的配合。

这还不是结束!这个时候,并不一定所有的类型都修改完全,可能还有一些需要手工处理,如:

特殊字符的处理:

SHELL$ vim mysql2pgsql-data.dump

:%s /\E'/E\'/g

——这里是去除一些地方转换出的问题,在插入语句中,"insert into tbl values(0, E\'0\')"会被处理成了"insert into tbl values(0, \E'0\')"。幸好只是少数!

日期默认值:

'0000-00-00' 改为CURRENT_DATE,或者某固定日期等

——在MySQL中,日期'0000-00-00'值为空、或者无效,但这个日期不是正确日期,因此在PG中不支持。CURRENT_DATE是指当前时间。

其他的修改,也只能见招拆招了。

数据导入

在转换处理完成了之后,剩下的也就简单了。依次导入结构和数据就好,如果有问题,只能具体问题具体分析了。

结构导入

~/Workspace/pg94/bin/psql -h pghost -p 3432 -U cvusr -d cvdb -f schm.dump

数据导入

~/Workspace/pg94/bin/psql -h pghost -p 3432 -U cvusr -d cvdb -f data.dump

检查、验证


数据导进来了,不代表就结束了。因为不确定中间是否出了什么幺蛾子。需要做一些验证:

  1. 表结构、类型

  2. 记录数

  3. 抽样记录

  4. 存储过程就算了

  5. 编码、乱码的问题

  6. 其他差异地方的对比,如blob、clob等

针对这些做过检查无误之后,需要应用做相应的全面的测试。

参考


  1. mysql-to-postgresql-migration-tips

  2. Converting_from_other_Databases_to_PostgreSQL

  3. Converting_MySQL_to_PostgreSQL

  4. mysql-postgresql-converter

  5. py-mysql2pgsql

  6. py-mysql2pgsql源码

  7. mysqldump参数解释

  8. Migrating from MySQL to PostgreSQL

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
11月前
|
存储 关系型数据库 MySQL
一个项目用5款数据库?MySQL、PostgreSQL、ClickHouse、MongoDB区别,适用场景
一个项目用5款数据库?MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景比较
|
9月前
|
关系型数据库 MySQL Linux
Linux下mysql数据库的导入与导出以及查看端口
本文详细介绍了在Linux下如何导入和导出MySQL数据库,以及查看MySQL运行端口的方法。通过这些操作,用户可以轻松进行数据库的备份与恢复,以及确认MySQL服务的运行状态和端口。掌握这些技能,对于日常数据库管理和维护非常重要。
342 8
|
9月前
|
关系型数据库 MySQL 数据库
市场领先者MySQL的挑战者:PostgreSQL的崛起
PostgreSQL(简称PG)是世界上最先进的开源对象关系型数据库,起源于1986年的加州大学伯克利分校POSTGRES项目。它以其丰富的功能、强大的扩展性和数据完整性著称,支持复杂数据类型、MVCC、全文检索和地理空间数据处理等特性。尽管市场份额略低于MySQL,但PG在全球范围内广泛应用,受到Google、AWS、Microsoft等知名公司支持。常用的客户端工具包括PgAdmin、Navicat和DBeaver。
367 4
|
10月前
|
存储 关系型数据库 MySQL
MySQL vs. PostgreSQL:选择适合你的开源数据库
在众多开源数据库中,MySQL和PostgreSQL无疑是最受欢迎的两个。它们都有着强大的功能、广泛的社区支持和丰富的生态系统。然而,它们在设计理念、性能特点、功能特性等方面存在着显著的差异。本文将从这三个方面对MySQL和PostgreSQL进行比较,以帮助您选择更适合您需求的开源数据库。
509 4
|
10月前
|
关系型数据库 MySQL PostgreSQL
postgresql和mysql中的limit使用方法
postgresql和mysql中的limit使用方法
314 1
|
11月前
|
XML 关系型数据库 MySQL
MySQL 导出某些数据的技术详解
MySQL 导出某些数据的技术详解
464 2
|
12月前
|
Oracle NoSQL 关系型数据库
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
2000 3
|
11月前
|
存储 关系型数据库 MySQL
四种数据库对比MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景
四种数据库对比 MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景
|
3月前
|
存储 关系型数据库 测试技术
拯救海量数据:PostgreSQL分区表性能优化实战手册(附压测对比)
本文深入解析PostgreSQL分区表的核心原理与优化策略,涵盖性能痛点、实战案例及压测对比。首先阐述分区表作为继承表+路由规则的逻辑封装,分析分区裁剪失效、全局索引膨胀和VACUUM堆积三大性能杀手,并通过电商订单表崩溃事件说明旧分区维护的重要性。接着提出四维设计法优化分区策略,包括时间范围分区黄金法则与自动化维护体系。同时对比局部索引与全局索引性能,展示后者在特定场景下的优势。进一步探讨并行查询优化、冷热数据分层存储及故障复盘,解决分区锁竞争问题。
327 2
|
关系型数据库 分布式数据库 PolarDB
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
495 0

相关产品

  • 云数据库 RDS MySQL 版
  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版
  • 推荐镜像

    更多