Setting up a PostgreSQL database for ActiveMQ

The default ActiveMQ configuration file (in conf/activemq.xml) has a sample fragment of Spring configuration that shows how to use PostgreSQL as a data source. So, pointing ActiveMQ to PostgreSQL is easy; the problem you may run into is how to configure PostgreSQL in the first place with an appropriate account for ActiveMQ.

The instructions below assume that you've installed postgres in the directory /usr/local/pgsql, and that postgres is being run by the user 'postgresql'. It also assumes that you're using an bash or sh shell; however, I"m sure it's not rocket science to map what follows to DOS shell for Windows.

You should include the PostgreSQL bin directory in your path:

 $ export PATH=/usr/local/pgsql/bin:$PATH

Decide on a file system location for the postgresql data files, say: /Users/postgresql/data. Set the PGDATA variable to point to this directory.

 $ export PGDATA=/Users/postgresql/data

The commands that follow will use $PGDATA to determine the location of the database files.

Initialize the database data directory:

 $ initdb 

... This will initialize the database in the $PGDATA directory.

Now start the database controller:

 $ pg_ctl -l logfile start

You should see a message 'server starting'

Now create a database called activemq-db

 $ createdb activemq-db

To log into the database, use the psql command:

 $ psql activemq-db

This gets you in as the current user ('postgresql') - the default security is very lax: let's enforce password protection for the database. To do this, first create a password for the postgresql user:

 activemq-db=> alter user postgresql with password 'foo';

Create a username for activemq with appropriate password:

 activemq-db=> create user activemq with password 'pa55w0rd';

Exit the psql shell using the \q command:

 \q

Edit the $PGDATA/conf/pg_hba.conf file, to specify that the activemq user can access the activemq-db.

 # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
 host    all          postgresql    127.0.0.1         255.255.255.255   md5
 local   all          postgresql                                        md5
 host    activemq-db  activemq      127.0.0.1         255.255.255.255   md5
 local   activemq-db  activemq                                        md5

Now, bounce the database.

 $ pg_ctl reload

You should now be prompted for a password when you log into to the database as the activemq user.

 $ psql activemq-db activemq
 Password for user activemq: 
 Welcome to psql 8.3.3, the PostgreSQL interactive terminal.
 
 Type:  \copyright for distribution terms
        \h for help with SQL commands
        \? for help with psql commands
        \g or terminate with semicolon to execute query
        \q to quit
 
 activemq-db=> 

In activemq, configure your postgres-ds data source to use the database 'activemq-db', with user 'activemq' and password 'pa55w0rd'. Now, when you start up, activemq will create the necessary tables to persist messages. You can validate that it has created these tables by using the \d command in psql:

 activemq-db=> \d
              List of relations
  Schema |     Name      | Type  |  Owner   
 --------+---------------+-------+----------
  public | activemq_acks | table | activemq
  public | activemq_lock | table | activemq
  public | activemq_msgs | table | activemq
 (3 rows)

And... you're done!