Amazon RDS

aws rds describe-db-engine-versions --engine postgres \
  --query 'DBEngineVersions[].{Version:EngineVersion,Family:DBParameterGroupFamily,Targets:ValidUpgradeTarget[].EngineVersion}' \
  --output table

# Default minor version for a major version in this Region
aws rds describe-db-engine-versions --engine postgres --engine-version 17 --default-only \
  --query 'DBEngineVersions[0].EngineVersion' --output text

export AWS_REGION=us-east-1

aws rds create-db-instance-read-replica \
  --db-instance-identifier bookshelf-pg-replica-1 \
  --source-db-instance-identifier bookshelf-pg \
  --db-instance-class db.m7g.large \
  --no-publicly-accessible

# Cross-Region replica: run against the destination Region and use the source ARN
aws rds create-db-instance-read-replica --region eu-west-1 \
  --db-instance-identifier bookshelf-pg-replica-eu \
  --source-db-instance-identifier arn:aws:rds:us-east-1:111122223333:db:bookshelf-pg \
  --kms-key-id alias/aws/rds

aws rds promote-read-replica --db-instance-identifier bookshelf-pg-replica-1

export AWS_REGION=us-east-1
export ACCOUNT_ID=$(aws sts get-caller-identity --query Account --output text)
SUBNETS="subnet-0aaa1111bbbb2222c subnet-0ddd3333eeee4444f"   # private subnets in two AZs
VPC_ID=vpc-0123456789abcdef0

aws rds create-db-subnet-group \
  --db-subnet-group-name bookshelf-db-subnets \
  --db-subnet-group-description "Private subnets for Bookshelf databases" \
  --subnet-ids $SUBNETS

DB_SG=$(aws ec2 create-security-group --group-name bookshelf-pg-sg \
  --description "Bookshelf PostgreSQL" --vpc-id "$VPC_ID" --query GroupId --output text)
APP_SG=sg-0abc123abc123abc1   # security group of the catalog-api tasks
aws ec2 authorize-security-group-ingress --group-id "$DB_SG" \
  --protocol tcp --port 5432 --source-group "$APP_SG"

aws rds create-db-parameter-group \
  --db-parameter-group-name bookshelf-pg17 \
  --db-parameter-group-family postgres17 \
  --description "Bookshelf PostgreSQL 17 settings"

aws rds modify-db-parameter-group --db-parameter-group-name bookshelf-pg17 \
  --parameters "ParameterName=rds.force_ssl,ParameterValue=1,ApplyMethod=immediate" \
               "ParameterName=log_min_duration_statement,ParameterValue=500,ApplyMethod=immediate"

aws rds create-db-instance \
  --db-instance-identifier bookshelf-pg \
  --engine postgres --engine-version 17 \
  --db-instance-class db.m7g.large \
  --allocated-storage 100 --max-allocated-storage 500 --storage-type gp3 \
  --storage-encrypted \
  --master-username postgres_admin --manage-master-user-password \
  --db-name catalog \
  --db-subnet-group-name bookshelf-db-subnets \
  --vpc-security-group-ids "$DB_SG" \
  --no-publicly-accessible \
  --multi-az \
  --enable-iam-database-authentication \
  --db-parameter-group-name bookshelf-pg17 \
  --backup-retention-period 14 \
  --preferred-backup-window 03:00-04:00 \
  --preferred-maintenance-window sun:04:30-sun:05:30 \
  --auto-minor-version-upgrade \
  --deletion-protection \
  --database-insights-mode advanced \
  --enable-performance-insights --performance-insights-retention-period 465 \
  --monitoring-interval 60 \
  --monitoring-role-arn "arn:aws:iam::${ACCOUNT_ID}:role/rds-monitoring-role" \
  --enable-cloudwatch-logs-exports postgresql upgrade \
  --copy-tags-to-snapshot \
  --tags Key=app,Value=bookshelf

