Automated PostgreSQL migration#

plans-img Available on all plans

deployment-img 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:

  1. 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.

  2. migration-assist postgres

    Creates the PostgreSQL database schema, and prepares it for Mattermost deployment by downloading the necessary migrations and applying them.

  3. 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.

Note

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

Troubleshooting#

See troubleshooting errors during migration from MySQL to PostgreSQL