从Mysql迁移到PostgreSQL
http://my.oschina.net/duangr/blog/182216
之前使用的数据库是MySql,现在要改成PostgreSQL,因此需要将MySql表中数据也迁移过来. 下面是一些具体操作步骤.
前言
之前使用的数据库是MySql,现在要改成PostgreSQL,因此需要将MySql表中数据也迁移过来. 下面是一些具体操作步骤.
@Author duangr
@Website http://my.oschina.net/duangr/blog/182216
1.相关环境
| Host Name | IP | OS | Arch |
| duangr-1 | 192.168.56.10 | CentOS 6.4 | x86_64 |
| DataBase | Version |
| MySql | 5.5.27 |
| PostgreSQL | 9.3.1 |
2.数据迁移
我的做法是将数据从MySQL库中导出到文件中,然后再将文件中的数据导入到PostgreSQL中.
2.1 创建数据文件存储目录
|
1
2
|
# mkdir /tmp/etl# chmod 777 /tmp/etl |
解释下, 我选择把数据文件放到 /tmp 目录下,是由于此目录权限是 777 ,这样一来MySQL的启动用户mysql,以及PostgreSQL的启动用户 postgres 都可以读写此目录下面的文件.
(切换成其他目录均可,但需要保证mysql和postgres用户都要拥有读写权限.)
2.2 从MySQL中导出表数据
以表 tab_duangr 为例,将表中数据导出到文件中
|
1
2
|
mysql> select * from tab_duangr into outfile "/tmp/etl/tab_duangr.dat" fields terminated by '^A';Query OK, 792 rows affected (0.01 sec) |
P.S. 此处使用ASCII字符 0x01 作为文件中字段分隔符,目的是避免与数据字段内容冲突. ^A 需要通过 "Ctrl+V" + "Ctrl+A" 的方式输入.
2.3 将数据导入到PostgreSQL
需要使用超级用户登陆PostgreSQL (数据库为duangr)
|
1
2
3
4
5
6
7
|
# su - postgres$ psql -d duangrpsql (9.3.1)输入 "help" 来获取帮助信息. duangr=# copy tab_duangr from '/tmp/etl/tab_duangr.dat' with delimiter '^A'; COPY 792 |
3. 相关链接
我们已对 Django1.6 app完成了数据库从mysql到PostgreSQL的迁移，如果你的环境很干净，这个过程就会很简单，只要允许syncdb 或者 migrate创建表，truncating表中的数据，然后运行dumpdata 和loaddatamanagement命令就完成了。
第一步，在你的PostgreSQL数据库中创建一个空的实例：
|
1
|
CREATE DATABASE dbname OWNER rolename; |
第二步，在你的Django中给创建的数据库加上配置
在setting.py 中，我们这样配置:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
DATABASES = { 'default': { 'ENGINE': 'django.db.backends.mysql', 'NAME': 'dbname', 'USER': 'dbuser', 'PASSWORD': 'dbpass', 'HOST': 'mysql.example.com', 'PORT': '', }, 'postgresql': { 'ENGINE': 'django.db.backends.postgresql_psycopg2', 'NAME': 'dbname', 'USER': 'dbuser', 'PASSWORD': 'dbpass', 'HOST': 'postgresql.example.com', 'PORT': '', }} |
这样我们就指定了以后名称操作的是哪个数据库。
第三步，在PostgreSQL实例中创建表
|
1
|
python manage.py syncdb --migrate --no-initial-data --database=postgresql |
在PostgreSQL中运行syncdb 和 migrations，这个过程并没有初始数据。
第四步，truncate新创建的表
尽管我们在先前的步骤中指定了 –no-initial-data ，为了防止在迁移过程中用户的自定义数据在未知情况下加入了表中，我们最好还是truncate一下新建的表，我们可以生成一个SQL脚本，运行：
|
1
|
python manage.py sqlflush --database=postgresql |
第五步，从mysql中备份数据到JSON 文件中
Django 有一个dumpdata命令，用它可以生成一个数据库无关的备份，默认格式是JSON。
|
1
|
python manage.py dumpdata --all --natural --indent=4 > dbname.json |
这里的 -all 参数是为了确保你在导出数据的过程中有可能有你自己的过滤和修改数据的需求，-natural 参数告诉Django使用natural keys(如果可用的话) –indent 参数是为了使输出更加可读。
你也许想只导出特定的apps里的数据，或者只导出一个celery logs ，这样的话，你就可以使用 –exclude参数，例如:
|
1
|
python manage.py dumpdata --all --natural --exclude=djcelery --exclude=search.SearchLog --indent=4 > dbname.json |
第六步，加载JSON数据到PostgreSQL数据库中
|
1
|
python manage.py loaddata dbname.json --database=postgresql |
基本上迁移的过程就结束了，现在你只要修改一下你的数据库配置，然后是PostgerSQL成为默认的数据库。
|
1
2
3
4
5
6
7
8
9
10
|
DATABASES = { 'default': { 'ENGINE': 'django.db.backends.postgresql_psycopg2', 'NAME': 'dbname', 'USER': 'dbuser', 'PASSWORD': 'dbpass', 'HOST': 'postgresql.example.com', 'PORT': '', }} |
在我们的情况下，我们的数据库不是很干净，我们的数据库是给一个PHP的遗留代码创建的，我们现在还在一步步摆脱它，我们有一些Django之外的数据库，并且都在被程序使用，为了把这些迁移到PostgreSQL中，我使用了这个工具，过程还是简单一些。
注意事项
Django 信号
你可能想禁止这些，当一个数据库记录创建的时候，你的程序可能就会发送给你的用过邮件，为了不打扰它们，在加载数据的时候，你需要保证它们被禁止了，Here’s one way to handle this 这里我们使用了检测器保证信号不会别触发。
约束(像非空，unique 和外键)
我们的数据库迁移过程碰到了很多这种问题，比如一个外键约束，但是其他的一个表不存在了，有一下空置的记录，但是模型定义中不允许，比如复制的过程中存在unique约束，这些都需要手动干预，我必须使用SQL语句清除掉这些，Dumpdata 和loaddata 将会检测到这些，所以你的数据库必须是一个干净的一致的状态。
主键的硬编码
这很痛苦，因为在我们的测试 suite中，到处都是主键的硬编码，所以很多测试到失败了，因为使用PostgreSQL 处理的序列的方法和mysql不太一样，我必须手动修改700多个测试用例，大都是很简单的修改但是很消耗时间。
原生SQL语句
幸运的是，我们只有一处使用了原生sql语句，有些mysql中的函数在PostgreSQL中不适用，我们只用修改为在PostgreSQl中相同功能的函数就可以了。
大小写敏感
字符串比较在PostgreSQL中是大小写敏感的，但是在Mysql中不是，我在迁移非Django数据表过程中也遇到了一些问题，索引创建的时候，命令需要id，但是字段的名字是Id(字母I)，我只要重命名为id就可以了。
