JDBC Master-Slave Persistence Setup With ActiveMQ Using PostgreSQL

This article will help in setting up JDBC master/slave for embedded ActiveMQ in Red Hat JBoss Fuse/AMQ 6.3 with PostgreSQL database from scratch.

Try to search for a PostgreSQL database in RHEL using this 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

Install the available package:

yum install postgresql-server.x86_64

This will install the PostgreSQL database and create a user called postgres. As this user, one can access PostgreSQL. The root user can change the password if required for this user with the following command:

passwd postgres

Now, switch to the postgres user. Then run the psql command.

su - postgres
psql

Create a schema activemq with username 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

We have to provide access to remote applications to connect to PostgreSQL. To open database port 5432 to the 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='*'

To whitelist remote IPs to connect to the 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

Stop and start the PostgreSQL server.

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

Now, we edit the broker and 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 the 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 the broker XML tag.
• The postgre-ds datasource is defined as a bean outside of the broker tag and within the beans tag.

Restart brokers to obtain the lock, then check the database.

[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 you understand and set up JDBC persistence for ActiveMQ using the PostgreSQL database.