PostgreSQL 9.4 deprecated

[tuxity]

MM 5.26 now supports PostgreSQL 10+, since 9.4 is now deprecated.

https://docs.mattermost.com/administration/changelog.html#compatibility

We need to update https://github.com/mattermost/mattermost-docker/blob/master/db/Dockerfile#L1 to tag :10-alpine and provide a documentation to correctly upgrade database with pg_upgrade

[ghost]

Has someone tested how to do this?
I'm just wondering would it be enough to do this on running instance (does someone have time to test?)

1. add RUN pg_upgrade to db entrypoint.sh
2. change the base image in the Dockerfile
3. run docker-compose build
4. run docker-compose up -d
5. remove RUN pg_upgrade in db entrypoint.sh

[tuxity]

Maybe it can be useful https://github.com/tianon/docker-postgres-upgrade

[ghost]

Actually this is still using postgres 9.4

mattermost-docker/db/Dockerfile

Line 1 in 124a8ba

FROM postgres:9.4-alpine

[ouafnico]

Mattermost 5.30 is supposed to be out on December. I guess the version of postgresql on this repo will change in the same time?
Has someone tried before?

[haivala]

Anyone else find this alarming?

[tuxity]

I guess the best would be to handle db upgrade in entrypoint.sh script (or separated file)

• Update FROM to postgres:10-alpine
• check in entrypoint.sh the content of PG_VERSION file in postgres data folder
• if 9.4 (or 9.x?) run pg_upgrade command
• startup as normal

can be done manually but would be nice to have it scripted to avoid any user errors

[maxraab]

Does this work? Why is no maintainer of this repo caring about this topic?

If we would check the PG_VERSION file of the postgres data folder - how could we determine if we're on the next major version? How could we retrive the current version of the database itself? Couldn't we simply do a pg_upgrade with every start?

[GuidoDr]

well the topic of this thread is not really correct, it is not the 9.6 which is deprecated but the 9.4 that the docker setup of mattermost currently still does use. ;-)

Since I have not seen any progress on this topic so far, I did today, after I had upgraded Mattermost from 5.29.1 to 5.30.1, a manual upgrade of the database.

But I did not use pg_upgrade, I did it the old fashioned way with dumps and reload of the dumps.

docker-compose stop app
docker-compose exec db pg_dumpall -U mmuser > pgdump

this dropped the dump of the 9.4 data in the directory where my docker-compose.yml is also located.
Then I stopped the database docker-compose stop db and moved the postgresql/data directory to a backup location.
Next I did edit the db/Dockerfile and replaced FROM postgres:9.4-alpine with FROM postgres:9.5-alpine
Then docker-compose build to do the new build and the start of the database with docker-compose up -d dbwhich does create a new empty database with the new schema.
Now I did import the dump with docker-compose exec -T db psql mattermost -U mmuser < pgdump.
In order to check if everything still worked I did then restart Mattermost itself docker-compose up -d. Everything still worked fine.

So I did restart the whole procedure again with the docker-compose stop app and another dump now of the 9.5 data and all the other steps then.
In the db/Dockerfile I replaced it then after every dump with the next major version, so from the original 9.4 to 9.5, 9.6, 10, 11, 12 and 13.
These different steps are needed since the database structure might have changed between the different PostgreSQL releases. Starting with 10 PostgreSQL major versions do always start with a new number, but until 9.6 even the second number could have been a major release, therefore I did also the 9.5 and 9.6 upgrade.

After the last upgrade to 13 I did at the end then enter the db container and did recreate the indexes:
docker-compose exec db sh to enter the container and inside the container then psql mattermost -U mmuser in order to enter the psql.
There then VACUUM VERBOSE ANALYZE; to optimize the PostgreSQL statistics and afterwards REINDEX SCHEMA CONCURRENTLY public; to re-create the indexes. Leave psql with \q and exit to leave the shell inside the container.

Now it is running on version 13 (actually 13.1) and everything runs fine.
Since I had the bleve indexing active and the bleve search I did delete the indexes in the system console and I am currently recreating these indexes.
As far as I can see the indexing is running faster then before with the old database. 12 days ago when I last had re-created the indexes it took 1295 minutes until they were ready. Now after 250 minutes I does show that 45% are already indexed.

