Amazon RDS Read Replica configuration Postgres database from an spring boot application deployed on PCF?

Question

Hi All currently we are have deployed our springboot code to pcf which is running on aws.

we are using aws database - where we have cup service and VCAP_SERVICES which hold the parameter of db.

Below our configuration to get datasource

 @Bean
    public DataSource dataSource() {
        if (dataSource == null) {
            dataSource = connectionFactory().dataSource();
            configureDataSource(dataSource);
        }
        return dataSource;
    }

    @Bean
    public JdbcTemplate jdbcTemplate() {
            return new JdbcTemplate(dataSource());
        }

    private void configureDataSource(DataSource dataSource) {
        org.apache.tomcat.jdbc.pool.DataSource tomcatDataSource = asTomcatDatasource(dataSource);
        tomcatDataSource.setTestOnBorrow(true);
        tomcatDataSource.setValidationQuery("SELECT 1");
        tomcatDataSource.setValidationInterval(30000);
        tomcatDataSource.setTestWhileIdle(true);
        tomcatDataSource.setTimeBetweenEvictionRunsMillis(60000);
        tomcatDataSource.setRemoveAbandoned(true);
        tomcatDataSource.setRemoveAbandonedTimeout(60);
        tomcatDataSource.setMaxActive(Environment.getAsInt("MAX_ACTIVE_DB_CONNECTIONS", tomcatDataSource.getMaxActive()));
    }

    private org.apache.tomcat.jdbc.pool.DataSource asTomcatDatasource(DataSource dataSource) {
        Objects.requireNonNull(dataSource, "There is no DataSource configured");
        DataSource targetDataSource = ((DelegatingDataSource)dataSource).getTargetDataSource();
        return (org.apache.tomcat.jdbc.pool.DataSource) targetDataSource;
    }

Now when we have read replicas created , what configuration do i need to modify so our spring boot application uses the read replicas?

is Just @Transactional(readOnly = true) on the get call is enough - that it will be automatically taken care? or do i need to add some more configuration

@Repository
public class PostgresSomeRepository implements SomeRepository {
  

    @Autowired
    public PostgresSomeRepository(JdbcTemplate jdbcTemplate, RowMapper<Consent> rowMapper) {
        this.jdbcTemplate = jdbcTemplate;
        this.rowMapper = rowMapper;
    }

    @Override
    @Transactional(readOnly = true)
    public List<SomeValue> getSomeGetCall(List<String> userIds, String applicationName, String propositionName, String since, String... types) {
      //Some Logic
        try {
            return jdbcTemplate.query(sql, rowMapper, paramList.toArray());
        } catch (DataAccessException ex) {
            throw new ErrorGettingConsent(ex.getMessage(), ex);
        }
    }  
}

Note:we have not added any spring aws jdbc dependency

Answer 1

Let's assume the cloud service name is my_db.

1. Map the cloud service to the application config appication-cloud.yml used by default in the CF (BTW this is better than using the connector because you can customize the datasource)

spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    # my_db
    url: ${vcap.services.my_db.credentials.url} 
    username: ${vcap.services.my_db.credentials.username} 
    password: ${vcap.services.my_db.credentials.password} 
    hikari:
      poolName: Hikari
      auto-commit: false
      data-source-properties:
        cachePrepStmts: true
        prepStmtCacheSize: 250
        prepStmtCacheSqlLimit: 2048
        useServerPrepStmts: true
  jpa:
    generate-ddl: false
    show-sql: true

2. put the service to the application manifest.yml:

---
applications:
  - name: my-app
    env:
      SPRING_PROFILES_ACTIVE: "cloud" # by default
    services:
      - my_db