Configuring a datasource with PostgreSQL and JBoss/WildFly

User Rating: 4 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Inactive
 

This tech tip shows how you can install and configure PostGreSQL with JBoss / Wildfly going into the details of PostGreSQL installation, configuration and tuning.  

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

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

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

Related articles available on mastertheboss.com

How to deploy a DataSource in jboss at application level ?

  Do you need to deploy your DataSource along with your Enterpri

JBoss Datasource HA

This tutorial has been updated for the new release of JBoss Appli

JBoss run out of Connections ?

Have you got No ManagedConnections available error message ? well

How to connect to a DataSource from a remote client?

  If you want to connect to your JBoss Connection Pool from a re

JBoss Datasource configuration

A Datasource is a Java Naming and Directory Interface (JNDI) obje

How do I get the list of Datasources available ?

JBOSS AS 4/5 Users You can use either twiddle: $ twiddle.sh que

Follow us on Twitter