A tutorial on migrating a Django-used database from MySQL to PostgreSQL _python

We have completed the migration of database from MySQL to PostgreSQL for Django1.6 app, and if your environment is clean, this process will be simple, just allow syncdb or migrate to create tables, truncating data in tables, Then run the DumpData and Loaddatamanagement commands to complete.
The first step is to create an empty instance in your PostgreSQL database:

CREATE DATABASE dbname OWNER rolename;

The second step is to add a configuration to the database you created in your Django

In setting.py, we configure this:

DATABASES = {'
  default ': {'
    ENGINE ': ' Django.db.backends.mysql ', '
    NAME ': ' dbname ',
    ' USER ': ' Dbuser ',
    ' PASSWORD ': ' Dbpass ', '
    HOST ': ' mysql.example.com ',
    ' PORT ': ',
  },
  ' PostgreSQL ': {
    ' ENGINE ': ' django.db.backends.postgresql_psycopg2 ', '
    NAME ': ' dbname ',
    ' USER ': ' Dbuser ',
    ' PASSWORD ': ' Dbpass ',
    ' HOST ': ' postgresql.example.com ',
    ' PORT ': ',
  }
}

This allows us to specify which database is the next name operation.
Step three, create a table in the PostgreSQL instance

Python manage.py syncdb--migrate--no-initial-data--database=postgresql

Running syncdb and migrations in PostgreSQL, this process does not have initial data.
Step fourth, truncate the newly created table

Although we specified –no-initial-data in the previous step, to prevent the user's custom data from being added to the table during the migration process, we'd better truncate the new table, we could generate an SQL script to run:

Python manage.py Sqlflush--database=postgresql

Step fifth, back up the data from MySQL to the JSON file

Django has a dumpdata command that allows you to generate a database-independent backup with JSON in the default format.

Python manage.py dumpdata--all--natural--indent=4 > Dbname.json

The-all parameter here is to ensure that you may have your own filtering and modifying data requirements in the process of exporting the data,-natural parameters tell Django to use the natural keys (if available) –indent parameters are to make the output more readable.

You may want to export only the data in a particular apps, or only one celery logs, so that you can use –exclude parameters, such as:

Python manage.py dumpdata--all--natural--exclude=djcelery--exclude=search. Searchlog--indent=4 > Dbname.json

Step sixth, load the JSON data into the PostgreSQL database

Python manage.py loaddata Dbname.json--database=postgresql

Basically the migration process is over, and now you just need to modify your database configuration, and then Postgersql becomes the default database.

DATABASES = {'
  default ': {
    ' ENGINE ': ' django.db.backends.postgresql_psycopg2 ', '
    NAME ': ' dbname ',
    ' USER ': ' Dbuser ', '
    PASSWORD ': ' Dbpass ',
    ' HOST ': ' postgresql.example.com ',
    ' PORT ': ',
  }
}

In our case, our database is not very clean, our database is created for a PHP legacy code, we are still stepping away from it, we have some Django database, and are being used by the program, in order to migrate these into the PostgreSQL, I use this tool, the process is still simple.
Attention Matters
Django Signal

You may want to ban these, and when a database record is created, your program may send your email, in order not to disturb them, when loading the data, you need to ensure that they are prohibited, here's one way to handle this Here we use the detector to ensure that the signal does not not trigger.
Constraints (like Non-empty, unique, and foreign keys)

Our database migration process has encountered many of these problems, such as a foreign key constraint, but the other table does not exist, there are vacant records, but the model definition is not allowed, such as the process of copying a unique constraint, which requires manual intervention, I must use the SQL statement to clear out these, DumpData and LoadData will detect these, so your database must be a clean and consistent state.

Hard encoding of primary key

It's painful, because in our test suite, there are all the hard coding of the primary key, so many tests fail because the method of using PostgreSQL processing is not the same as MySQL, I have to manually modify more than 700 test cases, most of them are very simple changes but time-consuming.

Native SQL statement

Luckily, we only have one place where native SQL statements are used, and some of the functions in MySQL do not apply in PostgreSQL, and we only use functions that are modified to the same function in PostgreSQL.

Case sensitive

String comparisons are case sensitive in PostgreSQL, but not in MySQL, I also encountered some problems migrating non-Django data tables, when the index was created, the command required ID, but the name of the field is ID (the letter i), I just rename the ID on it.