Upgrade strategies for Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL 12

Standard support for Amazon Aurora PostgreSQL-Compatible Edition and Amazon Relational Database Service (Amazon RDS) for PostgreSQL version 12 is set to conclude on February 28, 2025. To ensure continued support and optimal performance, it is essential to plan your database upgrades accordingly. The official announcements for both Aurora PostgreSQL and Amazon RDS for PostgreSQL provide detailed guidance on the upgrade process, offering crucial information to help maintain the security and efficiency of your PostgreSQL environments.

Upgrading to newer major versions of Aurora PostgreSQL and Amazon RDS for PostgreSQL requires careful planning and testing, as these updates may introduce changes that could impact existing application compatibility. A thorough evaluation is necessary to ensure seamless functionality post-upgrade. Although the upgrade process demands meticulous consideration, embracing the latest major releases can yield substantial benefits, including enhanced features, improved performance, and robust security enhancements. To maximize these advantages while minimizing operational impact, comprehensive assessment and preparation are vital prior to implementation.

In this discussion, we delve into the end-of-life (EOL) timeline for Aurora PostgreSQL and Amazon RDS for PostgreSQL, examining features in PostgreSQL major versions, Amazon RDS Extended Support, and various upgrade strategies such as in-place upgrades, Amazon RDS blue/green deployments, and out-of-place upgrades.

The PostgreSQL community adheres to an annual major version release cycle, supporting each version for five years post-release. The final update for PostgreSQL 12, version 12.22, was released on November 21, 2024. After this point, community-driven security and bug fixes will cease. For details regarding Aurora PostgreSQL version policies and Amazon RDS for PostgreSQL release schedules, please consult the official Amazon documentation.

Features available in PostgreSQL higher versions

The new features introduced in higher versions of PostgreSQL empower applications to achieve better performance, enhanced security, and reduced maintenance costs, while equipping businesses with advanced capabilities that can provide a competitive edge in managing their data infrastructure. Below is a summary of the features available in PostgreSQL versions 13 and above:

The availability of these features in Aurora PostgreSQL may vary depending on the specific version and release. Additionally, Aurora often includes custom enhancements and optimizations beyond the standard PostgreSQL features. For the most current and detailed information on feature availability and version compatibility, refer to Amazon Aurora PostgreSQL updates.

Performance improvements with Graviton4

Higher versions of Aurora PostgreSQL now support AWS Graviton3 based R7g database instances. The newly introduced Graviton4 instances offer up to 40% performance improvement and up to 29% price-performance enhancement for on-demand pricing over Graviton3-based instances of equivalent sizes on Amazon Aurora databases, depending on the database engine, version, and workload. Graviton4 processors represent AWS’s latest generation of custom-designed Graviton processors, built on the AWS Nitro System. These processors deliver significant advancements over their Graviton3 predecessors, equipped with cutting-edge DDR5 memory technology that provides exceptional memory bandwidth for rapid data access. R8g database instances also boast enhanced networking capabilities, featuring up to 50 Gbps bandwidth and up to 40 Gbps of bandwidth to Amazon Elastic Block Store (Amazon EBS).

Choosing a target engine version for upgrade

It is advisable to upgrade all instances to PostgreSQL 16 or newer, though your specific application and business requirements should guide your target version selection. Proactive planning and execution of the upgrade allow you to choose the most suitable version for your needs. For a streamlined process, we recommend first upgrading to minor version 12.22 or later and applying any pending operating system (OS) maintenance. Subsequently, upgrade directly to PostgreSQL 16 or beyond, bypassing intermediate major versions to minimize downtime and upgrade efforts. If PostgreSQL 16 is not compatible with your application, consider alternatives such as PostgreSQL 13, 14, or 15 based on your operational needs.

To identify available Aurora PostgreSQL target versions, you can utilize the AWS Command Line Interface (AWS CLI) with the following command:

aws rds describe-db-engine-versions --engine  aurora-postgresql --engine-version 12.22 --query "DBEngineVersions[*].ValidUpgradeTarget[*].{EngineVersion:EngineVersion}" --output table
--------------------------
|DescribeDBEngineVersions|
+------------------------+
|      EngineVersion     |
+------------------------+
|  13.18                 |
|  14.15                 |
|  15.10                 |
|  16.6                  |
+------------------------+

You can similarly use the AWS CLI to find the target Amazon RDS for PostgreSQL versions:

aws rds describe-db-engine-versions --engine  postgres --engine-version 12.22 --query "DBEngineVersions[*].ValidUpgradeTarget[*].{EngineVersion:EngineVersion}" --output table
--------------------------
|DescribeDBEngineVersions|
+------------------------+
|      EngineVersion     |
+------------------------+
|  13.18                 |
|  14.15                 |
|  15.10                 |
|  16.6                  |
|  17.2                 |
+------------------------+

PostgreSQL 12 upgrade options

There are three primary options for performing a major version upgrade: in-place upgrade, Amazon RDS blue/green deployment, and out-of-place upgrade. The in-place option upgrades the database on the existing instance or cluster. Blue/green deployment creates a fully managed staging environment using PostgreSQL community logical replication, allowing you to deploy and test production changes while keeping your current production database secure. An out-of-place upgrade utilizes an external database instance or cluster to accomplish the upgrade.

In-place upgrade

