Upgrade from 10 to 11 postgres update from v.13 db restore issue

Troubleshooting Upgrade
1

Hi.

We are running a Mattermost instance with docker compose.

Basically we are using the this .env-file

Currently we are running with
MATTERMOST_IMAGE_TAG=10.12
POSTGRES_IMAGE_TAG=13-alpine

I saw that version mattermost version 11 needs Postgres 14 or higher so I thought I should upgrade to Postgres 16.

I basically run these commands to make a sql-backup and try to restore.
Backup

  1. Shut down the stack docker compose down
  2. Start only the postgres container docker compose up -d postgres
  3. Create a backup docker exec -it docker-postgres-1 pg_dumpall -U mmuser > ./backup/postgres13-update-backup.sql
  4. Stop the db container docker stop docker-postgres-1

Restore

  1. Adjust .env file
    POSTGRES_IMAGE_TAG=16-alpine
    POSTGRES_DATA_PATH=./volumes/db16/var/lib/postgresql/data
  2. Bring up the db container only docker compose up -d postgres
  3. Import the data docker exec -i docker-postgres-1 psql -U mmuser < ./backup/postgres13-update-backup.sql

When running step 3 i get psql: error: connection to server on socket “/var/run/postgresql/.s.PGSQL.5432” failed: FATAL: database “mmuser” does not exist

I know this is more of a docker/postgres issue but I’m reaching out here first. I’m not very good at these things.

Any ideas?

Kind regards,
Olle

1 Like
2

I’m not using docker for production, but I do use it in a test instance for some internal tools development work. My guess is your compose file isn’t setting up the right user in your postgres container. This is what I have in my docker-compose.yml for the PG server:

services:
  db:
    image: postgres:15
    environment:
      POSTGRES_USER: mmuser
      POSTGRES_PASSWORD: mmuserpass
      POSTGRES_DB: mattermost
    volumes:
      - dbdata:/var/lib/postgresql/data
    ports: ["55432:5432"]
3

I found the issue in “Restore step 3“
I needed to supply the database as well. So the command becomes:

docker exec -i docker-postgres-1 psql -U mmuser -d mattermost < ./backup/postgres13-update-backup.sql
Note the ... -d mattermost ... param.

If I bring up the stack now with .env MATTERMOST_IMAGE_TAG=11.0
I get a new error Error: failed to initialize platform: cannot create store: error setting up connections: pq: password authentication failed for user “mmuser”

I guess I need to work some more with the DB-backup / restore commands.

Or are there some official documentation from Mattermost how to migrate that I’ve missed?
(Since I’m using the example docker compose pattern)

4

Hello! i am interested because this website is very helpful for everyon.

5

Here is my working solution

Backup

  1. Shut down the stack docker compose down

  2. Start only the postgres container docker compose up -d postgres

  3. Create a backup
    docker exec -it docker-postgres-1 pg_dump mattermost -U mmuser --no-privileges > ./backup/postgres13-update-backup.sql

  4. Stop the db container docker stop docker-postgres-1

Restore

  1. Adjust .env file
    POSTGRES_IMAGE_TAG=16-alpine
    POSTGRES_DATA_PATH=./volumes/db16/var/lib/postgresql/data

  2. Bring up the db container only docker compose up -d postgres

  3. Remove existing db:s
    docker exec -it docker-postgres-1 dropdb mattermost -U mmuser
    docker exec -it docker-postgres-1 createdb mattermost -U mmuser

  4. Import the data
    docker exec -i docker-postgres-1 psql -U mmuser -d mattermost < ./backup/postgres13-backup-mattermost.sql

Update Mattermost

  1. Adjust .env file
    MATTERMOST_IMAGE_TAG=11.0
  2. Bring up the stack (in my case)
    docker compose -f docker-compose.yml -f docker-compose.without-nginx.yml up -d
4 Likes
6

Hey @ollejacobsen , thank you for the nice instructions.
I have a few improvements, that I unfortunately had to experience the hard way (data loss) - absolutely my own fault and just in a hobby project but maybe this can help others.

  1. The name of the backup file (./backup/postgres13-update-backup.sql) and the restored file (./backup/postgres13-backup-mattermost.sql) do NOT match. These should be the same
  2. make sure that the output of the backup step is written to console as well (e.g. using | tee) - I had a typo in the backup and did not notice because the error was just written into the backup file
  3. should be common sense, but maybe add a step to check the backup content - I did not against better judgement. at least if there are some sensible looking lines in the sql dump

Thanks again for your effort of documenting the upgrade instructions :slight_smile:

2 Likes