Failover behaviour with Aurora Postgres + Spring Boot + HikariConnectionPool

[hitsuyagaa]

I've been trying to make this setup work but unfortunately am not quite understanding what's happening. I am currently testing multiple failovers. Our understanding is, that you can achieve failover handling with aws-advanced-jdbc-wrapper alone without modifying code.
We have a postgres aurora database with 3 nodes:

• pg-reader1
• pg-reader2
• pg-writer

(I know the naming isn't good but that's not something I chose)

So first failover seems to work, we get this:

Caused by: software.amazon.jdbc.plugin.failover.FailoverFailedSQLException: Unable to establish SQL connection to the writer instance.
	at software.amazon.jdbc.plugin.failover.FailoverConnectionPlugin.processFailoverFailure(FailoverConnectionPlugin.java:478)
	at software.amazon.jdbc.plugin.failover.FailoverConnectionPlugin.failoverWriter(FailoverConnectionPlugin.java:655)
	at software.amazon.jdbc.plugin.failover.FailoverConnectionPlugin.failover(FailoverConnectionPlugin.java:556)
	at software.amazon.jdbc.plugin.failover.FailoverConnectionPlugin.pickNewConnection(FailoverConnectionPlugin.java:727)
	at software.amazon.jdbc.plugin.failover.FailoverConnectionPlugin.dealWithOriginalException(FailoverConnectionPlugin.java:508)
	at software.amazon.jdbc.plugin.failover.FailoverConnectionPlugin.execute(FailoverConnectionPlugin.java:226)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$execute$5(ConnectionPluginManager.java:334)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$3(ConnectionPluginManager.java:267)
	at software.amazon.jdbc.ConnectionPluginManager.executeWithTelemetry(ConnectionPluginManager.java:240)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$4(ConnectionPluginManager.java:267)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$2(ConnectionPluginManager.java:268)
	at software.amazon.jdbc.plugin.AuroraConnectionTrackerPlugin.execute(AuroraConnectionTrackerPlugin.java:122)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$execute$5(ConnectionPluginManager.java:334)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$3(ConnectionPluginManager.java:267)
	at software.amazon.jdbc.ConnectionPluginManager.executeWithTelemetry(ConnectionPluginManager.java:240)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$4(ConnectionPluginManager.java:267)
	at software.amazon.jdbc.ConnectionPluginManager.executeWithSubscribedPlugins(ConnectionPluginManager.java:230)
	at software.amazon.jdbc.ConnectionPluginManager.execute(ConnectionPluginManager.java:331)
	at software.amazon.jdbc.util.WrapperUtils.executeWithPlugins(WrapperUtils.java:244)
	at software.amazon.jdbc.wrapper.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:224)
	... 

After which the application has a few more errors but after 3-4 minutes all is great again and everything works.
I know it isn't particularly likely to have 2 somewhat simultaneous failovers but I would have expected this to work nevertheless.
Now after another failover is triggered in AWS rds console, we get the following error:

org.postgresql.util.PSQLException: ERROR: cannot execute SELECT FOR UPDATE in a read-only transaction
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:502)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:194)
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:137)
	at software.amazon.jdbc.wrapper.PreparedStatementWrapper.lambda$executeQuery$15(PreparedStatementWrapper.java:230)
	at software.amazon.jdbc.plugin.DefaultConnectionPlugin.execute(DefaultConnectionPlugin.java:130)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$execute$5(ConnectionPluginManager.java:334)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$0(ConnectionPluginManager.java:263)
	at software.amazon.jdbc.ConnectionPluginManager.executeWithTelemetry(ConnectionPluginManager.java:240)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$1(ConnectionPluginManager.java:263)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$2(ConnectionPluginManager.java:268)
	at software.amazon.jdbc.plugin.efm2.HostMonitoringConnectionPlugin.execute(HostMonitoringConnectionPlugin.java:177)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$execute$5(ConnectionPluginManager.java:334)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$3(ConnectionPluginManager.java:267)
	at software.amazon.jdbc.ConnectionPluginManager.executeWithTelemetry(ConnectionPluginManager.java:240)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$4(ConnectionPluginManager.java:267)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$2(ConnectionPluginManager.java:268)
	at software.amazon.jdbc.plugin.failover.FailoverConnectionPlugin.execute(FailoverConnectionPlugin.java:222)
...

Seemingly somewhere, it retrieves a read only connection and continues to work with that one forever after.
Our configuration is like this:

db:
  app:
    datasource:
      driverClassName: software.amazon.jdbc.Driver
      idleTimeout: 1000
      data-source-properties:
        wrapperPlugins: auroraConnectionTracker,failover,efm2,awsSecretsManager
        wrapperDialect: aurora-pg
        wrapperLoggerLevel: INFO
        secretsManagerSecretId: ....
        secretsManagerRegion: .... 
        failoverTimeoutMs: 30000
        failoverMode: strict-writer
        failoverClusterTopologyRefreshRateMs: 250
        failureDetectionInterval: 2000
        failureDetectionTime: 10000
        failureDetectionEnabled: true
        enableClusterAwareFailover: true
        socketTimeout: 30
      connection-timeout: 60000
      exception-override-class-name: software.amazon.jdbc.util.HikariCPSQLException
      url: ${APP_DB_JDBC_URL:}
      jdbcUrl: ${APP_DB_JDBC_URL:}
      username: ${APP_DB_USER:}
      password: ${APP_DB_SECRET:}

Are we doing something wrong? Is there something else that needs to be configured? I am a bit confused as to where the read-only connections are coming from. Is this possibly some weird behaviour with internal rds aws failover? Since in the real world, in case of an AZ failure, there would be no more connectivity to the database. In this case, the connection would still be reachable but not be a writer anymore.

[hitsuyagaa]

The problem was that we use a R53 dns entry pointing to the cluster endpoint. As such you need to specify the clusterInstanceHostPattern to discover the other endpoints. After configuring that, the driver could detect the nodes and properly failover to the writer.