AWS Database Blog

PostgreSQL 11 upgrade strategies for Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL

Amazon Aurora PostgreSQL-Compatible Edition and Amazon Relational Database Service (Amazon RDS) for PostgreSQL version 11 end of standard support is February 29, 2024. See the Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL announcements to upgrade your databases:

Unlike minor version upgrades, major version upgrades can contain database changes that aren’t backward compatible with existing applications. Before initiating the upgrade, we recommend you carefully plan and test major version upgrades. The benefits of more recent major versions include new features, improved performance, and an ability to define a higher security posture.

In this post, we cover the Amazon Aurora PostgreSQL-Compatible Edition and Amazon RDS for PostgreSQL end-of-life (EOL) timeline, features available in PostgreSQL major versions, Amazon RDS Extended Support, and upgrade approaches, including in-place upgrade, Amazon RDS Blue/Green deployment, and out-of-place upgrade.

Amazon Aurora PostgreSQL-Compatible Edition and Amazon RDS for PostgreSQL EOL timeline

The PostgreSQL community releases a new major version on a yearly cadence with an EOL policy to support a major version for 5 years after its initial release. PostgreSQL 11.22, released on November 9, 2023, was the final release of PostgreSQL 11 major version. PostgreSQL 11 will no longer receive security and bug fixes from the PostgreSQL community after this release. For the Aurora version policy, see Amazon Aurora versions. For the final release dates of all versions of Amazon RDS for PostgreSQL, see Release calendars for Amazon RDS for PostgreSQL.

The end of standard support for Amazon Aurora PostgreSQL-Compatible Edition and Amazon RDS for PostgreSQL, which is derived from the community releases, is February 29, 2024.

Features available in major versions

The following table lists some of the major features across all versions. All newer major versions of PostgreSQL are feature inclusive, therefore version 15 will include new features added in versions 13 and 14.

Version Major Features
15 PostgreSQL 15 release notes

  • Support for the SQL MERGE command
  • Logical replication supports selective publication by column lists and row filter conditions
  • Support for zstd compression and more use cases for LZ4 compression
  • Support for structured server log output using JSON format
  • Performance improvements for in-memory and on-disk sorting
  • Impactful features in PostgreSQL 15
14 PostgreSQL 14 release notes

  • Enhancements for distributed workloads
  • Performance gains to the vacuuming system, reducing overhead from B-trees
  • Enhanced SQL performance, conformance, and convenience
  • Security enhancements
13 PostgreSQL 13 release notes

  • Improved performance for aggregates or partitioned tables
  • Space savings and performance gains in B-tree indexes
  • Extended statistics for query planning

Choose a target engine version for upgrade

We recommend upgrading all instances to PostgreSQL 15 or later. However, selection of the target major version depends on your application and business needs. Planning and completing the upgrade on your schedule allows you to determine the version that best meets your needs.

We recommend that you first upgrade to minor version 11.21 or later, apply any pending operating system maintenance, and then upgrade directly to PostgreSQL 15 or later so that you can skip intermediate major versions. Skipping major versions helps reduce database downtime and upgrade efforts. Alternatively, if your application isn’t ready for PostgreSQL 15, you might consider other major versions such as PostgreSQL 13 and PostgreSQL 14, depending on your business and application needs.

You can use the AWS Command Line Interface (AWS CLI) as shown in the following code to find the target Amazon Aurora PostgreSQL versions:

aws rds describe-db-engine-versions --engine aurora-postgresql --engine-version 11.21 --query "DBEngineVersions[*].ValidUpgradeTarget[*].{EngineVersion:EngineVersion}" --output table
--------------------------
|DescribeDBEngineVersions|
+------------------------+
|      EngineVersion     |
+------------------------+
|  12.16                 |
|  13.12                 |
|  14.9                  |
|  15.4                  |
+------------------------+

The following table summarizes the upgrade targets for Amazon Aurora PostgreSQL.

Current Source Version Newest Upgrade Target Other Available Upgrade Targets
11.21 15.4 14.9 13.12 12.16
11.20 15.3 14.8 13.11 12.15
11.19 15.3 15.2 14.8 14.7 13.11 13.10 12.15 12.14 11.20
11.18 15.3 15.2 14.8 14.7 14.6 13.11 13.10 13.9 12.15
11.17 15.3 15.2 14.8 14.7 14.5 13.11 13.10 13.8 12.15
11.16 15.3 15.2 14.8 14.7 14.5 14.4 14.3 13.11 13.10
11.15 15.3 15.2 14.8 14.7 13.11 13.10 13.6 12.15 12.14
11.14 15.3 15.2 14.8 14.7 13.11 13.10 13.5 12.15 12.14

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

