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.
    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 results of the query above on the MySQL database. This will make room for Postgres encoded UUID’s (with four “-” char inside).
  6. Execute the query on MySQL database.
    SELECT CONCAT('ALTER TABLE ', a.table_name, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;')
    FROM information_schema.tables a
    WHERE a.table_schema = 'DB_NAME';
  7. Execute the results of the query above on the MySQL database. This will enable MySQL to store auth_groups names in case-sensitive manner.
  8. Using DBeaver export the data from the PostgreSQL database into MySQL one.
  9. Execute the query on MySQL database.
  10. 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)';
  11. Execute the results of the query above on the MySQL database. This will convert the Postgres encoded UUID into what Django expects to be in the MySQL database.
  12. Execute the query on MySQL database.
    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)';
  13. Execute the results of the query above on the MySQL database. This will remove four unnecessary characters from the UUID fields.
  14. Enable key checks on MySQL server:
    SET GLOBAL FOREIGN_KEY_CHECKS = 1;
  15. 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 *