Host Your PostgreSQL Application With Enterprise DB Cloud

Developers have asked us about support for database replication and latest version of PostgreSQL. While we are working on these feature for OpenShift you can use EnterpriseDB Cloud database to get latest, replicated, and sharded PostgreSQL. EnterpriseDB is one of the companies that commits to PostgreSQL, providing enterprise class commercial PostgreSQL support. EnterpriseDB cloud database allows you to setup a replicated, sharded, and highly available PostgreSQL cluster either on Amazon EC2 or HP Cloud services. You can take periodic backup of your data, scale it horizontally without any administrative skills. In this blog, we will create a Java application and use EnterpriseDB cloud database as our database backend.

Step 1: Sign up for an OpenShift Account

If you don’t already have an OpenShift account, head on over to the website and signup. It is completely free and Red Hat gives every user three free Gears on which to run your applications. At the time of this writing, the combined resources allocated for each user is 1.5 GB of memory and 3 GB of disk space.

Step 2: Install the client tools on your machine

The OpenShift client tools are written in a very popular programming language called Ruby. With OSX 10.6 or later and most Linux distributions, ruby is installed by default so installing the client tools is a snap. Simply issue the following command on your terminal application:

sudo gem install rhc

Step 3 : Setting up OpenShift

The rhc client tool makes it very easy to setup your OpenShift instance with ssh keys, git and your applications namespace. The namespace is a unique name per user which becomes part of your application url. For example, if your namespace is t20 and application name is todo then url of the application will be https://todo-t20.rhcloud.com/. The command is shown below.

rhc setup -l openshift_login

Step 4 : Creating the Todo Java EE 6 application

After setting up OpenShift account, next step is to create todo application. To create the application, type the command shown below.

rhc app create todo jbosseap postgresql-8.4 --from-code=git://github.com/shekhargulati/todo-javaee6.git

The reason why are installing PostgreSQL cartridge in our application is to show how easy it is to switch from OpenShift provided PostgreSQL cartridge to a scalable and clustered Enterprise DB Cloud PostgreSQL database.

The command shown below will create an application container for us, called a gear, and setup all of the required SELinux policies and cgroup configuration.Next, it will install all the required softwares like PostgreSQL and JBoss EAP 6 on the gear. OpenShift will also setup a private git repository for you using the code from the git repository url, and then clone the repository to your local system. Finally, OpenShift will propagate the DNS to outside world.

The application will be accessible at https://todo-t20.rhcloud.com/. Please replace t20 with your account domain name. If you create a todo, it will get persisted in PostgreSQL running on OpenShift.

PostgreSQL datasource configuration

When a user creates a JBoss EAP 6 application, then OpenShift defines a PostgreSQL datasource in standalone.xml file. The standalone.xml file is a JBoss configuration file which includes the technologies required by the Java EE 6 full profile specification plus Java Connector 1.6 Architecture, Java XML API for RESTFul Web Services, and OSGi. Developers can override the configuration by making changes to standalone.xml file in .openshift/config folder of your application directory. So, if you open the .openshift/config/standalone.xml file in your favourite editor you will find the following PostgreSQL datasource configuration.

<datasource jndi-name="java:jboss/datasources/PostgreSQLDS"
    enabled="${postgresql.enabled}" use-java-context="true" pool-name="PostgreSQLDS"
    use-ccm="true">
    <connection-url>jdbc:postgresql://${env.OPENSHIFT_POSTGRESQL_DB_HOST}:${env.OPENSHIFT_POSTGRESQL_DB_PORT}/${env.OPENSHIFT_APP_NAME}
    </connection-url>
    <driver>postgresql</driver>
    <security>
        <user-name>${env.OPENSHIFT_POSTGRESQL_DB_USERNAME}</user-name>
        <password>${env.OPENSHIFT_POSTGRESQL_DB_PASSWORD}</password>
    </security>
    <validation>
        <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
    </validation>
</datasource>

Now if you open persistence.xml file, you will notice that we are using this datasource in our application.

<?xml version="1.0" encoding="UTF-8" ?>
<persistence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
    version="2.0" xmlns="http://java.sun.com/xml/ns/persistence">
 
    <persistence-unit name="todos" transaction-type="JTA">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <jta-data-source>java:jboss/datasources/PostgreSQLDS</jta-data-source>
        <class>com.todo.domain.Todo</class>
        <properties>
            <property name="hibernate.show_sql" value="true" />
            <property name="hibernate.hbm2ddl.auto" value="create" />
        </properties>
 
    </persistence-unit>
</persistence>

Step 5 : Sign up for EnterpriseDB Cloud Database trial account

Go to http://www.enterprisedb.com/cloud-database/amazon and click "Get Started Now" as shown below.

OpenShift EnterpriseDB cloud database trial account picture

After clicking on "Get Started Now", you will be redirected to sign up page as shown below. Enter the valid details and press submit.

OpenShift signup for trial EnterpriseDB cloud database account picture

