Over a million developers have joined DZone.

JDBC Master-Slave Persistence Setup With ActiveMQ Using PostgreSQL

DZone's Guide to

JDBC Master-Slave Persistence Setup With ActiveMQ Using PostgreSQL

Learn how to set up JDBC persistence for ActiveMQ using the PostgreSQL database in just ten quick and easy steps.

Free Resource

Read why times series is the fastest growing database category.

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

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

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

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

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

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

Learn how to get 20x more performance than Elastic by moving to a Time Series database.

database ,jdbc ,tutorial ,postgresql ,activemq

Published at DZone with permission of Chandra Shekhar Pandey, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}