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

Has anyone done this? Is it an easy process? We're thinking of switching over for transactions and becaus开发者_C百科e mysql seems to be "crapping out" lately.

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

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

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.