Migration of Django project from PostgreSQL to MySQL

  1. Create new MySQL database.
  2. Run Django migrations on the new database. This will require you to change settings of the project.
    python3 manage.py migrate
  3. Disable key checks on MySQL server.
    SET GLOBAL FOREIGN_KEY_CHECKS = 0;
  4. Execute the query on MySQL database. Then, execute the results of this query as another query. This will make room for Postgres encoded UUID’s (with four dashes inside).
    SELECT CONCAT('ALTER TABLE ', a.table_name, ' MODIFY COLUMN ' , a.column_name, ' CHAR (36);')
    FROM information_schema.columns a
    WHERE a.table_schema = 'DB_NAME'
    AND a.column_type = 'char(32)';
  5. Execute the queries on MySQL database. This will enable MySQL to store users and groups names in case-sensitive manner.
    ALTER TABLE auth_user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;
    ALTER TABLE auth_group CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;
  6. Using DBeaver export the data from the PostgreSQL database into MySQL one.
  7. Execute the query on MySQL database. Then, execute its results. This will convert the Postgres encoded UUID’s (with dashes) into what Django expects to be in the MySQL database (UUID’s with no dashes inside).
    SELECT CONCAT('UPDATE ', a.table_name, ' SET ' , a.column_name, ' = REPLACE(' , a.column_name, ', "-", "");')
    FROM information_schema.columns a
    WHERE a.table_schema = 'DB_NAME'
    AND a.column_type = 'char(36)';
  8. Execute the query on MySQL database. Then, execute the results of this query. This will remove the space needed for four unnecessary dashes from the UUID fields.
    SELECT CONCAT('ALTER TABLE ', a.table_name, ' MODIFY COLUMN ' , a.column_name, ' CHAR (32);')
    FROM information_schema.columns a
    WHERE a.table_schema = 'DB_NAME'
    AND a.column_type = 'char(36)';
  9. Enable key checks on MySQL server:
    SET GLOBAL FOREIGN_KEY_CHECKS = 1;
  10. Start the Django app and check is everything working.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *