Deprecating MySQL support and migration to PostgreSQL
Starting in OpenProject 9.0.0, OpenProject is deprecating support for MySQL and MariaDB variants in its community and enterprise editions. MySQL remains fully supported for the major branch of 9.x releases but may see removal starting in OpenProject 10 versions and above. This post will explain reasons and provide detailed guides on how to migrate safely without any loss of information.
Over the past years, SQL functionality in major DBMS such as PostgreSQL and MySQL have skyrocketed, but common SQL support is still stuck at old standards. For OpenProject, it has been difficult to make use of newer SQL features since we traditionally supported both PostgreSQL and MySQL with the same priority. While this works great in Rails for basic functionality, advanced SQL support such as json(b), CTE, full text search functionality and other, often performance related improvements could not be used or required efforts to make compatible with both systems given the plethora of version differences in MySQL 5.6, 5.7 and above. At the same time, the same standard-compliant SQL would often be optimized better by PostgreSQL DBMS, resulting in degraded performance in some edge cases for MySQL users.
For a while now, PostgreSQL has received increased importance such as in the full-text search functionality which simply does not exist on MySQL versions < 8.0., which is not available natively on major distributions we support. This has resulted in some confusion and performance impacts for users who installed OpenProject with MySQL due to missing functionality or degraded performance.
Migration to PostgreSQL
Thanks to a community project called pgloader, it is very easy to convert a running MySQL installation of OpenProject to PostgreSQL without any data loss in a matter of seconds to minutes. OpenProject provides detailed guides on how to perform the migration to PostgreSQL as part of 9.0 for all supported installations. We suggest installing the upgrade to 9.0.0., and then perform the migrations. The version comes with additional scripts and helpers to perform the update.
Please select your installation type below to get to the respective instructions for your OpenProject installation:
Frequently asked questions
Will I lose any data during the migration? No. The migration with pgloader is safe when pointing to an empty PostgreSQL database you wish to migrate to. Your installation will be unchanged for the regular user, and all data will be as-is. This is mainly possible due to our high compatibility of OpenProject between the two versions as of now. As detailed in the migration guides, we do recommend taking a full backup of OpenProject before performing the migration however.
Do I need to perform the migration before updating to 9.x? No. We recommend performing the update after upgrading to 9.0.0, because this version contains some fixes for older migrations that need to be re-run for full PostgreSQL support. The order does not matter however, you can install the new version, run the migration to PostgreSQL, and then install the upgrade, or do it at some later point after 9.0 is released.
When will you drop support for MySQL? As part of OpenProject 10.0. OpenProject 9.x. will support MySQL as the versions before, you can safely upgrade to all 9.X versions in the stable/9 branches. If you’re testing or working with OpenProject development branches, please watch our GitHub repository for changes in MySQL support. OpenProject 10.0. will likely still be released in 2019, but you will have enough time to migrate safely.
OpenProject 9.0.0
In OpenProject version 9.0.0 onwards, administrators of MySQL-based installations will be shown a notification at the bottom of their installation as detailed in the following screenshot. This notification can be permanently removed by hovering and hiding it through clicking the small X. It will serve as a reminder that action will have to be taken at some point in future to ensure compatibility with the newest versions of OpenProject.
The MySQL warning bar shown to administrators of OpenProject instances in 9.0.0
Contact us
We are determined to make this migration as smooth as possible for existing users of OpenProject on MySQL installations. Please reach out to use via support@openproject.com if you have further questions or feedback regarding the migration.
