Upgrade Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL version 10

Amazon Aurora PostgreSQL-Compatible Edition and Amazon Relational Database Service (Amazon RDS) for PostgreSQL version 10 end of life is approaching. If you’re currently running your Aurora or RDS instances on PostgreSQL 10, you must upgrade your databases.

• Announcement: Amazon Aurora PostgreSQL 10.x end of life is January 31, 2023
• Announcement: Amazon RDS for PostgreSQL 10.x end of life is April 17, 2023

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

In this post, we cover the following topics:

• Aurora PostgreSQL-compatible and Amazon RDS for PostgreSQL end of life timeline
• In-place upgrade compared to out-of-place upgrade approaches

Aurora PostgreSQL and Amazon RDS for PostgreSQL end of life timeline

The PostgreSQL community releases a new major version on a yearly cadence, with a defined end of life (EOL) policy for older major versions. This policy allows you to make version and upgrade decisions well in advance. The community EOL policy is to support a major version for 5 years after its initial release. After the fifth year, a major version has one final minor release containing any fixes, and is then considered EOL and no longer supported. PostgreSQL 10.23 is the final release of PostgreSQL 10 major version. PostgreSQL 10 will no longer receive security and bug fixes after this release. For the Aurora version policy, see Amazon Aurora versions . For the final release dates of all versions of RDS for PostgreSQL, see RDS for PostgreSQL release calendar .

The community PostgreSQL 10 end of life was set for November 10, 2022. The Aurora and RDS for PostgreSQL end of life dates are derived from the community end of life dates. Below are the important dates for RDS and Aurora PostgreSQL 10.x end of life:

• Starting January 31, 2023 00:00:01 UTC, Amazon Web Services may upgrade your Aurora PostgreSQL 10.x databases to a newer major version.
• Starting April 17, 2023 00:00:01 UTC, Amazon Web Services may upgrade your RDS for PostgreSQL 10.x databases to PostgreSQL 14 major version during a scheduled maintenance window. Any remaining RDS for PostgreSQL 10.x databases will be upgraded to PostgreSQL 14 starting July 18, 2023 00:00:01 UTC regardless of the maintenance window.

Choose a target engine version for upgrade

We recommend upgrading all instances to PostgreSQL 14 or newer. However, selection of the target major version may depend on your application and business needs. Planning and executing 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 10.21 or greater, apply any pending operating system maintenance, and then upgrade directly to PostgreSQL 14 or greater so that you can skip intermediate major versions. Skipping major versions helps save database downtime and upgrade efforts. Alternatively, if your application is not ready for PostgreSQL 14, you may consider other major versions such as PostgreSQL 12 and PostgreSQL 13 depending on your business and application needs.

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

[ec2-user@ip-~]$ aws rds describe-db-engine-versions --engine aurora-postgresql --engine-version 10.21 --query "DBEngineVersions[*].ValidUpgradeTarget[*].{EngineVersion:EngineVersion}" --output table

--------------------------
|DescribeDBEngineVersions|
+------------------------+
|      EngineVersion     |
+------------------------+
|  11.16                 |
|  12.11                 |
|  13.7                  |
|  14.3                  |
+------------------------+

The following table summarizes the upgrade targets for Aurora PostgreSQL .

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

[ec2-user@ip-~]$ aws rds describe-db-engine-versions --engine postgres --engine-version 10.21 --query "DBEngineVersions[*].ValidUpgradeTarget[*].{EngineVersion:EngineVersion}" --output table

--------------------------
|DescribeDBEngineVersions|
+------------------------+
|      EngineVersion     |
+------------------------+
|  11.16                 |
|  12.11                 |
|  13.7                  |
|  14.3                  |
|  14.4                  |
+------------------------+

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

Features available in major versions

The following table lists some of the major features across all the versions.

There are two options to perform major version upgrade: in-place and out-of-place. In-place option upgrades the database on the existing instance or cluster. Out-of-place involves external database instance or cluster to accomplish upgrade.

In-place upgrade

Both Aurora PostgreSQL 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; Amazon Web Services takes care of the heavy lifting and makes multi-version upgrades seamless.

In major version upgrades, Amazon RDS and Aurora completes 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.

Downtime required for an in-place upgrade primarily depends 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 Aurora PostgreSQL upgrades and How to perform a major version upgrade for RDS for PostgreSQL upgrades.

Out-of-place upgrade

The following are the high-level steps for an out-of-place upgrade. Actual steps needed may vary depending on your environment and the type of 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 higher PostgreSQL version.
3. Configure continuous replication between the source and new upgraded instance by using one of the methods as discussed below.
4. During the cutoff window, after both source and target database instances are in sync, point the application to the target database instance.

An out-of-place upgrade is more complex than an in-place upgrade as it requires manual steps and monitoring.

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

Option A: Logical replication

Aurora PostgreSQL and Amazon RDS for PostgreSQL version 10 and higher support logical replication using pglogical . The process typically 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. This Amazon Web Services user guide discusses about using Logical Replication with Aurora PostgreSQL.

This process has the following advantages:

• Downtime during the upgrade can be reduced
• Continuous replication

However, it has the following disadvantages:

• Takes several steps to configure replication
• Initial synchronization on large databases may take more time
• Can’t replicate large objects, sequences, DDL, and so on

Option B: Amazon Web Services DMS with CDC

Aurora PostgreSQL and Amazon RDS for PostgreSQL version 10 and higher support Amazon Web Services Database Migration Service (Amazon Web Services DMS) with change data capture (CDC). Amazon Web Services DMS can perform live migrations with minimum downtime. For more information about upgrading with Amazon Web Services DMS, see Upgrade your Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL database, Part 1: Comparing upgrade approaches .

This option has the following advantages:

• Downtime during the upgrade can be reduced
• Continuous replication

However, it has the following disadvantages:

• Amazon Web Services DMS doesn’t support certain data types, like timestamp with time zone
• You have to pay for Amazon Web Services DMS for the duration it takes this upgrade project to complete

Option C: Bucardo (Amazon RDS for PostgreSQL and Aurora PostgreSQL)

Bucardo is an open-source utility that can replicate data changes asynchronously to multiple secondary or multiple masters. It is a trigger-based replication and proven to be consistent and stable for extensive migrations and upgrades. It requires an 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:

• Secondary databases can be pre-warmed for quick setup
• Reduces downtime for upgrades
• Continuous replication

However, it has the following disadvantages:

• Takes several steps to configure
• Can’t handle large objects and DDL
• Can’t incrementally replicate tables without a unique key (it can full copy them)
• Requires an EC2 instance to host the tool

In-place upgrade compared to out-of-place upgrade approaches

Choosing one option over the other primarily depends on trading off between downtime and complexity. You need to determine how much downtime your business can afford and the complexity of upgrade you are willing to take on. An in-place upgrade can be performed from the RDS Console or CLI and requires downtime. In contrast, an out-of-place upgrade reduces the downtime because the upgrade happens on a copy of the database, but 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 10 instances. You can initiate upgrades at any time from now to before the end-of-life date (January 31, 2023, for Aurora PostgreSQL and April 17, 2023, for 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 approaches taken, (in-place or out-of-place upgrade), you should perform a dry 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, reach out to your usual Amazon Web Services support contacts, or post a message in the Amazon Web Services re:Post for RDS .

About the Authors

Vivek Singh is a Database Specialist with Amazon Web Services focusing on Amazon RDS/Aurora PostgreSQL engines. He works with enterprise customers providing technical assistance on PostgreSQL operational performance and sharing database best practices.

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