CONTACT:

team@tryolabs.com
(1) 650-319-7251

MySQL vs PostreSQL

We recently had the need to move one of our Django app's back-end from MySQL to PostgreSQL. We wanted to try some of PgSQL's features in that particular context, and so far, we are quite pleased! Unfortunately the migration process was not as painless as we would have liked. At least, not until we found Philip Southam's cool py-mysql2pgsql tool.

Being an improved port of Max Lapshin's ruby-written mysql2pgsql, py-mysql2pgsql allows you to connect to a MySQL database and dump it's contents into a PostgreSQL compatible dump file or directly pipe it into and already running Postgre Server. It handles large data sets (millions of rows) with more ease than its Ruby inspirator.

So we went ahead and tried the tool but, as it so often happens, the process wasn't smooth and when migrating a table's constraints we got this error:

ERROR: there is no unique constraint matching given keys for referenced table

Ouch! Apparently py-mysql2pgsql was having trouble with some of our Foreign Keys, making them refer tables not yet imported. We investigated a bit and found that someone had already coded a workaround to this here by letting you choose the order in which the tables will be imported. So this is the process we followed:

# Download and install anentropic's py-mysql2pgsql modified version
$ sudo python anatropics-py-mysql2pgsql/setup.py install

# Run py-mysql2pgsql so it creates a blank configuration file

# Edit the file with the connection data for the MySQL server and optionally
# the Postgre server.

# Put the tables you want to export (all, eventually) in the "only_tables"
# section of the config file. Here you have to tweak the order of the tables
# until you solve all dependencies issues. It may take a bit of trial-and-error
# but unless you have a massive amount of tables it shouldn't take long

# Run py-mysql2pgsql with your config file
$ py-mysql2pgsql -v -f my_config.yml

And that should be it. Hopefully you now have a fully functional Postgre database (or a very compatible and cool dump file) with all your data.


Comments

  1. Si Feng
    Si Feng on 03/12/2012 2:19 p.m.
    This is NOT the best practice. You may lose some database specific features if you check your imported PostgreSQL database carefully (Django uses different SQL to create database). I just migrated mine and I used a better solution. 1. Using "python manage.py dumpdata > django_data.json" from currently used MySQL database. This is a "pure" data set with no database specific information stored. 2. Create a new PostgreSQL database, modify settings.py to use it, and then run "python manage.py syncdb" to create tables. This is important as you may notice that the SQL commands used by Django for PostgreSQL is different from that of MySQL. 3. Run "python manage.py sqlflush | psql -U myusername mydatabase" to wipe out all data in the new database (make it empty). 4. If you happened to use "post_save" signal in your model, you may need to comment out the callback when loading data. Check my workaround: https://code.djangoproject.com/ticket/17880 If you haven't used any signals, ignore this step. 5. Then "python manage.py loaddata django_data.json". Now data should have been imported. 6. One last thing: remember to reset sequences by running "python manage.py sqlsequencereset appname | psql -U myusername mydatabase". You must do it for EVERY app you use, including Django's built-in and 3rd-party apps. I'm using PyCharm and it had an easy way to run management commands as well as show all the apps I'm using. 7. You are good to go! Note: it is okay to use the py-mysql2pgsql, but you will lose some features that only PostgreSQL has. Actually I used that to convert my database at the beginning, but soon I found problems. It is better to just import the "pure" data to a Django-created PostgreSQL database.
  2. Roh
    Roh on 12/02/2013 8:27 p.m.
    @Si Feng - This is the best and most thorough instructions I've found so far on the web. I was finally able to successfully migrate my data to Postgre!
  3. replique montre
    replique montre on 02/23/2014 1:51 a.m.
    Hey, il a parfaitement fonctionné du premier coup, je vous remercie beaucoup!

Post your comment

:

:

(Optional):

:

(Optional):