maciejwalkowiak commented 2 years ago

This issue collects all the requirements for RDS support in Spring Cloud AWS 3.0 (based on AWS SDK v2).

The key feature in RDS integration is support for read replicas:

use RDS read replicas for readOnly connections (transactions started with @Transactional(readOnly=true)

Additionally we need to support:

https://github.com/awspring/spring-cloud-aws/issues/320
failover

Both can be achieved with official drivers:

Regarding read replicas support, in addition to what has been offered in 2.x we must support:

ability to choose connection pool (in 2.x only Tomcat pool is supported)
ability to choose JDBC driver
Add support for passing query parameters to JDBC url #679
Aurora

To research:

should we support RDS proxy?
should we support Aurora Serverless?

srikanthnutigattu-tomo commented 2 years ago

Bump! Any plans to support this?

maciejwalkowiak commented 2 years ago

@srikanthnutigattu-tomo yes, still planned for 3.0, but the design work still pending. Is there something specific you are looking for in this integration or you would like to share ideas?

srikanthnutigattu-tomo commented 2 years ago

Specifically looking for support of Read replicas on AWS Postgres Aurora RDS. Currently all queries go to read/write, which no helping autoscaling of RDS.

maciejwalkowiak commented 2 years ago

Understood. Yes it is planned. For the time being, you can follow Vlad's tutorial: https://vladmihalcea.com/read-write-read-only-transaction-routing-spring/

frankjkelly commented 2 years ago

Excited for this!

mandarbk commented 2 years ago

Are there any plans to provide support for rotating credentials with spring cloud aws RDS ? Also anything to support TLS connections (RDS instances with ACM managed Certificates) ? Cloud AWS Documentation does not provide any view on either of these RDS features.

maciejwalkowiak commented 2 years ago

@mandarbk I only planned for supporting rotating credentials with RDS + Secrets Manager. I am missing knowledge about ACM Managed Crtificates.

areddy23 commented 2 years ago

Hi I am assuming spring-cloud-starter-aws-jdbc is a "io.awspring.cloud" version of "spring-cloud-aws-jdbc" ( 2.x.x version)

I feel this enhancement request should be backed in to 3.x.x if not already.

Steps to recreate:

Create a Primary RDS(abc) with Read Replicas in 2 diff regions us-east-1(abc-rr-1) and us-west-2(abc-rr-2). When you configure current "spring-cloud-aws-jdbc" to manage traffic to RR's it fails the reason is as follows.

In 2.x.x the library is attempting to "createInstance" and calls describe-db-instances which results below: (using cli to replicate the issue)

Class: AmazonRdsReadReplicaAwareDataSourceFactoryBean method: createInstance

"aws rds describe-db-instances --db-instance-identifier abc" Primary DB is yielding a list of RR associate with it across regions, please note it is returning DB Identifier for the RR in the same region and arn for the RR in diff region.

aws rds describe-db-instances --db-instance-identifier prod-pgsql

From result: "ReadReplicaDBInstanceIdentifiers": [ "abc-rr1", "arn:aws:rds:us-west-2:12345678910:db:abc-rr-2" ],

For the above list when li to do "describe-db-instances" for each one "arn:aws:rds:us-west-2:868769925241:db:prod-pgsql-pdx-replica" it is resulting in below error which is consistent with what we are receiving from the application.

aws rds describe-db-instances --db-instance-identifier arn:aws:rds:us-west-2:868769925241:db:prod-pgsql-pdx-replica

An error occurred (InvalidParameterValue) when calling the DescribeDBInstances operation: The parameter Filter: db-instance-id is not a valid identifier. Identifiers must begin with a letter; must contain only ASCII letters, digits, and hyphens; and must not end with a hyphen or contain two consecutive hyphens.

Class: AmazonRdsDataSourceFactoryBean method: createDataSourceInstance -> getDbInstance

DescribeDBInstancesResult describeDBInstancesResult = this.amazonRds.describeDBInstances((new DescribeDBInstancesRequest()).withDBInstanceIdentifier(identifier)); -> This results in failure as we are doing describe-db-instances with an arn.

Potential solution: getDbInstance method should identify if aws returned an ARN and convert the describe-db-instances to be following `aws rds describe-db-instances --db-instance-identifier abc-rr-2 --region us-west-2` instead of calling directly using ARN.

I am unable to open a PR as I am not sure where this module exists.

virajkanwade commented 2 years ago

For handling clusters -

can we add a flag cloud.aws.employee-db.isCluster=true

And in https://github.com/spring-attic/spring-cloud-aws/blob/e9e7b8cb158013464caa792b3e2de9cf7179678a/spring-cloud-aws-jdbc/src/main/java/org/springframework/cloud/aws/jdbc/rds/AmazonRdsReadReplicaAwareDataSourceFactoryBean.java#L73

Replace getDBInstance with getDBClusterInstance which uses this.amazonRds.describeDBClusters to get the writer instance and getReadReplicaIdentifiers to get read replica identifiers and rest of the code remains the same?

maciejwalkowiak commented 2 years ago

@virajkanwade we do not plan anymore to include JDBC module in 3.0 version. It MAY be included again in the future, but we prefer to wait for AWS to stabilize work on their JDBC drivers that partially cover what Spring Cloud AWS 2.x offered.

virajkanwade commented 2 years ago

so whats the solution for people who want to leverage the auto failover of Aurora RDS? Implement and maintain the convoluted work around available? When there could just be a small patch in the current version?

maciejwalkowiak commented 2 years ago

AWS has implemented/is implementing failover capabilities on the driver level:

for PostgreSQL: https://github.com/awslabs/aws-advanced-jdbc-wrapper
for MySQL: https://github.com/awslabs/aws-mysql-jdbc

areddy23 commented 2 years ago

Hi I am assuming spring-cloud-starter-aws-jdbc is a "io.awspring.cloud" version of "spring-cloud-aws-jdbc" ( 2.x.x version)

I feel this enhancement request should be backed in to 3.x.x if not already.

Steps to recreate:

Create a Primary RDS(abc) with Read Replicas in 2 diff regions us-east-1(abc-rr-1) and us-west-2(abc-rr-2). When you configure current "spring-cloud-aws-jdbc" to manage traffic to RR's it fails the reason is as follows.

In 2.x.x the library is attempting to "createInstance" and calls describe-db-instances which results below: (using cli to replicate the issue)

Class: AmazonRdsReadReplicaAwareDataSourceFactoryBean method: createInstance

"aws rds describe-db-instances --db-instance-identifier abc" Primary DB is yielding a list of RR associate with it across regions, please note it is returning DB Identifier for the RR in the same region and arn for the RR in diff region.

aws rds describe-db-instances --db-instance-identifier prod-pgsql

From result:

"ReadReplicaDBInstanceIdentifiers": [ "abc-rr1", "arn:aws:rds:us-west-2:12345678910:db:abc-rr-2" ],

For the above list when li to do "describe-db-instances" for each one "arn:aws:rds:us-west-2:868769925241:db:prod-pgsql-pdx-replica" it is resulting in below error which is consistent with what we are receiving from the application.

aws rds describe-db-instances --db-instance-identifier arn:aws:rds:us-west-2:868769925241:db:prod-pgsql-pdx-replica

An error occurred (InvalidParameterValue) when calling the DescribeDBInstances operation: The parameter Filter: db-instance-id is not a valid identifier. Identifiers must begin with a letter; must contain only ASCII letters, digits, and hyphens; and must not end with a hyphen or contain two consecutive hyphens.

Class: AmazonRdsDataSourceFactoryBean method: createDataSourceInstance -> getDbInstance

DescribeDBInstancesResult describeDBInstancesResult = this.amazonRds.describeDBInstances((new DescribeDBInstancesRequest()).withDBInstanceIdentifier(identifier)); -> This results in failure as we are doing describe-db-instances with an arn.

Potential solution: getDbInstance method should identify if aws returned an ARN and convert the describe-db-instances to be following

aws rds describe-db-instances --db-instance-identifier abc-rr-2 --region us-west-2 instead of calling directly using ARN. I am unable to open a PR as I am not sure where this module exists.

@maciejwalkowiak any recommendation on this ?

maciejwalkowiak commented 2 years ago

The recommendation is to use one of the solutions linked above as we are not adding JDBC module to 3.0 yet.

areddy23 commented 2 years ago

The recommendation is to use one of the solutions linked above as we are not adding JDBC module to 3.0 yet.

@maciejwalkowiak I am not sure if you read my summary, this is not entirely related to Fail over management which can be acheived by your suggestion. The above issue is with leveraging read replicas for an RDS that are cross regional(like we do using spring-cloud-aws-jdbc - @transaction annotation), I don't see how https://github.com/awslabs/aws-advanced-jdbc-wrapper can achieve that. Please let me know if I am missing anything ?

maciejwalkowiak commented 2 years ago

Oh apologies, I missed that I did not add 3rd link there.

Follow this approach for read & write replica.

https://vladmihalcea.com/read-write-read-only-transaction-routing-spring/

Vlad explained approach that we internally do in Spring Cloud AWS 2.x

virajkanwade commented 2 years ago

For anyone interested in keeping it simple and wishing to use the spring-cloud-aws-jdbc:

https://github.com/virajkanwade/spring-attic-spring-cloud-aws/commit/d0ed817e68f57722e764b83e93a98aabbdc16442

Also a compiled jar available. https://github.com/virajkanwade/spring-attic-spring-cloud-aws/releases/tag/v2.2.7.RELEASE

You can use local maven repo for using the jar.

NOTE: https://github.com/awslabs/aws-advanced-jdbc-wrapper was released very recently on 6th Oct 2022. It still has open issues related to transactions. Also, it currently only manages auto-failover. It still does not support read replica. So I personally feel its atleast months away from being useful.

The vladmihalcea approach is good. But it uses a custom JPA configuration file https://github.com/vladmihalcea/high-performance-java-persistence/blob/c75c3df14daf043d4014307e07efac4b5129739c/core/src/test/java/com/vladmihalcea/book/hpjp/util/spring/config/jpa/AbstractJPAConfiguration.java. It seems to hardcode additionalProperties. We would need additional logic to parse spring application.properties to dynamically configure these options.

virajkanwade commented 2 years ago

Submitted PR in correct repo https://github.com/awspring/spring-cloud-aws/pull/534

ghost commented 1 year ago

Regarding

should we support RDS proxy?

Yes please! Is there a temporary solution for using a proxy right now?

maciejwalkowiak commented 1 year ago

@roelmang you should be able to connect to RDS Proxy without Spring Cloud AWS? Perhaps you can explain what bits are problematic.

ghost commented 1 year ago

@roelmang you should be able to connect to RDS Proxy without Spring Cloud AWS? Perhaps you can explain what bits are problematic. @maciejwalkowiak

I was at first looking into overriding some beans from the library to fetch the rds proxy details, but opted for not using the library at all for datasource configuration. No issues configuring the datasource directly instead of using Spring Cloud AWS to autoconfigure it. Just would have been nice to be able to just replace the dbInstanceIdentifier instead of configuring the rds proxy jdbc url.

zeagord commented 1 year ago

@maciejwalkowiak - Are you planning to support IAM based auth for the RDS? I will be happy to support and implement if the work for 3.0 release has been started.

maciejwalkowiak commented 1 year ago

@zeagord there is a project from AWS that covers IAM auth: https://github.com/awslabs/aws-advanced-jdbc-wrapper

Or maybe you have something different in mind?

gnagy commented 11 months ago

Hi, any plans for r2dbc support?

maciejwalkowiak commented 11 months ago

Hi @gnagy. No. Perhaps you can ask AWS folks if they have any plans: https://github.com/awslabs/aws-advanced-jdbc-wrapper

avivunitq commented 10 months ago

@roelmang you should be able to connect to RDS Proxy without Spring Cloud AWS? Perhaps you can explain what bits are problematic.

native read-replica support with spring boot + rds proxy would be useful.

serhiikartashov commented 3 months ago

Hi folks! Have you made any progress with this task?

maciejwalkowiak commented 2 months ago

@serhiikartashov not yet and I'm not sure in the end if we will. Support for read replica should be eventually implemented on the spring boot level, the rest of the functionality is implemented in the official AWS driver

awspring / spring-cloud-aws

Integration with RDS #322

"aws rds describe-db-instances --db-instance-identifier abc" Primary DB is yielding a list of RR associate with it across regions, please note it is returning DB Identifier for the RR in the same region and arn for the RR in diff region.

From result: "ReadReplicaDBInstanceIdentifiers": [ "abc-rr1", "arn:aws:rds:us-west-2:12345678910:db:abc-rr-2" ],

For the above list when li to do "describe-db-instances" for each one "arn:aws:rds:us-west-2:868769925241:db:prod-pgsql-pdx-replica" it is resulting in below error which is consistent with what we are receiving from the application.

Potential solution: getDbInstance method should identify if aws returned an ARN and convert the describe-db-instances to be following `aws rds describe-db-instances --db-instance-identifier abc-rr-2 --region us-west-2` instead of calling directly using ARN.

"aws rds describe-db-instances --db-instance-identifier abc" Primary DB is yielding a list of RR associate with it across regions, please note it is returning DB Identifier for the RR in the same region and arn for the RR in diff region.

From result:

For the above list when li to do "describe-db-instances" for each one "arn:aws:rds:us-west-2:868769925241:db:prod-pgsql-pdx-replica" it is resulting in below error which is consistent with what we are receiving from the application.

Potential solution: getDbInstance method should identify if aws returned an ARN and convert the describe-db-instances to be following

awspring / spring-cloud-aws

Integration with RDS #322

"aws rds describe-db-instances --db-instance-identifier abc" Primary DB is yielding a list of RR associate with it across regions, please note it is returning DB Identifier for the RR in the same region and arn for the RR in diff region.

From result: "ReadReplicaDBInstanceIdentifiers": [ "abc-rr1", "arn:aws:rds:us-west-2:12345678910:db:abc-rr-2" ],

For the above list when li to do "describe-db-instances" for each one "arn:aws:rds:us-west-2:868769925241:db:prod-pgsql-pdx-replica" it is resulting in below error which is consistent with what we are receiving from the application.

Potential solution: getDbInstance method should identify if aws returned an ARN and convert the describe-db-instances to be following aws rds describe-db-instances --db-instance-identifier abc-rr-2 --region us-west-2 instead of calling directly using ARN.

"aws rds describe-db-instances --db-instance-identifier abc" Primary DB is yielding a list of RR associate with it across regions, please note it is returning DB Identifier for the RR in the same region and arn for the RR in diff region.

From result:

For the above list when li to do "describe-db-instances" for each one "arn:aws:rds:us-west-2:868769925241:db:prod-pgsql-pdx-replica" it is resulting in below error which is consistent with what we are receiving from the application.

Potential solution: getDbInstance method should identify if aws returned an ARN and convert the describe-db-instances to be following

Potential solution: getDbInstance method should identify if aws returned an ARN and convert the describe-db-instances to be following `aws rds describe-db-instances --db-instance-identifier abc-rr-2 --region us-west-2` instead of calling directly using ARN.