Migration guidelines from MySQL to PostgreSQL#

plans-img Available on all plans

deployment-img self-hosted deployments

From Mattermost v8.0, PostgreSQL is our database of choice for Mattermost to enhance the platform’s performance and capabilities. Recognizing the importance of supporting the community members who are interested in migrating from a MySQL database, we have taken proactive measures to provide guidance and best practices.

  • Automated migration from MySQL to PostgreSQL - A comprehensive set of guidelines and a migration-assist tool to streamline the migration process, alleviate potential challenges, and faciliate a smooth transition.

  • Manually migrate from MySQL to PostgreSQL - A good option if your organization has database administrators to own the migration process, or if you want to learn what the migration-assist tool automates for you.

Frequently asked questions#

  1. Can the migration-assist be run on the mattermost server?

Technically, yes. The migration-assist tool can be run on the Mattermost server. However, it is recommended to run the tool on a separate server to avoid any performance issues. We advise running the migration against a copy of the MySQL database to ensure that the migration process does not impact the production environment.

  1. How large should the PostgreSQL server be?

The size of the PostgreSQL server should match that of the MySQL server initially. We recommend monitoring the performance of the PostgreSQL server and adjusting the resources as needed.

  1. How large should the server running migration-assist server be?

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 for default configurations. However, you may need to adjust the resources based on the size of the MySQL database, your downtime limitations, and the configuration of pgloader.

  1. Do we/will we bundle pgloader or is that a separate install?

We do not bundle pgloader with the Mattermost server. You will need to install pgloader separately. For more information, see the install pgloader documentation.

  1. Are there any other migrations available for plugins, or just Boards, Playbooks and Calls?

We are working on providing migrations for other plugins as well such as NPS-plugin. Please stay tuned for updates.

  1. Does these processes support AWS RDS databases?

Yes, the processes support AWS RDS databases. However, you may need to adjust the security group settings to allow the migration process to access the databases.

Troubleshooting#

Unsupported authentication for MySQL#

If you are facing an error due to authentication with MySQL v8, it may be related to a known issue with pgloader. The fix is to set the default authentication method to mysql_native_password in your MySQL configuration. To do so, add the default-authentication-plugin=mysql_native_password value to your mysql.cnf file. Also, do not forget to update your user to use this authentication method.

ALTER USER '<mysql_user>'@'%' IDENTIFIED WITH mysql_native_password BY '<mysql_password>';

Errors during the pgloader command execution#

If you encounter errors during the execution of the pgloader command, ensure that both of the databases are accessible and that the users have the necessary permissions to access the database. Do not continue with the migration if there are errors during the execution of the pgloader command.

Also, there may be cases where pgloader continue to migration remaining tables and skip one or more tables. In such cases, it is recommended to identify issues with the table and fix them before running the pgloader command again with a clean database. It is possible to run the pgloader command with the --debug flag to get more information about the errors.

For experienced users, it is recoverable to run the pgloader without requiring to restart the migration from scratch. In this case, you will need to manually fix the issues with the table and run the pgloader command with a tailored configuration just for those tables. Also ensure that the schema name is reverted back to public and the search_path is restored or remove necessary clauses form the configuration.

Mattermost can’t connect to the PostgreSQL database#

If you are facing an issue where Mattermost can’t connect to the PostgreSQL database, ensure that the PostgreSQL server is running and that the database is accessible. If there were errors during the execution of the pgloader command, it can fail to revert shcema name back to public or potentially restoring the search_path. You can manually revert the schema name back to public and restore the search_path by running the following commands:

ALTER SCHEMA <schema_name> RENAME TO public;

Also ensure that the database user has the necessary settings to have default access to the public schema. You can do this by running the following commands:

ALTER USER <user> SET SEARCH_PATH TO 'public';
SELECT pg_catalog.set_config('search_path', '"$user", public', false); -- should give access for the session

You can check for the default search_path by running the following command:

SELECT boot_val FROM pg_settings WHERE name='search_path';

Contact Support#

Mattermost customers looking for guidance tailored to their Mattermost deployment can contact a Mattermost Expert for guidance.