Amazon Aurora PostgreSQL
Prerequisites
The Amazon Aurora PostgreSQL is a manage database hosted on aws, which can then be used as the L7|ESP backend database
AWS Subscription
CLI tools
To install AWS aurora database you must have:
An AWS account to deploy to
Access keys for the AWS account (see Managing access keys for IAM users)
The awscli command line tools will be used to programmatically access the Kubernetes cluster and the AWS account. To install the CLI tools, follow the link below and choose the correct operating sytem:
Infrastructure as Code
CloudFormation Template
The latest CloudFormation template is as follows:
CloudFormation template
---
AWSTemplateFormatVersion: 2010-09-09
Description: A basic CloudFormation template for an RDS Aurora cluster.
Parameters:
DatabaseInstanceType:
Default: db.r5.large
AllowedValues:
- db.r5.large
- db.r5.xlarge
- db.r5.2xlarge
- db.r5.4xlarge
- db.r5.8xlarge
Description: The instance type to use for the database.
Type: String
DBEngineVersion:
Default: 13.7
AllowedValues:
- 11.16
- 12.11
- 13.7
- 14.3
Description: Select Database Engine Version
Type: String
DatabasePassword:
AllowedPattern: "[a-zA-Z0-9]+"
ConstraintDescription: must contain only alphanumeric characters.
Description: The database admin account password.
MaxLength: '41'
MinLength: '8'
NoEcho: 'true'
Type: String
Default: "xekyxwxcu2mzy1KXA"
DatabaseUsername:
AllowedPattern: "[a-zA-Z0-9]+"
ConstraintDescription: must contain only alphanumeric characters.
Description: The database admin account user name.
MaxLength: '16'
MinLength: '1'
Type: String
Default: "admin"
DatabaseBackupRetentionPeriod:
Type: String
Default: 7
AllowedValues:
- 1
- 7
Default: 7
Description: The database backup retention period in days.
DatabaseSubnets:
Description: The subnets to place database instances in.
Type: List<AWS::EC2::Subnet::Id>
Default: "subnet-09c7fe8aea62f0361, subnet-02206efa4f182c7fb"
DatabaseSecurityGroups:
Type: List<AWS::EC2::SecurityGroup::Id>
Description: Security groups to apply to the RDS cluster.
Default: "sg-0baeabc6d976c41cf"
Metadata:
AWS::CloudFormation::Interface:
ParameterGroups:
- Label:
default: Database Configuration
Parameters:
- DatabaseInstanceType
- DatabaseName
- DatabaseUsername
- DatabasePassword
- DatabaseSubnets
- DatabaseSecurityGroups
- DatabaseBackupRetentionPeriod
- DBEngineVersion
ParameterLabels:
DatabaseInstanceType:
default: Database Instance Type
DatabasePassword:
default: Database Password
DatabaseUsername:
default: Database Username
DatabaseBackupRetentionPeriod:
default: Database Backup Retention Period
DatabaseSubnets:
default: Database Subnets
DatabaseSecurityGroups:
default: Database Security Groups
Resources:
StackAlarmTopic:
Type: AWS::SNS::Topic
Properties:
DisplayName: Stack Alarm Topic
DatabaseSubnetGroup:
Type: AWS::RDS::DBSubnetGroup
Properties:
DBSubnetGroupDescription: CloudFormation managed DB subnet group.
SubnetIds:
Ref: DatabaseSubnets
DatabaseCluster:
Type: AWS::RDS::DBCluster
Properties:
Engine: aurora-postgresql
EngineVersion:
Ref: DBEngineVersion
MasterUsername:
Ref: DatabaseUsername
MasterUserPassword:
Ref: DatabasePassword
BackupRetentionPeriod:
Ref: DatabaseBackupRetentionPeriod
PreferredBackupWindow: 02:00-03:00
PreferredMaintenanceWindow: mon:03:00-mon:04:00
DBSubnetGroupName:
Ref: DatabaseSubnetGroup
VpcSecurityGroupIds:
Ref: DatabaseSecurityGroups
DatabasePrimaryInstance:
Type: AWS::RDS::DBInstance
Properties:
Engine: aurora-postgresql
EngineVersion:
Ref: DBEngineVersion
DBClusterIdentifier:
Ref: DatabaseCluster
DBInstanceClass:
Ref: DatabaseInstanceType
DBSubnetGroupName:
Ref: DatabaseSubnetGroup
DatabaseReplicaInstance:
Type: AWS::RDS::DBInstance
Properties:
Engine: aurora-postgresql
EngineVersion:
Ref: DBEngineVersion
DBClusterIdentifier:
Ref: DatabaseCluster
DBInstanceClass:
Ref: DatabaseInstanceType
DBSubnetGroupName:
Ref: DatabaseSubnetGroup
DatabasePrimaryCPUAlarm:
Type: AWS::CloudWatch::Alarm
Properties:
AlarmDescription: Primary database CPU utilization is over 80%.
Namespace: AWS/RDS
MetricName: CPUUtilization
Unit: Percent
Statistic: Average
Period: 300
EvaluationPeriods: 2
Threshold: 80
ComparisonOperator: GreaterThanOrEqualToThreshold
Dimensions:
- Name: DBInstanceIdentifier
Value:
Ref: DatabasePrimaryInstance
AlarmActions:
- Ref: StackAlarmTopic
InsufficientDataActions:
- Ref: StackAlarmTopic
DatabaseReplicaCPUAlarm:
Type: AWS::CloudWatch::Alarm
Properties:
AlarmDescription: Replica database CPU utilization is over 80%.
Namespace: AWS/RDS
MetricName: CPUUtilization
Unit: Percent
Statistic: Average
Period: 300
EvaluationPeriods: 2
Threshold: 80
ComparisonOperator: GreaterThanOrEqualToThreshold
Dimensions:
- Name: DBInstanceIdentifier
Value:
Ref: DatabaseReplicaInstance
AlarmActions:
- Ref: StackAlarmTopic
InsufficientDataActions:
- Ref: StackAlarmTopic
DatabasePrimaryMemoryAlarm:
Type: AWS::CloudWatch::Alarm
Properties:
AlarmDescription: Primary database freeable memory is under 700MB.
Namespace: AWS/RDS
MetricName: FreeableMemory
Unit: Bytes
Statistic: Average
Period: 300
EvaluationPeriods: 2
Threshold: 700000000
ComparisonOperator: LessThanOrEqualToThreshold
Dimensions:
- Name: DBInstanceIdentifier
Value:
Ref: DatabasePrimaryInstance
AlarmActions:
- Ref: StackAlarmTopic
InsufficientDataActions:
- Ref: StackAlarmTopic
DatabasePrimaryMemoryAlarm:
Type: AWS::CloudWatch::Alarm
Properties:
AlarmDescription: Replica database freeable memory is under 700MB.
Namespace: AWS/RDS
MetricName: FreeableMemory
Unit: Bytes
Statistic: Average
Period: 300
EvaluationPeriods: 2
Threshold: 700000000
ComparisonOperator: LessThanOrEqualToThreshold
Dimensions:
- Name: DBInstanceIdentifier
Value:
Ref: DatabaseReplicaInstance
AlarmActions:
- Ref: StackAlarmTopic
InsufficientDataActions:
- Ref: StackAlarmTopic
DatabasePrimaryReplicationAlarm:
Type: AWS::CloudWatch::Alarm
Properties:
AlarmDescription: Database replication latency is over 200ms.
Namespace: AWS/RDS
MetricName: AuroraReplicaLag
Unit: Milliseconds
Statistic: Average
Period: 300
EvaluationPeriods: 2
Threshold: 200
ComparisonOperator: GreaterThanOrEqualToThreshold
Dimensions:
- Name: DBInstanceIdentifier
Value:
Ref: DatabaseReplicaInstance
AlarmActions:
- Ref: StackAlarmTopic
DatabaseReplicaReplicationAlarm:
Type: AWS::CloudWatch::Alarm
Properties:
AlarmDescription: Database replication latency is over 200ms.
Namespace: AWS/RDS
MetricName: AuroraReplicaLag
Unit: Milliseconds
Statistic: Average
Period: 300
EvaluationPeriods: 2
Threshold: 200
ComparisonOperator: GreaterThanOrEqualToThreshold
Dimensions:
- Name: DBInstanceIdentifier
Value:
Ref: DatabaseReplicaInstance
AlarmActions:
- Ref: StackAlarmTopicParameters
DatabaseInstanceType(String) - The instance type to use for the database.DatabasePassword(String) - The database admin account password.DatabaseUsername(String) - The database admin account user name.DatabaseBackupRetentionPeriod:(Number) - The database backup retention period in days.DatabaseSubnets(String) - The subnets to place database instances in.DatabaseSecurityGroups(String) - Security groups to apply to the RDS cluster.
Parameters File
Create a parameters.json file for use when deploying the CloudFormation template:
CloudFormation template
[
{
"ParameterKey": "DatabaseInstanceType",
"ParameterValue": "db.r5.large"
},
{
"ParameterKey": "DatabasePassword",
"ParameterValue": "admin1229"
},
{
"ParameterKey": "DatabaseUsername",
"ParameterValue": "espadmin"
},
{
"ParameterKey": "DatabaseBackupRetentionPeriod",
"ParameterValue": "7"
},
{
"ParameterKey": "DatabaseSubnets",
"ParameterValue": "subnet-09c7fe8aea62f0361, subnet-02206efa4f182c7fb"
},
{
"ParameterKey": "DatabaseSecurityGroups",
"ParameterValue": "sg-0fd00914adf643024"
},
{
"ParameterKey": "DBEngineVersion",
"ParameterValue": "13.7"
}
]Provisioning
Create a profile for use with AWS CLI:
~$ aws configure --profile <profile> AWS Access Key ID [None]: AKIAIOSFODNN7EXAMPLE AWS Secret Access Key [None]: wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY Default region name [us-east-1]: Default output format [None]:
Switch to the correct AWS profile for current shell session:
~$ export AWS_PROFILE=<profile>
Deploy CloudFormation stack:
$ aws cloudformation deploy \ --stack-name aurora-example \ --template-file aws_aurora.yml \ --parameter-overrides file://aurora.parameters.json \ --capabilities CAPABILITY_NAMED_IAM
Validate Resource Creation
Log into the AWS Console and navigate to CloudFormation. Under “Stacks”, validate that the CloudFormation stack was created:
$ aws rds describe-db-clusters \ --db-cluster-identifier <cluster name>
Connect ESP instance to AWS Aurora
Once you validate that the aurora database have been successfully created on the aws portal, gather the database connection strings. You can find this under the database options of RDS portal.
Next, we can add the database connection string on the esp helm chart. on the values.yml file under the database section add the connection string informations:
database: |
{
"host": "${POSTGRES_HOST}",
"port": ${POSTGRES_PORT},
"name": "${POSTGRES_DBNAME}",
"user": "${POSTGRES_USER}",
"pass": "${POSTGRES_PASSWORD}",
"schema": "${POSTGRES_SCHEMA}",
"start_service": false
}Now deploy the helm chart (see helm documentation). Verify that esp is up and the database schema was successfully created