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

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

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

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.

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.

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!

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.

I just went through this exercise and the answer provided by michael-van-de-waeter here is perfect.

However, I would like to add the following needed steps:

1. I had to first delete ContentType following this thread:

python manage.py shell

from django.contrib.contenttypes.models import ContentType ContentType.objects.all().delete() 

2. I had also to append a square bracket to the data json:

   echo ']' >> everything_else.json