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>

Try change connction-url to `jdbc:postgresql://server1:5432/db_name, jdbc:postgresql://server2:5432/db_name` — Federico Sierra, Mar 15 '16 at 17:30
@federico-sierra Thanks, but this doesn't work either. jdbc:postgresql://server1:5555/db_name,jdbc:postgresql://server2:5555/db_name => org.postgresql.util.PSQLException: ERROR: No such database: db_name,jdbc:postgresql://server2:5555/db_name — Marc, Mar 16 '16 at 08:17
Try a different `url-delimiter` for example `|` and check not exists space between urls — Federico Sierra, Mar 16 '16 at 13:59
@federico Hi, I've tried many more combinations, nothing helps and I have not enough insight in xa to clearly explain what the issue is. (imcompatibility or bug?). Thanks anyway, Marc — Marc, Mar 17 '16 at 11:16
I'm not sure if the problem is to configure the xa-datasource port, or if it won't work when configured. Anyway; here is how I configure the port: `/subsystem=datasources/xa-data-source=MyXA_DS/xa-datasource-properties=PortNumber:add(value=9999)`. Or you can add it as a property with name `PortNumber` in the gui-console. If this will work in HA-mode I do not know, though ... — jon martin solaas, Mar 20 '16 at 18:15

score 1 · Answer 1 · edited Dec 06 '20 at 02:53

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)))

edited Dec 06 '20 at 02:53

answered Dec 04 '20 at 13:40

Pradeep Kumawat

11
1

Atul · Answer 2 · 2017-03-06T04:17:59.063

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>

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

2 Answers2