After successful signup, you will be redirected to dashboard console as shown below.Here you can launch a database cluster , see the resources you are consuming, or see the service status update. At the bottom of the dashboard, there are links to tutorial and documentation about PostgreSQL CloudDB.

OpenShift EnterpriseDB Cloud Database Web Console picture

Step 6 : Launching database cluster

Now we will create our first db cluster on EnterpriseDB Cloud by clicking on "Launch DB Cluster". This will open up a pop up where you need to provide details of your cluster as shown below. The details include name of the cluster, PostgreSQL version, Amazon instance size, number of nodes, master username and password.

OpenShift Enter EnterpriseDB Cloud Database Cluster Details picture

After entering cluster details, then you can choose how many backups you want to keep and when would you like to take backup.

OpenShift Enter EnterpriseDB Cloud Database Data backup Details picture

Finally, press Launch button.This will initiate the process of creating a replicated db cluster as shown below. Now our cluster is being created.

OpenShift EnterpriseDB Cloud Database Cluster Initialising picture

It will take couple of minutes to launch the cluster so please be patient. After initialized, you will see as shown below in Clusters tab. From the Clusters tab, you can get information about database clusters you own. In the Details, you can see the address where master and replica are running.

OpenShift EnterpriseDB Cloud Database Cluster Initialised picture

Back in the Clusters tab, we can get information about our cluster by looking in Events section as shown below.

OpenShift EnterpriseDB Cloud Database Cluster Events Tab picture

You can also see the configuration of Master and replica under Configuration section and change these values.

OpenShift EnterpriseDB Cloud Database Cluster PostgreSQL Configuration picture

And finally you can monitor the state of the cluster as well.

OpenShift Monitor EnterpriseDB Cloud Database Cluster picture

Step 7 : Updating application to use Cloud Database instead of embedded PostgreSQL cartridge

To use Cloud Database we have to update postgresql datasource configuration in standalone.xml as shown below. In connection-url, use the url of master node and enter the username and password of the PostgreSQL master user to entered in step 6.

<datasource jndi-name="java:jboss/datasources/PostgreSQLDS"
    enabled="${postgresql.enabled}" use-java-context="true" pool-name="PostgreSQLDS"
    use-ccm="true">
    <connection-url>jdbc:postgresql://ec2-54-235-73-60.compute-1.amazonaws.com:9999/${env.OPENSHIFT_APP_NAME}
    </connection-url>
    <driver>postgresql</driver>
    <security>
        <user-name>postgres</user-name>
        <password>postgres</password>
    </security>
    <validation>
        <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
    </validation>
</datasource>

Step 8 : Creating todo database on EnterpriseDB Cloud Database

Before we can commit these changes we need to create the todo database which our application will use. To do that we need to connect to Cloud DB using PostgreSQL command line client tool called psql. Execute the command shown below to connect with Cloud DB ( I assume you have installed the Postgresql client tools)

psql -h ec2-54-235-73-60.compute-1.amazonaws.com -p 9999 -U postgres

After connecting, create the todo database as shown below.

postgres=# CREATE DATABASE todo;

Step 9 : Commit and push the changes to OpenShift

Commit the change

git commit -am "updated standalone.xml postgres datasource configuration to use EnterpriseDB Cloud Database instead of embedded postgresql cartridge"

And finally push the change to OpenShift.

git push

Step 10 : Taking Todo application for test run

After git push successfully completes, you can test the application by creating a new Todo as shown below. The application schema will get automatically created for you as in persistence.xml we have <property name="hibernate.hbm2ddl.auto" value="create" />

OpenShift Todo application picture

After creating the todo entry, you can connect with both the master and slave node to check the persisted data. On the master node, run the following command. Replace your host name.

psql -h ec2-54-235-73-60.compute-1.amazonaws.com -p 9999 -U postgres -d todo

After connecting with master, you can check the data by executing following commands.

todo=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | tags | table | postgres
 public | todo | table | postgres
(2 rows)
 
todo=# SELECT * from todo;
 id |        createdon        |        todo        
----+-------------------------+--------------------
  1 | 2013-02-25 10:54:23.964 | Learning OpenShift
(1 row)
 
todo=# 
todo=# SELECT * from tags;
 todo_id |    tag    
---------+-----------
       1 | openshift
       1 | cloud
       1 | paas
(3 rows)

Similarly you can connect with the slave node and check the persisted data.

psql -h ec2-23-20-133-130.compute-1.amazonaws.com -p 5432 -U postgres -d todo

After connecting with slave, you can check the data by executing following commands.

todo=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | tags | table | postgres
 public | todo | table | postgres
(2 rows)
 
todo=# SELECT * from todo;
 id |        createdon        |        todo        
----+-------------------------+--------------------
  1 | 2013-02-25 10:54:23.964 | Learning OpenShift
(1 row)

From here on it is like working with any Postgresql Instance. That's it for this blog and I hope you got a hint of the power combining cloud providers can bring to your application.

What's Next?

Nice article,

I have though a question regarding security ! Is the link between openshift and Enterprise DB secured ?

Thanks a lot,

Salim MANSOURI.