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.
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
1 in the dump, while PostgreSQL expects
'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.
You just have to copy the mattermost
data folder to
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 !