I'm new to JBoss and I haven't been able to find a complete step by step guide to configure it to work with PostgreSQL. I found a lot of scattered information in the forums and in the getting started manual but it took me too long to set it up, so I thought I'd post this to help others struggling with the same problem.
My purpose was to get JBoss to connect to PostgreSQL on a remote server as its datasource for the CMP2 template example but keep using the Local HyperSonic DB as the Default Data Source. After I got this working I decided to replace HyperSonic completely so I changed the Default Datasource to PostgreSQL. Here's what I did:
1. Install the JBoss templates according to the instructions here:
(You can skip the "template EJB and client" part if you're only interested in the cmp2 example).
2. Modify the file src/resources/ejb-jar/META-INF/jbosscmp-jdbc.xml in the cmp2 example:
a) Change the defaults section (lines 8-15) to use PostgresQL instead of Hypersonic SQL, now it looks like this:
Note that I changed 'remove-table' to false... by default the cmp2 example is set to drop all of its tables when it's undeployed (or when jboss is shutdown) so none of the data is preserved. Changing 'remove-table' to false makes the data stay in the database when the server goes down or if the beans are undeployed. If you're doing a lot of changes to the database structure you might want to leave it as true until things are more stable.
b) Change the 'column-name' from "desc" to "descr" for the description field in the organization table (line 29) - this is because DESC is a reserved PostgreSQL keyword.
3. Copy the file postgres-service.xml from $JBOSS_DIST/docs/examples/jca/
into the $JBOSS_DIST/server/default/deploy/ directory and modify it:
a) Uncomment the following line (line 34):
b) Modify the ConnectionURL config property (line 44) to match the database:
<config-property name="ConnectionURL" type="java.lang.String">jdbc:postgresql://[MY_HOST]:/[MY_DATABASE_NAME]</config-property>
4. Copy the login module configuration section from postgres-service.xml (lines 17-26) into the file $JBOSS_DIST/server/default/conf/login-config.xml (I put it in the end of the file right before the closing ). Modify the username and password in this section so JBoss can login to the database:
<application-policy name = "PostgresDbRealm">
<login-module code = "org.jboss.resource.security.ConfiguredIdentityLoginModule" flag = "required">
<module-option name = "principal">[MY_USERNAME]</module-option>
<module-option name = "userName">[MY_USERNAME]</module-option>
<module-option name = "password">[MY_PASSWORD]</module-option>
<module-option name = "managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=PostgresDS</module-option>
5. Download the PostgreSQL JDBC Driver (from http://jdbc.postgresql.org/download.html) and copy it into the $JBOSS_DIST/server/default/lib/ directory.
6. Restart JBoss and verify that everything works by running 'deploy', 'setup' and 'test-gui' on the cmp2 template.
If, after this works, you would like to configure JBoss to use PostgreSQL as its default database, you can make the following changes:
1. Modify standardjbosscmp-jdbc.xml in $JBOSS_DIST/server/default/conf/:
change datasource-mapping (line 16) from "Hypersonic SQL" to "PostgreSQL 7.2"
2. Modify postgres-service.xml in $JBOSS_DIST/server/default/deploy again:
replace PostgresDS with DefaultDS in lines 11, 23, 39, 41, 63
3. Modify login-config.xml:
In the application policy you inserted earlier, replace PostgresDS with DefaultDS in the managedConnectionFactoryName module-option
4. In the cmp2 example, modify src/resources/ejb-jar/META-INF/jbosscmp-jdbc.xml again:
change line 9 from PostgresDS to DefaultDS
5. If you have any other beans you are deploying that use the DefaultDS, you have to change the datasource-mapping in the jbosscmp-jdbc.xml file from "Hypersonic SQL" to "PostgreSQL 7.2"
6. At this point you should remove hsqldb-service.xml from the $JBOSS_DIST/server/default/deploy directory and if you want you can remove hsqldb.jar and hsqldb-plugin.jar from the $JBOSS_DIST/server/default/lib directory
7. Redeploy the cmp2 jar and any other jars that depend on DefaultDS, restart JBoss, and verify that everything still works.
Let me know if there are any corrections or comments.