We are gathered here today to discuss the use of datastores with ActiveMQ.

ActiveMQ(AMQ) supports various datastores for message persistence, including PostgreSQL(PG). When considering a migration to PG there are various considerations involved, two of which may be connection pooling and queue lock mechanism. Naturally, it would also require a database being setup on some accessible PG instance.

The first step could possibly be setting up the user, database and schema. From commandline, login as "postgres" user; if you're on the PG box then

psql -U postgres

CREATE ROLE activemq LOGIN PASSWORD 'activemq' SUPERUSER;

CREATE DATABASE activemq
WITH OWNER = activemq
    ENCODING = 'UTF8'
    TABLESPACE = pg_default
    LC_COLLATE = 'en_GB.UTF-8'
    LC_CTYPE = 'en_GB.UTF-8'
    CONNECTION LIMIT = -1;
    
GRANT CONNECT ON DATABASE activemq TO activemq;

Log in as user activemq and run below -

CREATE SCHEMA activemq;
ALTER DATABASE activemq SET SEARCH_PATH TO activemq;
ALTER ROLE activemq SET SEARCH_PATH = activemq;
GRANT ALL PRIVILEGES ON SCHEMA activemq TO activemq;

If you have other superusers on the same PG instance, you might want to revoke their access to this database

REVOKE ALL PRIVILEGES ON DATABASE activemq FROM anothersuperuser;

That is as much as you want to do on PG. Essentially the user has privilges to create tables, and other stuff.

ActiveMQ provides a configuration file, activemq.xml, to manage queue setup, located in the conf directory. If you're using AMQ 5.7.0, or above, it is possible to dictate the storage lock mechanism. Looking at thedocumentation it is advisable to use the Lease Database Locker(LDL). AMQ was previously hooked-up with Oracle and the Database Locker(DL) mechanism was in place. It caused no end of problems when there was failover, requiring sysadmin DB level commands to be used to purge locks etc. Thankfully, the LDL mechanism avoids the shortcomings of DL in that department, and PG does not seem to work to well with DL. The documentation, mentioned above, certainly provides sufficient explanation and example usage.

In terms of connection pooling, the AMQ documentation, and, indeed, as with many examples found online, the default connection pool mechanism to be used is found within the PostgresSQL JDBC jar i.e.org.postgresql.ds.PGPoolingDataSource. One may be tempted to think that is standard practice and what could possibly go wrong. Based on that, the configuration may look something like

  <bean id="postgres-ds" class="org.postgresql.ds.PGPoolingDataSource">
    <property name="url" value="jdbc:postgresql://server1:5432,server2:5432/activemq?" />
    <property name="user" value="user"/>
    <property name="password" value="password"/>
    .................
  </bean>

Having gone into production, there are connection timeout errors appearing in the PG logs, and related errors in the application log. After scrambling around for a while, it becomes clear that there is some issue with the connection pooling mechanism. So, you find some related PG documentation. Lo, and behold

...Among other things, connections are never closed until the pool itself is closed; there is no way to shrink the pool. As well, connections requested for users other than the default configured user are not pooled. Its error handling sometimes cannot remove a broken connection from the pool. In general it is not recommended to use the PostgreSQL™ provided connection pool...

Well, there is, evidently, some issue with the choice of connection pooling, and it's time to look elsewhere. One may be tempted to dwell on the AMQ documentation's use of the PG pooling mechanism, when it is clearly not that reliable, and how others have just followed suit, or, at least, seem to have. Anyway, time to move onto DBCP2, and the necessary jar files

  • commons-dbcp2-2.1.1.jar
  • commons-pool2-2.4.2.jar
  • commons-logging-1.2.jar

and below is one usage

<bean id="postgres-ds" class="org.apache.commons.dbcp2.BasicDataSource">
    <property name="driverClassName" value="org.postgresql.Driver" />
    <property name="url" value="jdbc:postgresql://server1:5432,server2:5432/activemq?targetServerType=master&readOnly=false" />
    <property name="username" value="activemq"/>
    <property name="password" value="activemq"/>
    <property name="initialSize" value="5" />
    <property name="maxTotal" value="20" />
    <property name="maxIdle" value="5" />
    <property name="validationQuery" value="select 1" />
    <property name="testWhileIdle" value="true" />
    <property name="timeBetweenEvictionRunsMillis" value="3000" />
  </bean>

It seems with DBCP2 setConnectionProperties has been implemented and it is possible to use

<property name="connectionProperties" value="[targetServerType=master,readOnly=false]"/>

instead of passing parameters with the url, just seems tidier.

Once pushed into production, it seems to work and all connection issues are resolved.

Amen.