aws rds describe-db-engine-versions --engine postgres --engine-version 11.22 --query "DBEngineVersions[*].ValidUpgradeTarget[*].{EngineVersion:EngineVersion}" --output table
--------------------------
|DescribeDBEngineVersions|
+------------------------+
|      EngineVersion     |
+------------------------+
|  12.16                 |
|  13.12                 |
|  14.9                  |
|  15.4                  |
|  16.1                  |
+------------------------+

The following table summarizes the upgrade targets for Amazon RDS for PostgreSQL.

Current Source Version Newest Upgrade Target Other Available Upgrade Targets
11.22 16.1 15.5 14.10 13.13 12.17
11.21 15.4 14.9 13.12 12.16
11.20 15.3 14.8 13.11 12.15
11.19 15.2 14.7 13.1 12.15 12.14 11.20
11.18 14.6 13.9 12.15 12.14 12.13 11.20 11.19
11.17 14.5 13.8 12.15 12.14 12.13 12.12 11.20 11.19 11.18
11.16 14.4 14.3 13.7 12.15 12.14 12.13 12.12 12.11 11.20
11.15 14.2 13.6 12.15 12.14 12.13 12.12 12.11 12.1 11.20
11.14 14.1 13.5 12.15 12.14 12.13 12.12 12.11 12.1 12.9
11.13 13.4 12.15 12.14 12.13 12.12 12.11 12.1 12.9 12.8
11.12 13.3 12.15 12.14 12.13 12.12 12.11 12.1 12.9 12.8

Amazon RDS Extended Support

On September 1, 2023, AWS announced Amazon RDS Extended Support. Amazon RDS Extended Support is a paid offering where Amazon RDS provides critical security and bug fixes for an Amazon Aurora PostgreSQL-Compatible Edition or Amazon RDS for PostgreSQL major version for up to 3 years past the end of support from the community.

On December 21, 2023, AWS announced that starting on February 29, 2024, all PostgreSQL database instances will be automatically enrolled into Amazon RDS Extended Support.

The minimum version requirements for Amazon RDS Extended Support are as follows:

  • Amazon Aurora PostgreSQL requires version 11.21 or 11.9 (LTS)
  • Amazon RDS for PostgreSQL requires version 11.22

Extended Support for Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL 11 will offer the following:

The additional charge for Amazon RDS Extended Support ends as soon as you upgrade to a supported major engine version or you delete the database that was running a major version past the Amazon RDS end of standard support date. For more information, see Amazon RDS for PostgreSQL pricing.

PostgreSQL 11 upgrade options

We discuss three options to perform 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, which allows you to deploy and test production changes, keeping your current production database safer. An out-of-place upgrade uses an external database instance or cluster to accomplish the upgrade.

In-place upgrade

Both Amazon Aurora PostgreSQL-compatible edition and Amazon RDS for PostgreSQL support in-place automated upgrades, which use the pg_upgrade utility. This option is less complex than an out-of-place upgrade because it’s a feature of the managed service; AWS takes care of the heavy lifting and makes multi-version upgrades seamless.

In major version upgrades, Amazon RDS and Amazon Aurora complete the following steps:

  1. Take a pre-upgrade snapshot. You can use this snapshot for rollbacks.
  2. Shut down the instance and prepare it for the upgrade.
  3. Use the pg_upgrade utility to run the upgrade job on the instance.
  4. Take a post-upgrade snapshot. Networking is now reconfigured on the instance.

The downtime required for an in-place upgrade depends primarily on the size and number of the objects in the database. This can be determined by restoring a snapshot and upgrading it. Choose this option if your business can afford the downtime with the fewest administration tasks.

To learn more about major version upgrades, see Upgrading the PostgreSQL DB engine for Aurora PostgreSQL for Amazon Aurora PostgreSQL upgrades, and How to perform a major version upgrade for Amazon RDS for PostgreSQL upgrades.

Amazon RDS Blue/Green deployment

Amazon Aurora PostgreSQL-Compatible Edition and Amazon RDS for PostgreSQL now support blue/green deployments for major version upgrades using versions 11.21 and higher, 12.16 and higher, 13.12 and higher, 14.9 and higher, and 15.4 and higher. You can create a blue/green deployment to create a separate fully managed staging environment (green) that mirrors the production environment (blue). The staging environment clones your production environment’s primary database and in-Region read replicas. Blue/green deployment keeps these two environments in sync using PostgreSQL community provided logical replication. Use the Amazon RDS provided one-click in-place major version upgrades in your staging environment, then test your application in your staging environment (green). When you’re ready to switch, in as fast as a minute, you can promote the staging environment to be the new production environment with no data loss.