aws rds wait db-instance-available --db-instance-identifier bookshelf-pg
aws rds describe-db-instances --db-instance-identifier bookshelf-pg \
  --query 'DBInstances[0].{Status:DBInstanceStatus,Endpoint:Endpoint.Address,Secret:MasterUserSecret.SecretArn}'

aws rds modify-db-instance --db-instance-identifier bookshelf-pg \
  --enable-iam-database-authentication --apply-immediately

-- PostgreSQL
CREATE USER catalog_app WITH LOGIN;
GRANT rds_iam TO catalog_app;
GRANT CONNECT ON DATABASE catalog TO catalog_app;
GRANT USAGE ON SCHEMA public TO catalog_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO catalog_app;

-- MySQL / MariaDB equivalent
-- CREATE USER 'catalog_app'@'%' IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS';

DBI_RESOURCE_ID=$(aws rds describe-db-instances --db-instance-identifier bookshelf-pg \
  --query 'DBInstances[0].DbiResourceId' --output text)
echo "$DBI_RESOURCE_ID"

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ConnectAsCatalogApp",
      "Effect": "Allow",
      "Action": "rds-db:connect",
      "Resource": "arn:aws:rds-db:us-east-1:111122223333:dbuser:db-ABCDEFGHIJKLMNOPQRSTUVWXYZ/catalog_app"
    }
  ]
}

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ReadManagedMasterSecret",
      "Effect": "Allow",
      "Action": ["secretsmanager:GetSecretValue", "secretsmanager:DescribeSecret"],
      "Resource": "arn:aws:secretsmanager:us-east-1:111122223333:secret:rds!db-*"
    }
  ]
}

HOST=$(aws rds describe-db-instances --db-instance-identifier bookshelf-pg \
  --query 'DBInstances[0].Endpoint.Address' --output text)
TOKEN=$(aws rds generate-db-auth-token --hostname "$HOST" --port 5432 --username catalog_app --region us-east-1)

PGPASSWORD="$TOKEN" psql "host=$HOST port=5432 dbname=catalog user=catalog_app sslmode=verify-full sslrootcert=global-bundle.pem"

<dependency>
  <groupId>io.awspring.cloud</groupId>
  <artifactId>spring-cloud-aws-starter-secrets-manager</artifactId>
</dependency>
<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <scope>runtime</scope>
</dependency>

spring:
  config:
    import: "aws-secretsmanager:bookshelf/catalog/db"
  datasource:
    url: jdbc:postgresql://bookshelf-pg.abcdefghijkl.us-east-1.rds.amazonaws.com:5432/catalog?sslmode=verify-full&sslrootcert=/etc/ssl/rds/global-bundle.pem
    username: ${username}
    password: ${password}
    hikari:
      maximum-pool-size: 10
      max-lifetime: 1500000        # 25 min, below any idle-connection timeout in the network path
      connection-timeout: 5000
  jpa:
    hibernate:
      ddl-auto: validate

<dependency>
  <groupId>software.amazon.jdbc</groupId>
  <artifactId>aws-advanced-jdbc-wrapper</artifactId>
  <!-- use the current release; pin it in your BOM or dependencyManagement -->
</dependency>

spring:
  datasource:
    driver-class-name: software.amazon.jdbc.Driver
    url: jdbc:aws-wrapper:postgresql://bookshelf-pg.abcdefghijkl.us-east-1.rds.amazonaws.com:5432/catalog?wrapperPlugins=iam,efm2&iamRegion=us-east-1&sslmode=verify-full&sslrootcert=/etc/ssl/rds/global-bundle.pem
    username: catalog_app          # no password: the iam plugin supplies a fresh token for each new connection
    hikari:
      maximum-pool-size: 10
      max-lifetime: 600000         # 10 min, so connections are replaced well before a token's age matters

package com.example.bookshelf.catalog;

import software.amazon.awssdk.regions.Region;
import software.amazon.awssdk.services.rds.RdsClient;
import software.amazon.awssdk.services.rds.RdsUtilities;

public final class RdsTokenProvider {

