Configure the database environment in which Mattermost is deployed by going to System Console > Environment > Database, or by editing the config.json file as described in the following tables. Changes to configuration settings in this section require a server restart before taking effect.

Mattermost Academy Learn about setting up the Mattermost database

Driver name#

Also available in legacy Mattermost Enterprise Edition E10 or E20

The type of database. Can be either:

mysql: (Default) Enables driver to MySQL database.
postgres: Enables driver to PostgreSQL database.

System Config path: N/A
config.json setting: ".SqlSettings.DriverName",
Environment variable: MM_SQLSETTINGS_DRIVERNAME

Data source#

Also available in legacy Mattermost Enterprise Edition E10 or E20

The connection string to the master database.

String input.

System Config path: N/A
config.json setting: ".SqlSettings.DataSource",
Environment variable: MM_SQLSETTINGS_DATASOURCE

PostgreSQL databases

When Driver Name is set to postgres, use a connection string in the form of: postgres://mmuser:password@hostname_or_IP:5432/mattermost_test?sslmode=disable&connect_timeout=10

To use TLS with PostgreSQL databases

The parameter to encrypt connection against a PostgreSQL server is sslmode. The library used to interact with PostgreSQL server is pq. Currently, it’s not possible to use all the values that you could pass to a standard PostgreSQL Client psql "sslmode=value" See the SSL Mode Descriptions documentation for details.

Your database admin must configure the functionality according to the supported values described below:

Short description of the `sslmode` parameter	Value	Example of a data source name
Don’t use TLS / SSL encryption against the PostgreSQL server. Default value in file `config.json`	`disable`	`postgres://mmuser:password@hostname_or_IP:5432/mattermost_test ?sslmode=disable&connect_timeout=10`
The data is encrypted and the network is trusted. Default value is `sslmode` when omitted.	`require`	`postgres://mmuser:password@hostname_or_IP:5432/mattermost_test ?sslmode=require&connect_timeout=10`
The data is encrypted when connecting to a trusted server.	`verify-ca`	`postgres://mmuser:password@hostname_or_IP:5432/mattermost_test ?sslmode=verify-ca&connect_timeout=10`
The data is encrypted when connecting to a trusted server.	`verify-full`	`postgres://mmuser:password@hostname_or_IP:5432/mattermost_test ?sslmode=verify-full&connect_timeout=10`

MySQL databases

When Driver Name is set to mysql, we recommend using collation over using charset.

To specify collation:

"SqlSettings": {
    "DataSource":
"<mmuser:password>@tcp(hostname or IP:3306)/mattermost?charset=utf8mb4,utf8&collation=utf8mb4_general_ci",
    [...]
 }

If collation is omitted, the default collation, utf8mb4_general_ci is used:

"SqlSettings": {
    "DataSource": "<mmuser:password>@tcp(hostname or IP:3306)/mattermost?charset=utf8mb4,utf8",
    [...]
 }

Note: If you’re using MySQL 8.0 or later, the default collation has changed to utf8mb4_0900_ai_ci. See our Database Software Requirements documentation for details on MySQL 8.0 support.

To use TLS with MySQL databases

The parameter to encrypt connection against a MySQL server is tls. The library used to interact with MySQL is Go-MySQL-Driver. For the moment, it’s not possible to use all the values that you could pass to a standard MySQL Client mysql --ssl-mode=value. See Connection-Encryption Option Summary documentation for a version 8.0 example.

Your database admin must configure the functionality according to supported values described below:

Short description of the `tls` parameter	Value	Example of a data source name
Don’t use TLS / SSL encryption against MySQL server.	`false`	`"<mmuser:password>@tcp(hostname or IP:3306)/mattermost_test ?charset=utf8mb4,utf8&writeTimeout=30s&tls=false"`
Use TLS / SSL encryption against MySQL server.	`true`	`"<mmuser:password>@tcp(hostname or IP:3306)/mattermost_test ?charset=utf8mb4,utf8&writeTimeout=30s&tls=true"`
Use TLS / SSL encryption with a self- signed certificate against MySQL server.	`skip-verify`	`"<mmuser:password>@tcp(hostname or IP:3306)/mattermost_test ?charset=utf8mb4,utf8&writeTimeout=30s&tls=skip-verify"`
Use TLS / SSL encryption if server advertises a possible fallback; unencrypted if it’s not advertised.	`preferred`	`"<mmuser:password>@tcp(hostname or IP:3306)/mattermost_test ?charset=utf8mb4,utf8&writeTimeout=30s&tls=preferred"`

AWS High Availablity RDS cluster deployments

For an AWS High Availability RDS cluster deployment, point this configuration setting to the write/read endpoint at the cluster level to benefit from the AWS failover handling. AWS takes care of promoting different database nodes to be the writer node. Mattermost doesn’t need to manage this. See the high availablility database configuration documentation for details.

Maximum idle database connections#

Also available in legacy Mattermost Enterprise Edition E10 or E20

The maximum number of idle connections held open to the database.

Numerical input. Default is 10.

System Config path: Environment > Database
config.json setting: ".SqlSettings.MaxIdleConns": 10,
Environment variable: MM_SQLSETTINGS_MAXIDLECONNS

Maximum open connections#

Also available in legacy Mattermost Enterprise Edition E10 or E20

The maximum number of open connections to the database.

Numerical input. Default is 300 for self-hosted deployments, and 100 for Cloud deployments.

System Config path: Environment > Database
config.json setting: ".SqlSettings.MaxOpenConns": 300,
Environment variable: MM_SQLSETTINGS_MAXOPENCONNS

Query timeout#

Also available in legacy Mattermost Enterprise Edition E10 or E20

The amount of time to wait, in seconds, for a response from the database after opening a connection and sending the query.

Numerical input in seconds. Default is 30 seconds.

System Config path: Environment > Database
config.json setting: ".SqlSettings.QueryTimeout: 30",
Environment variable: MM_SQLSETTINGS_QUERYTIMEOUT

Maximum connection lifetime#

Also available in legacy Mattermost Enterprise Edition E10 or E20

Maximum lifetime for a connection to the database, in milliseconds. Use this setting to configure the maximum amount of time a connection to the database may be reused

Numerical input in milliseconds. Default is 3600000 milliseconds (1 hour).

System Config path: Environment > Database
config.json setting: ".SqlSettings.ConnMaxLifetimeMilliseconds: 3600000",
Environment variable: MM_SQLSETTINGS_CONNMAXLIFETIMEMILLISECONDS

Maximum connection idle timeout#

Also available in legacy Mattermost Enterprise Edition E10 or E20

Maximum time a database connection can remain idle, in milliseconds.

Numerical input in milliseconds. Default is 300000 (5 minutes).

System Config path: Environment > Database
config.json setting: ".SqlSettings.ConnMaxIdleTimeMilliseconds: 300000",
Environment variable: MM_SQLSETTINGS_CONNMAXIDLETIMEMILLISECONDS

Minimum hashtag length#

Also available in legacy Mattermost Enterprise Edition E10 or E20

Minimum number of characters in a hashtag. This value must be greater than or equal to 2.	System Config path: Environment > Database `config.json` setting: `".SqlSettings.MinimumHashtagLength: 3",` Environment variable: `MM_SQLSETTINGS_MINIMUMHASHTAGLENGTH`
Note: MySQL databases must be configured to support searching strings shorter than three characters. See the MySQL documentation for details.

SQL statement logging#

Also available in legacy Mattermost Enterprise Edition E10 or E20

Executed SQL statements can be written to the log for development.

true: Executing SQL statements are written to the log.
false: (Default) SQL statements aren’t written to the log.

System Config path: Environment > Database
config.json setting: ".SqlSettings.Trace: false",
Environment variable: MM_SQLSETTINGS_TRACE

