Jooq + hikariCP + Postgres - cannot handle failovers

Question

I have a spring boot app that read/writes to postgres database. I use jooq and hikariCP to manage the database connections. My apps is connected to a patroni cluster consisting of two Postgresql 14.5 instances - one is the master and the other one is a read-only replica.

When the service is processing data and I trigger a failover in the database - killing the leader, choosing new leader, then changing the old leader to a replica - I start getting exceptions like

with\norg.jooq.exception.DataAccessException: SQL [delete from \"public\".\"my_table\" where \"public\".\"my_table\".\"username\" = ?]; ERROR: cannot execute DELETE in a read-only transaction

it looks like a hikariCP/jdbc driver issue where it is still using the connections to the old-master-now-replica instead of evicting them and creating new connections to the new leader.

How to solve it ?

My configuration looks like this:

org.jooq:jooq:3.16.10
org.postgresql:postgresql:42.5.0
org.jooq:jooq-postgres-extensions:3.16.10
com.zaxxer:HikariCP:4.0.3

spring:
  main:
    allow-bean-definition-overriding: true
    banner-mode: "off"
  jooq:
    sql-dialect: Postgres
  jpa:
    open-in-view: false
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: org.postgresql.Driver
    url: "jdbc:postgresql://my-db-cluster:5432/my-database?tcpKeepAlive=true&ApplicationName=my-app"
    username: ${DATASOURCE_USERNAME}
    password: ${DATASOURCE_PASSWORD}
    hikari:
      minimumIdle: 0
      maximumPoolSize: 10
      auto-commit: false
  autoconfigure:
    exclude: org.springframework.boot.autoconfigure.r2dbc.R2dbcAutoConfiguration

Answer 1

I found a solution by adding &targetServerType=primary to jdbc url as docs states: https://jdbc.postgresql.org/documentation/use/