ActiveMQ: configure PostgreSQL for Message Persistence

activemq-jdbc.txt

Sources:
  https://www.linkedin.com/pulse/activemq-configure-postgresql-message-persistence-syed-shabbir/
  https://access.redhat.com/documentation/en-us/red_hat_jboss_a-mq/6.2/html-single/configuring_broker_persistence/index#FuseMBPersistJDBCStore
  https://issues.apache.org/jira/browse/AMQ-6780?page=com.atlassian.jira.plugin.system.issuetabpanels%3Aall-tabpanel
  
-------------------------------------------------------------------------  
When the versioning format changed for JDBC driver between 9.4.1212 and 42.0.0 so did the name from "postgres_native_driver" to "postgres_jdbc_driver"
The PostgresqlJDBCAdapter uses the name to find the class but has no reference for postgres_jdbc_driver

The workaround is to add a file "postgres_jdbc_driver" to
"META-INF/services/org/apache/activemq/store/jdbc" in the "activemq-jdbc-store-5.15.3.jar"
-------------------------------------------------------------------------


Looking at the documentation it is advisable to use the Lease Database Locker(LDL).
- http://activemq.apache.org/pluggable-storage-lockers.html
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.

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.


Anyway, time to move onto DBCP2, the necessary jar files (commons-dbcp2-2.1.1.jar, commons-pool2-2.4.2.jar) are already present in recent ActiveMQ distributions (5.15.x)

and below is one usage (please notice leaseHolderId -> must be a unique value for every AMQ node)


<persistenceAdapter>
    <jdbcPersistenceAdapter dataDirectory="activemq-data" dataSource="#postgres-ds" createTablesOnStartup="false" lockKeepAlivePeriod="5000">
        <locker>
            <lease-database-locker leaseHolderId="i-12890abcdead81" lockAcquireSleepInterval="10000"/>
        </locker>
    </jdbcPersistenceAdapter>
</persistenceAdapter>


<bean id="postgres-ds" class="org.apache.commons.dbcp2.BasicDataSource">
    <property name="driverClassName" value="org.postgresql.Driver" />
    <property name="url" value="jdbc:postgresql://server:5432:5432/activemq" />
    <property name="connectionProperties" value="[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>


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