Support PostgreSQL 17

[toco-cam]

Acceptance Criteria (Required on creation)

• Support PostgreSQL 17, shipped with the patch release by the end of November.
• Use the declarative pod spec approach.
• https://github.com/camunda/team-automation-platform/issues/52 doesn't support combo pods. I.e., using the official Maven and the PostgreSQL Docker images in a combined pod (combo pod).
  • It only supports a pod using one container.
  • We need the combo pod approach to run the Maven command in the official Maven Docker image, which connects to the PostgreSQL database running in the same combo pod.
• We want to keep the Jenkinsfile length as small as possible so that we don't exceed line limits by leveraging methods from the https://github.com/camunda/cambpm-jenkins-shared-library
• We should migrate the following stages:
  • Main:
  • Add a new stage db-unit-postgresql-170 under UNIT DB tests.
  • Daily:
  • Replace the stages:
    • large-data-tests-postgresql-160 with large-data-tests-postgresql-170.
    • old-engine-postgresql-160 with old-engine-postgresql-170.
    • rolling-update-postgresql-160 with rolling-update-postgresql-170
    • sql-scripts-postgresql-160 with sql-scripts-postgresql-170
  • Main EE:
    • Add a new stage webapp-plugins-unit-postgresql-170 under Webapp Plugins DB tests.
  • Migrating the stages that currently use PostgreSQL 14 is out of scope. We can do it optionally if we still have time.
• The solution could be as simple as passing another image and setup script for the database container:

  cambpmConditionalRetry([
  podSpec: [
    cpu: 32,
    mvnImage: 'maven:3.9.7-eclipse-temurin-17',
    dbImage: 'postgres:17.0',
    setupDb: cambpmUtils.postgresScript()
  ],
  ])

Hints

• Postgres 17 has been released already: https://www.postgresql.org/about/news/postgresql-17-released-2936/
• Prototype:
  • https://github.com/camunda/camunda-bpm-platform/pull/4552
  • https://github.com/camunda/cambpm-jenkins-shared-library/pull/149

Links

• We can close the infra issue when we can leverage the declarative pod spec approach: https://github.com/camunda/team-infrastructure/issues/568

Breakdown

### Code | jenkins-shared-library
- [ ] https://github.com/camunda/cambpm-jenkins-shared-library/pull/149

### 7.23
- [ ] https://github.com/camunda/camunda-bpm-platform/pull/4552
- [ ] https://github.com/camunda/camunda-bpm-platform-ee/pull/989
- [ ] https://github.com/camunda/camunda-docs-manual/pull/1695
- [ ] https://github.com/camunda/camunda-docs-static/pull/512
- [ ] https://github.com/camunda/camunda-bpm-platform/pull/4801

### 7.22 | Backport
- [ ] https://github.com/camunda/camunda-bpm-platform-maintenance/pull/1292
- [ ] https://github.com/camunda/camunda-bpm-platform-ee-maintenance/pull/504
- [ ] https://github.com/camunda/camunda-docs-manual/pull/1696
- [ ] https://github.com/camunda/camunda-docs-static/pull/511
- [ ] https://github.com/camunda/camunda-bpm-platform-maintenance/pull/1310
- [ ] https://github.com/camunda/camunda-bpm-platform-maintenance/pull/1312

### Optional / Follow-up
- [ ] https://github.com/camunda/camunda-bpm-platform/issues/4768
- [ ] https://github.com/camunda/camunda-bpm-platform/issues/4789

Dev2QA handover

• [x] Does this ticket need a QA test and the testing goals are not clear from the description? Add a Dev2QA handover comment

QA Testing

The feature needs testing for the two supported versions: master (7.23) and the 7.22 backport.

Two supported environments can be used for each supported version with a slight variance to achieve a pluralistic testing result.

Please find below the breakdown of the testing scenarios:

Scenarios

