11 Replies Latest reply on Aug 11, 2006 12:44 AM by J W

    PostgreSQL as DefaultDS Tutorial

    Michael Ossareh Newbie

      Hi,

      I am relatively new to JBoss 4 and have been attempting to remove the HyperSonic DB dependance and use Postgres instead. There is a startling lack of documentation surrounding this in JBoss 4! I have just now sucessfully managed to do this and here is my step by step on doing it.

      I assume that you have installed JBoss and PostgreSQL. For reference's sake I'm using

      OS: Solaris 9
      Postgres: 7.4
      JBoss: 4.0.2RC1

      Step 1 - prep the db
      as your postgres user create a db.

      bash-2.05$ createdb jboss
      CREATE DATABASE
      


      then still as your postgres user create a db user

      bash-2.05$ createuser -P -D -A jboss
      Enter password for new user: password
      Enter it again: password
      CREATE USER
      


      add a pg_hba.conf entry here if needed. In my instance even with jboss and postgres on the same box I needed an entry for the connection coming in on the local IP:

      # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
      host jboss jboss 192.168.0.1 255.255.255.255 trust
      


      Step 2 - prep the instance and its deploy
      now as your jboss user/owner

      bash-2.05$ cd ${JBOSS_HOME}/server
      bash-2.05$ cp -R default postgres
      bash-2.05$ cd local/deploy
      bash-2.05$ rm hsqldb-ds.xml
      bash-2.05$ cp ${JBOSS_HOME}/docs/examples/jca/postgres-ds.xml ./
      


      Change the new local copy of postgres-ds.xml to be like the following, replacing your hostname, username, password as appropriate:

      <datasources>
       <local-tx-datasource>
       <jndi-name>DefaultDS</jndi-name>
       <connection-url>jdbc:postgresql://test-server/jboss</connection-url>
       <driver-class>org.postgresql.Driver</driver-class>
       <user-name>jboss</user-name>
       <password>tester</password>
       <!-- sql to call when connection is created
       <new-connection-sql>some arbitrary sql</new-connection-sql>
       -->
      
       <!-- sql to call on an existing pooled connection when it is obtained from pool
       <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
       -->
      
       <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
       <metadata>
       <type-mapping>PostgreSQL</type-mapping>
       </metadata>
       </local-tx-datasource>
      
      </datasources>
      


      Some changes are needed in the jms directory of the deploy directory

      bash-2.05$ cd jms
      bash-2.05$ rm hsqldb-jdbc2-service.xml
      bash-2.05$ mv hsqldb-jdbc-state-service.xml postgres-jdbc-state-service.xml
      bash-2.05$ cp ../../../../docs/examples/jms/postgres-jdbc3-service.xml ./
      


      Find the code below in the postgres-jdbc3-service.xml file:

      optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=PostgresDS</depends>
      


      and edit it to read:

      optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
      


      Renaming hsqldb-jdbc-state-service.xml to postgres-jdbc-state-service.xml is purely for asthetic reasons. In a weaker moment I may think "arrgh I though I got rid of all this Hypersonic suff!"

      Step 3 - prep the core configurations
      All core configurations for an instance of jboss are below the conf directory. These need to be changed to use the new DS.

      bash-2.05$ cd ${JBOSS_HOME}/server/postgres/conf
      


      The file standardjbosscmp-jdbc.xml should read

      <jbosscmp-jdbc>
      
       <defaults>
       <datasource>java:/DefaultDS</datasource>
       <!-- optional since 4.0 <datasource-mapping>Hypersonic SQL</datasource-mapping> -->
       <datasource-mapping>PostgreSQL</datasource-mapping>
      


      The file standardjaws.xml should read

      <jaws>
       <datasource>java:/DefaultDS</datasource>
       <type-mapping>PostgreSQL</type-mapping>
       <debug>false</debug>
      


      Step 4 - configuration of binaries
      Ensure you have an up to date jdbc driver for your version of postgres. Below this will be refered to as $PGJAR.

      bash-2.05$ cd ../conf
      bash-2.05$ rm rm hsqldb*
      bash-2.05$ cp $PGJAR ./
      


      Step 5 - home straight
      bash-2.05$ cd ${JBOSS_HOME}/bin
      bash-2.05$ ./run.sh -c postgres &
      


      a \dt in the jboss database should show you the following.

      jboss=> \dt
       List of relations
       Schema | Name | Type | Owner
      --------+---------------------+-------+-------
       public | hilosequences | table | jboss
       public | jms_message_log | table | jboss
       public | jms_reference_log | table | jboss
       public | jms_roles | table | jboss
       public | jms_subscriptions | table | jboss
       public | jms_transaction_log | table | jboss
       public | jms_users | table | jboss
       public | timers | table | jboss
      (8 rows)
      


      Thats all folks!

      --
      dayjah

        • 1. Re: PostgreSQL as DefaultDS Tutorial
          Michael Ossareh Newbie

          Correction....

          "dayjah" wrote:

          Step 4 - configuration of binaries
          Ensure you have an up to date jdbc driver for your version of postgres. Below this will be refered to as $PGJAR.

          bash-2.05$ cd ../conf
          bash-2.05$ rm rm hsqldb*
          bash-2.05$ cp $PGJAR ./
          


          Should read

          bash-2.05$ cd ../lib
          bash-2.05$ rm rm hsqldb*
          bash-2.05$ cp $PGJAR ./
          


          --
          dayjah
          Michael Ossareh

          • 2. Re: PostgreSQL as DefaultDS Tutorial
            Michael Ossareh Newbie

            note that after some testing I have found that the above procedure does not work on jboss 4.0.0 because of a bad/non-portable data definition in ${JBOSS_HOME}/server/postgres/deploy/ejb-deployer.xml.

            The timers table - which is handled differently in 4.0.2RC1 - defines two columns with the data type other - which in hypersonic is for java objects. I have tried various data types for postgres and none of them work (bytea, int, text, varchar, etc).

            Does anyone know either

            1) why the jbossall-client.jar shipped with jboss4.0.2RC1 errors when connecting to the version of jboss it is shipped with. A ctx.lookup call produces the following;

            javax.naming.CommunicationException [Root exception is java.rmi.UnmarshalException: error unmarshalling return; nested exception is:
             java.io.InvalidClassException: org.jnp.interfaces.MarshalledValuePair; local class incompatible: stream classdesc serialVersionUID = -3403843515711139134, local class serialVersionUID = -6485622724173581497]
             at org.jnp.interfaces.NamingContext.lookup(NamingContext.java:648)
             at org.jnp.interfaces.NamingContext.lookup(NamingContext.java:508)
             at javax.naming.InitialContext.lookup(InitialContext.java:347)
             at styejb21.day05.client.SignOnClient.main(SignOnClient.java:35)
            Caused by: java.rmi.UnmarshalException: error unmarshalling return; nested exception is:
             java.io.InvalidClassException: org.jnp.interfaces.MarshalledValuePair; local class incompatible: stream classdesc serialVersionUID = -3403843515711139134, local class serialVersionUID = -6485622724173581497
             at sun.rmi.server.UnicastRef.invoke(UnicastRef.java:164)
             at org.jnp.server.NamingServer_Stub.lookup(Unknown Source)
             at org.jnp.interfaces.NamingContext.lookup(NamingContext.java:529)
             ... 3 more
            Caused by: java.io.InvalidClassException: org.jnp.interfaces.MarshalledValuePair; local class incompatible: stream classdesc serialVersionUID = -3403843515711139134, local class serialVersionUID = -6485622724173581497
             at java.io.ObjectStreamClass.initNonProxy(ObjectStreamClass.java:463)
             at java.io.ObjectInputStream.readNonProxyDesc(ObjectInputStream.java:1521)
             at java.io.ObjectInputStream.readClassDesc(ObjectInputStream.java:1435)
             at java.io.ObjectInputStream.readOrdinaryObject(ObjectInputStream.java:1626)
             at java.io.ObjectInputStream.readObject0(ObjectInputStream.java:1274)
             at java.io.ObjectInputStream.readObject(ObjectInputStream.java:324)
             at sun.rmi.server.UnicastRef.unmarshalValue(UnicastRef.java:297)
             at sun.rmi.server.UnicastRef.invoke(UnicastRef.java:146)
             ... 5 more
            


            Which doesn't happen when using this jbossall-client.jar or the 4.0.0 one when connecting to jboss 4.0.0.

            or

            2) how I can make the timers table be created on postgresql without issues in 4.0.0?

            • 3. Re: PostgreSQL as DefaultDS Tutorial
              Egor Shalashnikov Newbie

              Great!, after a day of "google-search" I foung your article is the best explains me how to run PostgreSQL on JBOSS, thanks a lot

              • 4. Re: PostgreSQL as DefaultDS Tutorial
                James Schreuder Newbie

                Thanks very much for the easy to follow step-by-step instructions. I just spent 2 hours google-searching for how to switch to postgresql. Eventually I landed upon your post and 15 minutes later my app is up and going on postgres.

                These instructions worked perfectly on my WindowsXP box too.

                • 5. Re: PostgreSQL as DefaultDS Tutorial
                  michele donato Newbie

                  hi all
                  why don't i have the /local folder under JBOSS_HOME/server dir???
                  i have jbos 4.04GA, at install i chose "all"
                  help...

                  • 6. Re: PostgreSQL as DefaultDS Tutorial
                    jaikiran pai Master

                    By default JBoss comes with 3 server configurations - minimal, default,all. You will find the corresponding directories under %JBOSS_HOME%/server directory. If at all you want to create your own custom configuration(something like a /local folder in %JBOSS_HOME%/server directory) then you can do so as mentioned here:

                    http://wiki.jboss.org/wiki/Wiki.jsp?page=CustomConfiguration

                    http://wiki.jboss.org/wiki/Wiki.jsp?page=ServerConfiguration

                    • 7. Re: PostgreSQL as DefaultDS Tutorial
                      michele donato Newbie

                      Thanks for you interest
                      My jboss installation comes with one server configuration.
                      But, assuming that there's no need for other than "default", how should i interpretate these lines?

                      bash-2.05$ cd local/deploy
                      bash-2.05$ rm hsqldb-ds.xml
                      bash-2.05$ cp ${JBOSS_HOME}/docs/examples/jca/postgres-ds.xml ./


                      have i to remove hsqldb-ds.xml from "default\deploy" folder" and to copy the postgres-ds.xml file here (in default)?

                      or have i to create the "local" configuration from the "default"?

                      excuse me for my ignorance, but i am very sad about this

                      • 8. Re: PostgreSQL as DefaultDS Tutorial
                        jaikiran pai Master

                         

                        have i to remove hsqldb-ds.xml from "default\deploy" folder" and to copy the postgres-ds.xml file here (in default)?


                        Yes, you got it right.

                        or have i to create the "local" configuration from the "default"?


                        Not neccessary





                        • 9. Re: PostgreSQL as DefaultDS Tutorial
                          michele donato Newbie

                          hi again,
                          i am at the point 3, in the standardjbosscmp.xml i found type-mapping for PostgreSQL, PostgreSQL 7.2 and PostgreSQL 8.0
                          assuming that i have installed 8.0, should i change the type-mapping in postgres-ds.xml and in standardjaws.xml in PostgreSQL 8.0 instead of PostgreSQL?
                          Any other changes? Or it isn't needed?
                          TIA,
                          mic

                          • 10. Re: PostgreSQL as DefaultDS Tutorial
                            jaikiran pai Master

                             

                            should i change the type-mapping in postgres-ds.xml and in standardjaws.xml in PostgreSQL 8.0 instead of PostgreSQL?


                            I havent tried changing Hypersonic DB to any other DB, but going by what is mentioned in the first post, you will have to change it to PostgreSQL 8.0 in the standardjaws.xml file and also in your postgres-ds.xml. Have a look at the link below, to see how PostgreSQL ds is setup:

                            http://wiki.jboss.org/wiki/Wiki.jsp?page=SetUpAPostgreSQLDatasource

                            In your ds.xml you will be having:

                            <metadata>
                             <type-mapping>PostgreSQL 8.0</type-mapping>
                             </metadata>



                            • 11. Re: PostgreSQL as DefaultDS Tutorial
                              J W Newbie

                              Hi,

                              I am also using Postgres with Jboss 4.0.4 GA and the newest EJB 3.0 implementation. I have been experiencing some strange behaviour when I shutdown JBoss and then restart it. When I shutdown the app server, my entire database and all its data disappears. When I restart it, i get my database back but not any of my data. If this is normal behaviour for JBoss, I think it is a big cause for concern. Has anyone else experienced this problem and know how to work around it?