Storing application logs into Postgresql database using Log4j2

Within application, logging is common to provide information about ongoing process, issues and state that helps us understand the processes that are running behind the scenes. These logs provide insights into application state and also help troubleshoot the issues when application is not running as expected. 

Logging into a text file or console is common. But as the application grows and there is a need to further analyze the logs, it is efficient to store the logs into database where information can be searched using query languages and further insights can be generated using power of database technologies. For this, the first step is to log the application into the database. 

In this post, I will go over how to store application logs into database. The post assumes that application is already using Log4j2 to log applications such as logging in console window or in text file a Java application. The code uses a maven project using InteliJ IDEA and postgresql database server on a windows machine. I will be using Java Database Connectivity (JDBC) for connecting to database. 

I will create a database named 'demo' with and create a table named 'log'. using query tool in pgadmin. 

--Create a database
CREATE DATABASE demo;

--Create a table
CREATE TABLE log(
   eventdate timestamp  DEFAULT NULL,
    logger varchar(100),
    level varchar(100),
    message varchar(100),
    exception varchar(100)
);

The log table has columns for eventdata, logger, level, message and exception. 

The database 'demo' can be connected wtih username and password. So I setup a user 'demouser' with a passoword phrase 'demopassword' and grant all the previllege to this user for log table. 

--Create a user
CREATE USER demouser with ECNCRYPTED PASSWORD 'demopassword';

--Grant privilege to user on log table
GRANT ALL PRIVILEGES ON TABLE log TO demouser;

In log4j2 configuration, add appender for jdbc with name of the appender as 'databaseAppender'.

<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="WARN">
  <Appenders>
    <Console name="Console" target="SYSTEM_OUT">
      <PatternLayout pattern="%d{HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n"/>
    </Console>
   
   <Jdbc name="databaseAppender" tableName="log">
      <DriverManager connectionString="jdbc:postgresql://localhost:5432/demo" driverClassName="org.postgresql.Driver" username="demouser" password="demopassword"  />
      <Column name="eventdate" isEventTimestamp="true" />
     <Column name="level" pattern="%level" isUnicode="false" />
     <Column name="logger" pattern="%logger{36}" isUnicode="false"/>
      <Column name="message" pattern="%message" isUnicode="false" />
     <Column name="exception" pattern="%exception"  isUnicode="false" />
    </Jdbc>
      
  </Appenders>
  <Loggers>
    <Logger name="Main.Bootstrap" level="trace" additivity="false">
      <AppenderRef ref="Console"/>
     <AppenderRef ref="databaseAppender"/>
    </Logger>
    <Root level="trace">
      <AppenderRef ref="Console"/>
     <AppenderRef ref="databaseAppender"/>
    </Root>
  </Loggers>
</Configuration>

In the configuration, I am specifying the name of the table where the logs will be inserted. In this case, the name of that table is 'log'. The connectionstring property includes the location and source of the database, the jdbc driver which is specific to the postgresql database and name of the database. The column name property is used to map the log message to table columns.  

In the logger section, I am using 'AppenderRef' to also log the information to database (i.e., to  databaseAppender).

Since the connection requires a postgresql driver, I am going to add depenency as below as in any Maven project:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.10</version>
</dependency>

Now that I have set up the logging, I will now focus on application that will utilize the logger. The application code is simple.I have a method where I will log some information, warning and exception to demonstrate how different logs are inserted into database table.  

public static void main(String[] args) throws Exception {
    logger.info("Starting the demo. ");

    //Lets throw exception but before that let's warn.
    logger.warn("Exception must occur now.");
    try {
        throw new Exception("Throwing exception for demonstration");
    }
    catch(Exception e){
        logger.error("Exception occured.", e.getMessage(), e);
    }

    logger.info("Ending the demo. ");
}

When I run the application, the applicaiton logs information and exception in console. Since I have configured log4j2 to append the information into database, the logs are also visible in database table. 

The datbase table shows the log level, the class that created the log, message and actual exception, when applicable.