How to use PostgreSQL with WildFly and JBoss
updated: 28.12.2021, tested with WildFly 26.0.0 and PostgreSQL 14. This procedure describes the steps required for WildFly but it should work with JBoss EAP too.
Download the PostgreSQL driver
Download a compatible drive with your instance (e.g. PostgreSQL JDBC 4.2 Driver, 42.3.1):
https://jdbc.postgresql.org/download.html
Add PostgreSQL driver to WildFly
In [WILDFLY_HOME]/modules
create the directory:
/org/postgresql/main
and copy the jdbc file.
Some developers suggest creating the structure in modules/system/layers/base adding the custom module to the WildFly/JBoss exiting layered modules. This approach is not recommended by RedHat, here you can read an explanation.
Create the module in WildFly
In /org/postgresql/main
create the module.xml file
<?xml version='1.0' encoding='UTF-8'?>
<module xmlns="urn:jboss:module:1.1" name="org.postgresql">
<resources>
<!--the name of your driver -->
<resource-root path="postgresql-42.3.1.jar"/>
</resources>
<dependencies>
<module name="javax.api"/>
<module name="javax.transaction.api"/>
</dependencies>
</module>
- change the filename according to your jdbc driver
At the end you should have a similar structure:
In alternative you can use the Wildfly CLI:
[WILDFLY_HOME]/bin/jboss-cli.sh
module add --name=org.postgresql --resources=[JDBC_FILE_PATH]postgresql-42.3.1.jar --dependencies=javax.api,javax.transaction.api
Add the datasource
In our case we use the standalone instance of WildFly.
- Open
[WILDFLY_HOME]/standalone/configuration/standalone.xml - Locate the existing datasources, probably you have an instance of H2 already configured:
<subsystem xmlns="urn:jboss:domain:datasources:6.0">
<datasources>
- Add the postgresql datasource, update according to your configuration:
<datasource jndi-name="java:jboss/datasources/PostgresDS" pool-name="PostgresDS">
<connection-url>jdbc:postgresql://localhost:5432/postgres</connection-url>
<driver>postgresql</driver>
<security>
<user-name>postgres</user-name>
<password>secret</password>
</security>
</datasource>
This datasource references a driver names ‘postgresql’ that we have to add, in the section <datasources><drivers> :
<driver name="postgresql" module="org.postgresql">
<!-- 1. choose your connection driver -->
<driver-class>org.postgresql.Driver</driver-class>
</driver>
Restart the server and check that the connection worked correctly.
You shuld see in the log a similar message that confirm the connection to your database.
22:28:20,210 INFO [org.jboss.as.connector.subsystems.datasources] (ServerService Thread Pool -- 44) WFLYJCA0005: Deploying non-JDBC-compliant driver class org.postgresql.Driver (version 42.3)
22:28:20,221 INFO [org.jboss.as.connector.deployers.jdbc] (MSC service thread 1-3) WFLYJCA0018: Started Driver service with driver-name = postgresql
Deploying non-JDBC-compliant driver?! Is it an error?
No, it’s not an error, the official JDBC driver is not compliant JDBC. You can find the false return value in the GitHub code here. In the code is clarified that: For PostgreSQL, this is not yet possible, as we are not SQL92 compliant (yet).
Alternative
As an alternative you can use the WildFly UI:
- add your user to your instance
[WILDFLY_HOME]/bin/add-user.sh
Add the Datasource in: https://127.0.0.1:9990
Errors
WFLYJCA0047: Connection is not valid
The inclusion of the datasource-class in your configuration e.g.
<datasource-class>org.postgresql.ds.PGSimpleDataSource</datasource-class>
could throw the following error: WFLYJCA0047: Connection is not valid
here some references about this problem:
https://issues.jboss.org/browse/WFLY-6157
Requirements
Wildfly
For the last version of this post I used Wildfly 26.0.0.Final. I tested the configuration using a MacBook Pro.
PostgreSQL
I installed a Docker image of PG using docker pull postgres the current version was the 14.
I started the docker instance with:
docker run --name postgres-14 -e POSTGRES_PASSWORD=secret -e POSTGRES_USER=postgres -p 5432:5432 -d postgres
Video tutorial?
If you want a video tutorial you can contact me.