This article will help in setting up JDBC Master/Slave for embedded Activemq in Red Hat JBoss Fuse/AMQ 6.3 with postgresql db from scratch.

1. Try to search for  postgresql db in RHEL using command

yum list postgre*
Loaded plugins: product-id, refresh-packagekit, search-disabled-repos, security, subscription-
: manager
Available Packages
postgresql.x86_64 8.4.20-7.el6 @rhel-6-workstation-rpms
postgresql-libs.x86_64 8.4.20-7.el6 @rhel-6-workstation-rpms
postgresql-server.x86_64 8.4.20-7.el6 @rhel-6-workstation-rpms
postgresql.i686 8.4.20-7.el6 rhel-6-workstation-rpms
postgresql-contrib.x86_64 8.4.20-7.el6 rhel-6-workstation-rpms

2. Install available package.

yum install postgresql-server.x86_64

3. This will install postgresql db and create a user called postgres. As this user, one can access postgresql. Root user can change password if required for this user with the following command:

passwd postgres

4. Now switch to postgres user. Then run psql command.

su - postgres

5. Create a schema activemq with user and password activemq. Also, provide this schema access to connect with user activemq.

postgres=# create ROLE activemq LOGIN PASSWORD 'activemq' SUPERUSER;
postgres=# CREATE DATABASE activemq WITH OWNER = activemq;
postgres=# GRANT CONNECT ON DATABASE activemq TO activemq;

6. We have to provide access to remote applications to connect to postgresql. To open db port 5432 to remote application, we will have to edit postgresql.conf and set listen_addresses to *.

[root@vm252-99 cpandey]# vi /var/lib/pgsql/data/postgresql.conf

7. To whitelist remote IP's to connect postgresql server, we will have to edit pg_hba.conf.

[root@vm252-99 cpandey]# vi /var/lib/pgsql/data/pg_hba.conf

#all remote connection

host all all md5

8. Stop and Start postgresql server.

[root@vm252-99 cpandey]# service postgresql stop
Stopping postgresql service: [ OK ]
[root@vm252-99 cpandey]# service postgresql start
Starting postgresql service:

9. Now we edit the broker configure activemq.xml to have the following configuration:

<broker... brokerName="testPostgre1"....>
<jdbcPersistenceAdapter dataSource="#postgres-ds" lockKeepAlivePeriod="5000">
<lease-database-locker lockAcquireSleepInterval="10000"/>

<bean id="postgres-ds" class="org.postgresql.ds.PGPoolingDataSource">
<property name="url" value="jdbc:postgresql://"/>
<property name="user" value="activemq"/>
<property name="password" value="activemq"/>
<property name="initialConnections" value="1"/>
<property name="maxConnections" value="10"/>

Some points to note in step above:

  • Remember to set brokerName. It should be unique for each broker.
  • persistenceAdapter, referring to datasource #postgres-ds, is set with lease-database-locker within broker xml tag.
  • postgre-ds datasource is defined as a bean outside of broker tag and within beans tag.

10. Restart brokers to obtain lock, then check db.

[cpandey@vm252-99 ~]$ su - postgres
-bash-4.1$ psql
# connect to activemq schema
postgres=# \c activemq
# check tables
activemq=# \dt
List of relations
Schema | Name | Type | Owner
public | activemq_acks | table | activemq
public | activemq_lock | table | activemq
public | activemq_msgs | table | activemq
(3 rows)
# run select query.
activemq=# select * from activemq_lock;
id | time | broker_name
1 | 1506947282760 | testPostgre1

Above broker testPostgre1 has occupied the lock.

I hope this article helps to understand and setup JDBC persistence for Activemq using Postgresql database.

Last updated: October 3, 2017