We discussed again our journey to a single RDBM in another thread and I want to pull it to the top:
The ultimate goal is to have only a single RDBM for both development and production. This will simplify code, support and allow us to leverage specific feature of the picked system which is PostgreSQL. The proposal and schedule is:
announce on our blog that 1.23 will be the last release supporting MySQL
implement a warning UI bar that MySQL is ending soon
release 1.23 with tested documentation on how to migrate to MySQL
drop MySQL from CI
drop MySQL from docker image
remove all MySQL custom queries from the codebase
remove MySQL from the installer
release 1.24 with tested documentation on how to upgrade
solve technical challenge of generating apipie bindings without SQLite
drop SQLite
remove all custom code SQLite
start working on integrating modern PostgreSQL features, for example
macaddr, macaddr6, ip, ip6
JSON/JSONB
full text search
hash indexes
partitioning
stored procedures (for very specific updates - e.g. fact or report import)
Optionally:
push a warning bar in a 1.22.x update to inform users even more early
I am willing to be owner of this effort if we agree with the plan.
Now, this is possibly disturbing change for our users. Unfortunately we donāt have data on how many users have MySQL, I looked in Community Surveys 2016-2019 and we havenāt asked. So no idea how many users will be affected.
The goal for the initial phase is to gather feedback from the field. In the worst case, we end up with instructions how to migrate database (we already have something in our docs) and we can cease or postpone actual removal if it turns out too many users are affected.
I like the idea, as it allows the developers to support one DB really well, rather than all of them somehow and sets the proper expectations, rather then claiming we support something that just minority of developers would use in reality. It also goes in sync towards more opinionated setup, as the feedback weāre hearing is ātoo many optionsā.
It would be good to know the reasons why people are on MySQL. Possible options I can think of:
I just know MySQL more than PostgreSQL, therefore itās my default choice:
seems like this itself should not be hard blocker: I would guess avoiding dealing with Postgres in one way or another is pretty hard in 2019
I need to run DB in very specific setup, that is not possible in PostgreSQL:
In this case, we should hear more about the specifics, consider options to achieve them in PostgreSQL and compare the negative impact in case we donāt support it with the positive impact of being able to use the PostgreSQL specific features.
Iām not allowed to use anything but MySQL in my organization:
Iām not sure it would even be realistic, just keeping it here just for completeness
I like the plan. I didnāt see droppping foreman-mysql2 and foreman-sqlite rpm packages, removing respective bundler groups in the list, but I suppose it didnāt aim to be complete list. Btw hereās a blog post about migrating the DB Foreman :: Migrating Foreman from MySQL to Postgresql it would be good to verify it still works on some production DB, but most likely can be recycled
Would be interesting to hear from @Dirk and @Bernhard_Suttner- do you have customers using MySQL in production? If so, what are the considerations for it?
I know Red Hat Satellite customers are all using PostgreSQL as that is the only database supported by Katello. I assume that Orcharhino also only ships with PG for the same reason, but would be good to confirm.
For the record, another MySQL problematic thing is that MySQL does not support transactions for DDL, so when migration fails it can easily get into bad state which can be only fixed by manual changes:
I support this effort. Foreman should be installed on its own dedicated machine, so it makes sense to focus on one dedicated path forward re: databases.
And I have refactoring reports to a single blob column on my short-term plate now Time flies!
Btw itās not the index limit what I refer to but the fact that DDL is not secured by transactions. All migrations for MySQL can fail and get the DB into unrepairable state (or big effort to investigate).
Considering we are branching for 1.23 today and the blog post is not published yet, lets make 1.25 the PG-only version. This will give us more time to test and verify the migrations work and our users more time to prepare for the migration.
