Cache with jdbc persistence

seravat commented 3 years ago

Is it possible to create a cache with jdbc persistence? In our case Postgresql or Cockroach DB.

We are running on Openshift 4 with the infinispan operator.

I have been trying and it fails due to missing drivers.

Cache XML config:

<infinispan>
    <cache-container>
        <distributed-cache-configuration name="distributed-cache-template">
            <expiration interval="10000" lifespan="10000" max-idle="10000"/>
            <persistence>
                <string-keyed-jdbc-store xmlns="urn:infinispan:config:store:jdbc:12.1" shared="true" dialect="POSTGRES">
                    <simple-connection connection-url="jdbc:postgresql://postgresql:5432/sampledb"
                                    username="user2PW"
                                    password="grGpsOdaV7tQnfdl"
                                    driver="org.postgresql.Driver"/>
                    <string-keyed-table drop-on-exit="true"
                                        prefix="ISPN_STRING_TABLE">
                        <id-column name="ID_COLUMN" type="VARCHAR(255)" />
                        <data-column name="DATA_COLUMN" type="VARCHAR(255)" />
                        <timestamp-column name="TIMESTAMP_COLUMN" type="BIGINT" />
                        <segment-column name="SEGMENT_COLUMN" type="INT" />
                    </string-keyed-table>
                </string-keyed-jdbc-store>
            </persistence>
            <encoding>
                <key media-type="application/x-java-object; type=java.lang.String"/>
                <value media-type="application/x-java-object; type=java.lang.String"/>
            </encoding>
        </distributed-cache-configuration>
    </cache-container>
</infinispan>

Error on the Infinispan instance:

Caused by: org.infinispan.commons.CacheConfigurationException: Unable to instantiate class org.postgresql.Driver
    at org.infinispan.commons.util.Util.loadClass(Util.java:140)
    at org.infinispan.commons.util.Util.getInstance(Util.java:305)
    at org.infinispan.persistence.jdbc.impl.connectionfactory.SimpleConnectionFactory.loadDriver(SimpleConnectionFactory.java:85)
    at org.infinispan.persistence.jdbc.impl.connectionfactory.SimpleConnectionFactory.start(SimpleConnectionFactory.java:44)
    at org.infinispan.persistence.jdbc.stringbased.JdbcStringBasedStore.start(JdbcStringBasedStore.java:122)
    at org.infinispan.persistence.support.NonBlockingStoreAdapter.lambda$start$0(NonBlockingStoreAdapter.java:107)
    at org.infinispan.util.concurrent.BlockingManagerImpl.runBlockingOperation(BlockingManagerImpl.java:109)
    at org.infinispan.util.concurrent.BlockingManagerImpl.runBlockingOperation(BlockingManagerImpl.java:99)
    at org.infinispan.util.concurrent.BlockingManagerImpl.runBlocking(BlockingManagerImpl.java:65)
    at org.infinispan.persistence.support.NonBlockingStoreAdapter.start(NonBlockingStoreAdapter.java:101)
    ... 150 more
Caused by: java.lang.ClassNotFoundException: org.postgresql.Driver

Can we easily do this without having to create a custom infinispan image?

dmvolod commented 3 years ago

@seravat you can embed any library you want with the following solution https://infinispan.org/docs/infinispan-operator/2.1.x/operator.html#deploying-code

dmvolod commented 3 years ago

Next release will support to download custom libraries/files from any http/ftp sources https://github.com/infinispan/infinispan-operator/pull/1006

crisp2u commented 3 years ago

Hi guys, I'm gonna highjack this, feel free to shame me. I'm trying to setup a cache with jdbc persistance also but I'm facing a different issue: I have no clue how to set environments variables on the infinispan containers and my cache control reads the jdbc connection parms like this ${env.DB_ADDR:localhost}.

I could not find anything in the docs and I tried also reading the oprator code. The only thing I've seen what something related to ADDITIONAL_VARS enviroment var into the operator deployment

infinispan / infinispan-operator

Cache with jdbc persistence #1153