Changing Django database backend from MySql to PostgreSQL

Question

I use Django 1.2 and 1.3 and MySql backends.

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

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?

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.

just try the mysql2postgres given in answer stackoverflow.com/a/8385094/540341 - if everything goes OK it will take minutes. you can backup your db and configs before migration to be 100% safe. — filiprem
– filiprem, Commented Jan 17, 2012 at 14:22
another option is to stay on MySQL but change tables InnoDB engine. see stackoverflow.com/questions/4834415/… — filiprem
– filiprem, Commented Jan 17, 2012 at 14:26
@filiprem, thanks, that probably works. I'm interested though if specifically migrating to postgreSQL will also avoid this. I have several other reasons to want to migrate. I read about mysql2postgres, it will be a good fallback in case something goes wrong with simple dumpdata/loaddata. — Density 21.5
– Density 21.5, Commented Jan 17, 2012 at 15:08
incidentally this stackoverflow.com/questions/4834415/… does not show that South stops complaining if you use InnoDB. South works on InnoDB, but it still complains when your migrations fail, due to lack of transaction functionality. — Anentropic
– Anentropic, Commented Jan 19, 2012 at 16:33

Anentropic · Accepted Answer · 2012-09-11 10:04:34Z

5

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

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

The only gotcha I found was:

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).

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.

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.

UPDATE:
My pull request for the hack described above got accepted so you can do this now from the main version:
https://github.com/philipsoutham/py-mysql2pgsql

edited Sep 11, 2012 at 10:04

answered Jan 19, 2012 at 16:30

Anentropic

34.2k13 gold badges111 silver badges170 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Density 21.5 Over a year ago

Thanks, this is exactly the information I needed. I am going to try your successful migration route.

Density 21.5 Over a year ago

I am currently deploying this solution. It works, only problem encountered so far is this: stackoverflow.com/questions/6466836/…

Collectives™ on Stack Overflow

Changing Django database backend from MySql to PostgreSQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related