[maxraab]

Well, thanks for the detailed explanation. I'm quite unsure if we really need to go through all of the versions in between. IMHO the dump should contain the data as well as the structure for the tables - no internal stuff that might has been changed..?

[philipkozeny]

Maybe it can be useful https://github.com/tianon/docker-postgres-upgrade

I've used the images provided there to upgrade successfully from 9.4 to 13 in one step. Although I had to add

host all all 172.0.0.1/8 trust

to my pg_hba.conf after upgrading, but everything works now with PostgreSQL 13.

[maxraab]

I'm going to try this.
Has anyone tried it without any changes..? With PostgreSQL 9.4, I mean? Does it start?

[gysel]

Yes, it starts and I have not yet seen any errors related to the database. (But I'm only running a small server.)

[maxraab]

Okay, I also can confirm that it's still running with v9.4.

After this test I upgraded my instance like proposed from @GuidoDr, but in only one step from 9.4 to 10.1.
I also needed to change one lib from python-dev to python3-dev in db/Dockerfile after changing the base image.

Now, everything seems to be fine.

[m-a-v]

@amyblais Is there anything planned to solve this problem?

[gpopesc]

Thank you @maxraab for you hint and thank you @GuidoDr for detailed instructions . I confirm it works. I am now on 12.5 and everything works perfect. I avoid to put version 13 because is being release at the end of September and it has low maturity.

[m-a-v]

The upgrade using @tuxity/@philipkozeny method I get the following issue.

#411

[haivala]

Are we getting scripted version of the update from the maintainers?

[philipkozeny]

The upgrade using @tuxity/@philipkozeny method I get the following issue.

#411

I also have that in my logs when starting (afaik this happened before with a previous upgrade), does this affect your installation?

[m-a-v]

I also have that in my logs when starting (afaik this happened before with a previous upgrade), does this affect your installation?

As it was an ERROR, I cancelled the migration test.

[tuxity]

@GuidoDr oups, you are right, it's 9.4 not 9.6, was a mistake. I have edited the title.

[amyblais]

Thanks for letting us know! The issue was escalated earlier, and our build engineer will be looking at this.

[ccoenen]

I upgraded to Postgres 13.1 by using most of the instructions from @GuidoDr (thank you very much!). I did skip from 9.4 to 13.1 directly, though (with backup, of course!) and so far everything looks fine.

I did not need to add anything to pg_hba.conf in my setup.
I did need to alter python-dev to python3-dev as pointed out by @maxraab (thank you very much as well!).
I also did a reindex (not sure if it was neccessary), but my exact command was REINDEX DATABASE mattermost;.

I do see the index error mentioned in #411, but that's not a new thing AFAIR.

[m-a-v]

I also have that in my logs when starting (afaik this happened before with a previous upgrade), does this affect your installation?

As it was an ERROR, I cancelled the migration test.

2021-02-07 15:23:18.913 UTC [37] ERROR: relation "idx_teams_description" does not exist

I did the migration again and everything seems to work. So hopefully the error about idx_teams_description isn't critical.

[ouafnico]

Thanks for letting us know! The issue was escalated earlier, and our build engineer will be looking at this.

Do you have any news ?

[haivala]

DB upgrade is now required..

[tgly307]

The 5.32 is released now. Is pg 9.4 still compatible for 5.32?

[haivala]

The 5.32 is released now. Is pg 9.4 still compatible for 5.32?

No.

[haivala]

Thanks for letting us know! The issue was escalated earlier, and our build engineer will be looking at this.

Any news on this?

[oli-ver]

The mattermost app from the app store in iOS was also showing the error that the server version is not supported anymore.
Just to understand the upgrade process: Why was there an update for the mattermost client in the app stores on iOS and Android, if this repo here is not yet up to date?

Upgrading to the latest version of this repository's master branch did not help, so I ran the instructions in this thread.

@bor8 Your additional upgrade suggestions helped a lot, thanks. I was also facing the issue:

database user "mmuser" is not the install user
Failure, exiting

The script provided by @coltoneshaw ran afterwards.
The health status of the containers seems to be fine in my case:

           Name                       Command               State        Ports  
--------------------------------------------------------------------------------
mattermost-docker_app_1      /entrypoint.sh mattermost   Up (healthy)   8000/tcp
mattermost-docker_clamav_1   /bootstrap.sh               Up             3310/tcp
mattermost-docker_db_1       /entrypoint.sh postgres     Up (healthy)   5432/tcp
mattermost-docker_web_1      /entrypoint.sh              Up (healthy)   80/tcp  

The mattermost app on iOS showed the version error again once, but now after restarting it, everything seems to work.

Will this script be provided on the master branch as part of the upgrade process now?
I would like to run this in production, but I don't suppose it's a good idea to use this manual process, right?

[Kukks]

This is a breaking change to the db image version. A major version bump should have been issued or an automatic Postgres migration done.

[x4e-jonas]

What about users that already use an external PostgreSQL 10+ database? Just not updating the Docker image is not a good option (#445, #532).

[jwillmer]

I did the upgrade and I can only tell everyone to wait. The upgrade went smooth but the plugin migration did not. I had to delete plugins, convert plugin api users into regular user to delete them and reinstall plugins in order to fix them. Startup raven still does not work. So if you upgrade I would delete all plugin and reinstall them afterwards in the hope that prevents them from breaking.

Plugins that broke for me:

• Standup Raven
• Remind plugin

[juanrgm]

I am using a external MySQL server and I am stuck at 5.31.6 version due to this issue. Upgrade with MM_VERSION=5.34.2 didn't work.

[ouafnico]

On my side, I forked this repos, changed the Dockerfile for database, to use postgresql 13.
For the migration, just had to :

• dump the 9.4 database with pg_dump
• Update the docker to latest version
• import the dump on postgresql 13

And it's working.

[andreaskurz]

On my side, I forked this repos, changed the Dockerfile for database, to use postgresql 13.
For the migration, just had to :

* dump the 9.4 database with pg_dump

* Update the docker to latest version

* import the dump on postgresql 13

And it's working.

Thanks, that helped a lot :)
For people who are not that familiar with postgres, like me: I also had to delete all tables before importing. So my steps were:

• docker exec into the db container
• create a backup: pg_dump --username=$POSTGRES_USER --dbname=$POSTGRES_DB > PATH_TO_YOUR_PERSISTENT_VOLUME
• exit from db container
• Stop old version: docker-compose down
• Checkout the latest version and change the Dockerfile in db to have FROM postgres:13.3-alpine and also python-dev to python3-dev
• Delete the old database from the persistent volume to have it reinitialised
• Build the new version: docker-compose build
• Start the db only: docker-compose up -d db
• docker exec into the db container
• psql -U $POSTGRES_USER $POSTGRES_DB
• empty the database (THIS WILL DELETE ALL YOUR DATA, DOUBLE CHECK YOUR BACKUP)
  execute: DROP SCHEMA public CASCADE; CREATE SCHEMA public;
• execute: GRANT ALL ON SCHEMA public TO $POSTGRES_USER; (you have to replace $POSTGRES_USER with the actual username)
• execute: quit
• Import old db: psql -U $POSTGRES_USER $POSTGRES_DB < PATH_TO_YOUR_DB_DUMP
• exit from the container
• Restart everything: docker-compose down && docker-compose up -d

Hope that saves some time for people =)

