How to Migrate or Upgrade a PostgreSQL Database to a New Host or Version

Applies to: Mattermost — All versions
Symptoms: Missing roles, tablespaces, or other global database objects after a PostgreSQL migration; incomplete database state on the destination instance.

🛑 Problem

When migrating a Mattermost PostgreSQL database to a new host, or upgrading to a new version of PostgreSQL, an in-place upgrade is not supported. The database must be exported from the source instance and imported into the destination instance using a dump/restore approach.

A common mistake during this process is using pg_dump instead of pg_dumpall. While pg_dump exports table data and schema for a specific database, it does not include global objects such as:

• Roles (users)
• Tablespaces
• Other cluster-wide settings

This results in an incomplete migration, where the destination instance is missing critical configuration that Mattermost depends on.

✅ Solution

To perform a complete 1:1 migration, use pg_dumpall instead of pg_dump. This ensures all global objects are included in the dump file alongside the database contents.

Step 1 — Dump the Source Database

Run the following command on the source PostgreSQL instance, replacing POSTGRES_USER with a user that has rights to dump global objects (typically the postgres superuser):

pg_dumpall -c -U POSTGRES_USER > /var/lib/postgresql/data/DB_DUMP.sql

⚠️ Important: The -c flag adds SQL DROP statements to the dump file. When the dump is imported, these statements will automatically drop any existing tables, roles, and other objects of the same name on the destination instance before recreating them. This ensures a clean, conflict-free import — but be aware that any pre-existing data on the destination with matching names will be removed.

Step 2 — Import the Dump on the Destination Instance

Run the following command on the destination PostgreSQL instance:

psql -U postgres < /var/lib/postgresql/data/DB_DUMP.sql

This will restore all global objects (roles, tablespaces, etc.) and the full Mattermost database to the new instance.

Additional Resources

For more information, see:

• PostgreSQL: pg_dumpall Documentation
• PostgreSQL: pg_dump Documentation
• Mattermost Database Configuration