• [ ] Master
  • [ ] Test Postgres 17 against WildFly Application Server 33
  • [ ] Test Postgres 17 against JBoss EAP 8
• [ ] 7.22 Backport
  • [ ] Test Postgres 17 against WebLogic
  • [ ] Test Postgres 17 against Spring Boot 3.3

[yanavasileva]

Note:

• EE pipeline also have stages for postgresql
• I don't understand at the moment how the combo pod will work with matrix. I will have a look the prototype for my own understanding.

[tasso94]

EE pipeline also have stages for postgresql

They are using PostgreSQL 14 right now which is still supported for versions <= 7.23. No urgent need to bump them to PostgreSQL 17 right now. I see it as a bonus.

[psavidis]

Kickoff

1. Context

Our CI is already capable of utilizing the declarative podspecs approach to do basic parameterisation of the docker images used (see respective feature)

The above already works and is capable of specifying the image for spinning one pod using one docker image.

The context of this feature is to extend the existing solution further to i) work with multiple containers per pod and ii) use the underlying new functionality to introduce Postgres 17

Why are combopods needed

In most stages, the jenkins files describe stages that execute specific testing logic (e.g spring boot tests, unit tests during assembly stage, e2e tests etc.).

This is done by invoking maven and and the respective profile as in the following screenshots:

• One container is needed with a linux OS to execute the maven command for the tests
• One container is needed for a database to be tested
• Both combined in one pod is a simple solution and works well for this need

2. Business Value

2.1 Users

Users are going to benefit from the latest supported version of Postgres, a database that has been supported for all the lifetime of the product so far.

2.2 Developers

Developers will be able to utilize the declarative podspecs to configure multiple containers (typically dbs) in a single pod. With the first iteration, only postgres 17 will be supported

3. Customer Requirements

• As a user of the product  i want to be able to use Postgres 17 with Camunda 7

• As a developer, I want to be able to add Postgres 17 support to the CI using a podspec approach. Furthermore, the following criteria need to be met:

  • The Jenkinsfile should be frugally used due to max length limitation

  • The Scripts should be able to support podspecs with multiple containers per pod

  • The underlying scripts should support adding new containers in an easy and maintainable  way

4. Documentation

The new Postgres 17 version needs to be mentioned in 

1. announcements and 
2. supported environments 

sections of the docs.

5. Technical Proposal

The following high level changes are needed :

1. Add multi-image support to Podspec Execution of Jenkins-shared-library Code

   • Introduction

     • All stages (calculated, non-calculated) end up calling the cambpmConditionalRetry script to execute a stage.

     • The script currently considers two modes with priority:

     • Podspec mode

       • If a podspec is configured in the incoming parameters, the script is going to consider that first.

     • AgentLabel mode

       • If no podspec is configured, the script will try to execute the given agentLabel using the legacy behavior

   • What needs to Change

     • Change cambpmConditionalRetry | podspec mode to support two pod executions

     • Single-image pod execution

       • Will be used for node execution (alpine, maven images)

     • Multi-image pod execution

       • Will be used for executing maven and database containers into a single pod
       • Important: The underlying script should facilitate the addition of new database and node execution containers in an easy way

     • Change cambpmConditionalRetry | podspec mode to consider agentLabels

     • Why: The calculated stages pass the db information in agentLabel as discriminator value for that stage

       • Example: DB Unit | Postgres 17 (see screenshot)

   • Algorithm

     • If podspec is configured
       • Search in the podSpec images to find an image to execute the agentLabel
       • If found, execute the pod using the first image found (db)
       • If no image is found, execute the agentNode using nodeExecution (legacy behavior)
     • If no podSpec is configured
       • execute the agentLabel using nodeExecution (legacy behavior) 

2. Incorporate the new Podspec Execution in the CI of camunda-bpm-platform

   • Ensure all existing cambpmConditionalRetry invocations in the Jenkinsfiles (main, daily, sidetrack) work with the new changes
   • Configure Postgres 17 in matrices.yml
   • Configure the stage-types.yml to execute the  Postgres 17 stages with multipod-execution

