19

Has anyone done this? Is it an easy process? We're thinking of switching over for transactions and because mysql seems to be "crapping out" lately.

Ryan Detzel
  • 5,519
  • 9
  • 37
  • 49
  • 1) MySql does support transactions (InnoDB) 2) What makes you think MySql is "crapping out" lately? – The Scrum Meister Feb 11 '11 at 01:13
  • For now reason we'll just start getting tones of these errors: OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction') Also, using South with Django sometimes hurts without real transactions. – Ryan Detzel Feb 11 '11 at 14:43
  • 1
    MySQL doesn't support schema transactions - which can cause a massive headache for migrations – yarbelk Feb 18 '13 at 05:20

7 Answers7

32

Converting MySQL database to Postgres database with Django

First backup your data of the old Mysql database in json fixtures:

$ python manage.py dumpdata contenttypes --indent=4 --natural-foreign > contenttype.json
$ python manage.py dumpdata --exclude contenttypes --indent=4 --natural-foreign > everything_else.json

Then switch your settings.DATABASES to postgres settings.

Create the tables in Postgresql:

$ python manage.py migrate

Now delete all the content that is automatically made in the migrate (django contenttypes, usergroups etc):

$ python manage.py sqlflush | ./manage.py dbshell

And now you can safely import everything, and keep your pk's the same!

$ python manage.py loaddata contenttype.json
$ python manage.py loaddata everything_else.json

Tested with Django==1.8

Michael van de Waeter
  • 1,473
  • 15
  • 29
  • 1
    Worked perfectly for me with Django==1.11.4 – endur Aug 28 '17 at 00:40
  • 3
    OMG You saved my day... I've been trying to move my data from MySQL to PGSQL over the weekend with pdloader. The tool always gives me a weird error that I don't even know how to start debugging... Your way is just fantastic. Thanks a lot and your answer should be the accepted one! – MiDaa May 27 '19 at 01:26
  • Thanks! I also has success here with my django project, but was getting duplicate `PK` key errors until I commented out all my `post_save.connect()` callbacks (which were creating additional entries). – jozxyqk Aug 18 '20 at 18:51
  • Also works great for moving data from PGSQL to MySQL! Didn't work in one fixture before...Django 3.2... Thanks a lot! :) – chakmear Nov 23 '22 at 07:25
3

I just used this tool to migrate an internal app and it worked wonderfully. https://github.com/maxlapshin/mysql2postgres

Philip Southam
  • 15,843
  • 6
  • 28
  • 20
2

You can do that using Django serializers to output the data from MySQL's format into JSON and then back into Postgres. There are some good artices on internet about that:

Migrating Django from MySQL to PostgreSQL the Easy Way

Move a Django site to PostgreSQL: check

inigomedina
  • 1,791
  • 14
  • 21
1

I've never done it personally, but it seems like a combination of the dumpdata and loaddata options of manage.py would be able to solve your problem quite easily. That is, unless you have a lot of database-specific things living outside the ORM such as stored procedures.

arussell84
  • 2,443
  • 17
  • 18
  • that work, but sometime there are need truncate all tables just created by syncdb – Alexey Feb 18 '13 at 04:27
  • dump data loads the entire dataset into memoery before serializing it - this is a problem for a large data set. http://www.ofbrooklyn.com/2010/07/18/migrating-django-mysql-postgresql-easy-way/ shows how to chunk this process to avoid crashing your server. – yarbelk Feb 18 '13 at 05:22
0
  1. python manage.py dump.data >> data.json

  2. Create database and user in postrgesql

  3. Set your just created database in postrgesql as default database in django settings or use param --database=your_postrgesql_database next steps
  4. Run syncdb for create tables.

    python syncdb [--database=your_postrgesql_database] --noinput

  5. Create dump without data, drop all tables and load dump. Or truncate all tables (table django_content_type whith data which can be not equals your old data - it is way to many errors). At this step we need empty tables in postgresql-db.

  6. When you have empty tables in postgresql-db just load your data:

    python manage.py loaddata data.json

And be fun!

Alexey
  • 812
  • 13
  • 22
0

I wrote a Django management command that copies one database to another: https://gist.github.com/mturilin/1ed9763ab4aa98516a7d

You need to add both database in the settings and use this command:

./manage.py copy_db from_database to_database app1 app2 app3 --delete --ignore-errors

What cool about this command is that it recursively copy dependent objects. For example, if the model have 2 foreign keys and two Many-to-Many relationships, it will copy the other objects first to ensure you won't get foreign key violation error.

0

I've not done it either. I'd first follow this migration guide, there is a MySql section which should take care of all your data. Then django just switch the mysql to postgre in the settings. I think that should be ok.

I found another question on stackoverflow which should help with the converting mysql to postgre here.

Community
  • 1
  • 1
darren
  • 18,845
  • 17
  • 60
  • 79