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.
-
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
-
1MySQL doesn't support schema transactions - which can cause a massive headache for migrations – yarbelk Feb 18 '13 at 05:20
7 Answers
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

- 1,473
- 15
- 29
-
1
-
3OMG 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
I just used this tool to migrate an internal app and it worked wonderfully. https://github.com/maxlapshin/mysql2postgres

- 15,843
- 6
- 28
- 20
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:

- 1,791
- 14
- 21
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.

- 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
python manage.py dump.data >> data.json
Create database and user in postrgesql
- Set your just created database in postrgesql as default database in django settings or use param --database=your_postrgesql_database next steps
Run syncdb for create tables.
python syncdb [--database=your_postrgesql_database] --noinput
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.
When you have empty tables in postgresql-db just load your data:
python manage.py loaddata data.json
And be fun!

- 812
- 13
- 22
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.

- 75
- 1
- 7
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.