    private final RdsUtilities utilities = RdsClient.builder().region(Region.US_EAST_1).build().utilities();

    public String token(String hostname, int port, String username) {
        return utilities.generateAuthenticationToken(r -> r.hostname(hostname).port(port).username(username));
    }
}

SECRET_ARN=arn:aws:secretsmanager:us-east-1:111122223333:secret:bookshelf/catalog/db-AbCdEf

aws rds create-db-proxy \
  --db-proxy-name bookshelf-pg-proxy \
  --engine-family POSTGRESQL \
  --auth "AuthScheme=SECRETS,SecretArn=${SECRET_ARN},IAMAuth=REQUIRED" \
  --role-arn "arn:aws:iam::${ACCOUNT_ID}:role/bookshelf-rds-proxy-role" \
  --vpc-subnet-ids $SUBNETS \
  --vpc-security-group-ids "$DB_SG" \
  --require-tls

aws rds register-db-proxy-targets \
  --db-proxy-name bookshelf-pg-proxy \
  --db-instance-identifiers bookshelf-pg

aws rds describe-db-proxies --db-proxy-name bookshelf-pg-proxy \
  --query 'DBProxies[0].{Status:Status,Endpoint:Endpoint}'

aws rds create-db-snapshot --db-instance-identifier bookshelf-pg \
  --db-snapshot-identifier bookshelf-pg-pre-release
aws rds wait db-snapshot-available --db-snapshot-identifier bookshelf-pg-pre-release

# Restore a snapshot into a new instance
aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier bookshelf-pg-restored \
  --db-snapshot-identifier bookshelf-pg-pre-release \
  --db-subnet-group-name bookshelf-db-subnets \
  --vpc-security-group-ids "$DB_SG" --no-publicly-accessible

# Point-in-time restore to the latest restorable time, or to a specific UTC timestamp
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier bookshelf-pg \
  --target-db-instance-identifier bookshelf-pg-pitr \
  --use-latest-restorable-time
# --restore-time 2026-10-01T09:30:00Z

# Copy a snapshot to another Region (run in the destination Region)
aws rds copy-db-snapshot --region eu-west-1 \
  --source-db-snapshot-identifier arn:aws:rds:us-east-1:111122223333:snapshot:bookshelf-pg-pre-release \
  --target-db-snapshot-identifier bookshelf-pg-pre-release-eu \
  --source-region us-east-1 --kms-key-id alias/aws/rds

aws rds create-db-snapshot --db-instance-identifier legacy-db --db-snapshot-identifier legacy-db-plain
aws rds copy-db-snapshot --source-db-snapshot-identifier legacy-db-plain \
  --target-db-snapshot-identifier legacy-db-encrypted --kms-key-id alias/aws/rds
aws rds restore-db-instance-from-db-snapshot --db-instance-identifier legacy-db-enc \
  --db-snapshot-identifier legacy-db-encrypted --storage-encrypted

aws rds create-blue-green-deployment \
  --blue-green-deployment-name bookshelf-pg-17-to-18 \
  --source "arn:aws:rds:us-east-1:${ACCOUNT_ID}:db:bookshelf-pg" \
  --target-engine-version 18 \
  --target-db-parameter-group-name bookshelf-pg18

BGD=$(aws rds describe-blue-green-deployments \
  --filters Name=blue-green-deployment-name,Values=bookshelf-pg-17-to-18 \
  --query 'BlueGreenDeployments[0].BlueGreenDeploymentIdentifier' --output text)

aws rds switchover-blue-green-deployment \
  --blue-green-deployment-identifier "$BGD" --switchover-timeout 300

aws rds delete-blue-green-deployment \
  --blue-green-deployment-identifier "$BGD" --no-delete-target

aws rds modify-db-instance --db-instance-identifier bookshelf-pg \
  --engine-lifecycle-support open-source-rds-extended-support-disabled
aws rds describe-db-instances --db-instance-identifier bookshelf-pg \
  --query 'DBInstances[0].EngineLifecycleSupport'