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

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

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.