How can I easily convert a Django app from mySQL to PostgreSQL?

Question

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

score 32 · Answer 1 · answered May 08 '15 at 16:53

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

answered May 08 '15 at 16:53

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

score 3 · Accepted Answer · answered Feb 16 '11 at 08:46

3

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

answered Feb 16 '11 at 08:46

Philip Southam

15,843
6
28
20

score 2 · Answer 3 · answered Jun 15 '12 at 19:26

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

score 1 · Answer 4 · answered Feb 11 '11 at 01:38

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.

answered Feb 11 '11 at 01:38

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

score 0 · Answer 5 · answered Feb 18 '13 at 05:02

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!

score 0 · Answer 6 · answered Apr 30 '14 at 00:10

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.

2

broken git link – Anders Rune Jensen Dec 07 '22 at 10:50

score 0 · Answer 7 · edited May 23 '17 at 10:30

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.

edited May 23 '17 at 10:30

Community

1
1

answered Feb 11 '11 at 08:09

darren

18,845
17
60
79

How can I easily convert a Django app from mySQL to PostgreSQL?

7 Answers7

Linked