Database conversion from SQLite to PostgreSQL (or MySQL)

[Roberto Rosario]

At the moment Django (the web framework used by Mayan) doesn't provide an official method to migrate from database engine to another.

It should not be up to an application software to perform platform, framework or OS work.

However, since many Mayan users are not familiar with Django or database engines (and they shouldn't need to) we've started a branch to add support for conversion of the database backend by Mayan itself. The branch is available here: https://gitlab.com/mayan-edms/mayan-edms/tree/feature/db_migration

We are focusing on making the database conversion as simple as possible. For this we are creating a command line program that will export the data from SQLite and import it to a new initialized PostgreSQL or MySQL database. We are calling this command 'convertdb'. Since the SQL file from one database vendor is not compatible with the SQL format of another vendor the data is exported using JSON (a database independent format) format using Django's DUMPDATA and LOADDATA commands. DUMPDATA and LOADDATA were not made for database migrations, they were designed to create FIXTURE files. FIXTURES are files that initialized a database with some data for use in test suits. Our tests indicate that these commands are suitable for export and import of data.

Since not all databases increment primary keys in the same way, the ordering of records can be lost during the conversion and the import will fail. To avoid this we are adding support to reference database objects using natural keys. These allow referencing a database object by a field or a collection of fields that are naturally unique, instead of using an autogenerate primary key number. These natural keys are used only during the database conversion and not for normal operation. Natural keys ensure that all database object relationships remain intact after the conversion.

Creating natural keys is a slow process of analysis and introspection of all the models defined in the Mayan apps. It is also a process that involves a lot of testing and simulation. We've given this feature top priority. Once this support is stable it will be included in the next planned release, version 3.1 so that user can start enjoying the benefits of the 3.x series without worrying about database migrations. Thank you.