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


#1

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 https://github.com/mattermost/platform/issues/4911 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 !


#2

how to create MySQl DB for mattermost in windows.


#3

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