Mattermost-server: Convert DB from MySQL to PostgreSQL

[NotSqrt]

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.

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.

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 !

[jasonblais]

Thanks @NotSqrt for writing this up!

Would you be interested adding this in our Mattermost Forums as well? That way more people will see the steps you took, as the Forums have more visitors :)

[NotSqrt]

Done : https://forum.mattermost.org/t/converting-db-from-mysql-mattermost-standalone-install-to-postgresql-omnibus-gitlab/2723

[mqu]

you may have a look at https://pgloader.io/