Automate Interval Partitioning, maintenance and monitoring in Amazon RDS for PostgreSQL and Amazon Aurora PostgreSQL

This template deploys a Lambda function that is triggered by an EventBridge Schedule. The Lambda function monitors the PostgreSQL cron job logs and alert when failures are found by sending notification using Amazon SNS.

Learn more about this pattern at Serverless Land Patterns: https://serverlessland.com/patterns/

Important: this application uses various AWS services and there are costs associated with these services after the Free Tier usage - please see the AWS Pricing page for details. You are responsible for any AWS costs incurred. No warranty is implied in this example.

Requirements

• Create an AWS account if you do not already have one and log in. The IAM user that you use must have sufficient permissions to make necessary AWS service calls and manage AWS resources.
• AWS CLI installed and configured
• Git Installed
• AWS Serverless Application Model (AWS SAM) installed

Deployment Instructions

1. Create a new directory, navigate to that directory in a terminal and clone the GitHub repository:

   git clone https://github.com/aws-samples/aws-postgresql-interval-partitioning.git
   

2. Change directory to the pattern directory:

   cd aws-postgresql-interval-partitioning/check-cron-job-status
   

3. From the command line, use AWS SAM to deploy the AWS resources for the pattern as specified in the template.yml file:

   sam deploy --guided
   

4. During the prompts (Sample below):

* Stack Name [sam-app]: check-cron-job-status
* AWS Region [us-east-1]: us-west-2
* Parameter pNotificationEmail [example@example.com]: notifydba@xyz.com
* Parameter pVpc []: vpc-01234567890awsvpc
* Parameter pPrivateSubnet1 []: subnet-01234567890abcdef
* Parameter pPrivateSubnet2 []: subnet-01234567890ghijkl
* Parameter pApgClusterName [demopg]: ec2-db-aurorapgcluster
* Parameter pCronHist [2]:
* Parameter pDbHost [demopg.abcdefgh.us-east-1.rds.amazonaws.com]: vpc-ec2-db-aurorapgcluster.cluster-abcdefgh.us-west-2.rds.amazonaws.com
* Parameter pDbPort [5432]:
#Shows you resources changes to be deployed and require a 'Y' to initiate deploy
* Confirm changes before deploy [y/N]: y
#SAM needs permission to be able to create roles to connect to the resources in your template
* Allow SAM CLI IAM role creation [Y/n]: Y
#Preserves the state of previously provisioned resources when an operation fails
* Disable rollback [y/N]: N
* Save arguments to configuration file [Y/n]: Y
* SAM configuration file [samconfig.toml]:
* SAM configuration environment [default]:

Once you have run `sam deploy -guided` mode once and saved arguments to a configuration file (samconfig.toml), you can use `sam deploy` in future to use these defaults.

1. Note the outputs from the SAM deployment process. These contain the resource names and/or ARNs which are used for testing.

How it works

This SAM template creates a necessary AWS Services for Lambda function to check the details on PostgreSQL cron.job_run_details table

Testing

After running sam deploy, go to the Lambda console. Find your Lambda function, and open the CloudWatch logs. You will see the new events based on the event schedule configured.

Cleanup

1. Delete the stack

   aws cloudformation delete-stack --stack-name STACK_NAME

2. Confirm the stack has been deleted

   aws cloudformation list-stacks --query "StackSummaries[?contains(StackName,'STACK_NAME')].StackStatus"

---

Security

See CONTRIBUTING for more information.

Copyright 2021 Amazon.com, Inc. or its affiliates. All Rights Reserved.

SPDX-License-Identifier: MIT-0