Skip to main content

Set up the Mattermost PostgreSQL database

PostgreSQL v14+ is required for Mattermost server installations. Create the database, user, and grants before you install Mattermost Server, so the installer has something to connect to.

You can run PostgreSQL on the same host as Mattermost, on a separate server, or as a managed service such as Amazon RDS or Azure Database for PostgreSQL. See the database software documentation for the supported versions and the minimum version policy.

Create the database and user​

  1. Create an PostgreSQL server instance. See the PostgreSQL documentation for details. When the installation is complete, the PostgreSQL server is running, and a Linux user account called postgres has been created.

  2. Create the Mattermost database and user:

    1. Access PostgreSQL by running:

      sudo -u postgres psql

    2. Create the database:

      CREATE DATABASE mattermost WITH ENCODING 'UTF8' LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8' TEMPLATE=template0;

    If this steps fails with an error message like invalid LC_COLLATE locale name: "en_US.UTF-8", you need to generate the locale first using locale-gen en_US.UTF-8.

    1. Create the Mattermost user with a secure password:

      CREATE USER mmuser WITH PASSWORD 'mmuser-password';

    2. Grant database access to the user:

      GRANT ALL PRIVILEGES ON DATABASE mattermost to mmuser;

    3. If using PostgreSQL v15.x or later, additional grants are required:

      ALTER DATABASE mattermost OWNER TO mmuser;
      -- Connect to the mattermost database so the schema grants below apply to the right schema
      \c mattermost
      ALTER SCHEMA public OWNER TO mmuser;
      GRANT USAGE, CREATE ON SCHEMA public TO mmuser;

Allow remote connections​

Complete this section only if the database runs on a different host from Mattermost Server. A database local to the application host needs no additional configuration.

  1. Edit postgresql.conf to allow remote connections:

Edit /etc/postgresql/{version}/main/postgresql.conf:

listen_addresses = '*'

  1. Configure client authentication by editing pg_hba.conf.

    Add the following line, replacing {mattermost-server-IP}:

    host all all {mattermost-server-IP}/32 md5

    Restrict the source to the Mattermost server's address rather than a wider range, so the database only accepts connections from the application layer.

  2. Restart the PostgreSQL service to apply the configuration changes:

    sudo systemctl restart postgresql

Troubleshooting​

Mattermost can't use a fresh PostgreSQL v15 installation​

PostgreSQL v15 introduces changes that may affect compatibility with previous releases. If you're deploying a fresh installation of PostgreSQL v15 and Mattermost can't create its schema, run this command to ensure that you can use Mattermost:

GRANT CREATE ON SCHEMA public TO PUBLIC;

Full-text search doesn't use indexes with a non-English default_text_search_config​

Mattermost uses default_text_search_config for full-text search in PostgreSQL databases, as opposed to a hardcoded text search config. However, indexes are still created with a hardcoded text search config (english) and as a result, full-text search may never use the indexes.

Some of the tables in Mattermost, like Posts or Users, contain GIN indexes to improve the database full-text search feature in PostgreSQL.

These indexes need to be built against a specific language, and when they're created they're hard-coded to English. Full-text search queries are always performed using the default_text_search_config database setting. In order for the full-text search feature to leverage the indexes, the language specified in the query needs to match the language specified in the index.

If the default_text_search_config is not set to english, the GIN indexes will not be used. Database administrators can work around this by dropping the specific GIN index they're interested in and rebuilding it with the value of default_text_search_config.

For example, if the default language of your server is Spanish:

-- Create the new index with a new name before dropping the old one
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_posts_message_txt_spanish ON posts USING gin(to_tsvector('spanish', message));

-- Check that the new index does work. If it does, drop the old one and rename the new one:
DROP INDEX CONCURRENTLY IF EXISTS idx_posts_message_txt;
ALTER INDEX idx_posts_message_txt_spanish RENAME TO idx_posts_message_txt;

Change the PostgreSQL username and password​

We recommend changing the PostgreSQL username and password in the .env file.

Next steps​

Once the database is ready, continue with the rest of the preparation checklist, or go straight to your install method: Kubernetes, Linux, or containers.