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?

Solution 1

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)

Solution 2

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

Solution 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.

Solution 4

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

View more solutions

Comments

[aro_tech]

This does not work for me. I still get the WFLYJCA0041 error that czerny got. Also, did you change anything besides the name of the module (which you changed from org.postgres to org.postgresql)?

[aro_tech]

It works for me now. My JBOSS_HOME environment variable was not up to date.