Schedule jobs for Amazon RDS and Aurora PostgreSQL using Lambda and Secrets Manager - AWS Prescriptive Guidance

Schedule jobs for Amazon RDS and Aurora PostgreSQL using Lambda and Secrets Manager

Created by Yaser Raja (AWS)

R Type: N/A

Source: Databases: Relational

Target: PostgreSQL on AWS

Created by: AWS

Environment: PoC or pilot

Technologies: Databases

Workload: Open-source

AWS services: Amazon RDS; AWS Lambda; AWS Secrets Manager

Summary

For on-premises databases and databases that are hosted on Amazon Elastic Compute Cloud (Amazon EC2) instances, database administrators often use the cron utility to schedule jobs.

For example, a job for data extraction or a job for data purging can easily be scheduled using cron. For these jobs, database credentials are typically either hard-coded or stored in a properties file. However, when you migrate to Amazon Relational Database Service (Amazon RDS) or Amazon Aurora PostgreSQL-Compatible Edition, you lose the ability to log in to the host instance to schedule cron jobs. 

This pattern describes how to use AWS Lambda and AWS Secrets Manager to schedule jobs for Amazon RDS for PostgreSQL and Aurora PostgreSQL-Compatible databases after migration.  

Prerequisites and limitations

Prerequisites

  • An active AWS account 

  • An Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible database

Limitations

Architecture

Source technology stack

This stack features jobs written in languages such as Bash, Python, and Java. Database credentials are stored in the properties file, and the job is scheduled using Linux cron.

Target technology stack

This stack has a Lambda function that uses the credentials stored in Secrets Manager to connect to the database and to perform the activity. The Lambda function is initiated at the scheduled interval by using Amazon CloudWatch Events.

Target architecture

Tools

  • AWS LambdaAWS Lambda is a compute service that lets you run code without provisioning or managing servers. AWS Lambda runs your code only when needed and scales automatically, from a few requests per day to thousands per second. You pay only for the compute time you consume; there is no charge when your code is not running. With AWS Lambda, you can run code for virtually any type of application or backend service with zero administration. AWS Lambda runs your code on a high-availability compute infrastructure and manages all the compute resources, including server and operating system maintenance, capacity provisioning and automatic scaling, code monitoring, and logging. All you need to do is provide your code in one of the languages that AWS Lambda supports.

  • CloudWatch EventsAmazon CloudWatch Events delivers a near real-time stream of system events that describe changes in AWS resources. Using simple rules that you can quickly set up, you can match events and route them to one or more target functions or streams. CloudWatch Events becomes aware of operational changes as they occur. It responds to these operational changes and takes corrective action as necessary, by sending messages to respond to the environment, activating functions, making changes, and capturing state information. You can also use CloudWatch Events to schedule automated actions that self-initiate at certain times using cron or rate expressions.

  • Secrets ManagerAWS Secrets Manager helps you protect secrets for accessing your applications, services, and IT resources. You can easily rotate, manage, and retrieve database credentials, API keys, and other secrets throughout their lifecycle. Users and applications retrieve secrets by calling Secrets Manager APIs, which eliminates the need to hard-code sensitive information in plain text. Secrets Manager offers secret rotation with built-in integration for Amazon RDS, Amazon Redshift, and Amazon DocumentDB. The service is extensible to other types of secrets, including API keys and OAuth tokens. Secrets Manager enables you to control access to secrets using fine-grained permissions and to audit secret rotation centrally for resources in the AWS Cloud, third-party services, and on-premises.

Epics

TaskDescriptionSkills required
Create a database user for the Lambda function.

It is a good practice to use separate database users for different parts of your application. If a separate database user already exists for your cron jobs, use that; otherwise, create a new database user. For more information about users and permissions, see the link in the References and Help section.

DBA
Store database credentials as a secret in Secrets Manager.

Follow the steps in the Secrets Manager tutorial. See the "Related resources" section for a link.

DBA, DevOps
TaskDescriptionSkills required
Choose a programming language supported by AWS Lambda.

See the "Related resources" section for a link to a list of supported languages.

Developer
Write the logic to fetch the database credentials from Secrets Manager.

For sample code, see the link in the "Related resources" section.

Developer
Write the logic to perform the scheduled database activity.

Migrate your existing code for the scheduling job that you're using on premises to the AWS Lambda function.

Developer
TaskDescriptionSkills required
Create the Lambda function deployment package.

This package contains the code and its dependencies. For details, see the link in the "Related resources" section.

Developer
Create the Lambda function.

In the AWS Lambda console, choose "Create function," enter a function name, choose the runtime environment, and then choose "Create function."

DevOps
Upload the deployment package.

Choose the Lambda function you created to open its configuration. You can write your code directly in the code section or upload your deployment package. To upload your package, go to the "Function code" section, choose the "Code entry type" to upload a .zip file, and then select the package.

DevOps
Configure the Lambda function per your requirements.

For example, you can set the “Timeout” parameter to the duration you expect your Lambda function to take. For more information about Lambda configurations, see the link in the "Related resources" section.

DevOps
Set permissions for the Lambda function role to access Secrets Manager.

For instructions, see the link in the "Related resources" section.

DevOps
Test the Lambda function.

Initiate the function manually to make sure it works as expected.

DevOps
TaskDescriptionSkills required
Create a rule to run your Lambda function on a schedule.

Schedule the Lambda function by using CloudWatch Events. For instructions, see the link in the "Related resources" section.

DevOps

Instructions for epics and tasks

Additional resources