Upgrade PostgreSQL¶

Available on Entry, Professional, Enterprise, and Enterprise Advanced plans

Mattermost follows the PostgreSQL community versioning policy, which provides 5 years of support per major version. When a PostgreSQL version reaches end-of-life, Mattermost drops support for it in a subsequent release. See the software and hardware requirements documentation for the currently supported PostgreSQL versions.

When upgrading PostgreSQL, refer to the official PostgreSQL upgrade documentation for comprehensive guidance. This page covers the steps specific to Mattermost deployments.

Before you begin¶

Back up your database. Always take a full database backup before upgrading. See the backup and disaster recovery documentation.
Check supported versions. Confirm the target PostgreSQL version is supported by your Mattermost release. See software and hardware requirements.
Stop Mattermost. Shut down the Mattermost server before starting the database upgrade to prevent data writes during the process.

Upgrade a bare-metal PostgreSQL server¶

There are two main approaches for upgrading PostgreSQL on a bare-metal or virtual machine:

pg_upgrade (in-place): Faster; upgrades the data directory without a full dump/restore cycle. Recommended for large databases.
pg_dump / pg_restore (logical): Simpler and safer for cross-machine migrations or when in-place upgrade is not possible.

Using pg_upgrade¶

pg_upgrade allows you to upgrade between major PostgreSQL versions without a full export. Both the old and new PostgreSQL versions must be installed side-by-side.

Install the new PostgreSQL version alongside the existing one using your package manager.
Stop the existing PostgreSQL service:
```
sudo systemctl stop postgresql
```

Run pg_upgrade as the postgres user, specifying the binary and data directories for both versions. Replace <old_version> and <new_version> with the appropriate version numbers (e.g. 15 and 16):

sudo -u postgres /usr/lib/postgresql/<new_version>/bin/pg_upgrade \
  --old-datadir /var/lib/postgresql/<old_version>/main \
  --new-datadir /var/lib/postgresql/<new_version>/main \
  --old-bindir /usr/lib/postgresql/<old_version>/bin \
  --new-bindir /usr/lib/postgresql/<new_version>/bin

Update your system to start the new PostgreSQL version by default, then start the service:
```
sudo systemctl start postgresql
```

For full pg_upgrade reference, see the official pg_upgrade documentation.

Using pg_dump and pg_restore¶

This approach exports the entire database, installs the new PostgreSQL version, and restores the data. It is simpler but requires downtime proportional to database size.

Export the Mattermost database (replace mattermost with your database name and mmuser with your database user):
```
pg_dump -U mmuser -Fc mattermost > mattermost_backup.dump
```

Install the new PostgreSQL version and create the database user and database:

sudo -u postgres psql -c "CREATE USER mmuser WITH PASSWORD 'your_password';"
sudo -u postgres psql -c "CREATE DATABASE mattermost OWNER mmuser;"

Restore the database into the new PostgreSQL instance:

pg_restore -U mmuser -d mattermost mattermost_backup.dump

Upgrade PostgreSQL in Docker¶

Note

The steps below are written for the official Mattermost Docker deployment. If you are using a custom Docker setup, adapt the container and volume names accordingly.

When running PostgreSQL in Docker, pg_dump/pg_restore is the recommended upgrade approach. In-place pg_upgrade is complex in containers because it requires both old and new binaries in the same container.

Stop the Mattermost container:
```
docker stop mattermost
```
Dump the database from the running PostgreSQL container (replace mattermost, mmuser, and db with your database name, user, and container name):
```
docker exec postgres pg_dump -U mmuser -Fc mattermost > mattermost_backup.dump
```
Stop the existing PostgreSQL container:
```
docker stop postgres
```
Update POSTGRES_IMAGE_TAG in your .env file to the new version. For example:
```
POSTGRES_IMAGE_TAG=16-alpine
```
Recreate the PostgreSQL container so the new image tag from .env is applied:
```
docker compose up -d --force-recreate postgres
```
Note

If your volume already contains data from the old major version, PostgreSQL will refuse to start. In that case, create a new named volume for the new container, then restore from the dump in the next step.

Recreate the database user and database in the new container, then restore:

docker exec -i postgres psql -U postgres -c "CREATE USER mmuser WITH PASSWORD 'your_password';"
docker exec -i postgres psql -U postgres -c "CREATE DATABASE mattermost OWNER mmuser;"
docker exec -i postgres pg_restore -U mmuser -d mattermost < mattermost_backup.dump

After the upgrade¶

After upgrading PostgreSQL, run ANALYZE VERBOSE on the Mattermost database. This re-populates the pg_statistics table used by PostgreSQL to generate optimal query plans. Skipping this step can result in degraded database performance.

sudo -u postgres psql -d mattermost -c "ANALYZE VERBOSE;"

Once complete, restart Mattermost:

sudo systemctl start mattermost