Tutorial on migrating Django databases from MySQL to PostgreSQL

This article mainly introduces how to migrate the database used by Django from MySQL to PostgreSQL. It also mentions some precautions, if you want to migrate the database from mysql to PostgreSQL, you can see that we have already migrated the database to the Django1.6 app. if your environment is clean, this process will be very simple, you only need to allow syncdb or migrate to create a table, truncating the data in the table, and then run the dumpdata and loaddatamanagement commands.
Step 1: Create an empty instance in your PostgreSQL database:

CREATE DATABASE dbname OWNER rolename;

Step 2: Add configuration to the created database in your Django

In setting. py, we configure it as follows:

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': '',  }}

In this way, we specify the database to be operated by the name in the future.
Step 3: Create a table in the PostgreSQL instance

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

Run syncdb and migrations in PostgreSQL. there is no initial data in this process.
Step 4: truncate the newly created table

Although we specified-no-initial-data in the previous step, to prevent the user-defined data from being added to the table in unknown conditions during the migration process, we 'd better truncate the newly created table. we can generate an SQL script and run it:

python manage.py sqlflush --database=postgresql

Step 5: back up data from mysql to the JSON file

Django has a dumpdata command that can generate a database-independent backup. the default format is JSON.

python manage.py dumpdata --all --natural --indent=4 > dbname.json

Here, The-all parameter is used to ensure that you may have your own data filtering and modification requirements during data export. the-natural parameter tells Django to use natural keys (if available) -The indent parameter is used to make the output more readable.

You may want to export only the data in a specific app or only one celery logs. in this way, you can use the-exclude parameter, for example:

python manage.py dumpdata --all --natural --exclude=djcelery --exclude=search.SearchLog --indent=4 > dbname.json

Step 6: load JSON data to the PostgreSQL database

python manage.py loaddata dbname.json --database=postgresql

Basically, the migration process is over. now you only 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, and our database is created for a legacy PHP code. we are still getting rid of it step by step, we have some databases other than Django that are used by programs. to migrate these databases to PostgreSQL, I used this tool. The process is simpler.
Notes
Django signal

You may want to disable this. when a database record is created, your program may send emails to you. to avoid disturbing them, when loading data, you need to ensure that they are disabled. Here's one way to handle this Here we use a detector to ensure that the signal will not be triggered.
Constraint (such as non-null, unique, and foreign key)

We encountered many such problems in the database migration process, such as a foreign key constraint, but another table does not exist. there are some vacant records, but the model definition is not allowed, for example, if the unique constraint exists during the replication process, all of these require manual intervention. I must clear these with SQL statements, and Dumpdata and loaddata will detect these, therefore, your database must be in a clean and consistent state.

Hard encoding of the primary key

This is very painful, because in our test suite, the hard encoding of the primary key is everywhere, so many tests fail, because the sequence method processed by PostgreSQL is not the same as that of mysql, I have to manually modify more than 700 test cases, most of which are simple modifications but time-consuming.

Native SQL statement

Fortunately, we only use one native SQL statement. some mysql functions are not applicable in PostgreSQL. we only need to modify them to functions with the same functions in PostgreSQl.

Case Sensitive

Strings are case-sensitive in PostgreSQL, but not in Mysql. I encountered some problems during the migration of non-Django data tables. when creating indexes, the command requires id, however, the field name is Id (I). I only need to rename it to id.