3. Incorporate the new Podspec Execution in the CI of camunda-bpm-platform-ee

   • Similar to point section above

4. Update Documentation with Postgres 17

   • Update the manual
   • Add Internal dev documentation:
     • Mention explicitly in the code of jenkins-shared-library the isolation level of postgres and add an informative comment to check isolation level for new db entries

5. Add Portainer support for Postgres 17

   • This can be addressed in a follow-up ticket
     • https://github.com/camunda/camunda-bpm-platform/issues/4768

---

6. Breakdown

• [x] cambpm-jenkins-shared-library

  • Add postgres 17 to available databases

  • See resources/cambpm/config/databases.yml

  • Enrich cambpmConditionalRetry function to support multipod parameterisation.

  The function can support two hard-coded modes:

  • Node execution (maven, alpine)

  • Node + Db execution

  • Only Postgres 17 will be supported at the beginning. New dbs can be added in the future.

  • Add a comment targeting devs to inspect the isolation level when adding a new database

  • Add a comment to mention the isolation level of Postgres 17

    • It can be incorporated as javadoc in the function that will handle the postgres podspec in cambpmConditionalRetry

• [x] cam-bpm-platform

  • Configure postgres 17 in matrices.yml
    • Add postgres_170 under engine-webapp-unit.databases
    • Replace postgres_160 with postgres_170 in daily-stages.databases
  • Replace existing podSpec.image with podSpec.images in stage-types.yml
  • Add podspec config in stage-types.yml
    • Main pipeline
      • db-unit-postgresql-170 - Add podSpec with images config under db-unit
    • Daily pipeline
      • large-data-tests-postgresql-170 - Add podSpec with images config under large-data-tests
      • old-engine-postgresql-170 - Add podSpec with images config under old-engine
        • rolling-update-postgresql-170 - Add podSpec with images config under rolling-update
        • sql-scripts-postgresql-170 - Add podSpec with images config under sql-scripts
  • Update maven 3.8.5 references (ref) to 3.9.7 in the JDK stages (ref) if possible

• [x] camunda-docs-manual & camunda-docs-static

  • Add Postgres 17 under Supported Database Products
  • Add under Announcements | Camunda Platform 7.23 the following entry:
    • Support for PostgreSQL 17

• [x] camunda-bpm-platform-ee

  • Add Postgres 17 in the CI dbs (see matrices.yml)
  • Change all podSpec.image references to podSpec.images found in:
    • Jenkinsfiles, 
    • matrices.yml
    • stage-types.yml

• [ ] Testing

  • [x] CI

    • The testing can be done by invoking different Jenkins builds to verify that:
      • Postgres 17 builds run using the new combopod and 
      • Other builds continue to run successfully with the new code of jenkins-shared-library
    • Scenarios
      • Build passes against: [“ci:migration”, “ci:postgresql”, “ci:rolling-update”, “ci:jdk”]
        1. CE | Main, Daily, (Sidetrack)
        2. EE | Main, Daily, (Sidetrack)
           • Build passes against: [“ci:default”, “ci:all-db”]
        3. CE Main, Daily, (Sidetrack)
        4. EE Main, Daily, (Sidetrack)

  • [ ] Manual

    • Extra Testing can be done manually using:
    • [x] one container of the dev’s choice (e.g tomcat) and
    • [ ] two more containers can be tested manually from the QA side.

  • [ ] (Bonus) Docker Image Update camunda-run to support Postgres 17 in docker-camunda-bpm-platform

---

