Setting up an external PostgreSQL database

To use IBM Process Mining in a production environment, installation of an external database is recommended.

External means that it is not managed by a process mining operator. The following list shows examples of external configurations:

This section describes how to setup PostgreSQL using the open source operator provided by cloudnative-pg.io. A CloudnativePG operator is used to deploy and manage PostgreSQL in your OpenShift cluster. The CloudNativePG operator is an open source solution that provides automated deployment, management, and monitoring of PostgreSQL databases:

Bare metal PostgreSQL
Commercial operator (e.g. enterprisedb.com)
Open source operator (e.g. cloudnative-pg.io)
DBaaS (e.g. AWS RDS)

Configuring an external PostgreSQL instance in the CRD

You need to make changes to the storage section of the processmining CRD to configure your external PostgreSQL:

processmining:
  storage:
    database:
      external:
        connectionstring: ''
        credential:
          passwordkey: <THE KEY THAT CONTAINS THE PASSWORD VALUE>
          secretname: <YOUR SECRET NAME>
        database: <database name>
        port: <database port>
        user: <database username>

You must replace the placeholders with your actual database configuration.

Example: Configuring a CloudNativePostgreSQL database

Do the following steps to configure a CloudNativePG with IBM Process Mining:

Search for "CloudNativePG" or "Cloud Native PostgreSQL" operator in the OperatorHub.
Select the operator and click Install it into a single namespace (for example, cp4ba, production, or processmining.
After installation completes, create a new cluster resource, reduce it to 1 instance and name it.
For a standard environment, one node is enough. You can scale the cluster later if needed for high availability or performance requirements.
Wait until the cluster is ready and all pods are running.
Monitor the cluster status in the OpenShift console. The cluster is ready when all pods show a Running status and the cluster phase is Cluster in healthy state.
Locate the Secret named <YOUR_INSTANCE_NAME>-app.
This secret contains the connection parameters: host, port, username, password, and database name. These values will be used to configure the connection from IBM Process Mining to the database.

You can view the secret contents using the OpenShift console or by running:
```
oc get secret <YOUR_INSTANCE_NAME>-app -n <YOUR_NAMESPACE> -o yaml
```
Configure the processmining instance to use the external PostgreSQL database. Replace the placeholders with your actual database configuration:
```
processmining:
  storage:
    database:
      external:
        connectionstring: ''
        credential:
          passwordkey: password
          secretname: <YOUR_PSQL_INSTANCE_NAME>-app
        database: app
        host: <YOUR_PSQL_INSTANCE_NAME>-rw
        port: 5432
        user: app
```
The secret parameters are automatically read from the secret specified in secretname, with the password retrieved using the key specified in passwordkey.

Configure the Task Mining instance to use the external PostgreSQL database:

Tip: Install the basic CR first, then add the database spec sections. Without them the reconciler will log errors about missing PostgreSQL fields, but it is expected and does not block the installation.

Use the following code to install a basic CR (before adding database config):

cat <<EOF | oc apply -f -
apiVersion: processmining.ibm.com/v1beta1
kind: ProcessMining
metadata:
  name: processmining-instance
  namespace: <YOUR_NAMESPACE>
spec:
  license:
    accept: true
    cloudPak: IBM Cloud Pak for Business Automation
  defaultStorageClassName: <YOUR_STORAGE_CLASS>
  taskmining:
    install: false
EOF

Configure the taskmining instance to use the external PostgreSQL database. Replace the placeholders with your actual database configuration:

taskmining:
  storage:
    database:
      create: true
      dbtype: pgsql
      external:
        credential:
          passwordkey: password
          secretname: <YOUR_PSQL_INSTANCE_NAME>-app
        database: app
        host: <YOUR_PSQL_INSTANCE_NAME>-rw
        port: 5432
        schema: tm
        user: app
      size: '100'
      version: s11.5.9.0

Replace <YOUR_PSQL_INSTANCE_NAME> with the name of your CloudNativePG cluster instance.

Example: Configuring a bare metal PostgreSQL on top of IBM Cloud Pak for Business Automation environment

Do the following steps to install a bare metal PostgreSQL with IBM Process Mining installed in a CP4BA environment.

Note: Your username and password should be in accordance with CP4BA standards. If you change your username, you must update it in the pg_hba.conf file and in the following steps.

Create the databases:

For Process Mining:

export POSTGRES_PROCESSMINER_PWD="OCP-PM_USER1245"
export POSTGRES_PROCESSMINER_USER="pm_user"
export POSTGRES_PROCESSMINER_DATABASE="processmining"

sudo -u postgres psql -tc "SELECT 1 FROM pg_database WHERE datname = '${POSTGRES_PROCESSMINER_DATABASE}'" | grep -q 1 || sudo -u postgres psql -c "CREATE DATABASE ${POSTGRES_PROCESSMINER_DATABASE}"

sudo -u postgres psql -tc "SELECT 1 FROM pg_user WHERE usename = '${POSTGRES_PROCESSMINER_USER}'" | grep -q 1 || sudo -u postgres psql -c "CREATE USER ${POSTGRES_PROCESSMINER_USER}"

sudo -u postgres psql -c "ALTER USER ${POSTGRES_PROCESSMINER_USER} WITH ENCRYPTED PASSWORD '${POSTGRES_PROCESSMINER_PWD}';"

sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE ${POSTGRES_PROCESSMINER_DATABASE} TO ${POSTGRES_PROCESSMINER_USER};"

sudo -u postgres psql -d ${POSTGRES_PROCESSMINER_DATABASE} -c "GRANT ALL ON ALL TABLES IN SCHEMA public TO ${POSTGRES_PROCESSMINER_USER};"

sudo -u postgres psql -c "ALTER DATABASE ${POSTGRES_PROCESSMINER_DATABASE} OWNER TO ${POSTGRES_PROCESSMINER_USER};"

For Task Mining:

export POSTGRES_TASKMINER_DATABASE="taskmining"
export POSTGRES_TASKMINER_USER="taskminer"
export POSTGRES_TASKMINER_PWD="TaskMinerPwd01"

sudo -u postgres psql -tc "SELECT 1 FROM pg_database WHERE datname = '${POSTGRES_TASKMINER_DATABASE}'" | grep -q 1 || sudo -u postgres psql -c "CREATE DATABASE ${POSTGRES_TASKMINER_DATABASE}"

sudo -u postgres psql -tc "SELECT 1 FROM pg_user WHERE usename = '${POSTGRES_TASKMINER_USER}'" | grep -q 1 || sudo -u postgres psql -c "CREATE USER ${POSTGRES_TASKMINER_USER}" 

sudo −u postgrespsql −c "ALTERUSER ${POSTGRES_TASKMINER_USER} WITH ENCRYPTED PASSWORD '${POSTGRES_TASKMINER_PWD}';"

sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE ${POSTGRES_TASKMINER_DATABASE} TO ${POSTGRES_TASKMINER_USER};"

Update the hba.conf file:
1. Find the PostgreSQL data directory:
```
sudo -u postgres psql -c "SHOW data_directory;"
```
2. Open the file:
```
vi /var/lib/pgsql/17/data/pg_hba.conf
```
  Note: The file path may vary depending on the installed version of PostgreSQL.
3. Add the following lines at the bottom of the file. Make sure the username matches the DB user:
```
hostnossl all pm_user 0.0.0.0/0 scram-sha-256
hostnossl all taskminer 0.0.0.0/0 scram-sha-256
```

Restart the PostgreSQL service with the following code:

sudo systemctl restart postgresql-17
sudo systemctl status postgresql-17
sudo journalctl -u postgresql-17

Create secrets in OpenShift with the following code:

# Process Mining secret
oc create secret generic pm-external-postgres-secret \
  --from-literal=username=pm_user \
  --from-literal=password=OCP-PM_USER1245 \
  --from-literal=host=<YOUR_POSTGRES_HOST> \
  --from-literal=port=5432 \
  --from-literal=database=processmining \
  -n <YOUR_NAMESPACE>

# Task Mining secret
oc create secret generic tm-external-postgres-secret \
  --from-literal=username=taskminer \
  --from-literal=password=TaskMinerPwd01 \
  --from-literal=host=<YOUR_POSTGRES_HOST> \
  --from-literal=port=5432 \
  --from-literal=database=taskmining \
  -n <YOUR_NAMESPACE>

Replace <YOUR_POSTGRES_HOST> with your PostgreSQL server hostname or IP address, and <YOUR_NAMESPACE> with your OpenShift namespace.

Verify that the secrets were created with the following code:
```
oc get secret -n <YOUR_NAMESPACE> | grep postgres
```

Update the ProcessMining Custom Resource to reference the external database secrets:

apiVersion: processmining.ibm.com/v1beta1
kind: ProcessMining
spec:
  loglevel: INFO
  license:
    accept: true
    cloudPak: IBM Cloud Pak for Business Automation
  backup:
    schedule: 0 1 * * *
  cloudpak:
    zen:
      create: true
  defaultStorageClassName: <YOUR_STORAGE_CLASS>
  version: <YOUR_VERSION>
  usagemetering:
    anonymize: false
    enable: true
    entitlementkeysecret: ibm-usage-metering-upload-token
    frequency: 5 0 * * *
    sandbox: true
  taskmining:
    agent_plugin: true
    images:
      imagepullpolicy: Always
    install: true
    storage:
      database:
        create: true
        dbtype: pgsql
        external:
          credential:
            passwordkey: password
            secretname: tm-external-postgres-secret
          database: taskmining
          host: <YOUR_POSTGRES_HOST>
          port: 5432
          schema: tm
          user: taskminer
        size: '100'
        version: s11.5.9.0
      events:
        create: true
        name: taskmining-data
        size: '100'
  processmining:
    images:
      imagepullpolicy: Always
    monetdb:
      probe:
        enabled: true
        initialdelayseconds: 25
        periodseconds: 20
        timeoutseconds: 600
    storage:
      accelerator:
        create: true
        name: processmining-accelerator
        size: '10'
      custom_process_app:
        create: true
        name: processmining-custom-process-app-data
        size: '10'
      database:
        create: true
        external:
          connectionstring: ''
          credential:
            passwordkey: password
            secretname: pm-external-postgres-secret
          database: processmining
          host: <YOUR_POSTGRES_HOST>
          port: 5432
          user: pm_user
        size: '10'
      events:
        create: true
        name: processmining-repository
        size: '50'
      postgresedb:
        replicas: 1
        size: 10Gi
      redis:
        install: false
        name: ''

Replace the placeholders with your actual configuration values.