Prepare your Mattermost database#

plans-img Available on all plans

deployment-img self-hosted deployments

You need a PostgreSQL database. See the database software documentation for details on database version support, and see the Migrate from MySQL to PostgreSQL documentation for details on migrating from MySQL to PostgreSQL.

Tip

Looking for information on working with a MySQL database? See the prepare your Mattermost MySQL database documentation for details.

To set up a PostgreSQL database for use by the Mattermost server:

  1. Log in to the server that will host the database, and install PostgreSQL. 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. Access PostgreSQL by running:

sudo -u postgres psql

  1. Create the Mattermost database by running:

CREATE DATABASE mattermost;

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

  1. Switch to the new database by running:

\connect mattermost

  1. Create the Mattermost user mmuser by running the following command. Ensure you use a password that’s more secure than mmuser-password.

CREATE USER mmuser WITH PASSWORD 'mmuser-password';

  1. If you’re configuring PostgreSQL v15.x or later:

  1. Grant the user access to the Mattermost database by running:

GRANT ALL PRIVILEGES ON DATABASE mattermost to mmuser;

  1. Grant the user to change the owner of a database to a user mmuser by running:

    ALTER DATABASE mattermost OWNER TO mmuser;

  2. Grant access to objects contained in the specified schema by running:

GRANT USAGE, CREATE ON SCHEMA PUBLIC TO mmuser;

  1. Exit the PostgreSQL interactive terminal by running:

\q

  1. (Optional) If you use separate servers for your database and the Mattermost server, you may allow PostgreSQL to listen on all assigned IP addresses. We recommend ensuring that only the Mattermost server is able to connect to the PostgreSQL port using a firewall.

Open /etc/postgresql/{version}/main/postgresql.conf as root in a text editor.

Replace {version} with the version of PostgreSQL that’s currently running.

  1. Find the following line: #listen_addresses = 'localhost'

  2. Uncomment the line and change localhost to *: listen_addresses = '*'

  3. Restart PostgreSQL for the change to take effect by running:

sudo systemctl restart postgresql-{version}

Open /var/lib/pgsql/{version}/data/postgresql.conf as root in a text editor.

Replace {version} with the version of PostgreSQL that’s currently running.

  1. Find the following line: #listen_addresses = 'localhost'

  2. Uncomment the line and change localhost to *: listen_addresses = '*'

  3. Restart PostgreSQL for the change to take effect by running:

sudo systemctl restart postgresql-{version}

  1. Modify the file pg_hba.conf to allow the Mattermost server to communicate with the database by ensuring host connection types are set to trust.

These host connections are specific to Ubuntu 20.04, and will differ depending on the operating system version you’re running. For example, in Ubuntu 22.04, the peer connection types are listed as sha-256 instead.

Local Database (same server)

If the Mattermost server and the database are on the same machine:

  1. Open /etc/postgresql/{version}/main/pg_hba.conf as root in a text editor.

  2. Find the following lines:

local   all             all                        peer

host    all             all         ::1/128        ident

  1. Change peer and ident to trust:

local   all             all                        trust

host    all             all         ::1/128        trust

Remote Database (separate server)

If the Mattermost server and the database are on different machines:

  1. Open /etc/postgresql/{version}/main/pg_hba.conf in a text editor as root user.

  2. Add the following line to the end of the file, where {mattermost-server-IP} is the IP address of the Mattermost server: host all all {mattermost-server-IP}/32 md5.

These host connections are specific to Red Hat 8, and will differ depending on the operating system version you’re running.

Local Database (same server)

If the Mattermost server and the database are on the same machine:

  1. Open /var/lib/pgsql/{version}/data/pg_hba.conf as root in a text editor.

  2. Find the following lines:

local   all             all                        peer

host    all             all         ::1/128        scram-sha-256

  1. Change peer and ident to trust:

local   all             all                        trust

host    all             all         ::1/128        trust

Remote Database (separate server)

If the Mattermost server and the database are on different machines:

  1. Open `/var/lib/pgsql/{version}/data/pg_hba.conf in a text editor as root user.

  2. Add the following line to the end of the file, where {mattermost-server-IP} is the IP address of the Mattermost server: host all all {mattermost-server-IP}/32 md5.

  1. Reload PostgreSQL by running:

sudo systemctl reload postgresql-{version}

  1. Verify that you can connect with the user mmuser.

If the Mattermost server and the database are on the same machine, use the following command:

psql --dbname=mattermost --username=mmuser --password

If the Mattermost server is on a different machine, log into that machine and use the following command:

psql --host={postgres-server-IP} --dbname=mattermost --username=mmuser --password

Note

You might have to install the PostgreSQL client software to use the command.

The PostgreSQL interactive terminal starts. To exit the PostgreSQL interactive terminal, type \q and press Enter on Windows or Linux, or ↵ on Mac.

When the PostgreSQL database is installed, and the initial setup is complete, you can install the Mattermost server.

Important

If you are upgrading a major version of Postgres, it is essential that ANALYZE VERBOSE is run on the database post upgrade. This is necessary to re-populate the pg_statistics table used to generate optimal query plans. The database performance might suffer if this step is not done.