Configuring a datasource with PostgreSQL and JBoss/WildFly

This tech tip shows how you can install and configure PostgreSQL with JBoss / WildFly going into the details of PostGreSQL installation, configuration and tuning.  We will also learn how to configure hibernate postgresql dialect in a JPA application.

Installing PostgreSQL

We will assume that you are running on a Linux machine. Firstly, install PostgreSQL:

$ apt-get install postgresql postgresql-client

Next, start PostgreSQL by issuing:

$ service postgresql start

If you want PostgreSQL to startup automatically on each reboot:

$ chkconfig postgresql on

By installing PostgreSQL you should have an user named postgres on your machine. You need to set up a password for connecting with this user. From the superuser issue:

# passwd postgres

Now log into postgres user and set the password for the database user postgres using psql which is the sql terminal interface towards PostgreSQL

$ su - postgres

psql -c "ALTER USER postgres WITH PASSWORD 'postgres'" -d template1

exit

Last thing we need to do is changing is the allowed socket connection interfaces:

$ vi /etc/postgresql/9.1/main/pg_hba.conf

Make sure the “local section” has md5 rather than ident otherwise a connection refused error will be issued when the application server tries to acquire a connection:

# "local" is for Unix domain socket connections only
local all all md5

# IPv4 local connections:
host all postgres samehost md5
host all nuxeo samehost md5
host cspace cspace samehost md5

Finally, make sure entries under “IPv6 local connections” are disabled:

# IPv6 local connections:
#host all all ::1/128 md5

Starting PostgreSQL as Container image

A faster solution, if you are using PostgreSQL for development/testing purposes, is to start it in a Container as follows:

docker run --rm=true --name health_test -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=mypassword123 -e POSTGRES_DB=postgresdb -p 5432:5432 postgres

We cover in greater detail how to run PostgreSQL in a Container in the following tutorial: How to manage a PostgreSQL Database with Docker

Configuring PostgreSQL Datasource

Good, now the database is configured correctly. Now let’s download the PostgreSQL JDBC Driver: http://jdbc.postgresql.org/

As shortcut, you can simply download it from Maven repository:

wget https://repo1.maven.org/maven2/org/postgresql/postgresql/42.2.5/postgresql-42.2.5.jar

Now start up JBoss / Wildfly application server and launch the CLI:

./jboss-cli.sh

Install module containing the JDBC Driver. Specify the module name and the location where the jdbc driver has been downloaded:

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

Now install the JDBC Driver on the application server:

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

Finally install the datasource which will connect to the default “postgres” database on localhost:

data-source add --jndi-name=java:/PostGreDS --name=PostgrePool --connection-url=jdbc:postgresql://localhost/postgresdb --driver-name=postgres --user-name=postgres --password=mypassword123

Here is the resulting XML configuration:

<subsystem xmlns="urn:jboss:domain:datasources:6.0">
    <datasources>     
        <datasource jndi-name="java:/PostGreDS" pool-name="PostgrePool">
            <connection-url>jdbc:postgresql://localhost/postgresdb</connection-url>
            <driver>postgres</driver>
            <security>
                <user-name>postgres</user-name>
                <password>mypassword123</password>
            </security>
        </datasource>
        <drivers>           
            <driver name="postgres" module="org.postgres">
                <driver-class>org.postgresql.Driver</driver-class>
            </driver>
        </drivers>
</datasources>

Test the Connection:

/subsystem=datasources/data-source=PostgrePool:test-connection-in-pool
{
    "outcome" => "success",
    "result" => [true]
}

Hibernate PostgreSQL Dialect

The dialect specifies the type of database used in hibernate so that hibernate generate appropriate type of SQL statements. For connecting any hibernate application with the database, it is required to provide the configuration of SQL dialect. Let’s see a sample hibernate postgresql dialect coded in the persistence.xml file:

<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.1"
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
    <!-- Define persistence unit -->
    <persistence-unit name="my-persistence-unit">
        <description>Sample Hibernate Postgresql Dialect</description> 
        <jta-data-source>java:/jboss/PostgreDS</jta-data-source>
        <properties>      
            <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
        </properties>
    </persistence-unit>
