[FOLIO-2145] Upgrade to PostgreSQL 12 Created: 04/Jul/19  Updated: 07/Nov/23  Resolved: 03/Jun/21

Status: Closed
Project: FOLIO
Components: None
Affects versions: None
Fix versions: None

Type: New Feature Priority: P3
Reporter: Julian Ladisch Assignee: Steve Ellis
Resolution: Done Votes: 0
Labels: R2, back-end, ci, core, platform-backlog
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original estimate: Not Specified

Issue links:
Blocks
blocks RMB-424 Replace tolower() by case-insensitive... Blocked
blocks RMB-425 Use computed columns, replace foreign... Blocked
is blocked by FOLIO-1845 Replace postgresql-embedded by testco... Closed
is blocked by FOLIO-3168 Update folio-ansible to use Postgres 12 Closed
is blocked by RMB-483 TestContainers instead of postgresql-... Closed
is blocked by FOLIO-3167 Update folio-tools to use Postgres 12 Closed
is blocked by FOLIO-2144 Upgrade to PostgreSQL 11 Closed
Duplicate
is duplicated by FOLIO-2406 SSL/TLS, SCRAM-SHA-256, migration to ... Closed
Relates
relates to FOLIO-3094 SPIKE: Investigate Upgrade to Postgr... Closed
relates to RMB-605 Combining characters (diacritics, uml... Closed
Sprint: CP: sprint 115
Story Points: 1
Development Team: Core: Platform

 Comments   
Comment by Julian Ladisch [ 04/Jul/19 ]

PostgreSQL 12 is still beta: https://www.postgresql.org/

We cannot update to 12 for the same reasons we cannot update to 11, see FOLIO-2144 Closed .

Comment by Jon Miller [ 31/Oct/19 ]

Should this be unblocked now that 12 has been released?

Comment by Julian Ladisch [ 20/Nov/19 ]

Amazon RDS Database does not support PostgreSQL 12 for production. There is preview only: https://aws.amazon.com/about-aws/whats-new/2019/11/postgresql-12-0-now-available-in-amazon-rds-database-preview-environment/

Comment by Julian Ladisch [ 20/Nov/19 ]

PostgreSQL 12.1 is released and ready for production: https://www.postgresql.org/about/news/1976/
However, FOLIO cannot update to it for the same reasons we cannot update to PostgreSQL 11, see FOLIO-2144 Closed and the issues that block FOLIO-2144 Closed .

Comment by Jon Miller [ 18/Nov/20 ]

Looks like it should be PostgreSQL 13.1 at this point.

Comment by Julian Ladisch [ 18/Nov/20 ]

Jon Miller PostgreSQL 13 in Amazon RDS is still beta: https://aws.amazon.com/rds/postgresql/

Comment by Steve Ellis [ 03/May/21 ]

It looks like as of today, AWS RDS supports up to version 13.2: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.version13

That doesn't seem to mean we would go from 11 to 13. See here for a mapping between current source version and "Preferred major upgrade targets": https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html#USER_UpgradeDBInstance.PostgreSQL.MajorVersion

So for example if we are currently using 11.9 we would move to 12.6, and then presumably to 13.2.

Major version upgrades can contain database changes that are not backward-compatible with previous versions of the database. This functionality can cause your existing applications to stop working correctly.

 

 

Comment by Julian Ladisch [ 05/May/21 ]

https://aws.amazon.com/rds/postgresql/ still says:

"Amazon RDS for PostgreSQL currently supports PostgreSQL 9.6, 10, 11, and 12."

PostgreSQL 12.4 is the latest version that Aurora supports: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Updates.20180305.html

Comment by Julian Ladisch [ 19/May/21 ]

CI places that need to change PostgreSQL 10 to 12:

https://github.com/folio-org/folio-tools/blob/master/jenkins-slave-docker/Dockerfile.agent-focal-java-11

https://github.com/folio-org/folio-tools/blob/master/jenkins-slave-docker/Dockerfile.focal-java-11

https://github.com/folio-org/folio-tools/blob/master/jenkins-slave-docker/Dockerfile.xenial-java-8

https://github.com/folio-org/folio-ansible/blob/master/roles/postgresql/tasks/main.yml

 

Comment by Jakub Skoczen [ 20/May/21 ]

We discussed this during the DevOps meeting and agreed to do it in 2 steps:

1. Update folio-tools first to check that test are passing fine

If successful:

2. Upgrade folio-ansible to 12.

Comment by Steve Ellis [ 20/May/21 ]

Jakub Skoczen per our discussion in the dev ops meeting I created two tickets, one which covers folio-tools and one which covers folio-ansible:

https://folio-org.atlassian.net/browse/FOLIO-3167

https://folio-org.atlassian.net/browse/FOLIO-3168

I also set this ticket's status to blocked.

However there were two things I wasn't able to do (perhaps I need a new permission or two):

  • I'm not able to create a reciprocal link (a "blocks" type link) either on the two tickets I created or on this ticket. There's no option for creating links in my More dropdown. I don't see options for creating links of any kind.
  • I'm also not able to move the tickets into the DevOps Requests queue like I think the team asked for. In this case I get a notification saying that I don't have scheduling permissions.
Comment by David Crossley [ 21/May/21 ]

I have done those afore-mentioned Jira tweaks.

 

Comment by Marc Johnson [ 08/Jun/21 ]

Jakub Skoczen Steve Ellis

This issue appears to have changed the version of PostgreSQL for the jenkins builds and reference environments to 12. Have I understood that correctly?

If so, does that mean that the officially supported version of PostgreSQL is now version 12?

Comment by Steve Ellis [ 08/Jun/21 ]

Marc Johnson yes I think you're understanding this ticket correctly. This is about upgrading the jenkins builds to postgres 12. I'll defer to Jakub Skoczen about what official support would look like. Are there other things we need to do to socialize the change within the community?

I can say that a lot of work went into investigating and validating the upgrade from 10 to 12. Here are the relevant parts for that work:

 

Comment by Marc Johnson [ 08/Jun/21 ]

Steve Ellis thanks for responding to my question.

yes I think you're understanding this ticket correctly. This is about upgrading the jenkins builds to postgres 12.

I knew you had been investigating the impact of this. I didn't know that this issue was also about enacting this change.

Are there other things we need to do to socialize the change within the community?

I think there are two parts to that.

I think this is an architectural decision, that I would've thought some governance body, like the TC would be involved in making it.

Once decided, I would hope that there was some documentation that stated this as the official version and this was communicated to folks like the Sys-Ops SIG (maybe they'd even have been involved in the decision), the EBSCO FSE team and the developers.

I can say that a lot of work went into investigating and validating the upgrade from 10 to 12

I was aware of that, and I appreciate it.

The ticket for the investigatory work is here

Did this include non-RMB modules, like the ones based upon the Spring Way or the ERM modules which use different tools?

Comment by Jon Miller [ 08/Jun/21 ]

How is upgrading to a new version of the DBMS an architectural change? Something is wrong if parts of FOLIO are that hard coded to a specific version of PostgreSQL that an upgrade like this is a huge issue. PostgreSQL 13 is out and 14 is in beta testing.

Comment by Marc Johnson [ 08/Jun/21 ]

Jon Miller

How is upgrading to a new version of the DBMS an architectural change?

I consider it an architectural change because of the impact it has on hosting providers. As soon as modules depend upon features in PostgreSQL 12, any adopting organisations need to upgrade their production environments to that version or if that version is the only supported version, they will need to upgrade in order to get community support.

Comment by Steve Ellis [ 08/Jun/21 ]

Marc Johnson I agree that an "official" FOLIO-wide postgres version would be useful, especially for implementers, in the same way that there should be an official java version and so on. (I just had a colleague who is building our AWS cluster ask me what version of Java he should install to get builds to work. It's not obvious.)

Another example: I lost time in my upgrade testing because the readme of more than one module stated that postgres 9.6 was required, when in fact it was 10, and so on. I figured this out by asking my team which pointed out that 10 was what the reference environments had been using for a while. So a documented and better shared understanding of minimal tool set would help onboarding of new people (like me) too.

Regarding the ERM modules and Spring Way modules: based on your comment I took a look at modules that have "erm" in their name and are backend Java/data access oriented. It looks like there are 3 or 4. I'm not sure why these weren't included in my testing other than I missed them. I think the wiki page has at least one Spring-oriented module which I didn't persevere with long enough to get its tests to work.

The testing ended up taking a ton of time which probably helps account for why some things didn't get included. Embedded postgres was the culprit. Embedded pg didn't support 12, and it wouldn't have mattered even if it did since (If I'm remembering right) we're getting that from vertex so it's pretty deeply embedded in our own code. So what I ended up doing was running tests against a local pg 12 and more than a few modules required hacks to get even this scenario to work. But everything with a green checkmark in the wiki means I ran the unit tests against a local pg 10 and 12, compared the results, and everything looked good.

To be fair part of what we were trying to do was "explore the work required" (it was a spike) to upgrade to 12, rather than fully validating upgrading to 12 for every piece of FOLIO code. Our findings generally were "upgrading to 12 should not be a huge deal" since for pretty much 100% of the tests we ran everything was fine. Ultimately it should probably fall to module owners to work through any as yet undetected upgrade problems in their code, although again, we think that will be minimal. I think getting pg 12 on the reference environments could be thought of as a step in this process.

Going forward this process should get a lot easier now that embedded pg is being phased out.

 

 

Comment by Marc Johnson [ 09/Jun/21 ]

Steve Ellis Thank you for your thorough response. Much of my response isn't aimed at the work you did, rather my thoughts on the broader impact of this work.

I agree that an "official" FOLIO-wide postgres version would be useful, especially for implementers, in the same way that there should be an official java version and so on. (I just had a colleague who is building our AWS cluster ask me what version of Java he should install to get builds to work. It's not obvious.)

Agreed.

Strictly speaking, FOLIO as a runtime system doesn't support any particular version of Java. Most folks (by convention) use the Docker images built by the central build systems to deploy the official reference module versions. Folks could instead, rather than rely on docker images, they could use the underlying module artefacts, at which point things like Java version become important. I'm not aware of any folks that do that.

I think it would be great if we had documentation for:

  • the runtime environment that FOLIO supported e.g. PostgreSQL version, Kafka version, Elastic Search version etc
  • the build environment (which your module has to be a part of to be included in the official reference configuration) e.g. what versions of build tools it supports like JVM, maven, gradle, npm, JS, perl etc

Another example: I lost time in my upgrade testing because the readme of more than one module stated that postgres 9.6 was required, when in fact it was 10, and so on. I figured this out by asking my team which pointed out that 10 was what the reference environments had been using for a while. So a documented and better shared understanding of minimal tool set would help onboarding of new people (like me) too.

That's unfortunate. It's likely caused by FOLIO starting with 9.6 and folks wanting to document that for each module, then underlying tooling switched to needing 10 without the module developers realising.

Regarding the ERM modules and Spring Way modules: based on your comment I took a look at modules that have "erm" in their name and are backend Java/data access oriented. It looks like there are 3 or 4. I'm not sure why these weren't included in my testing other than I missed them. I think the wiki page has at least one Spring-oriented module which I didn't persevere with long enough to get its tests to work.

The ERM modules use groovy and grails and are built using gradle (damn, I miss gradle). They probably weren't included as the attention was on the modules based upon RMB.

The testing ended up taking a ton of time which probably helps account for why some things didn't get included. Embedded postgres was the culprit. Embedded pg didn't support 12

It's unfortunate that this took up your time, as this was known prior to you beginning this work.

and it wouldn't have mattered even if it did since (If I'm remembering right) we're getting that from vertex so it's pretty deeply embedded in our own code.

The use of embedded PostgreSQL came from RMB not vert.x. You are correct that for storage modules that use RMB, embedded PostgreSQL was deeply integrated into the test code at least.

So what I ended up doing was running tests against a local pg 12 and more than a few modules required hacks to get even this scenario to work. But everything with a green checkmark in the wiki means I ran the unit tests against a local pg 10 and 12, compared the results, and everything looked good.

To be fair part of what we were trying to do was "explore the work required" (it was a spike) to upgrade to 12, rather than fully validating upgrading to 12 for every piece of FOLIO code. Our findings generally were "upgrading to 12 should not be a huge deal" since for pretty much 100% of the tests we ran everything was fine.

What is the follow up for the modules that have a hazard sign against them e.g. mod-courses, mod-licenses?

Ultimately it should probably fall to module owners to work through any as yet undetected upgrade problems in their code, although again, we think that will be minimal.

If that is the expectation, I think it could be useful for someone to let the module developers know that this is what has happened and that they should allocate some time to investigating this. I don't think that is your responsibility.

I think getting pg 12 on the reference environments could be thought of as a step in this process.

I think that's where I got confused. That sequence means that the first opportunity module developers have to discover issues is in the hosted environments, where diagnosis could be challenging, especially if they don't know the PostgreSQL version has changed.

Going forward this process should get a lot easier now that embedded pg is being phased out.

Agreed, for the modules that are based upon RMB.

Comment by Steve Ellis [ 09/Jun/21 ]

Thanks Marc Johnson for engaging on this. Thank you for the details on how implementers are grabbing the docker builds of modules rather than compiling them themselves which of course makes sense. I'm going to discuss that here to make sure it's an option we're aware of (if not already).

I'm going to add some of the missing ERM modules to the wiki page in case any one lands there looking for them so they can better see what was and wasn't done.

Finally I'm going to bring up your concerns about socializing the upgrade to 12 more widely in our core platform meeting tomorrow or Monday. I'm not familiar enough with how the TC, Sys-Ops SIG or the EBSCO FSE teams do their work to jump in on my own. But if someone were to invite me to those groups (for example) or guide me in some way, I'd be happy to present and discuss the testing work that was done.

Comment by Jakub Skoczen [ 10/Jun/21 ]

Distributed nature of FOLIO makes enforcing uniform version of runtime component like the JVM tricky – the DevOps team spent at least on release cycle upgrading default containers to JDK11 and rolling out this change across various modules. But there are still some orphaned or non-maintained modules where this change wasn't applied.

I think for Postgres the situation was simpler – the switch to Postgres 12 was discussed with various hosting providers before R2 and it has been agreed that version 12 is a safe target as most managed solutions support it (RDS, Aurora). In fact, many organization already deploy FOLIO on version 12. Steve's work to verify support in individual modules was done as an extra verification step, after which the DevOps team migrated hosted reference envs to validate this further.

I agree that including ERM modules in this testing would be a good idea but in practice this is hard for the Platform team to support it.

I think it's a good idea to bring the topic of a "supported runtime" to the TC – unlike versioning individual modules, FOLIO doesn't have a good model for versioning infrastructure (ES, Kafka, PG, etc ..)

Comment by Marc Johnson [ 10/Jun/21 ]

Jakub Skoczen

I think for Postgres the situation was simpler – the switch to Postgres 12 was discussed with various hosting providers before R2 and it has been agreed that version 12 is a safe target as most managed solutions support it (RDS, Aurora).

Who made that decision?

Where is that decision documented?

In fact, many organization already deploy FOLIO on version 12. Steve's work to verify support in individual modules was done as an extra verification step, after which the DevOps team migrated hosted reference envs to validate this further.

It is up to every implementor to decide what they use in their infrastructure.

I think that is different and somewhat separate to the FOLIO community stated which version they support.

I agree that including ERM modules in this testing would be a good idea but in practice this is hard for the Platform team to support it.

I sympathise with that. Maybe the module developers could have been contacted about this intention prior to the changes being made and given an opportunity to do their own testing .

I think it's a good idea to bring the topic of a "supported runtime" to the TC – unlike versioning individual modules, FOLIO doesn't have a good model for versioning infrastructure (ES, Kafka, PG, etc ..)

+lots and lots

Are you going to raise this with the TC?

Comment by Marc Johnson [ 10/Jun/21 ]

Steve Ellis

Thank you for the details on how implementers are grabbing the docker builds of modules rather than compiling them themselves which of course makes sense. I'm going to discuss that here to make sure it's an option we're aware of (if not already).

I don't know that for certain, it's based upon some of the conversations I've been involved in.

I'm going to add some of the missing ERM modules to the wiki page in case any one lands there looking for them so they can better see what was and wasn't done.

Thank you.

Finally I'm going to bring up your concerns about socializing the upgrade to 12 more widely in our core platform meeting tomorrow or Monday. I'm not familiar enough with how the TC, Sys-Ops SIG or the EBSCO FSE teams do their work to jump in on my own. But if someone were to invite me to those groups (for example) or guide me in some way, I'd be happy to present and discuss the testing work that was done.

Thank you for raising it with the Core Platform team. I don't think it's your responsibility to raise it with the other groups. I'll leave it to Jakub Skoczen and the team to advise you on that.

Generated at Thu Feb 08 23:18:33 UTC 2024 using Jira 1001.0.0-SNAPSHOT#100246-sha1:7a5c50119eb0633d306e14180817ddef5e80c75d.