[jboss-user] [Installation, Configuration & DEPLOYMENT] - Re: Howto move jbm and quartz tables to postgresql schema?

[nikolajg]

Yes, I was a bit mystified about your answer :-). Anyway, I was jumping around when I saw the jbm_ and qrtz_ tables end up in the schema. But sorry to say, its not 100% bulletproof (yet). When making several jboss restarts a jbm_dual table suddenly goes into Public. Also Ive seen all the qrtz_ tables end up there. What I did will follow below.

To be consistent with your setup Im using the jbia files as you recommend:
  postgres-ds.xml
  ejb3-timer-service.xml
  postgresql-persistence-service.xml

Ive tried several things, but ended up with leaving the ejb3-timer-service.xml and the postgresql-persistence-service.xml files as is - only adding the search path to the postgres-ds.xml file. Trying to prefix the jbm_ and qrtz_ code will make strange things happen when starting jboss. Sometimes it works - sometimes not, as if the jboss team does not wait on startup events. Ive also tried to fiddle with "org.quartz.jobStore.tablePrefix=QRTZ_" in the ejb3-timer-service.xml file, but it also give some strange errors.

As said, on your recommendation Ive added the below to postgres-ds.xml:

  | <check-valid-connection-sql>SET search_path TO xxx,public;</check-valid-connection-sql>
  | 

Also, here is my persistence.xml:

  | <?xml version="1.0" encoding="UTF-8"?>
  | <persistence
  |     xmlns="http://java.sun.com/xml/ns/persistence"
  |     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  |     xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xm
  | l/ns/persistence/persistence_1_0.xsd"
  |     version="1.0">
  |     <persistence-unit name="smsgatewayUnit">
  |         <provider>org.hibernate.ejb.HibernatePersistence</provider>
  |         <jta-data-source>java:DefaultDS</jta-data-source>
  |         <properties>
  |             <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
  |             <property name="hibernate.connection.driver_class" value="org.postgresql.Driver" />     
  |             <property name="hibernate.connection.url" value="jdbc:postgresql://localhost:5432/smsgateway" />
  |             <property name="hibernate.connection.username" value="smsgateway_user" />
  |             <property name="hibernate.connection.password" value="test1234" />
  |             <property name="hibernate.hbm2ddl.auto" value="create"/> 
  |             <property name="hibernate.show_sql" value="true" />
  |             <property name="hibernate.default_schema" value="smsgateway"/>
  |         </properties>
  |     </persistence-unit>
  | </persistence>
  | 

Just to be sure - the postgres-ds.xml file:
<?xml version="1.0" encoding="UTF-8"?>
  | <datasources>
  |   <local-tx-datasource>
  |     <jndi-name>DefaultDS</jndi-name>
  |     <connection-url>jdbc:postgresql://127.0.0.1:5432/smsgateway</connection-url>
  |     <driver-class>org.postgresql.Driver</driver-class>
  |     <check-valid-connection-sql>SET search_path TO smsgateway,public;</check-valid-connection-sql>
  |     <user-name>smsgateway_user</user-name>
  |     <password>test1234</password>
  |     <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
  |     <min-pool-size>5</min-pool-size>
  |     <max-pool-size>20</max-pool-size>
  |     <idle-timeout-minutes>15</idle-timeout-minutes>
  |     <metadata>
  |       <type-mapping>PostgreSQL 8.0</type-mapping>
  |     </metadata>
  |   </local-tx-datasource>
  | </datasources>
  | 

Again, thanks for the help.

/nikolaj

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4206620#4206620

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4206620