• [ ] Backport the above changes to 7.22

  • [x] Code & Docs The changes required span across the following repositories:

  • camunda-bpm-platform

  • camunda-bpm-platform-ee

  • camunda-docs-manual

  • camunda-docs-static

  • [x] Testing

  • [x] CI

    • Similarly to the main feature, the CI pipelines can be used to test the changes against the same profiles

  • [x] Manual

    • Manual testing is required for the backport as well. Similarly to the main feature, the dev can try
    • [x] One container of choice against Postgres 17 and
    • [ ] Delegate 2 more to the QA

  • Task Structure

  • The Backport can be implemented in one individual task. Once code & testing is done, the task can be forwarded to the QA to complete the testing.

---

• [ ] (Bonus) Migrate stages that use postgres 14 to postgres 17:

  • Stages

  • engine-IT-tomcat-9-postgresql-142

  • engine-IT-tomcat-10-postgresql-142

  • engine-IT-wildfly-postgresql-142

  • engine-IT-wildfly26-postgresql-142

  • engine-IT-XA-wildfly-postgresql-142

  • engine-IT-XA-wildfly26-postgresql-142****

  • Task

  • If there is time left for the bonus stages, a new task can be opened to incorporate them.

[psavidis]

Status: The code, repositories and doc pull requests all pass through the respective CI tests.

Next Steps: Assigning the ticket for Review to @mboskamp. In the meantime, @psavidis will do the the manual testing.

Review Tips:

• The Code of jenkins-shared-library ideally shouldn't change unless there is a bug or it does not fulfill the feature requirements.
  • Reason: Any change will have to undergo all pipeline tests for main and backport.
• Suggestion: Any enhancement, refactoring or improvement can be done in a separate ticket as a follow-up given there is time left before the feature delivery.

[psavidis]

Testing Status

• [x] 7.23 against Postgres 17
  • Server / Environment: Tomcat 10
• [x] 7.22 Backport against Postgres 17
  • Server / Environment: camunda-run

[psavidis]

✅ The minimum viable feature is merged to master and 7.22.1-SNAPSHOT respectively.

Note: The following optional tasks can be taken into consideration and are not mandatory for the completion of this ticket:

➡️ What's Next

• Important
  • Portainer support
• Improvements
  • Refactor | Improve the Existing Implementation

[psavidis]

Jenkins Build Failures Issue Investigation

Problem: After merging the code changes to jenkins-shared-library and the successful 7.23, 7.22 PR builds, there are builds failing on master.

Failing Builds

• 7.23
  • Daily Build #13
  • Failing Stages
    • instance-migration-postgresql-170 (see stage, logs)
    • JDKs (see stage, logs)
      • Jenkins Runner: jenkins-job-runner-vrp0
• 7.22
  • Daily Build #9
  • Failing Stages:
    • instance-migration-postgresql-170 (see logs, stage)
    • JDKs ( see logs, stage)
      • Jenkins Runner: jenkins-job-runner-nj4ll

When: Both builds failed at 2014-11-16

Observations:

• The underlying pod of execution is shut down during instance-migration-postgresql-170 stage
• The Memory Usage for both Jenkins runners looks very close to the ceiling
  • Grafana dashboards for Runner: jenkins-job-runner-vrp0
  • Grafana dashboards for Runner jenkins-job-runner-nj4ll
• The jenkins-job-runner config does not specify a default Max Memory config
• The n1-standard4 machine type mentions 15Gb memory

Side effect: The execution of instance-migration stage with Postgres 17 is not executed with Podspec and the build freezes. Root-cause: The legacy behaviour tries to execute the stage with an image searching in the infra-team images. The image is not found and the build waits for ever.

Solution: Execute instance-migration with podspec.

PR:

• https://github.com/camunda/camunda-bpm-platform/pull/4801
• https://github.com/camunda/camunda-bpm-platform-maintenance/pull/1310

[psavidis]

Backport Update

The 7.22 backport still uses maven:3.8.5 in the stage-types.

PR to sync with latest maven:3.9.7 according to latest decision:

• https://github.com/camunda/camunda-bpm-platform-maintenance/pull/1312