Recycle database connections#

Note

Available only on Enterprise plans

Also available in legacy Mattermost Enterprise Edition E20

Select the Recycle Database Connections button to manually recycle the connection pool by closing the current set of open connections to the database within 20 seconds, and then creating a new set of connections.

To fail over without stopping the server, change the database line in the config.json file, select Reload Configuration from Disk via Environment > Web Server, then select Recycle Database Connections.

System Config path: Environment > Database
config.json setting: N/A
Environment variable: N/A

Disable database search#

Also available in legacy Mattermost Enterprise Edition E10 or E20

When other search engines are configured, such as Elasticsearch, the database can be disabled to perform searches.

true: Disables the use of the database to perform searches. If another search engine isn’t configured, setting this value to true will result in empty search results.
false: (Default) Database search isn’t disabled.

System Config path: Environment > Database
config.json setting: ".SqlSettings.DisableDatabaseSearch: false",
Environment variable: MM_SQLSETTINGS_DISABLEDATABASESEARCH

Search behavior in Mattermost depends on which search engines are enabled.

When Elasticsearch is enabled, Mattermost will try to use it first.
If Elasticsearch fails or is disabled, Mattermost will attempt to use Bleve, if enabled. If this occurs, you will see the warning Encountered error on SearchPostsInTeamForUser.
If both Elasticsearch and Bleve fail or are disabled, Mattermost tries to search the database directly, if this is enabled.
If all of the above methods fail or are disabled, the search results will be empty.

Applied schema migrations#

Also available in legacy Mattermost Enterprise Edition E10 or E20

A list of all migrations that have been applied to the data store based on the version information available in the db_migrations table. Select About Mattermost from the product menu to review the current database schema version applied to your deployment.

Active Search Backend#

Read-only display of the currently active backend used for search. Values can include none, database, elasticsearch, or bleve.

Read replicas#

Note

Available only on Enterprise and Professional plans

Also available in legacy Mattermost Enterprise Edition E10 or E20

Specifies the connection strings for the read replica databases.

System Config path: N/A
config.json setting: ".SqlSettings.DataSourceReplicas": []
Environment variable: MM_SQLSETTINGS_DATASOURCEREPLICAS

Note: Each database connection string in the array must be in the same form used for the Data source setting.

AWS High Availablity RDS cluster deployments

For an AWS High Availability RDS cluster deployment, point this configuration setting directly to the underlying read-only node endpoint within the RDS cluster to circumvent the failover/load balancing that AWS/RDS takes care of (except for the write traffic). Mattermost has its own method of balancing the read-only connections, and can also balance those queries to the data source/write+read connection should those nodes fail. See the high availablility database configuration documentation for details.

Search replicas#

Note

Available only on Enterprise and Professional plans

Also available in legacy Mattermost Enterprise Edition E10 or E20

Specifies the connection strings for the search replica databases. A search replica is similar to a read replica, but is used only for handling search queries.

System Config path: N/A
config.json setting: ".SqlSettings.DataSourceSearchReplicas": []
Environment variable: MM_SQLSETTINGS_DATASOURCESEARCHREPLICAS

Note: Each database connection string in the array must be in the same form used for the Data source setting.

AWS High Availablity RDS cluster deployments

Replica lag settings#

Note

Available only on Enterprise plans

Also available in legacy Mattermost Enterprise Edition E20

String array input specifies a connection string and user-defined SQL queries on the database to measure replica lag for a single replica instance.

These settings monitor absolute lag based on binlog distance/transaction queue length, and the time taken for the replica to catch up.

String array input consists of:

DataSource: The database credentials to connect to the database instance.
QueryAbsoluteLag: A plain SQL query that must return a single row. The first column must be the node value of the Prometheus metric, and the second column must be the value of the lag used to measure absolute lag.
QueryTimeLag: A plain SQL query that must return a single row. The first column must be the node value of the Prometheus metric, and the second column must be the value of the lag used to measure the time lag.

