Configuring a xa-datasource(Wildfly) in HA mode with a Postgresql JDBC driver

Question

After many attempts, it seems that the combination xa-datasource <-> postgres driver does not support a failover configuration with non default port(5432). I guess, that the driver does not implements all methods expected by xa.

I'd be glad if somebody can show me that I'm wrong about that...

This example is working, but use the default port:

<xa-datasource jndi-name="java:/Foo" pool-name="Foo" enabled="true" use-ccm="true" statistics-enabled="true">
    <xa-datasource-property name="url">
    jdbc:postgresql://server1,server2/db_name
    </xa-datasource-property>
    <xa-datasource-property name="ApplicationName">
    MyApp
    </xa-datasource-property>
    <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
    <driver>postgresql-jdbc4</driver>
    <url-delimiter>,</url-delimiter>
    <xa-pool>
    <min-pool-size>6</min-pool-size>
    <max-pool-size>40</max-pool-size>
    <prefill>true</prefill>
    <is-same-rm-override>false</is-same-rm-override>
    <interleaving>false</interleaving>
    <pad-xid>false</pad-xid>
    <wrap-xa-resource>false</wrap-xa-resource>
    </xa-pool>
    <security>
    <user-name>foo</user-name>
    <password>blah</password>
    </security>
    <validation>
    <validate-on-match>false</validate-on-match>
    <background-validation>false</background-validation>
    </validation>
    <timeout>
    <blocking-timeout-millis>3000</blocking-timeout-millis>
    <idle-timeout-minutes>60</idle-timeout-minutes>
    </timeout>
    <statement>
    <share-prepared-statements>false</share-prepared-statements>
    </statement>
</xa-datasource>

Answer 1

For Oracle database you can use below configuration.

jdbc:oracle:thin:@(description=(connect_timeout=5)(address_list=(load_balance=on)(failover=on) (address=(protocol=tcp)(host= server1)(port=1521)) (address=(protocol=tcp)(host= server2)(port=1521)) )(connect_data=(service_name=xyzabc)))

Answer 2

This might be late reply but it might help someone who is looking for postgres xa-datasource configuration. First of all you need to install postgre driver in Wildfly modules then do the configuration. You can install it via command line using subsystems or set postgresql(enterprise) driver name as "org.edb.Driver" for enterprise driver. Place edb-jdbc17.jar under WILDFLY_HOME\modules\system\layers\base\org\edb\main directory with module.xml.

Some of the drivers does not have getURL() method implemented in their datasource classes. So, we have to specify datasource configuration differently for them.

Postgres does not have getURL method. So, we will specify properties like this in our stanalone.xml/domain.xml file.

<xa-datasource-property name="ServerName">DatabaseHostName</xa-datasource-property>
<xa-datasource-property name="PortNumber">DatabasePortName</xa-datasource-property>
<xa-datasource-property name="DatabaseName">DatabaseName</xa-datasource-property>
<xa-datasource-class>com.edb.xa.PGXADataSource</xa-datasource-class>
<driver>postgresql</driver>
 <xa-pool>
                            <min-pool-size>5</min-pool-size>
                            <initial-pool-size>5</initial-pool-size>
                            <max-pool-size>30</max-pool-size>
                            <use-strict-min>true</use-strict-min>
                            <is-same-rm-override>false</is-same-rm-override>
                            <no-tx-separate-pools>true</no-tx-separate-pools>
                        </xa-pool>
<security>
     <user-name>database.username</user-name>
     <password>database.password</password>
</security> 
<validation>
        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/>
        <background-validation>true</background-validation>
        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"/>
 </validation>