How to use PostgreSQL with WildFly and JBoss

updated: tested with WildFly 30.0.1 and PostgreSQL 15. This procedure describes the steps required for WildFly, but it should work with JBoss EAP too.

The video refers to an old version of Wildfly, the changes to apply using the latest version are noted in the post.

Download the PostgreSQL driver

Download a compatible drive with your instance (e.g. PostgreSQL JDBC 4.2 Driver, 42.7.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.9" name="org.postgresql"> 
 
    <resources> 
    <!--the name of your driver --> 
        <resource-root path="postgresql-42.7.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.7.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:7.1"> 
    <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" password="secret"/> 
    <!-- this is the old format, in case you have a precedent version of WildFly --> 
    <!-- 
    <security> 
    <user-name>postgres</user-name> 
    <password>secret</password> 
   </security> 
   --> 
</datasource> 

This datasource references a driver named ‘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 should see in the log a similar message that confirms the connection to your database.

10:12:15,424 INFO  [org.jboss.as.connector.subsystems.datasources] (ServerService Thread Pool -- 44) WFLYJCA0005: Deploying non-JDBC-compliant driver class org.postgresql.Driver (version 42.7) 
10:12:15,426 INFO  [org.jboss.as.connector.deployers.jdbc] (MSC service thread 1-1) 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 are some references for this problem:

https://issues.jboss.org/browse/WFLY-6157

https://superuser.com/questions/1371142/wildfly-14-connect-to-remote-postgresql-wflyjca0047-connection-is-not-valid

Test with a dockerized PostgreSQL

Install a Docker 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-15 -e POSTGRES_PASSWORD=secret -e POSTGRES_USER=postgres -p 5432:5432 -d postgres 

You verify if docker correctly started your instance with:

➜  marcodev git:(master) ✗ docker ps 
CONTAINER ID   IMAGE      COMMAND                  CREATED          STATUS          PORTS                    NAMES 
3e8973e233a7   postgres   "docker-entrypoint.s…"   26 seconds ago   Up 24 seconds   0.0.0.0:5432->5432/tcp   postgres-15