Schedule jobs for Amazon RDS for PostgreSQL and Aurora PostgreSQL by using Lambda and Secrets Manager
Created by Yaser Raja (AWS)
Environment: PoC or pilot | Source: Databases: Relational | Target: PostgreSQL on AWS |
R Type: N/A | Workload: Open-source | Technologies: Databases |
AWS services: AWS Lambda; Amazon RDS; AWS Secrets Manager; Amazon Aurora |
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
A job must complete within 15 minutes, which is the Lambda function timeout limit. For other limits, see the AWS Lambda documentation.
Job code must be written in a language supported by Lambda.
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 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.
Amazon 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.
AWS 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
| Task | Description | Skills 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, see Managing PostgreSQL users and roles | DBA |
Store database credentials as a secret in Secrets Manager. | Follow the instructions in Create a database secret (Secrets Manager documentation). | DBA, DevOps |
| Task | Description | Skills required |
|---|---|---|
Choose a programming language supported by AWS Lambda. | For a list of supported languages, see Lambda runtimes (Lambda documentation). | Developer |
Write the logic to fetch the database credentials from Secrets Manager. | For sample code, see How to securely provide database credentials to Lambda functions by using AWS Secrets Manager | 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. For more information, see Deploying Lambda functions (Lambda documentation). | Developer |
| Task | Description | Skills required |
|---|---|---|
Create the Lambda function deployment package. | This package contains the code and its dependencies. For more information, see Deployment packages (Lambda documentation). | 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, see Configuring function options (Lambda documentation). | DevOps |
Set permissions for the Lambda function role to access Secrets Manager. | For instructions, see Use secrets in AWS Lambda functions (Secrets Manager documentation). | DevOps |
Test the Lambda function. | Initiate the function manually to make sure it works as expected. | DevOps |
| Task | Description | Skills required |
|---|---|---|
Create a rule to run your Lambda function on a schedule. | Schedule the Lambda function by using CloudWatch Events. For instructions, see Schedule Lambda functions using CloudWatch Events (CloudWatch Events tutorial). | DevOps |
Related resources
