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:

Now start PostGreSQL by issuing:

If you want PostgreSQL to startup automatically on each reboot:

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:

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

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

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:

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

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:

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

Now install the JDBC Driver on the application server:

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

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:

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:

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:

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

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:

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

Alternatively, you can use sysctl to change it: