AppDynamics: Java Spring PetClinic and PostgreSQL configured for monitoring

As an exploration of AppDynamics’ APM functionality, you may find it useful to deploy a sample application that can quickly return back useful data. The Java Spring PetClinic connecting back to a PostgreSQL database provides a simple code base that exercises both database and application monitoring.

In a previous article, I went over the detailed steps for monitoring PetClinic with a MySQL backend, so I will refer back to that article for some of the details and will focus on the PostgreSQL specific steps here.

Build petclinic.war

I forked the github spring-framework-petclinic to support an older PostgreSQL SQL syntax. This is because the SQL syntax in the original project uses Postgres 9.5 syntax while the default Postgres package in the Ubuntu repositories is only Postgres 9.3.

> sudo apt-get install git openjdk-7-jdk maven -y
> git clone https://github.com/fabianlee/spring-framework-petclinic.git
> cd spring-framework-petclinic
> ./mvnw package -P PostgreSQL91 –DskipTests

If we were going to use PostgreSQL 9.5, the last command would have been:

> ./mvnw package -P PostgreSQL –DskipTests

The war will be found at target/petclinic.war. Copy this over to the /tmp directory of the target host.

Install the PostgreSQL Database

If you wanted to install the latest PostgreSQL (>9.5), you could use their apt repository and follow the instructions here. But let’s assume you will use the standard Ubuntu repositories.

# apt-get -y install postgresql

You can validate the installation with the following commands which list the tablespaces and then databases. Note that we must first enter into a shell as the user ‘postgres’ because the default authentication is local user based.

# su - postgres
> psql
\db
\l
\q

Which should shows 2 tablespaces: pg_default and pg_global. And then 3 databases: postgres, template0, and template1.

Then set the default password for the ‘postgres’ user to ‘petclinic’ and exit the shell belonging to the ‘postgres’:

> psql
\password
Enter new password: petclinic
Enter it again: petclinic
\q
> exit

Now we check connectivity using an explicit username and password over TCP/IP and while we are there let’s create the petclinic database and list the databases available:

> export PGPASSWORD=petclinic
> psql -h 127.0.0.1 -U postgres
CREATE DATABASE petclinic WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default CONNECTION LIMIT = -1;
\l
\q

Install Java and Tomcat

Now, we need to install Java and then Tomcat as the application server.

# apt-get install openjdk-7-jdk tomcat7 -y

Then edit /etc/default/tomcat7 so that it points at the JDK and increases the heap size to 512m:

JAVA_HOME=/usr/lib/jvm/java-7-openjdk-amd64
JAVA_OPTS="-Djava.awt.headless=true -Xmx512m -XX:+UseConcMarkSweepGC"

Make sure the Tomcat public port is open on the firewall:

# ufw allow 8080/tcp

Then copy petclinic.war to the Tomcat deployment directory and restart:

# cp /tmp/petclinic.war /var/lib/tomcat7/webapps/.
# chmod ugo+r+x /var/lib/tomcat7/webapps/petclinic.war
# service tomcat7 restart

Create Database Schema

Now we need to create the schema necessary to support the application. While this is usually done upon app server startup by ‘src/main/resources/spring/datasource-config.xml’, because we are working with Postges 9.1 SQL that cannot check for table/index/row pre-existence, we do it manually:

> cd /var/lib/tomcat7/webapps/petclinic/WEB-INF/classes/db/postgres91
> export PGPASSWORD=petclinic
> psql -h 127.0.0.1 -U postgres -a -f initDB.sql
> psql -h 127.0.0.1 -U postgres -a -f populateDB.sql

Validate Base Application

Now we should be able to validate the petclinic application. The AppDynamics Java agent is not configured yet, this is just to test that the application itself is deployed properly.

# service tomcat7 restart
# tail –f /var/log/tomcat7/catalina.out

The bottom of catalina.out should say ‘Server startup in xxxx ms’. Then open your browser to the application server, where you should see a screen like below:

http://:8080/petclinic

Click on “Find Owners” tab, “Find Owner” button (should return list of users)

Click on “George Franklin”, press “Edit Owner”

Change name randomly, press “Update Owner”

Click on “Find Owners” tab, “Find Owner” button (list of users, with George Franklin modified).

The steps above will be repeated several times in this article to validate the extra telemetry.

Configure Java AppDynamics Agent

Refer back to my first article on configuring the Java AppDynamics Agent The exact same instructions apply.

The APM screen should look something like:

Configure AppDynamics Database Agent

Refer back to my first article for installing the AppDynamics Database Agent. The instructions start exactly the same. But where they differ is when you to go the AppDynamics Controller Web GUI.

Controller Web UI > Databases, and press “Add”.

Type: PostgreSQL
name: petclinic-postgres
jdbc:postgresql://127.0.0.1:5432/petclinic
user: postgres
password: petclinic

Now you should go back to the petclinic application in your browser and follow the same steps of listing and modifying an owner as described in the “Validate Base Application” section of the first article.

Within two minutes, the data should be reflected back into the AppD Controller GUI similar to the screenshot below: