[Genie 2.2] Modify database connection settings for PostgreSQL

[Joe]

Hey guys,

In the setup docs there is a segment on modifying database connection settings, but the documentation is pretty sparse.  I was hoping to get some help setting up a connection to a local Postgres database.

Disclaimer: We're a Ruby shop, with minimal knowledge of Java frameworks or tools.  And this is where I think we're getting a little lost.  In Genie v1 we hacked some settings in the persistence-sql.xml file.  In attempting to deploy Genie v2, we're getting stuck in the genie-jpa.xml.

In any case, if I set CATALINA_OPTS to "-Dspring.profiles.active=prod -Darchaius.deployment.applicationId=genie -Darchaius.deployment.environment=prod", then I believe the relevant block from genie-jpa.xml is this one:

    <beans profile="prod">
        <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
            <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
            <property name="url" value="jdbc:mysql://127.0.0.1/genie"/>
            <property name="username" value="root"/>
            <property name="password" value=""/>
            <property name="minIdle" value="5"/>
            <property name="maxIdle" value="20"/>
            <property name="maxTotal" value="40"/>
            <property name="validationQuery" value="select 0;"/>
            <property name="testOnBorrow" value="true"/>
            <property name="testOnCreate" value="true"/>
            <property name="testWhileIdle" value="true"/>
            <property name="testOnReturn" value="true"/>
            <property name="minEvictableIdleTimeMillis" value="60000"/>
            <property name="timeBetweenEvictionRunsMillis" value="10000"/>
        </bean>
    </beans>

Changing the username and password seems fairly straightforward.  But I could really use an example on how to swap out these first few lines in order to properly load a PostgreSQL driver in Beans:

        <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
            <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
            <property name="url" value="jdbc:mysql://127.0.0.1/genie"/>

We considered just going with MySQL, but everything else we run here is Postgres.  If we can easily sub in connection drivers, that would be the ideal solution.  I'd appreciate any help you guys can offer.  

~J

[Tom Gianos]

Joe,

Should be simple online to find postgres examples of configuring Spring datasources.

Swap out these lines:

https://github.com/Netflix/genie/blob/master/genie-web/src/main/resources/genie-jpa.xml#L68 through https://github.com/Netflix/genie/blob/master/genie-web/src/main/resources/genie-jpa.xml#L71 with the expected values you want.

One example is here:

http://postgresintl.blogspot.com/2014/04/using-postgresql-jdbc-with-spring.html

(raw pertinent info: https://gist.githubusercontent.com/davecramer/10988815/raw/8194f6dc1ff415fd372ca01095e01293aadb390f/context.xml)

Really you need the driver class name, your connection string (swap out his example for your host:port/database) and then your username and password.

Finally you'll need to download the appropriate driver for what JDK you're running on:

https://jdbc.postgresql.org/download.html

(Assuming you've put genie into tomcat as ROOT.war if not change to whatever your context root is)

take that Jar and stick it in <tomcat-root>/webapps/ROOT/WEB-INF/lib/ and restart Tomcat.

That should get you connecting to Postgresql.

One thing I will say is that moving to Genie 3 we'll be providing upgrade scripts for MySQL since that's what we'll be using. We don't currently have plans to support Postgres but the upgrade script should be relatively easy to replicate. Contributing it back would be great as we could then add Postgres as a dependency to Genie itself and you won't have to do that download/install step above.

Tom

[Joe]

Hey Tom,

We're using Oracle JDK (1.7), so I pulled down https://jdbc.postgresql.org/download/postgresql-9.4.1207.jre7.jar and installed it as instructed.

Relevant bit from genie-jdpa.xml:

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="org.postgresql.Driver"/>
        <property name="url" value="jdbc:postgresql://127.0.0.1:5432/genie"/>
        <property name="username" value="postgres"/>
        <property name="password" value="########"/>
    </bean>

Here is a gist of the full prod block.  And my genie.properties.

On tomcat start, I'm getting an error from the logs involving Karyon that I don't understand.  Gist.

SEVERE: Error configuring application listener of class com.netflix.karyon.server.guice.KaryonGuiceContextListener

java.lang.UnsupportedClassVersionError: com/netflix/genie/server/startup/GenieGuiceBootstrap : Unsupported major.minor version

Any thoughts on that?

One thing I will say is that moving to Genie 3 we'll be providing upgrade scripts for MySQL since that's what we'll be using. We don't currently have plans to support Postgres but the upgrade script should be relatively easy to replicate. Contributing it back would be great as we could then add Postgres as a dependency to Genie itself and you won't have to do that download/install step above.

Sounds good.  We may very well consider dropping Postgres and switching to the default MySQL in the future.  But right now we're a small shop (<15 devs) supporting two relational DB (postgres and greenplum) and two non-relational DBs (mongo and hbase).   So our efforts to continue with Postgres come from an operational desire not to support a comedy third relational DB in production.  

In any case, if we continue with Postgres we will contribute back to the project.  And FWIW, we're working on a community application cookbook to deploy Genie.  Work in progress posted here.  

We know about the docker project, but I've also been told that is not intended for production and that your team deploys this to bare-vm.  We wanted build code we could use for either.  Hopefully others in the Chef community will find it useful as well.

Regards,
Joe Reid

[Tom Gianos]

That error usually means you're trying to run a .class file in an older version of the JDK than the one that the class was compiled for. JDK 8 is major version 52, JDK 7 is 51, JDK 6 is 50, etc.

A few things that pop to mind as possible things to look into on your end:

1. are you really running tomcat with JDK 7? Have you set JAVA_HOME? what does java --version report?
2. Verify what your class files say the version is
1. Unizip the war: jar -xf genie-web-{version}.war
2. cd WEB-INF/lib
3. jar -xf genie-server-{version}.jar
4. cd com/netflix/genie/server/startup/
   
5. javap -verbose GenieGuiceBootstrap.class | grep "major"
6. Should say 51
3. I tried the above with the latest from bintray: https://bintray.com/netflixoss/maven/genie/2.2.3/view#files/com/netflix/genie/genie-web/2.2.3 and it said 51 so it should run fine on JDK 1.7

I'm not sure what your environment looks like so it's hard to debug but I suspect you might be running tomcat with java 1.6 somehow.

FYI Genie 3 will only run on Java 8. Java 7 (and 6 for that matter) are EOL.

Tom

[Joe]

Hey Tom,

Thank!  That pointed to a bug in the library cookbook we were using to install Tomcat.  It was reverting our Java version.

Raised an issue with the Tomcat cookbook maintainers and hacked my way around it.  Thanks!

Regards,
Joe

[Tom Gianos]

No problem. Good to hear it was something traceable.