Automated PostgreSQL migration

plans-img-yellow Available on Entry, Professional, Enterprise, and Enterprise Advanced plans

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, even in air-gapped deployment environments.

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. If preferred, you can download and compile the migration-assist tool yourself.

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. You can use our official Mattermost Docker image for pgloader (mattermost/pgloader:latest); please note that it does not currently support MySQL’s caching_sha2_password authentication plugin. If you require caching_sha2_password support, you’ll need to build your own image and include the qitab/qmynd library. See the pgloader installation documentation for details.

Usage

Important

  • If you encounter heap exhaustion errors in pgloader, edit your generated migration.load and under the WITH block set: prefetch rows = 1000 and consider reducing it if the issue persists.

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

  • If you were previously utilizing a database for handling the Mattermost configuration, those tables will not be migrated from your MySQL database with the migration script. You will need to manually migrate those configuration settings to your PostgreSQL database after completing the migration process. See the configuration in database documentation for details.

  • This migration requires a maintenance window. Mattermost must be taken offline on the MySQL host before pgloader runs, and must not be started against the target PostgreSQL database until the migration is complete.

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 (you can combine all of them):

--fix-artifacts   Removes the artifacts from older versions of Mattermost
--fix-unicode     Strips unsupported Unicode characters from MySQL tables
--fix-varchar     Removes rows exceeding column lengths

Step 2 - Create the PostgreSQL database schema

Before running migrations, ensure the public schema is owned by your migration user:

sudo -u postgres psql -d mattermost -c "ALTER SCHEMA public OWNER TO mmuser; GRANT ALL ON SCHEMA public TO mmuser;"

Then run:

migration-assist postgres "<POSTGRES_DSN>" \
  --run-migrations \
  --mattermost-version="<MATTERMOST_VERSION>"

  • <POSTGRES_DSN> example: postgres://user:password@address:5432/db_name

  • <MATTERMOST_VERSION> example: 10.5.4

Important

--run-migrations builds the PostgreSQL schema by applying Mattermost’s migration scripts — it does not directly copy the schema structure from MySQL. Set --mattermost-version to the version of Mattermost currently running against your source MySQL database (or a later patch release on the same line). Specifying an older version omits columns that were added in later patch releases, causing pgloader to fail with pgloader failed to find column … in target table. Alternatively, if your database schema has diverged from an official release, --applied-migrations can be supplied with the mysql.output file generated by migration-assist mysql to apply only the migrations matching your source database’s state — see Air-gapped environments for details.

To confirm your actual version before migrating:

  • Mattermost: click the grid menu in the top left and select About Mattermost.

  • CLI: run mattermost version on the server.

If multiple environments run the same Mattermost version, use the same --mattermost-version value. Otherwise, use the version running against each environment’s source MySQL database. Version skew between environments produces inconsistent target schemas and is a common source of pgloader failures.

The tool ships with pre-packaged migration scripts through v10.12 (migration 141), covering all MySQL-supported Mattermost versions — no internet access is required for these versions. If your environment is air-gapped or your database schema has diverged from an official release, see Air-gapped environments for an alternative approach using --applied-migrations.

Note

Once this step completes, do not start Mattermost against the new PostgreSQL database until pgloader has finished. Starting the app writes initial data to the target (for example, plugin key-value store entries) that will conflict with pgloader’s inserts and produce duplicate key value violates unique constraint errors.

By default, two pre-checks run before migration:

  • --check-schema-owner=true

  • --check-tables-empty=true

To disable them:

--check-schema-owner=false \
--check-tables-empty=false

Step 3 - Generate a pgloader configuration

Run the following command to emit a pgloader configuration file:

migration-assist pgloader \
  --mysql="<MYSQL_DSN>" \
  --postgres="<POSTGRES_DSN>" \
  --remove-null-chars \
  > migration.load

  • By default, null characters in text columns are stripped. Disable with --remove-null-chars=false.

  • If you run out of heap, edit migration.load and under the WITH block set:

    prefetch rows = 1000

Step 4 - Run pgloader

Before running pgloader, confirm that Mattermost is offline on the MySQL host so that no new data is written to the source database during the transfer.

Run pgloader with the generated configuration file:

pgloader migration.load > migration.log

Carefully review migration.log for errors. In particular, check the summary table printed at the end of the run: any table whose errors column is non-zero did not load completely; some rows may have transferred. For those tables, compare row counts between source and target to confirm what is missing, then see Reloading a single failed table for recovery steps. Use the mattermost/pgloader:latest Docker image to avoid build/auth issues.

Step 5 - Restore full-text indexes & create all indexes

Run:

migration-assist postgres post-migrate --create-indexes "<POSTGRES_DSN>"

  • The --create-indexes flag rebuilds full-text indexes on Posts and FileInfo, plus all other Mattermost indexes.

  • Omitting that flag only restores full-text indexes for Posts and FileInfo.

See the Restore full-text indexes documentation for details.

Step 6 - Complete plugin migrations

Generate pgloader configs for 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

Run each:

pgloader boards.load           > boards_migration.log
pgloader playbooks.load        > playbooks_migration.log
pgloader calls.load            > calls_migration.log

Skip any plugin you don’t use; check logs for JSON or missing-table errors. See the Plugin migrations guide for more.

Step 7 - Configure Mattermost to use PostgreSQL

In your config.json or via environment variables, update:

"SqlSettings": {
  "DriverName": "postgres",
  "DataSource": "postgres://mmuser:pass@db:5432/mattermost?sslmode=disable"
}

If your config was stored in the database, update MM_CONFIG accordingly. See the environment configuration settings documentation for details.

Note

If your Mattermost deployment was initially configured with MySQL, there’s a good chance your systemd service file has a BindsTo=mysql.service directive in it. This will cause the Mattermost server to be shut down if you deactivate your MySQL service. To fix this, update all references to mysql.service in your service file to use postgresql.service instead. This is only an issue if your Database and Mattermost are running on the same system.

Air-gapped environments

Follow these steps to migrate within an air-gapped environment:

  1. Verify that the migration-assist binary is the latest version available to benefit from improvements and fixes.

  2. Transfer the latest migration-assist binary into the air-gapped environment (e.g., via secure media).

  3. Generate the MySQL schema+data output using fix flags. This produces mysql.output:

    migration-assist mysql "user:pass@tcp(localhost:3306)/mattermost" \
  --fix-artifacts --fix-unicode --fix-varchar > mysql.output

  4. Apply migrations using the generated output:

    migration-assist postgres \
  "postgres://mmuser:pass@localhost:5432/imported?sslmode=disable" \
  --run-migrations --applied-migrations="./mysql.output"

  5. Continue from Step 3 through Step 7 above to complete data transfer and configuration.

Tool commands

The migration-assist tool offers 3 core commands:

  1. migration-assist mysql — Checks MySQL schema readiness and offers fixes.

  2. migration-assist postgres — Builds the PostgreSQL schema and applies migrations.

  3. migration-assist pgloader — Generates a pgloader config for data transfer.

Compile the migration-assist tool

Requires Go ≥ v1.22. Install with:

go install github.com/mattermost/migration-assist/cmd/migration-assist@latest

Troubleshooting

See troubleshooting errors during migration from MySQL to PostgreSQL.