Automated PostgreSQL migration#
Available on all plans
self-hosted deployments
Migrating databases can be a daunting task, and it can be easy to overlook or misinterpret some of the required steps if you haven’t performed a migration before. Our migration-assist
tool provides an efficient, error-free migration experience that automates the tasks to be executed.
The migration-assist
tool offers 3 core utility commands:
migration-assist mysql
Checks the MySQL database schema to ensure readiness for migration, and offers fixes for common issues. See the install section below for details on installing the
migration-assist
tool.migration-assist postgres
Creates the PostgreSQL database schema, and prepares it for Mattermost deployment by downloading the necessary migrations and applying them.
migration-assist pgloader
Generates a pgloader configuration from DSN values, ensuring accurate data transfer. See the install pgloader documentation for details on installing the pgloader tool.
Not sure this tool is right for your Mattermost deployment? Mattermost customers looking for tailored guidance based on their Mattermost deployment can contact a Mattermost Expert.
Install#
Download the Mattermost migration-assist
tool from the GitHub repository releases page.
While you can run the migration-assist
tool on the same server as your Mattermost deployment, we recommend running the tool in a virtual machine on the same network as your Mattermost server instead. The tool itself is lightweight and does not require a large server. A server with 2 CPU cores and 16 GB of RAM should be sufficient.
Tip
If preferred, you can download and compile the migration-assist
tool yourself. See the compile section below for details.
You’ll also need to install the pgloader
tool to migrate your data from MySQL to PostgreSQL. We recommend running pgloader
in a virtual machine on the same network as your Mattermost server. See the pgloader installation documentation for details.
Usage#
Important
Please make sure you have the necessary environment to perform the migration. Ensure that the MySQL and PostgreSQL databases are running and accessible. To set up a PostgreSQL instance, see the prepare your Mattermost database documentation for details.
Step 1 - Check the MySQL database schema#
Run the following command to check the MySQL database schema:
migration-assist mysql "<MYSQL_DSN>" # example DSN: "user:password@tcp(address:3306)/db_name"
This command outputs the readiness status and prints required fixes for common issues. The flags for fixes are as follows (where all fixes can be used together at the same time):
--fix-artifacts Removes the artifacts from older versions of Mattermost
--fix-unicode Removes the unsupported unicode characters from MySQL tables
--fix-varchar Removes the rows with varchar overflow
Step 2 - Create the PostgreSQL database schema#
Run the following command to create the Postgres database schema:
migration-assist postgres "<POSTGRES_DSN>" --run-migrations --mattermost-version="<MATTERMOST_VERSION>" # example DSN: "postgres://user:password@address:5432/db_name", example Mattermost version: "v9.4.0"
This command downloads the necessary migrations and applies them to the Postgres database. The --mattermost-version
flag is required to specify the Mattermost version you are migrating from.
There are two flags that can be used with the migration-assist postgres
command to run a few checks before running the migrations. You can disable them by setting the following flags to false:
--check-schema-owner Check if the schema owner is the same as the user running the migration (default true)
--check-tables-empty Check if tables are empty before running migrations (default true)
Step 3 - Generate a pgloader configuration#
Run the following command to generate a pgloader configuration:
migration-assist pgloader --mysql="<MYSQL_DSN>" --postgres="<POSTGRES_DSN>" > migration.load
This command will generate a pgloader configuration file that can be used to migrate the data from MySQL to Postgres.
The generated configuration has the setting to remove the null character from the text type data. This is to ensure the migration won’t return errors while inserting data into Postgres. However, if you want to disable this behavior, you can set the --remove-null-chars
to false
.
Step 4 - Run pgloader#
Run pgloader with the generated configuration file:
pgloader migration.load > migration.log
Carefully read the log file to analyze whether there were any errors during the migration process. If there were any errors, please contact Mattermost for further guidance.
Step 5 - Restore full-text indexes#
Run the following command to create the full-text indexes for the Posts
and FileInfo
tables:
migration-assist postgres post-migrate "<POSTGRES_DSN>"
This command creates the full-text indexes for the Posts
and FileInfo
tables. See the Restore full-text indexes documentation for more information.
Step 6 - Complete plugin migrations#
Generate migration configuration for collaborative playbooks, boards and calls:
migration-assist pgloader boards --mysql="<MYSQL_DSN>" --postgres="<POSTGRES_DSN>" > boards.load
migration-assist pgloader playbooks --mysql="<MYSQL_DSN>" --postgres="<POSTGRES_DSN>" > playbooks.load
migration-assist pgloader calls --mysql="<MYSQL_DSN>" --postgres="<POSTGRES_DSN>" > calls.load
Then run pgloader with the generated configuration files:
pgloader boards.load > boards_migration.log
pgloader playbooks.load > playbooks_migration.log
pgloader calls.load > calls.log
Carefully read the log file to analyze whether there were any errors during the migration process. See the Plugin migrations documentation for information on migrating Playbooks, Boards and Calls.
Step 7 - Configure Mattermost to utilize the new PostgreSQL database#
This is the final step of the migration process, where we need to update the Mattermost configuration to point to the new PostgreSQL database. To do so, locate the SqlSettings.DataSource
and SqlSettings.DriverName
fields in the config.json
then modify these fields to reflect the new PostgreSQL database connection details.
If your configuration was stored in the database, see the configuration in database documentation for migration details. Once migrated, you should also update the MM_CONFIG
environment variable to point to the new DSN.
Compile the migration-assist tool#
The migration-assist
tool can be downloaded and compiled with the Go toolchain. The tool requires at least v1.22
of the Go compiler.
Use go install
to install the tool:
go install github.com/mattermost/migration-assist/cmd/migration-assist@latest