Simple Monitor not working with Postgresql as datasource

[vctrmn]

trafficstars

Hello everyone!

I am trying to get Simple Monitor working with Postgresql as datasource. The data is streamed by ZeeQS into Postgresql.

I get this error in Simple Monitor UI :

InvalidDataAccessResourceUsageException

could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

                org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259)
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)
...
Caused by: org.postgresql.util.PSQLException: ERROR: column processent0_.resource_ does not exist
  Position: 86
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2674)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2364)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:354)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:484)

I noticed that both ZeeQS and Simple Monitor create/update tables in Postgresql during their startup.

How to get a consistent data model between ZeeQS and Simple Monitor ?

[saig0]

Do you use the simple monitor and ZeeQS together with the same data source? Both applications store data from Zeebe but in a different data model. If you use both applications then it is recommended to use different data sources (or schemas).

Do you observe any issue when you run the simple monitor on PostgreSQL alone?

[younes-io]

Hi @saig0

I also have the same issue. I have two different PostgreSQL databases (one for Simple Monitor and one for ZeeQS), and yet, I get the same error.

[saig0]

@vctrmn / @younes-io thank you for reporting :+1:

Zeebe Simple Monitor and ZeeQS don't share the same data model. Both applications have their own data model that contains different data (with some overlap).

It is not recommended to run both applications with the same database (i.e. the same Postgres instance) :warning:

Instead, I recommend to use two databases or two database schemes. The following docker-compose.yml works for me:

version: "3"

networks:
  zeebe_network:
    driver: bridge

services:
  zeebe:
    container_name: zeebe_broker
    image: ghcr.io/camunda-community-hub/zeebe-with-hazelcast-exporter:8.0.5
    environment:
      - ZEEBE_LOG_LEVEL=debug
    ports:
      - "26500:26500"
      - "9600:9600"
      - "5701:5701"
    networks:
      - zeebe_network

  simple-monitor-postgres:
    container_name: zeebe-simple-monitor-postgres
    image: ghcr.io/camunda-community-hub/zeebe-simple-monitor:2.4.0
    environment:
      - zeebe.client.broker.gateway-address=zeebe:26500
      - zeebe.client.worker.hazelcast.connection=zeebe:5701
      - spring.datasource.url=jdbc:postgresql://postgres-zeebe-simple-monitor:5432/postgres
      - spring.datasource.username=postgres
      - spring.datasource.password=zeebe
      - spring.datasource.driverClassName=org.postgresql.Driver
      - spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
    ports:
      - "8082:8082"
    depends_on:
      - zeebe
      - postgres-zeebe-simple-monitor
    networks:
      - zeebe_network
    profiles:
      - postgres

  postgres-zeebe-simple-monitor:
    image: postgres:12.2
    restart: always
    environment:
      POSTGRES_PASSWORD: zeebe
    volumes:
      - postgres-zeebe-simple-monitor-data:/var/lib/postgresql/data
    networks:
      - zeebe_network
    profiles:
      - postgres

  postgres-zeeqs:
    image: postgres:12.2
    restart: always
    environment:
      POSTGRES_PASSWORD: zeebe
    volumes:
      - postgres-zeeqs-data:/var/lib/postgresql/data
    networks:
      - zeebe_network
    profiles:
      - postgres

  zeeqs:
    container_name: zeeqs
    image: ghcr.io/camunda-community-hub/zeeqs:2.3.1
    environment:
      - zeebe.client.worker.hazelcast.connection=zeebe:5701
      - spring.datasource.url=jdbc:postgresql://postgres-zeeqs:5432/postgres
      - spring.datasource.username=postgres
      - spring.datasource.password=zeebe
      - spring.datasource.driverClassName=org.postgresql.Driver
      - spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
      - spring.jpa.hibernate.ddl-auto=create
    ports:
      - "9000:9000"
    depends_on:
      - zeebe
      - postgres-zeeqs
    networks:
      - zeebe_network
    profiles:
      - postgres

volumes:
  postgres-zeebe-simple-monitor-data:
    driver: local

  postgres-zeeqs-data:
    driver: local

Does this help you?

[nitram509]

Closing, due to inactivity ... please re-open, if still an issue.