[bor8]

Nice. But: Can you mark DROP SCHEMA… as -- dangerous for people without backup? =)

[andreaskurz]

Nice. But: Can you mark DROP SCHEMA… as -- dangerous for people without backup? =)

Thanks for the hint. I edited the comment and also updated the steps to also cover the dump process :)

[zwnk]

With what @tuxity & @GuidoDr provided above I was able to put together an upgrade script that has worked each time I've tested this locally. You can find the gist here.

Steps to use this:

1. On your server navigate to the folder named `mattermost-docker`

2. Run `sudo touch upgrade.sh`. then paste the contents of the script into the file. **Edit the variables for postgres at the top to match your environment and make sure this file is in your `mattermost-docker` folder.**

3. Run `sudo bash upgrade.sh`. This upgrade process can take some time, depending on the size of your database.

4. Refresh your Mattermost screen and check the `About Mattermost` to ensure you're now on 5.32.1.

Script

#!/bin/bash

# Credit for helping to outline the correct upgrade process goes to @tuxity and @GuidoDr

##
## Instructions
##

# 1. Edit the variables below to match your environment. This uses default variables and assumes you're on 5.31.0.
#    If you're wanting to use another version of Postgres/Mattermost , update the variables as desired.

# 2. Copy the contents of this script into a file within your mattermost-docker folder. This is essential. 
#    You can use 'sudo touch upgrade.sh && sudo nano upgrade.sh' then paste the script.
........

