Joget DX on Apache Tomcat and PostgreSQL on K8s

• The PostgreSQL JDBC driver
• The deployment of PostgreSQL using Helm on K8s
• The configuration of Joget to utilize this database
• Creating the database
• Importing an SQL script
• How to configure and restart the Joget service

See the following sections for more details.

Create Joget image with PostgreSQL JDBC driver

Customize a Docker image for Joget DX to include the JDBC driver necessary for connecting to PostgreSQL. To do so, follow these steps:

1. Create a Dockerfile that starts from a base image of Joget DX and copies the PostgreSQL JDBC driver to the corresponding Tomcat folder.
2. Built the image using the docker build command.
3. Upload it to a repository with the docker push command.

# create custom Dockerfile
cat <<EOF > joget-dx8-postgresql-tomcat9.Dockerfile
FROM jogetworkflow/joget-dx8-tomcat9:8.1.1
 
# Copy postgresql jdbc driver (download from https://jdbc.postgresql.org/download/postgresql-42.7.3.jar)
COPY postgresql-42.7.3.jar /deployments/apache-tomcat/lib/postgresql-42.7.3.jar
EOF
 
# build docker image
docker build -f joget-dx8-postgresql-tomcat9.Dockerfile --rm -t quay.io/replaceWithYourAccount/joget-dx8-postgresql-tomcat9:8.1.1 .
 
# push docker image
docker push quay.io/replaceWithYourAccount/joget-dx8-postgresql-tomcat9:8.1.1

Deploy PostgreSQL on K8s using Helm

To deploy PostgreSQL in your Kubernetes environment using Helm, a package manager for K8s, execute the following commands: 

# deploy postgresql using Helm
helm install postgres oci://registry-1.docker. 
io/bitnamicharts/postgresql
helm list
helm get notes postgres

# wait for postgresql to startup
kubectl wait \
  --for=condition=ready pod \
  --selector=app.kubernetes.io/name=postgresql \
  --timeout=180s

Create PostgreSQL database

This code snippet enables users to create a PostgreSQL database for storing and managing data in their platform (application or system built or implemented using Joget). It sets a password to access PostgreSQL and executes a command to create a new database named "jwdb". Upon running the command, users exit the psql environment. Users must execute this code to configure the PostgreSQL database on the platform.

# Check for postgresql password
export POSTGRES_PASSWORD=$(kubectl get secret --namespace default postgres-postgresql -o jsonpath="{.data.postgresql-password}" | base64 --decode)
 
# run psql
kubectl run postgres-postgresql-client --rm --tty -i --restart='Never' --namespace default --image docker.io/bitnami/postgresql:16.3.0-debian-12-r4 --env="PGPASSWORD=$POSTGRES_PASSWORD" \
      --command -- psql --host postgres-postgresql -U postgres -d postgres -p 5432
 
$ CREATE DATABASE jwdb;
$ \quit

Import Joget DB SQL

When this command is executed, it sends the content of the SQL file named jwdb-postgresql-dx8.pgsql to the PostgreSQL pod. Subsequently, it utilizes the previously set PostgreSQL password to authenticate the connection and executes the psql command to insert the script's content into the "jwdb" database. This process initializes the database with the schema and data necessary for the operation of the Joget platform.

# import sql
cat jwdb-postgres.sql | kubectl exec -i postgres-postgresql-0 -- env PGPASSWORD=$POSTGRES_PASSWORD psql -U postgres -d jwdb

Deploy Joget

To deploy Joget, follow the instructions provided at Joget on Kurbenetes, but ensure to replace the jogetworkflow/joget-dx8-tomcat9 image with the custom image quay.io/replaceWithYourAccount/joget-dx8-postgresql-tomcat9 in the YAML file as instructed.

Configure Joget datasource for PostgreSQL

These commands allow Joget users to configure Joget's connection with a PostgreSQL database. Creating and defining the appropriate configuration files within the Tomcat container sets the necessary parameters for Joget to interact correctly with the PostgreSQL database. This enables efficient management and utilization of data in applications and systems built with Joget.

# ssh into tomcat pod and create datasource files
kubectl exec --stdin --tty deployments/joget-dx8-tomcat9 -- /bin/bash
 
$ cat <<EOF > /opt/joget/wflow/app_datasource-postgresql.properties
workflowUser=postgres
workflowPassword=REPLACE_POSTGRES_PASSWORD_HERE
workflowDriver=org.postgresql.Driver
workflowUrl=jdbc\:postgresql\://postgres-postgresql/jwdb
EOF
 
$ cat <<EOF > /opt/joget/wflow/app_datasource.properties
currentProfile=postgresql
EOF
 
$ exit

Restart Joget

These commands allow you to restart the Joget instance to apply changes or troubleshoot issues. The instance is restarted by scaling the replica count of the Joget deployment to zero and then back to one. Additionally, by viewing the deployment logs, users can monitor the process and verify if the changes have been successfully applied.

# restart pods
 
kubectl scale --replicas=0 deployment/joget-dx8-tomcat9
 
kubectl scale --replicas=1 deployment/joget-dx8-tomcat9
 
# view logs
 
kubectl logs -f deployment/joget-dx8-tomcat9