将Django数据库后端从MySql更改为PostgreSQL [英] Changing Django database backend from MySql to PostgreSQL

问题描述

我使用Django 1.2和1.3和MySql后端。

I use Django 1.2 and 1.3 and MySql backends.

一旦在South中迁移MySql数据库时收到一条错误消息：

Once in while a get an error message when migrating my MySql database with South:

! Error found during real run of migration! Aborting.

! Since you have a database that does not support running
! schema-altering statements in transactions, we have had 
! to leave it in an interim state between migrations.
...
! The South developers regret this has happened, and would
! like to gently persuade you to consider a slightly
! easier-to-deal-with DBMS

我想知道如果将我的数据库从MySql迁移到PostgreSQL可能会阻止这个错误。其次，将从MySql迁移到PostgreSQL更多地涉及到在MySql dbase上执行dumpdata，将设置更改为指向PostgreSQL和新的后端的loaddata？

I was wondering if migrating my databases from MySql to PostgreSQL might prevent this error. Secondly, would migrating from MySql to PostgreSQL be more involved dan doing a dumpdata on the MySql dbase, change the settings to point to PostgreSQL and the loaddata on the new backend?

我看到这个 stackoverflow问题，但它谈到他的数据库太大。我不认为我的数据库将是这样。我的Django项目中没有任何自定义的SQL命令。

I saw this stackoverflow question, but it talks about his database being too big. I don't think that will be the case with my databases. I don't have any custom SQL commands in my Django projects.

推荐答案

我厌倦了使用South看到这个错误，切换到PostgreSQL已经放弃了！

I got tired of seeing this error using South and yes, switching to PostgreSQL has banished it!

以Ruby编写的mysql2postgres应用程序在上述注释中建议不适用于我（它会运行，输出一些细节屏幕但不复制任何数据行，对我来说）。不知道为什么但是很高兴有一个Python的重写工作，完美无瑕（对我来说，最终）：

http://pypi.python.org/pypi/py-mysql2pgsql

The mysql2postgres app, written in Ruby, suggested in comments above didn't work for me (it would run, output some details to screen but not copy any rows of data, for me). Not sure why. But gladly there's a Python rewrite of it that worked flawlessly (for me, eventually):
http://pypi.python.org/pypi/py-mysql2pgsql

我发现的唯一缺点是：

最初我以为通过syncdb在PostgreSQL数据库中设置表是最安全的，然后仅迁移数据。我试过这个，但是这些表按字母顺序进行迁移，这违反了一些表的外键约束（与尚未导入的表中的行相关的行）。

Initially I thought it would be safest to set up the tables in the PostgreSQL db via a syncdb and then migrate the data only. I tried this, but the tables are migrated across in alphabetical order and this violates the foreign key constraints for some tables (rows relate to rows in tables not yet imported).

我接下来尝试了一个结构+数据迁移。这个迁移很好，但是Django之后遇到了一些问题，特别是管理员的网站。看起来像迁移脚本已经创建了一些不同的表约束从Django将有。

I next tried a structure+data migration. This migrated fine, but I encountered some problems in Django afterwards, especially the admin site. It seemed like the migration script had created some different table constraints from what Django would have.

我通过黑客mysql2pgsql脚本来解决这个问题，以尊重在yaml config only_tables 属性...然后执行syncdb +仅数据迁移。通过反复试验，我会顺序排列我的迁移表，直到它们都成功导入。

I solved this by hacking the mysql2pgsql script to respect the order of tables given in the yaml config only_tables property... and then doing a syncdb + data-only migration. By trial and error I shuffled around the ordering of the tables for my migration until they all imported successfully.

更新：

我的以上描述的黑客已被接受，所以您可以从主版本执行此操作：

https： //github.com/philipsoutham/py-mysql2pgsql

这篇关于将Django数据库后端从MySql更改为PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！