i tried to do the update as mentioned but my app container afterwards is complaining that it can't connect to the db. maybe somebody has an idea what i'm missing?

[lucasbasquerotto]

@zwnk Perhaps your issue is the same as the one of @daydr3am3r, related to the postgres user?

In my case, although the database user specified in docker-compose was set to mmuser, the db container was in fact using postgres instead. Cue some stressful moments later, I finally realized what was going on and proceeded with the upgrade.
Not sure why that was, pointers would be appreciated.

I would strongly suggest for the script to perform tests to check whether database credentials actually work before proceeding with actions. Also, it might be a good idea to document recommended steps on how to revert things in case of trouble.

Like @tuxity said before:

You need to adjust variables user/password/db in the script, like it is in the docker-compose.yml

(I don't know if this is your case, you haven't posted your exact error, but if the credentials in the script are different than the ones in your docker-compose file, you should give it a try)

[zwnk]

of course i changed the credentials to the credentials used in my docker-compose file. but after the update script ran, the app container isn't able to connect to the db(invalid credentials). i checked the credentials at the config.json of the app container, they are also correct. don't know what is going wrong.

i rolled back to the backup but still have the updated version sitting around, maybe i check it later tonight.

[w4rc0n]

Okay, so as someone who used the upgrade script provided above, what must be done to continue to update to the latest versions of Mattermost? Do we just update the version number in the app/Dockerfile and then upgrade as normal?

[cayalav]

I am using a external MySQL server and I am stuck at 5.31.6 version due to this issue. Upgrade with MM_VERSION=5.34.2 didn't work.

Same here ! I have my docker-compose with db image of mysql:8.0.20 and my current version of the App is relase-5.29, when updating to release-5.33 looks as all the data dosent show up in the chats.

version: "3.7"
services:
  db:
    image: mysql:8.0.20
    environment:
      MYSQL_ROOT_PASSWORD: $PASSWORD
      MYSQL_DATABASE: $DB
      MYSQL_USER: $USER
      MYSQL_PASSWORD: $PASSWORD
    command: mysqld --default-authentication-plugin=mysql_native_password
    volumes: 
      - ./volumes/db/mysql:/var/lib/mysql
    ports:
      - "3306:3306"
    networks:
      - my-network
  app:
    #image: mattermost/mattermost-team-edition:release-5.33
    image: mattermost/mattermost-team-edition:release-5.29
    container_name: mattermost-docker_app_1 
    restart: unless-stopped
    volumes:
      - ./volumes/app/mattermost/config:/mattermost/config:rw
      - ./volumes/app/mattermost/data:/mattermost/data:rw
      - ./volumes/app/mattermost/logs:/mattermost/logs:rw
      - ./volumes/app/mattermost/plugins:/mattermost/plugins:rw
      - ./volumes/app/mattermost/client-plugins:/mattermost/client/plugins:rw
      - /etc/localtime:/etc/localtime:ro
    ports: 
      - "8000:8000"
    environment:
      - MM_USERNAME=$USER
      - DB_PORT_NUMBER=$DBPORT
      - MM_DBNAME=$DBNAME
      - DB_HOST=$MY_IP_HOST
      - VIRTUAL_HOST=mattermost.mydomain.com
      - VIRTUAL_PORT=8000
      - LETSENCRYPT_HOST=mattermost.mydomain.com
      - MM_PASSWORD=$PASSWORD
      - MM_SQLSETTINGS_DRIVERNAME=$DNAME
      - MM_SQLSETTINGS_DATASOURCE=$DB_CONTAINER_URL_WITH_CREDENTIALS
    networks:
      - my-network
