ActiveMQ: configure PostgreSQL for Message Persistence
来源:互联网 发布:vba是什么软件 编辑:程序博客网 时间:2024/06/02 05:53
We are gathered here today to discuss the use of datastores with ActiveMQ.
ActiveMQ(AMQ) supports various datastores for message persistence, including PostgreSQL(PG). When considering a migration to PG there are various considerations involved, two of which may be connection pooling and queue lock mechanism. Naturally, it would also require a database being setup on some accessible PG instance.
The first step could possibly be setting up the user, database and schema. From commandline, login as "postgres" user; if you're on the PG box then
psql -U postgres
CREATE ROLE activemq LOGIN PASSWORD 'activemq' SUPERUSER;
CREATE DATABASE activemq
WITH OWNER = activemq
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'en_GB.UTF-8'
LC_CTYPE = 'en_GB.UTF-8'
CONNECTION LIMIT = -1;
GRANT CONNECT ON DATABASE activemq TO activemq;
Log in as user activemq and run below -
CREATE SCHEMA activemq;
ALTER DATABASE activemq SET SEARCH_PATH TO activemq;
ALTER ROLE activemq SET SEARCH_PATH = activemq;
GRANT ALL PRIVILEGES ON SCHEMA activemq TO activemq;
If you have other superusers on the same PG instance, you might want to revoke their access to this database
REVOKE ALL PRIVILEGES ON DATABASE activemq FROM anothersuperuser;
That is as much as you want to do on PG. Essentially the user has privilges to create tables, and other stuff.
ActiveMQ provides a configuration file, activemq.xml, to manage queue setup, located in the conf directory. If you're using AMQ 5.7.0, or above, it is possible to dictate the storage lock mechanism. Looking at thedocumentation it is advisable to use the Lease Database Locker(LDL). 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. The documentation, mentioned above, certainly provides sufficient explanation and example usage.
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. So, you find some related PG documentation. Lo, and behold
...Among other things, connections are never closed until the pool itself is closed; there is no way to shrink the pool. As well, connections requested for users other than the default configured user are not pooled. Its error handling sometimes cannot remove a broken connection from the pool. In general it is not recommended to use the PostgreSQL™ provided connection pool...
Well, there is, evidently, some issue with the choice of connection pooling, and it's time to look elsewhere. One may be tempted to dwell on the AMQ documentation's use of the PG pooling mechanism, when it is clearly not that reliable, and how others have just followed suit, or, at least, seem to have. Anyway, time to move onto DBCP2, and the necessary jar files
- commons-dbcp2-2.1.1.jar
- commons-pool2-2.4.2.jar
- commons-logging-1.2.jar
and below is one usage
<bean id="postgres-ds" class="org.apache.commons.dbcp2.BasicDataSource">
<property name="driverClassName" value="org.postgresql.Driver" />
<property name="url" value="jdbc:postgresql://server1:5432,server2:5432/activemq?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>
It seems with DBCP2 setConnectionProperties has been implemented and it is possible to use
<property name="connectionProperties" value="[targetServerType=master,readOnly=false]"/>
instead of passing parameters with the url, just seems tidier.
Once pushed into production, it seems to work and all connection issues are resolved.
Amen.
- ActiveMQ: configure PostgreSQL for Message Persistence
- ActiveMQ 之 Topic Persistence
- ActiveMQ的Kaha Persistence
- SD--Message Control Configure
- SD--Message Control Configure
- PostgreSQL ./configure问题
- PostgreSQL ./configure问题
- PostgreSQL ./configure问题
- install and configure postgresql
- Java Message Servicec - ActiveMQ
- ActiveMQ - message cursor
- ActiveMQ:Message Groups
- Apply for Google FCM ( Firebase Cloud Message ) Project & Configure Android Project
- how to change activemq's persistence database
- ActiveMQ使用STOMP协议的一个错误问题:Unexpected ACK received for message-id
- Object-Relational Persistence for .NET
- Java Persistence for Relational Databases
- ActiveMQ vs Fuse Message Broker
- 大数据学习笔记2--待补全
- USACO1.3]修理牛棚 Barn Repair
- 欢迎使用CSDN-markdown编辑器
- Maven生命周期
- 快速排序
- ActiveMQ: configure PostgreSQL for Message Persistence
- USACO1.5]回文质数 Prime Palindromes
- Netty与TCP、HTTP、WebSocket协议简明摘要
- CodeBlocks 调试及快捷键
- SDL_Surface表面
- Android滑动菜单特效实现,侧滑菜单实现
- org.hibernate.HibernateException: Could not obtain transaction-synchronized Session for current thre
- Android动画之Drawable Animation
- Oracle字符集引发文字化问题(一)