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. Start by installing PostGreSQL by running:

$ apt-get install postgresql postgresql-client

Now 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. We have covered all details about running 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/

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=/tmp/postgresql-9.3-1101.jdbc41.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/postgres --driver-name=postgres --user-name=postgres --password=postgres

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.PostgreSQL95Dialect"/>
        </properties>
    </persistence-unit>
</persistence>

Please mind it that in older configuration files you may use as hibernate postgresql dialect “PostgreSQLDialect” which is deprecated. You should update to PostgrwSQL95Dialect as in the above example.

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

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