Using PostgreSQL in JBoss AS as default database

In refer to the document on jboss.org, ChangeDefaultDataSource. Here is the simplified version.

By default, JBoss AS is using Hypersonic as the database. Although Hypersonic is good for development purpose but in the production environment, it is way more stable to use the enterprise level database, such as PostgreSQL. In this posting, we will discussing switching to PostgreSQL as the default database for JBoss.

To get started, first download and installed both JBoss AS and PostgreSQL. In our example, we are using JBoss AS 4.2.3 and PostgreSQL 8.2.

On PostgreSQL

1. In PostgreSQL, login as postgres and create new user "jboss" with password "jbossrul3"


2. Create new database "jbossdb" and assign owner to "jboss"

On JBoss AS

1. Delete hsqldb-ds.xml on <JBOSS_ROOT>\server\default
   
   
2. Copy postgres-ds.xml into <JBOSS_ROOT>\server\default\deploy\ from <JBOSS_ROOT>\docs\examples\jca
   
   
3. Edit postgres-ds.xml to put in the appropriate value
   <datasources>
   <local-tx-datasource>
   <jndi-name>DefaultDS</jndi-name>
   <connection-url>jdbc:postgresql://localhost:5432/jbossdb</connection-url>
   <driver-class>org.postgresql.Driver</driver-class>
   <user-name>jboss</user-name>
   <password>jbossrul3</password>
   <new-connection-sql>select 1</new-connection-sql>
   <check-valid-connection-sql>select 1</check-valid-connection-sql>
   <metadata>
   <type-mapping>PostgreSQL</type-mapping>
   </metadata>
   </local-tx-datasource>
   </datasources>
   
   
4. Add the following entry into <JBOSS_ROOT>\server\default\deploy\jms\jbossmq-destinations-service.xml, if you are planning use custom queue. You can named your queue to the appropriate name, I am naming it as mpgateQueue
   <mbean code="org.jboss.mq.server.jmx.Queue" name="jboss.mq.destination:service=Queue,name=mpgateQueue">
   <depends optional-attribute-name="DestinationManager"> jboss.mq:service=DestinationManager
   </depends>
   <depends optional-attribute-name="SecurityManager">
   jboss.mq:service=SecurityManager
   </depends>
   <attribute name="MessageCounterHistoryDayLimit">-1</attribute>
   <attribute name="SecurityConf">
   <security>
   <role name="guest" read="true" write="true"/>
   <role name="publisher" read="true" write="true" create="false"/>
   <role name="noacc" read="false" write="false" create="false"/>
   </security>
   </attribute>
   </mbean>
   
   
5. Delete <JBOSS_ROOT>\server\default\deploy\jms\hsqldb-jdbc2-service.xml
   
   
6. Copy <JBOSS_ROOT>\docs\examples\jms\postgres-jdbc2-service.xml into <JBOSS_ROOT>\server\default\deploy\jms\
   
   
7. Now, startup the JBoss AS, it should automatic create the required schema in PostgreSQL under jboss user and everything should be good to go.
   

Good Luck.