PostgreSQL File Store

[Frank]

Hi all,
I'm trying to configure a JDBC file store for my application using EJBs so that passivation is stored on a PostgreSQL DB.

I've come up with the following batch script:
batch
 /subsystem=infinispan/cache-container=ejb/distributed-cache=dist/store=jdbc:remove
 /subsystem=infinispan/cache-container=ejb/distributed-cache=dist/store=jdbc:add(data-source=PostgrePool, dialect=POSTGRES)
run-batch
 
When I deploy an EJB in the HA configuration, I see there's an error in the table creation:

WildFly logs:

Caused by: org.infinispan.commons.CacheConfigurationException: ISPN000659: Component org.infinispan.persistence.manager.PersistenceManager failed to start
Caused by: java.util.concurrent.CompletionException: org.infinispan.persistence.spi.PersistenceException: org.postgresql.util.PSQLException: ERROR: type \"binary\" does not exist
Position: 70
Caused by: org.infinispan.persistence.spi.PersistenceException: org.postgresql.util.PSQLException: ERROR: type \"binary\" does not exist
Position: 70
Caused by: org.postgresql.util.PSQLException: ERROR: type \"binary\" does not exist

Database logs:

2022-11-15 10:55:37.583 UTC [95] ERROR:  type "binary" does not exist at character 70
2022-11-15 10:55:37.583 UTC [95] STATEMENT:  CREATE TABLE "ispn_entry_ee_ejb_server_basic_jar_default_META" (data BINARY NOT NULL)

My understanding looking at similar threads is that the table creating can be customized in the jdbc store. Anyone who managed to configure a PostgreSQL store cares to share the configuration used?

Thanks

Frank

[Paul Ferraro]

Use this:

/subsystem=infinispan/cache-container=ejb/distributed-cache=dist/store=jdbc/table=string:write-attribute(name=data-column.type, value=BYTEA)

I assume you are using a PostgreSQL database per cluster member?

If not, you will either need to use a shared jdbc-store:

e.g. /subsystem=infinispan/cache-container=ejb/distributed-cache=dist/store=jdbc:add(data-source=PostgrePool, dialect=POSTGRES, shared=true)

... or configure the jdbc-store to use distinct schema or table name prefix per cluster member:

e.g.

/subsystem=infinispan/cache-container=ejb/distributed-cache=dist/store=jdbc/table=string:write-attribute(name=prefix, value=${jboss.node.name})

Paul

[Frank]

Thanks immensely! That worked out!