Storing log data in PostGreSql using logback db appender

3 min readAug 27, 2016

In an enterprise application, data that gets logged is very very important not only for troubleshooting purposes but also for auditing and other stuff. Depending on what information gets logged, we can use this data for intelligent analytics systems to come up with predictive analytical models. For example, in an e-commerce landscape we can establish customer buying patterns based on the transaction history logs for its customers. Nowadays, with the advent of tools like apache spark,flume we can process huge chunks of log data in matter of milliseconds. This post is about how to use the logback framework in order to store the application log data in a relational database such as postgre. In a later blog, I would describe how to store log data in a no-sql db such as couchbase.

Logback dependencies

In order to use logback framework, you need to include the following maven dependencies in your project object model(pom) file.

<dependencies>
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.0.13</version>
</dependency>
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-core</artifactId>
<version>1.0.13</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.5</version>
</dependency>

Now, you are ready to use logback api-s. To establish connection with Postgresql database, you need to use the postgresql jdbc jar in your projects classpath. Include the following maven dependency in the pom file

<groupId>org.postgresql</groupId>

<artifactId>postgresql</artifactId>

</dependency>

Logback Configuration

Logback framework uses logback configuration file to do all the logging work. The logback.xml needs to be present in the project’s classpath. In addition to Console Appender and File Appenders which are used widely, logback provides a powerful database appender to store application logs in a database. Here’s how you configure the out-of-the-box logback db appender in your logback configuration

<appender name=”db” class=”ch.qos.logback.classic.db.DBAppender”>
<connectionSource
class=”ch.qos.logback.core.db.DriverManagerConnectionSource”>
<driverClass>org.postgresql.Driver</driverClass>
<url>jdbc:postgresql://127.0.0.1:5432/testdb</url>
<user>myadmin</user>
<password>myadmin</password>
</connectionSource>
</appender>

The xml snippet above is quite self-explanatory and of-course simple enough. You can also write your own appenders if you want to tweak the default logback behavior. More on that in a later blog post.

Database Script

When you use the logback db appender, logback stores the data in the following three tables

logging_event

logging_event_exception and

logging_event_property

You can find the DDL scripts for creating the tables here.

You can provide your own mechanism of creating and maintaining jdbc connections towards the db; if you are concerned about the performance of logging data to db, then it’s recommended to use some sort of connection pooling along with the db appender. Take a look at what logback recommends.

Ok, now you are all set to use the appender in your application. Below is the code for the java class that uses logback to store log events to postgresql db.

public class App {

final static Logger log = LoggerFactory.getLogger(App.class);

public App () {

log.info(“Inside App Constructor…creating instance”+ DateFormat.getInstance().format(new Date()));

}

public static void main( String[] args )

{

log.warn(“main…”);

new App().writeToDB();

log.error(“Exception “, new RuntimeException(“Testing Exception”));

}

private void writeToDB() {