Is there a way to log correlation id (sent from microservice) in Postgres

user_4341933 0
Viewed 216

I am setting up the logging for a project and was wondering, whether it is possible to send id to postges database. Later I would collect all logs with fluentd and use the efk(elastic search, fluentd, kibana) stack to look through the logs. That is why it would be very helpful if can set the id in the database logs.

spring-boot logging postgresql
2 Answers

You can have your connection set the application_name to something that includes the id you want, and then configure your logging to include the application_name field. Or you could include the id in an SQL comment, something like select /* I am number 6 */ whatever from whereever. But then the id will only be visible for log messages that include the sql. (The reason for putting the comment after the select keyword is that some clients will strip out leading comments so the serve never sees them)

user_17212390

Thank you @jjane, for giving me such a great idea. After some googling I found a page describing how to intercept hibernate logs then I made some more research on how to add the inspector to spring-boot and this is the solution I came up with:

@Component
public class SqlCommentStatementInspector
    implements StatementInspector {

    private static final Logger LOGGER = LoggerFactory
        .getLogger(
            SqlCommentStatementInspector.class
        );

    private static final Pattern SQL_COMMENT_PATTERN = Pattern
        .compile(
            "\\/\\*.*?\\*\\/\\s*"
        );

    @Override
    public String inspect(String sql) {
        LOGGER.info(
            "Repo log, Correlation_id:"+ MDC.get(Slf4jMDCFilter.MDC_UUID_TOKEN_KEY),
            sql
        );

        return String.format("/*Correlation_id: %s*/", MDC.get(Slf4jMDCFilter.MDC_UUID_TOKEN_KEY)) + SQL_COMMENT_PATTERN
            .matcher(sql)
            .replaceAll("");
    }
}

and its configuration:

@Configuration
public class HibernateConfiguration implements HibernatePropertiesCustomizer {
    @Autowired
    private SqlCommentStatementInspector myInspector;

    @Override
    public void customize(Map<String, Object> hibernateProperties) {
       hibernateProperties.put("hibernate.session_factory.statement_inspector", myInspector);
    }
}
user_43419330
Related