Connecting Tomcat with PostgreSQL on OpenShift

Now that we have created Tomcat on OpenShift we can use one of the Gears to install Database server. One of the advantages of OpenShift is that it allows user to create full flagged RDBS for free. I will explain here how to install PostgreSQL but you can choose MySQL or MongoDB if you need NoSQL database.

Creating a gear

You should log in your account on OpenShift and select My Applications tab and click on a name of your application. This will take you to configuration page.


Now you should choose Add Cartridge and select PostgreSQL database. On the next screen you should just click Add Cartridge. After initialization is done you will be prompted connection parameters for your new database and instructions how can you remove this cartridge. You should take note of information on this screen because once you close it you will not be able to get that information.

If you followed my instructions PostgreSQL will be installed on a same Gear as Tomcat so you will still have 2 free Gears.

Connecting Tomcat and PostgreSQL

If you want to connect to your new database from Eclipse or pgAdmin you need to start port forwarding and that can be only done from RHC tool and is only enabled while you run port forwarding command (rhc port-forward -a tomcat).
If you install MySQL database you can use  phpMyAdmin interface for interaction with database, but I prefer PostgreSQL.

Next we need to copy PostgreSQL JDBC driver to Tomcat /lib directory, I used WinSCP for this, or you can do it trough git if you followed my instructions from last post.

You need to add resource setting to global section of server.xml

 <Resource   name="jdbc/tomcat"   
       auth="Container" type="javax.sql.DataSource"   
       driverClassName="org.postgresql.Driver"   
       url="jdbc:postgresql://OPENSHIFT_INTERNAL_IP:5432/tomcat"   
       username="admin"   
       password="****"   
       maxActive="20"   
       maxIdle="10"   
       maxWait="-1"/>  

And also add in context.xml:

 <ResourceLink name="jdbc/tomcat" global="jdbc/tomcat"  
     type="javax.sql.DataSource" />  

After that you should restart server.

You should note that I put OPENSHIFT_INTERNAL_IP in DB url and this constant will be changed in start script that is in action_hooks directory. If you are not using GIT for managing Tomcat configuration you should set this to real IP address.

You can check internal IP with command: echo $OPENSHIFT_INTERNAL_IP
(You shoud be logged in with putty), or you can note what IP is displayed when you port forward.

Getting data source in Java is out of the scope of this post, but you can check out my demo program on GitHub at https://github.com/McNullty/db-tester.

There are examples of connecting to database with Apache BasicDataSource and using JNDI.