Automating Amazon Aurora PostgreSQL Upgrades with AWS Systems Manager and EC2

A new solution has been developed to automate major and minor version upgrades for Amazon Aurora PostgreSQL database clusters, reducing manual effort by up to 80% and minimizing downtime risks. This approach leverages AWS Systems Manager to orchestrate upgrades, Amazon Elastic Compute Cloud (EC2) to run automation scripts, and AWS Secrets Manager to securely handle database credentials.

The solution is built around two main modules: PREUPGRADE, which performs readiness checks and database preparation to identify potential issues, and UPGRADE, which handles the actual database upgrade process. The program uses tags to identify Aurora clusters that are candidates for upgrade and relies on AWS Systems Manager running automation documents containing Unix shell scripts that run psql and AWS Command Line Interface (CLI) commands from an EC2 instance.

The solution supports both minor and major version upgrades without interfering with Aurora's built-in Automatic minor version upgrades feature. It has been tested and validated in a single Amazon Virtual Private Cloud (VPC) and AWS Region environment for one account, but can be extended to multi-VPC or cross-account deployments by addressing additional considerations for networking, security, replication, and data consistency.

To implement this solution, users must complete prerequisite steps such as replacing placeholders with actual values and creating IAM roles and permissions. The solution also requires the use of AWS CloudFormation templates to create required resources, including an IAM role and an SSM automation document.

The workflow consists of several key components: tagging Aurora clusters for upgrade, running the PREUPGRADE module, executing the UPGRADE module, and uploading logs to Amazon S3. The solution provides comprehensive logging throughout the upgrade process, storing logs in LOGS_DIR on the EC2 instance and uploading them to Amazon S3 upon completion.

The script also sends email notifications through Amazon Simple Notification Service (SNS) to subscribed users. To avoid future charges, users must remove resources created during this walkthrough, including emptying an S3 bucket and deleting a CloudFormation stack.

This solution demonstrates how AWS Systems Manager can be used to automate database upgrades across multiple clusters, reducing manual effort and minimizing downtime risks. By adopting this automation approach, organizations can achieve consistent, repeatable upgrade procedures for their Aurora PostgreSQL databases.