How to add PostgreSQL datasource to WildFly 9.0?

Question

I've tried tutorial at mastertheboss.com:

1. ./jboss-cli.sh
2. module add --name=org.postgres --resources=/tmp/postgresql-9.3-1101.jdbc41.jar --dependencies=javax.api,javax.transaction.api
3. /subsystem=datasources/jdbc-driver=postgres:add(driver-name="postgres",driver-module-name="org.postgres",driver-class-name=org.postgresql.Driver)
4. data-source add --jndi-name=java:/PostGreDS --name=PostgrePool --connection-url=jdbc:postgresql://localhost/postgres --driver-name=postgres --user-name=postgres --password=postgres

This tutorial works with WildFly 8.2, but it doesn't work with WildFly 9.0. 3rd step fails with error message:

{
"outcome" => "failed",
"failure-description" => "WFLYJCA0041: Failed to load module for driver [org.portgres]",
"rolled-back" => true
}

How to add Postgres datasource to WildFly 9.0?

Answer 1

Just a note: I have tested the CLI commands, as taken from the tutorial mentioned, against WildFly 10 and it works correctly in creating the JDBC Driver and the datasource. Besides it, I can see that the error log reported contains a mispelling of the module name ("org.portgres"):

{
"outcome" => "failed",
"failure-description" => "WFLYJCA0041: Failed to load module for driver [org.portgres]",
"rolled-back" => true
}

Disclaimer: I'm the owner of mastertheboss.com

Answer 2

You don't mention your java/jdbc version. I've just experienced the same issue and it was due to a driver vs Java 1.8 mismatch. With the wildfly 9 upgrade did you also upgrade Java?

The ".jdbc41." driver version is built for Java 1.7. Postgres has a matrix showing the combinations of Java/JDBC and Postgres driver versions that are compatible. Perhaps you need: postgresql-9.4.1209.jar (which is for 1.8/jdbc42)

Then in the CLI (assuming domain mode and profile=full)

module add --name=org.postgresql.Driver --resources=/tmp/postgresql-9.4.1209.jar
connect
/profile=full/subsystem=datasources/jdbc-driver=postgres:add(driver-name="postgres",driver-module-name="org.postgresql.Driver",driver-class-name=org.postgresql.Driver)
exit

Answer 3

Its very simple but could take more time if you will be new with JBOSS EAP/WilFly Use below steps to create a datasource:

1. Go to bin folder of server where jboss-cli(Power script) file present: right click on jboss-cli(power script file)--> Run with power shell (a console will open).

2. Add the PostgreSQL JDBC driver as a core module.

module add --name=com.postgresql --resources=/path/to/postgresql-9.3-1102.jdbc4.jar --dependencies=javax.api,javax.transaction.api

3. Register the PostgreSQL JDBC driver.

/subsystem=datasources/jdbc-driver=postgresql:add(driver-name=postgresql,driver-module-name=com.postgresql,driver-xa-datasource-class-name=org.postgresql.xa.PGXADataSource)

4. Add the PostgreSQL datasource.

data-source add --name=PostgresDS --jndi-name=java:jboss/PostgresDS --driver-name=postgresql --connection-url=jdbc:postgresql://localhost:5432/postgresdb --user-name=admin --password=admin --validate-on-match=true --background-validation=false --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter

be careful with path path/to this is path where your downloded Postgresql-jdbc.jar is present.

Answer 4

I am running wildfly 10 in a docker:

#ADD DATASOURCES
RUN mkdir -p $JBOSS_HOME/modules/org/postgres/main
COPY files/postgresql-9.4.1208.jre7.jar $JBOSS_HOME/modules/org/postgres/main/
COPY files/module.xml $JBOSS_HOME/modules/org/postgres/main/
COPY files/standalone.xml $JBOSS_HOME/standalone/configuration

Where module.xml is

<module xmlns="urn:jboss:module:1.1" name="org.postgres">
    <resources>
        <resource-root path="postgresql-9.4.1208.jre7.jar"/>
    </resources>
    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
        <module name="javax.servlet.api" optional="true"/>
    </dependencies>
</module>

And standalone contains driver:

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

then datasource can be:

<datasource jndi-name="java:jboss/datasources/PostgresDS" pool-name="PostgresDS" enabled="true" use-java-context="true">
    <connection-url>jdbc:postgresql://ndis-db:5432/postgres</connection-url>
        <driver>postgresql</driver>
        ...

Note that my ndis-db is a postgres docker. In your case can be localhost.

How I ended up with the error mentioned by you: 1. file name spelled wrongly 2. /modules/org ...etc contain a typo 3. module.xml misspelled as modules.xml 4. ...

Answer 5

I've encountered the same error and behavior of WildFly 9. I'm a complete newbie to WF, but after some research I've found that the trouble is in the module naming. If I'm getting it well, the actual package names in the module are used to resolve the path to module.xml.
I've changed the steps to those below and it worked:

module add --name=org.postgresql --slot=main --resources=/usr/local/lib/postgresql-9.4-1201.jdbc4.jar --dependencies=javax.api,javax.transaction.api

/subsystem=datasources/jdbc-driver=postgres:add(driver-name="postgres",driver-module-name="org.postgresql",driver-class-name=org.postgresql.Driver)

Answer 6

Put your Postgres JDBC driver into deployment folder (just deploy). Now use CLI console and enter this command:

data-source add --name=PostgresqlDS --jndi-name=java:jboss/datasources/PostgresqlDS --driver-name=postgresql-9.4-1201.jdbc41.jar --connection-url=jdbc:postgresql://localhost:5432/test --user-name=USER --password=PASSWORD

Check if your driver is jdbc4.

I don't know why but adding datasources by web console doesn't work. By CLI works.

The right solution for extending JDBC drivers is add driver as module to server. In WildFly 9 you can do it using cli console. You can't do this by copy JDBC jar file (with xml) to "module" folder like in WildFly 8.

Execute commands:

module add --name=org.postgres --resources=postgresql-9.4-1201.jdbc41.jar --dependencies=javax.api,javax.transaction.api    

/subsystem=datasources/jdbc-driver=postgres:add(driver-name="postgres",driver-module-name="org.postgres",driver-class-name=org.postgresql.Driver)

To list instaled drivers enter:

/subsystem=datasources:installed-drivers-list

With driver creating datasources will be easy.

Please use 9.0 Final version. In CR are bugs.

Regards, Pawel M