Converting DB from MySQL (mattermost standalone install) to PostgreSQL (omnibus-gitlab)

Summary

This is more of a summary of what I did, to help others if need be.
I switched from a source installation of mattermost, with Mysql DB, to the packaged version in omnibus-gitlab with the included PostgreSQL DB.

Copied from Convert DB from MySQL to PostgreSQL · Issue #4911 · mattermost/mattermost-server · GitHub for more visibility.

Steps

Activate mattermost in gitlab-omnibus and launch reconfigure
Prepare the SQL dump

Dump the MySQL DB:
mysqldump --compatible=postgresql --default-character-set=utf8 -r mattermost.mysql -u root mattermost -p --complete-insert

Update the dump to match the PostgreSQL DB schema: Only keep the INSERT INTO commands, convert to lowercase all the column and table names, insert TRUNCATE table_name before each block of INSERT commands if you want to clean the DB.

The tricky part : booleans are stored as int4 in MySQL, and appear as 0 and 1 in the dump, while PostgreSQL expects '0' and '1' for boolean values. You need to edit all the corresponding values.
(I wrote a script using https://sqlparse.readthedocs.io to do this.)

Now the cleaned-up dump is ready.

Copy data

You just have to copy the mattermost data folder to /var/opt/gitlab/mattermost/data.

Restore dump

sudo -u gitlab-psql /opt/gitlab/embedded/bin/psql -h /var/opt/gitlab/postgresql -d mattermost_production < mattermost_good.sql

I hope it can help someone !

how to create MySQl DB for mattermost in windows.

Very inappropriate
If you have a problem, please post in the forum.

Thank you!

Do you still have the sqlparse script by any chance?

Has anyone been working on getting a solid path forward from mysql to postgresql with current releases? The various benefits simply cannot be ignored and we’d like to migrate.