System Config path: N/A
config.json setting: ".SqlSettings.ReplicaLagSettings": []
Environment variable: MM_SQLSETTINGS_REPLICALAGSETTINGS

Notes:

The QueryAbsoluteLag and QueryTimeLag queries must return a single row.
To properly monitor this you must setup performance monitoring for Mattermost.

Configure the replica lag metric based on your database type. See the following tabs for details on configuring this for each database type.

AWS Aurora
Add the configuration highlighted below to your SqlSettings.ReplicaLagSettings array. You only need to add this once because replication statistics for AWS Aurora nodes are visible across all server instances that are members of the cluster. Be sure to change the DataSource to point to a single node in the group.

For more information on Aurora replication stats, see the AWS Aurora documentaion.

Example:
{
  "SqlSettings": {
      "ReplicaLagSettings": [
        {
            "DataSource": "replica-1",
            "QueryAbsoluteLag": "select server_id, highest_lsn_rcvd-durable_lsn as bindiff from aurora_global_db_instance_status() where server_id=<>",
            "QueryTimeLag": "select server_id, visibility_lag_in_msec from aurora_global_db_instance_status() where server_id=<>"
        }
      ]
  }
}
MySQL Group Replication
Add the configuration highlighted below to your SqlSettings.ReplicaLagSettings array. You only need to add this once because replication statistics for all nodes are shared across all server instances that are members of the MySQL replication group. Be sure to change the DataSource to point to a single node in the group.

For more information on group replication stats, see the MySQL documentation.

Example:
{
  "SqlSettings": {
      "ReplicaLagSettings": [
        {
            "DataSource": "replica-1",
            "QueryAbsoluteLag": "select member_id, count_transactions_remote_in_applier_queue FROM performance_schema.replication_group_member_stats where member_id=<>",
            "QueryTimeLag": ""
        }
      ]
  }
}
PostgreSQL replication slots
Add the configuration highlighted below to your SqlSettings.ReplicaLagSettings array. This query should run against the primary node in your cluster, to do this change the DataSource to match the SqlSettings.DataSource setting you have configured.

For more information on pg_stat_replication, see the PostgreSQL documentation.
Example:
{
  "SqlSettings": {
      "ReplicaLagSettings": [
        {
            "DataSource": "postgres://mmuser:password@localhost:5432/mattermost_test?sslmode=disable&connect_timeout=10.",
            "QueryAbsoluteLag": "select usename, pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) as metric from pg_stat_replication;",
            "QueryTimeLag": ""
        }
      ]
  }
}
Grant permissions to the database user for pg_monitor. This user should be the same user configured above in the DataSource string.
For more information on roles, see the PostgreSQL documentation.
sudo -u postgres psql
postgres=# GRANT pg_monitor TO mmuser;

Save the config and restart all Mattermost nodes.
Navigate to your Grafana instance monitoring Mattermost and open the Mattermost Performance Monitoring v2 dashboard.
The QueryTimeLag chart is already setup for you utilizing the existing Replica Lag chart. If using QueryAbsoluteLag metric clone the Replica Lag chart and edit the query to use the below absolute lag metrics and modify the title to be Replica Lag Absolute.

mattermost_db_replica_lag_abs{instance=~"$server"}

A screenshot showing how to clone a chart within Grafana

A screenshot showing the specific edits to make to the cloned grafana chart.

Replica monitor interval (seconds)#

Available on all plans

self-hosted deployments

Specifies how frequently unhealthy replicas will be monitored for liveness check. Mattermost will dynamically choose a replica if it’s alive.

Numerical input. Default is 5 seconds.

System Config path: N/A
config.json setting: ".SqlSettings.ReplicaMonitorIntervalSeconds": 5
Environment variable: MM_SQLSETTINGS_REPLICAMONITORINTERVALSECONDS