Both Aurora PostgreSQL and Amazon RDS for PostgreSQL support in-place automated upgrades, utilizing the pg_upgrade utility. This option is less complex than an out-of-place upgrade, as it is a feature of the managed service; AWS handles the heavy lifting, making multi-version upgrades seamless. During major version upgrades, Amazon RDS and Aurora complete the following steps:

1. Take a pre-upgrade snapshot, which can be used for rollbacks.
2. Shut down the instance and prepare it for the upgrade.
3. Utilize the pg_upgrade utility to execute the upgrade job on the instance.
4. Take a post-upgrade snapshot, with networking reconfigured on the instance.

The downtime required for an in-place upgrade primarily depends on the size and number of objects in the database. This can be assessed by restoring a snapshot and performing the upgrade. This option is suitable if your business can accommodate the downtime with minimal administrative tasks. For further details on major version upgrades, please refer to Upgrading Amazon Aurora PostgreSQL DB clusters and Upgrades of the RDS for PostgreSQL DB engine.

Amazon RDS blue/green deployment

Aurora PostgreSQL and Amazon RDS for PostgreSQL support blue/green deployments for version 11.1 and all higher major and minor versions. This deployment strategy creates a separate fully managed staging environment (green) that mirrors the production environment (blue). The staging environment clones the primary database and in-region read replicas of your production environment. Blue/green deployment keeps these two environments synchronized using PostgreSQL community-provided logical replication. You can utilize the Amazon RDS provided one-click in-place major version upgrades in your staging environment, followed by testing your application in this safe environment. When ready, you can promote the staging environment to become the new production environment in as little as a minute, ensuring no data loss.

This process offers several advantages:

• Stay current with database patches and system updates.
• Test major version database upgrades in a secure staging environment without impacting production.
• Safely switch over using built-in switchover guardrails.

For more information, see Switching a blue/green deployment in Amazon RDS and Using Amazon Aurora Blue/Green Deployments for database updates.

Out-of-place upgrade

The following outlines the high-level steps for an out-of-place upgrade. Actual steps may vary based on your environment and chosen synchronization method:

1. Create a copy of the production database instance using RDS snapshots or Aurora clones.
2. Upgrade the new instance to a later PostgreSQL version.
3. Configure continuous replication between the source and the new upgraded instance using one of the methods discussed in the following sections.
4. During the cutoff window, after both the source and target database instances are in sync, direct the application to the target database instance.

In the subsequent sections, we will discuss two replication options for performing an out-of-place upgrade.

Option A: Logical replication with pglogical

Aurora PostgreSQL and Amazon RDS for PostgreSQL version 12 and later support logical replication using pglogical. This process begins with taking a snapshot of the data on the publisher database and copying it to the subscriber. Changes on the publisher are then sent to the subscriber in real-time as they occur. For more information on using logical replication with Aurora PostgreSQL, refer to Overview of PostgreSQL logical replication with Aurora.

This method has its advantages and disadvantages:

• Downtime during the upgrade can be minimized.
• Offers continuous replication.
• Configuration of replication involves several steps.
• Initial synchronization on large databases may require additional time.
• As of this writing, logical replication does not support certain PostgreSQL data types, including sequences, schema modification commands (DDL), and large objects.
• Potential additional costs may apply.

Option B: AWS DMS with change data capture

Aurora PostgreSQL and Amazon RDS for PostgreSQL version 12 and later support AWS Database Migration Service (AWS DMS) with change data capture (CDC). This service allows for live migrations with minimal downtime. For more information about upgrading with AWS DMS, see Upgrade your Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL database, Part 1: Comparing upgrade approaches.

This option also has its advantages and disadvantages:

• Can effectively reduce downtime.
• Provides continuous replication.
• AWS DMS does not support certain data types, such as timestamp with time zone.
• Costs are incurred for AWS DMS throughout the duration of the upgrade project.

Choosing between the different upgrade approaches

When selecting an upgrade strategy, it is important to balance business requirements for system availability against implementation complexity and resource demands. Assess how much downtime your business can tolerate and the complexity of the upgrade you are prepared to undertake. The following table summarizes the various upgrade options and the trade-offs to consider:

Amazon RDS Extended Support

While upgrading is encouraged, Amazon RDS Extended Support is available for those who may not wish to upgrade immediately. This option allows customers to continue running databases on a major version beyond the standard support end date for Aurora and Amazon RDS, albeit at an additional cost. During the extended support period, Amazon RDS provides critical security updates and bug fixes, though no new features will be introduced. Extended Support incurs mandatory fees to cover the engineering costs associated with maintaining security patches and bug fixes for deprecated versions. It can sustain the deprecated engine version for up to three years after the community EOL date, with charges varying based on vCPU count, AWS Region, and the duration since standard support expiration. For Aurora PostgreSQL 12.x and Amazon RDS for PostgreSQL 12.x, these charges will begin on March 1, 2025, and will cease immediately upon upgrading the databases. New Aurora and RDS for PostgreSQL 12 instances can opt into this extended support by specifying ‘open-source-rds-extended-support’ for the –engine-lifecycle-support option when using AWS CLI commands to create database instances or clusters. For Extended Support eligibility, Aurora PostgreSQL databases will be automatically upgraded to either version 12.9 (LTS) or 12.22, while Amazon RDS for PostgreSQL databases will be upgraded to version 12.22. These versions are mandatory; no other PostgreSQL 12.x minor versions may remain active during the Extended Support period.