</persistence>

Please note that the Hibernate Dialect for PostgreSQL 11 and newer is org.hibernate.dialect.PostgreSQLDialect. If you are using a previous version of the Database you should use org.hibernate.dialect.PostgreSQL95Dialect .

Here is another example of hibernate postgresql dialect using hibernate.cfg.xml file:

<session-factory>
	<property name="connection.url">jdbc:postgresql://localhost/DB</property>  
	<property name="connection.driver_class">org.postgresql.Driver</property>  
	<property name="connection.username">postgres</property> 
	<property name="connection.password">password</property>   		
	<property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property> <!-- hibernate postgresql dialect -->
	<property name="hbm2ddl.auto">update</property> <!-- create / create-drop / update -->
	<property name="show_sql">true</property> <!-- Show SQL in console -->
	<property name="format_sql">true</property> <!-- Show SQL formatted -->

	<mapping class="beans.Customer"/>	
</session-factory>

Troubleshooting

A common issue that can arise when configuring a JDBC Connection with PostgreSQL is the following one:

Caused by: org.postgresql.util.PSQLException: FATAL: Ident authentication failed for user "postgres"

There are several things to check when your authentication fails. Firstly, make sure that the password for the user (“postgres”) is correct. Use the \password command from the psql shell to verify your password:

root# su postgres
postgres$ psql -U postgres
psql (9.3.6) 
Type "help" for help. 
postgres=#\password 
Enter new password: 
Enter it again: 
postgres=#

Next, you need to check your PostgreSQL authentication ethod. By default PostgreSQL uses IDENT-based authentication and this will never allow you to login via -U and -W options. Typically the JDBC Driver requires these options. You can fix it by modifying the pg_hba.conf file.

You can identify the location of the pg_hba.conf file by running the following command in psql command line, you’ll need to be  superuser in the database:

postgres=# show hba_file ;
 hba_file
--------------------------------------  
/var/lib/postgresql/data/pgdata/pg_hba.conf

There, update the configuration for the host 127.0.0.1/32 with “trust” as authentication method.

local	all	all	trust
host	all	127.0.0.1/32	trust

Restart PostgreSQL for changes to take effect:

# service postgresql restart

Tuning PostgreSQL

Once you have tested the connectivity with PostgreSQL, you can move from the basics and set up the Connection pool size for the PostgrePool:

/subsystem=datasources/data-source=PostgrePool/:write-attribute(name=max-pool-size,value=50)
/subsystem=datasources/data-source=PostgrePool/:write-attribute(name=max-pool-size,value=10)

The amount of connections configured in the pool should not be anyway be greater than the maximum number of connections which are allowed by PostGreSQL. This value is configured in the file /etc/postgres/postgresql.conf file as follows:

max_connections = 60  

Besides the maximum number of connections, when you are going in production with PostGreSQL you should consider tweaking other parameters as outlined in PostGreSQL wiki (http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server) in particular the shared_buffers configuration parameter which controls the amount of memory PostgreSQL uses for its private buffer cache. Experience has shown that it’s usually best to set this parameter to about 25% of system memory on UNIX and Linux systems, but not more than about 8GB.
An initial configuration could be:

shared_buffers = 30MB

On a production system running a 16GB machine you could set this parameter to:

shared_buffers = 4096MB

Consider that in order to allocate such a large shared memory process, you must be running a 64 bit machine with a kernel which allows a SHMMAX of that size. To determine the maximum size of a shared memory segment, run:

# cat /proc/sys/kernel/shmmax
2147483648

The default shared memory limit for SHMMAX can be changed in the proc file system without reboot:

# echo 2147483648 > /proc/sys/kernel/shmmax

Alternatively, you can use sysctl to change it:

# sysctl -w kernel.shmmax=2147483648

Conclusion

This article was a walk through the installation and configuration of PostgreSQL Datasource in WildFly and Hibernate applications. We have also covered some tuning tips specific for PostgreSQL applications.