networks:
  my-network:
    external:
      name: my-network

[thepill]

First of all: Thank you for your work @mattermost team :)

I wonder: what is the final migration plan? Does it exist? Will there be an automatic migration option "at some point", or will manual steps (like some guides here) have to be taken to ever upgrade to a newer version?

Thanks a lot!

[NicolasDorier]

All: While I can't fix the situation for you, I can suggest a course of action to the mattermost team.

The reason the update is problematic is that updating postgres between major version require some difficult manual procedure (running pg_upgrade)
Postgres own docker image, on which mattermost is based, does not make it any easier.

Since I was in the same problematic for an open source project of mine, I am here to suggest a solution to the mattermost team so that updating would not require any manual intervention for the users.

The idea is that in your docker entrypoint, you should add some script which check the current version of postgres, and if not the right version, download the right binaries and run pg_upgrade.

The plan of action would be:

1. Bump the postgres image on which mattermost-prod-db is based
2. Insert the same script that I did this commit btcpayserver/btcpayserver-infra@e29b1ee in your own entrypoint

My commit on btcpayserver/btcpayserver-infra@e29b1ee does not work because mattermost-prod-db is still based on postgres 9.4. But if it was version 13, my postgres/migrate-docker-entrypoint.sh script would upgrade the server properly.

In reality, the content of migrate-docker-entrypoint.sh should be copied inside the current entrypoint.sh of mattermost-prod-db.

By doing so, your users will get upgraded without any manual intervention and difficult documentation.

If some of you want to be unstuck, I would suggest you to try to build mattermost-prod-db by yourself with postgres 13, and follow my example on btcpayserver/btcpayserver-infra@e29b1ee . This should work. (make your backups before though)

The only downside is that you need 2x the storage. Since the upgrade, dump the DB then reimport it.
Probably can prevent this with --link of pg_upgrade, but this bring additional compromise: What if it fail or if the user stop it on the middle?

[AntoineDavidSupersoniks]

Now i've the error : database "mattermost" does not exist !!!

[langemeijer]

I have used the instructions here #489 (comment)

I had to add this to get the alpine postgres db image working:

sed -i "s/libressl-dev/openssl-dev/" ./db/Dockerfile

Because of this packaging inconsistency bug in Alpine: https://gitlab.alpinelinux.org/alpine/aports/-/issues/12763

Also I'd like to suggest this change:

POSTGRES_DOCKER_TAG='13-alpine'

Also the last line didn't work for me. I changed it to:

echo "REINDEX SCHEMA CONCURRENTLY public;" | docker-compose exec -T db psql -U $POSTGRES_USER $POSTGRES_DB

[maxozerov]

After upgrade (docker and steps from @coltoneshaw scripts):
5.25.0 --> 5.37.0
the log of both the application and the database (PostgreSQL 13.2) - filled with errors:

2021-07-19 23:02:31.638 UTC [1431] ERROR:  duplicate key value violates unique constraint "status_pkey"
2021-07-19 23:02:31.638 UTC [1431] DETAIL:  Key (userid)=(XxX) already exists.
2021-07-19 23:02:31.638 UTC [1431] STATEMENT:  insert into "status" ("userid","status","manual","lastactivityat","dndendtime","prevstatus") values ($1,$2,$3,$4,$5,$6)

The problem seems to be described 13 hours ago on mattermost.atlassian.net MM-37202

"probably fix this by replacing the current store logic for StatusStore.SaveOrUpdate with a native upsert query."

It is not entirely clear, this will be fixed in the next. version?
Thx!

[wiersgallak]

@maxozerov We do not have an ETA at this time for the fix, but in a discussion with our Engineers, they believe this error is not a regression and there is no loss in functionality; it seems it is just an invalid error and not caused by the upgrade. If you do have functionality loss, we would like to learn more about your environment and the behavior you are seeing as a result.

[wiersgallak]

For anyone else following along on this issue, please see this note for more information and context of the solution.