Supported Postgresql Version

Questions, comments, discussions. Over time certain topics might be moved to their own category.
Post Reply
Harvey
Posts: 11
Joined: Tue Oct 30, 2018 12:03 pm

Supported Postgresql Version

Post by Harvey » Wed Nov 14, 2018 10:44 am

Just out of curiosity: The docker installation refers to the postgres 9.5 container while Version 11.1 is out. Is there any backdraw in using the postgresql:latest docker container instead of version 9.5?

Greetings
Harvey

User avatar
rosarior
Posts: 159
Joined: Tue Aug 21, 2018 3:28 am

Re: Supported Postgresql Version

Post by rosarior » Thu Nov 15, 2018 5:56 am

Version 9.5 was the latest version when the documentation was written. We tend to pin all versions (Python, Django, OS binaries) to a specific one to keep external error factors to a minimum otherwise as you've seen we could spend days debugging errors that were in fact caused by platform, infrastructure or binaries and not by the code itself. When a dependency offers an LTS (long time support) release, we stick to those or in the absence of an LTS the most tested version.

In version 9.6 we found some regressions (some schema migration issues) and decided to recommend 9.5 which was well tested at the time. This was because versions 9.5 and 9.6 were in fact major releases even when only the minor number of the version number was increased. Since version 10 they've adopted a semantic versioning scheme (https://www.postgresql.org/support/versioning/). Upgrading from 10.1 to 10.2 or 11.x to 11.x+1 should not cause trouble now.

Version 9.5 will received updates until 2021 so there is no immediate major downside. That being said there some interesting new feature like parallel queries that could be beneficial when using Mayan. We'll need upgrade some deployments to more recent versions and monitor for regressions for a good amount of time. If no serious problems are encountered we can recommend a new version of PostgreSQL later in the documentation.

KevinPawsey
Posts: 50
Joined: Wed Aug 22, 2018 2:52 pm

Re: Supported Postgresql Version

Post by KevinPawsey » Thu Nov 15, 2018 7:33 am

This is interesting... being that I am using Docker, I don't mind trialling using the :latest of Postgres.

Will the existing code benefit from the new additions (such as the parallel queries), or will this need to be something that will come in future versions?

Also, will I be able to just spin up a new container of the latest version, and point it to the existing data volume?

Thanks again.

Kevin

Harvey
Posts: 11
Joined: Tue Oct 30, 2018 12:03 pm

Re: Supported Postgresql Version

Post by Harvey » Thu Nov 15, 2018 2:04 pm

Well, I have been brave (or nuts ;)) and used the :latest container. Let's see what happens...

KevinPawsey
Posts: 50
Joined: Wed Aug 22, 2018 2:52 pm

Re: Supported Postgresql Version

Post by KevinPawsey » Thu Nov 15, 2018 2:18 pm

Hoping for a big smiley-face feedback ;) ... you beat me to the upgrade, so there is no point in two of us breaking our installations! lol

Harvey
Posts: 11
Joined: Tue Oct 30, 2018 12:03 pm

Re: Supported Postgresql Version

Post by Harvey » Thu Nov 15, 2018 2:24 pm

Yeah, but I did a completely new installation without the need to upgrade anything. It might be different from your path.

KevinPawsey
Posts: 50
Joined: Wed Aug 22, 2018 2:52 pm

Re: Supported Postgresql Version

Post by KevinPawsey » Thu Nov 15, 2018 2:30 pm

Harvey wrote:
Thu Nov 15, 2018 2:24 pm
Yeah, but I did a completely new installation without the need to upgrade anything. It might be different from your path.
Boooooo... was hoping that you were going to just drop in the new container and prey it works... not that I would do something like that... :shock:

User avatar
rosarior
Posts: 159
Joined: Tue Aug 21, 2018 3:28 am

Re: Supported Postgresql Version

Post by rosarior » Thu Nov 15, 2018 11:29 pm

That is a good point you both bring, I honestly don't have any idea if the PostgreSQL container image does an auto update!
Looking at the Dockerfile or entrypoint file I don't see anything obvious that supports it detecting an upgrading existing files.

https://github.com/docker-library/postg ... Dockerfile
https://github.com/docker-library/postg ... rypoint.sh

For now I think the best advice would be to do a dump and restore when switching PostgreSQL docker image versions (https://docs.mayan-edms.com/topics/admi ... ml#backups).

Harvey
Posts: 11
Joined: Tue Oct 30, 2018 12:03 pm

Re: Supported Postgresql Version

Post by Harvey » Fri Nov 16, 2018 11:55 am

rosarior wrote:
Thu Nov 15, 2018 11:29 pm
For now I think the best advice would be to do a dump and restore when switching PostgreSQL docker image versions (https://docs.mayan-edms.com/topics/admi ... ml#backups).
Yes, that's what I think too, based on a quick google search. By the way, is there any way to see a logfile of an initial installation to check if something has gone wrong?

Greetings
Harvey

KevinPawsey
Posts: 50
Joined: Wed Aug 22, 2018 2:52 pm

Re: Supported Postgresql Version

Post by KevinPawsey » Fri Nov 16, 2018 2:41 pm

Maybe this will help:
https://www.postgresql.org/docs/11/pgupgrade.html

Mainly, the differences between major versions are in the system table it seems... it may still work if you upgrade the container without any other input... but I wouldn't guarantee that :P

Basically it states that you can just update the binaries (in our case the container) and then run pg_upgrade (in our case within the Postgres container).

I may give this a go soon... obviously I will backup my existing data before completely binning everything! :lol:

EDIT...
Saying all this though... it wont work, as it has been discussed here already:
https://github.com/docker-library/postgres/issues/37
Basically the pg_upgrade process expects both the old and new versions of Postgres to exist side-by-side... and also that the old and new data be existing in parallel... the above link discusses this in great detail, along with all the pitfalls. The last post (from 3 days ago as of time of writing this!), suggested a different container to temporarily upgrade Postgres to the version you want..

But this may be an option to try out:
https://github.com/tianon/docker-postgres-upgrade

I may give THIS a try... as it seems to be a more sensible option. Basically the above link has a list of docker containers that have the from/to versions (so in our case 9.5 to 11 is the one that we need)... then once upgraded you can use the vanilla v11 container.

Post Reply