JDBC Master-Slave Persistence setup with Activemq using Postgresql database.

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
postgresql-devel.i686

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
psql

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;
CREATE ROLE
postgres=#
postgres=# CREATE DATABASE activemq WITH OWNER = activemq;
CREATE DATABASE
postgres=#
postgres=# GRANT CONNECT ON DATABASE activemq TO activemq;
GRANT

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
listen_addresses='*'

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 0.0.0.0/0 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:

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

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

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
Password:
-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.