PostgreSQL Datasource and XADatasource on WildFly 8.2.0

These are my notes for installing PostgreSql datasources (both regular and XA) into WildFly 8.2.0.

*See a very short note about XA transactions below

My notes below presume you followed my directions for installation found here or have already successfully installed WildFly on your system.

These instructions also presume you have successfully installed and setup a PostgreSQL 9.4 server and have a valid username, password and database setup in PostgreSQL. My instructions are here if you need help getting started with PostgreSQL.

Two important things to know before you begin is the base or home directory for WildFly (formally called JBoss) and that you know the user/group under which the WildFly server is running.

On my system (Ubuntu Linux) I’ve installed WildFly (JBOSS_HOME) at

/opt/wildfly

On my system the server runs under group/user wildfly.wildfly (Yes, I know, zero points for originality)

So, here we go…

Get the latest and greatest PostgreSQL JDBC driver.
https://jdbc.postgresql.org/download.html
BTW, thank you to the PostgreSQL folks for a VERY FINE database and jdbc driver!
As of this writing the current version was postgresql-9.4-1201.jdbc41.jar

 

Add the JDBC driver as a module to WildFly
Elevate to root
sudo -s
cd /opt/wildfly/modules
mkdir org
cd org
mkdir postgresql
cd postgresql
mkdir main
cd main
pwd

 

You should now be in
/opt/wildfly/modules/org/postgresql/main

Copy the JDBC driver ( postgresql-9.4-1201.jdbc41.jar ) that you downloaded above into this directory.

 

Create a new file in this directory named: module.xml
Paste this content into this new file

<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.3" name="org.postgresql">
    <resources>
        <resource-root path="postgresql-9.4-1201.jdbc41.jar"/>
        <!-- Make sure this matches the name of the JAR you are installing -->
    </resources>
    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
        <module name="javax.servlet.api" optional="true"/>
    </dependencies>
</module>

Notice that the “path” value exactly matches the name of the JDBC driver jar file.
postgresql-9.4-1201.jdbc41.jar

Save the file (of course.)

 

Make sure the new postgresql-9.4-1201.jdbc41.jar, module.xml files AND the directory structure we just added/created have the same user and group ownership as the WildFly server

chown -R wildfly.wildfly /opt/wildfly/modules/org

 

Change directory to where your WildFly configuration is located.

cd /opt/wildfly/standalone/configuration

 

Stop WildFly
service wildfly stop (You should get an OK)

 

BACKUP YOUR CONFIGURATION FILE (Always backup!!!)

cp standalone-full.xml standalone-full.xml.beforeAddingPostgresql
(I’m assuming you are using standalone-full.xml instead of standalone.xml. Edit the one that is appropriate for your needs just backup!)

 

Open standalone-full.xml and find the section of the file that begins

<datasources>
   ...
   <drivers>

Just inside/after this XML start tag add this XML snippet which will make WildFly fully aware of the driver.

<driver name="postgresql" module="org.postgresql">
    <datasource-class>org.postgresql.Driver</datasource-class>
    <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
</driver>

Save and close the file. Now ANY datasource we add later can then use the driver. At this point it wouldn’t be a bad idea to try and start WildFly just to make sure we didn’t mangle the configuration file.

service wildfly start (You should get an OK) — If not restore the backup file and try again.
service wildfly stop (You should get an OK)

 

 

Add a regular datasource
Open standalone-full.xml and find the section of the file that begins

<datasources>

Just inside/after this XML start tag add this XML snippet (edit as needed of course) to make WildFly configured for your datasource.

<datasource jndi-name="java:jboss/datasources/PgDsAui" pool-name="PgDsAui" enabled="true" use-java-context="true">
    <connection-url>jdbc:postgresql://localhost:5432/aui?ApplicationName=AUI</connection-url>
    <driver>postgresql</driver>
    <pool>
        <min-pool-size>5</min-pool-size>
        <initial-pool-size>5</initial-pool-size>
        <max-pool-size>100</max-pool-size>
        <prefill>true</prefill>
    </pool>
    <validation>
        <valid-connection-checker
                class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"></valid-connection-checker>
        <exception-sorter
                class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"></exception-sorter>
    </validation>
    <security>
        <user-name>wildfly</user-name>
        <password>wildfly</password>
    </security>
</datasource>

 

 

Add a XA datasource
Open standalone-full.xml and find the section of the file that begins

<datasources>

Just inside/after this XML start tag add this XML snippet (edit as needed of course) to make WildFly configured for your XA datasource.

<xa-datasource jndi-name="java:jboss/datasources/PgDsXaAui" pool-name="PgDsXaAui" enabled="true"
               use-java-context="true">
    <driver>postgresql</driver>

    <!-- <xa-datasource-property name="URL">jdbc:postgresql://localhost:5432/aui?ApplicationName=AUI</xa-datasource-property> -->
    <xa-datasource-property name="ServerName">localhost</xa-datasource-property>
    <xa-datasource-property name="PortNumber">5432</xa-datasource-property>
    <xa-datasource-property name="DatabaseName">aui</xa-datasource-property>

    <xa-pool>
        <min-pool-size>5</min-pool-size>
        <initial-pool-size>5</initial-pool-size>
        <max-pool-size>100</max-pool-size>
        <prefill>true</prefill>
    </xa-pool>
    <validation>
        <valid-connection-checker
                class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker">
        </valid-connection-checker>
        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter">
        </exception-sorter>
    </validation>
    <security>
        <user-name>wildfly</user-name>
        <password>wildfly</password>
    </security>
</xa-datasource>

 

 

Note: You can read all about the differences between a regular datasource (which is fine for most applications) and an XA datasource in great detail in the JDBC specification here. The short of it is that when you need to ensure a completed transaction across two or more datasources you need XA capabilities. You’ll see this referred to as a two phase commit. In practice I’ve used XA drivers (datasources) in my Java EE projects to pull data from one database product (e.g. MS-SQL) and insert into another database product (e.g PostgreSQL). XA helps you ensure that all went well on BOTH sides of the action.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s