This process has the following advantages:

  • You can stay current with database patches and system updates
  • You can test major version database upgrades in a safe staging environment without affecting the production environment
  • You can safely switch over through the use of built-in switchover guardrails

Out-of-place upgrade

The following are the high-level steps for an out-of-place upgrade. Actual steps might vary depending on your environment and the synchronization method you choose.

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

In the following sections, we discuss three replication options to perform an out-of-place upgrade.

Option A: Logical replication with pglogical

Amazon Aurora PostgreSQL-compatible edition and Amazon RDS for PostgreSQL version 11 and later support logical replication using pglogical. The process starts with taking a snapshot of the data on the publisher database and copying that to the subscriber. Then the changes on the publisher are sent to the subscriber as they occur in real time. For more information about using logical replication with Amazon Aurora PostgreSQL-compatible edition, refer to Using PostgreSQL logical replication with Aurora.

This process has the following advantages and disadvantages:

  • Downtime during the upgrade can be reduced
  • It offers continuous replication
  • It takes several steps to configure replication
  • Initial synchronization on large databases might take more time
  • As of this writing, you can’t use logical replication for some PostgreSQL data, including sequences, schema modification commands (DDL), and large objects
  • There may be additional cost

Option B: AWS DMS with change data capture

Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL version 11 and later support AWS Database Migration Service (AWS DMS) with change data capture (CDC). AWS DMS can perform live migrations with minimum 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 has the following advantages and disadvantages:

  • You can use this method to reduce downtime
  • It offers continuous replication
  • AWS DMS doesn’t support certain data types, such as timestamp with time zone
  • You have to pay for AWS DMS for the duration it takes this upgrade project to complete

Option C: Bucardo

Bucardo is an open source utility that can replicate data changes asynchronously to multiple secondary or multiple master databases. It’s a trigger-based replication and proven to be consistent and stable for extensive migrations and upgrades. It requires an Amazon Elastic Compute Cloud (Amazon EC2) instance to host the tool and runs as a Perl daemon that communicates with all the databases involved in the replication.

For instructions to configure Bucardo, see Migrating legacy PostgreSQL databases to Amazon RDS or Aurora PostgreSQL using Bucardo.

This process has the following advantages and disadvantages:

  • Secondary databases can be pre-warmed for quick setup
  • It reduces downtime for upgrades
  • It offers continuous replication
  • It takes several steps to configure
  • It can’t handle large objects or DDL
  • It can’t incrementally replicate tables without a unique key (it can full copy them)
  • It requires an EC2 instance to host the tool
  • It uses trigger-based replication, which can be slow

Choosing between the different upgrade approaches

Choosing one option over the other depends primarily on trading off between downtime and complexity. You need to determine how much downtime your business can afford and the complexity of upgrade you’re willing to take on. The following table provides a summary of the different upgrade options and the trade-offs you need to consider.

Upgrade Option Advantages and Disadvantages
In-place upgrade
  • Can be performed from the Amazon RDS console or AWS CLI
  • Requires downtime
Blue/green deployments
  • Can also be performed from the Amazon RDS console and can minimize risks and downtime
  • It may restrict some of the operations that a user can perform until the operation is complete
Out-of-place upgrade
  • It reduces downtime because the upgrade happens on a copy of the database
  • It takes several steps to configure continuous replication with the new database instance until cutover happens

Summary

Now is the time to upgrade all your PostgreSQL 11 instances. You can initiate upgrades at any time from now to before the end of support date (February 29, 2024) for both Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL. We recommend planning and testing these major version upgrades and performing them according to the needs of your application.

Depending on the approach taken—in-place, blue/green deployment, or out-of-place upgrade—you should perform a test run using the selected approach on a copy of the production database instance to estimate the time needed and prepare for the final production upgrade.

If you need assistance or have feedback, please reach out to your usual AWS support contacts, or post a message in the AWS re:Post for Database.

Garry Knox is an Enterprise Support Lead and Technical Account Manager for AWS Enterprise Support. He works with external customers on a variety of projects, helping them improve the value of their solutions when using AWS.

Vivek SinghVivek Singh is a Principal Database Specialist Technical Account Manager with AWS focusing on Amazon RDS for PostgreSQL and Amazon Aurora PostgreSQL engines. He works with enterprise customers providing technical assistance on PostgreSQL operational performance and sharing database best practices. He has over 17 years of experience in open source database solutions, and enjoys working with customers to help design, deploy, and optimize relational database workloads on AWS.