4 Replies Latest reply on May 30, 2007 12:21 PM by peterj

    Setting DefaultDS to use Postgresql in JBoss AS 5.0.0 Beta2

    fatbatman May 28, 2007 6:07 AM


      How do I set the DefaultDS to use postgresql in Jboss AS 5.0.0 Beta 2?

      regards

      James

      • 6644 Views
      • Tags:
        • 1. Re: Setting DefaultDS to use Postgresql in JBoss AS 5.0.0 Be
          peterj
          peterj May 29, 2007 11:02 AM (in response to fatbatman)

          Do this:

          1) Copy docs/examples/jca/postgres-ds.xml to server/xxx/deploy

          2) Edit the postgres-ds.xml to contain information for your database.

          3) Add a < metadata > entry to postgres-ds.xml (you need this to get some of the services to work correctly):

          <datasources>
 <local-tx-datasource>
 . . .
 <metadata>
 <type-mapping>PostgreSQL 8.0</type-mapping>
 </metadata>
 </local-tx-datasource>
</datasources>


          4) Delete the server/xxx/deploy/hsqldb-ds.xml file

          5) Start the server. Any required tables are created automatically.

            • Actions
          • 2. Re: Setting DefaultDS to use Postgresql in JBoss AS 5.0.0 Be
            peterj
            peterj May 29, 2007 12:53 PM (in response to fatbatman)

            Looks like I made a few assumptions in my post. Here are the missing steps:

            1a) Copy the PostgreSQL JDBC driver JAR file to the server/xxx/lib directory.

            1b) Add a user with a password. and create a database in PostgreSQL. (Use this info for step 2)

            In step 2, also change the JNDI name in the postgres-ds.xml file to DefaultDS.

            By the way, you can rename postgres-ds.xml to anything you like, as long as it ends with -ds.xml. I usually use the JNDI name, that is, defaultds-ds.xml.

              • Actions
            • 3. Re: Setting DefaultDS to use Postgresql in JBoss AS 5.0.0 Be
              fatbatman May 29, 2007 4:50 PM (in response to fatbatman)

              Thanks for your response, sorry I should have been more specific in my initial question.
              I'd done what you suggested above but I'm getting problems. These seem to stem from the fact that "qrtz_locks" table does not exist.

              ............
21:46:03,515 INFO [RARDeployment] Required license terms exist, view vfsfile:/D:/workspace/jboss-5.0.0.Beta2/server/default/deploy/jboss-local-jdbc.r
ar/META-INF/ra.xml
21:46:03,750 INFO [WrapperDataSourceService] Bound ConnectionManager 'jboss.jca:service=DataSourceBinding,name=DefaultDS' to JNDI name 'java:DefaultD
S'
21:46:06,218 WARN [QuartzTimerServiceFactory] sql failed: CREATE TABLE qrtz_job_details(JOB_NAME VARCHAR(80) NOT NULL, JOB_GROUP VARCHAR(80) NOT NULL
, DESCRIPTION VARCHAR(120) NULL, JOB_CLASS_NAME VARCHAR(128) NOT NULL, IS_DURABLE VARCHAR(1) NOT NULL, IS_VOLATILE VARCHAR(1) NOT NULL, IS_STATEFUL VA
RCHAR(1) NOT NULL, REQUESTS_RECOVERY VARCHAR(1) NOT NULL, JOB_DATA BINARY NULL, PRIMARY KEY (JOB_NAME,JOB_GROUP))
21:46:06,656 INFO [SimpleThreadPool] Job execution threads will use class loader of thread: main
21:46:06,750 INFO [QuartzScheduler] Quartz Scheduler v.1.5.2 created.
21:46:06,750 INFO [JobStoreCMT] Using db table-based data access locking (synchronization).
21:46:06,781 ERROR [AbstractKernelController] Error installing to Start: name=jboss.ejb:service=EJB3TimerService state=Create mode=Manual requiredStat
e=Installed
org.quartz.SchedulerConfigException: Failure occured during job recovery. [See nested exception: org.quartz.impl.jdbcjobstore.LockException: Failure o
btaining db row lock: ERROR: relation "qrtz_locks" does not exist [See nested exception: org.postgresql.util.PSQLException: ERROR: relation "qrtz_lock
s" does not exist]]
 at org.quartz.impl.jdbcjobstore.JobStoreSupport.initialize(JobStoreSupport.java:493)
 at org.quartz.impl.jdbcjobstore.JobStoreCMT.initialize(JobStoreCMT.java:144)
 at org.quartz.impl.StdSchedulerFactory.instantiate(StdSchedulerFactory.java:1010)
 at org.quartz.impl.StdSchedulerFactory.getScheduler(StdSchedulerFactory.java:1152)
 at org.jboss.ejb3.timerservice.quartz.QuartzTimerServiceFactory.start(QuartzTimerServiceFactory.java:257)
 at org.jboss.ejb3.timerservice.quartz.jmx.EJB3TimerService.startService(EJB3TimerService.java:105)
 at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:289)
 at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMBeanSupport.java:245)
 at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
...........


                • Actions
              • 4. Re: Setting DefaultDS to use Postgresql in JBoss AS 5.0.0 Be
                peterj
                peterj May 30, 2007 12:21 PM (in response to fatbatman)

                Yet some more steps. These replace the Hypersonic-specific messaging configuration files with their PostgreSQL equivalents:

                4a) Download the binary files for JBoss Messaging. Make sure you get the correct version (1.2.0.sp1).

                4b) Delete the file server/xxx/deploy/messaging/hsqldb-persistence-service.xml

                4c) Copy the file examples/config/mysql-persistence-service.xml from the JBoss Messaging binary download to the server/xxx/deply/messaging directory

                However, this doesn't fix the problem with Quartz. Looking at http://www-128.ibm.com/developerworks/java/library/j-quartz/, it would appear that you have the manually create the database tables before Quartz can use a database as a job store. But what bugs me is that in the quartz.properties file, the org.quartz.jobStore.class property is set to org.quartz.simpl.RAMJobStore, which means that it should not be using the database.

                  • Actions