AWS Database Blog

Upgrade Amazon RDS and Amazon Aurora PostgreSQL version 9.6

 

Amazon Relational Database Service (Amazon RDS) for PostgreSQL and Amazon Aurora PostgreSQL-Compatible Edition version 9.6 end of life is approaching. If you’re currently running PostgreSQL 9.6, you must upgrade your databases or AWS will automatically upgrade your RDS for PostgreSQL databases starting January 18, 2022, and Aurora PostgreSQL databases starting January 31, 2022.

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. As a result, you must manually perform major version upgrades of your DB instances.

In this post, we cover the following topics:

  • Amazon RDS for PostgreSQL and Aurora PostgreSQL end of life timeline
  • In-place upgrade vs. side-by-side upgrade approaches

Amazon RDS for PostgreSQL and Aurora PostgreSQL end of life timeline

The PostgreSQL community releases a new major version yearly, with a defined end of life (EOL) policy of older major versions. This allows you to make version and upgrade decisions well into the future. 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. For the final release dates of all versions of PostgreSQL, see Versioning Policy. For Aurora version policy , see Amazon Aurora Versions.
The next major version to reach EOL is 9.6, which is happening on November 11, 2021. The following table summarizes the EOL support for Amazon RDS for PostgreSQL and Aurora PostgreSQL.

 

Amazon RDS Aurora
End of life date January 18, 2022 00:00:01 UTC January 31, 2022 00:00:01 UTC
Starting this date, databases automatically upgrade within the earliest scheduled maintenance window that follows January 18, 2022 00:00:01 UTC January 31, 2022 00:00:01 UTC
Starting this date, any remaining database instances upgrade to the appropriate Aurora PostgreSQL major version whether or not they are in a maintenance window. February 22, 2022 00:00:01 UTC. February 15, 2022

To find out if you have any 9.6 instances in a given Region, on the Amazon RDS console, navigate to the Depreciated major versions section on the Recommendations page.

Choose a target engine version for upgrade

We recommend upgrading all the instances to PostgreSQL 12 or newer, but there may also be business reasons why upgrading to a different major version is necessary. For example, some applications aren’t yet certified on PostgreSQL 12 and must move to a slightly earlier version. When manually upgrading ahead of the automatic upgrades, you can choose the version of PostgreSQL that best meets your needs.

Be aware that starting with PostgreSQL 10, the PostgreSQL community changed the numbering formation for major versions. Before PostgreSQL 10, the second digit could also indicate a major version, such as 9.5 to 9.6, but starting with PostgreSQL 10, a major version is indicated by increasing the first part of the version, such as 9.6 to 10.

Upgrade targets for Amazon RDS for PostgreSQL and Aurora PostgreSQL

We recommend that you first upgrade to minor version 9.6.20 or greater and then upgrade directly to PostgreSQL 12 or greater so that you can skip intermediate major versions.

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

Current Source Version Newest Upgrade Target Preferred Major Upgrade Targets
9.6.21 13.2 13.2 12.6 11.11 10.16
9.6.20 13.1 13.1 12.5 11.1 10.16
9.6.19 12.4 12.4 11.9 10.16
9.6.18 12.3 12.3 11.8 10.16
9.6.17 12.2 12.2 11.7 10.16
9.6.16 11.6 11.6 10.16
9.6.15 11.5 11.5 10.16
9.6.14 11.4 11.4 10.16
9.6.12 11.2 11.2 10.16
9.6.11 11.1 11.1 10.16
9.6.10, 9.6.9, 9.6.8, 9.6.6, 9.6.5, 9.6.3, 9.6.2, 9.6.1 10.16 10.16

The following table summarizes the upgrade targets for Aurora PostgreSQL.

Current Source Version Major Upgrade Targets
9.6.9 and higher minor versions 10.11 or higher minor versions
10.7 and higher minor versions 11.7 or higher minor versions
11.7 and higher minor versions 12.4 or higher minor versions
12.7 and higher minor versions 13.3 or higher minor versions

Features available in major versions

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

Version Major Features
9.6
  • Parallel query
  • Full text phrase search
10
  • Extension to import data from Amazon S3
  • Native table partitioning
  • Logical replication
11
  • Stored procedures
  • Covering indexes
12
  • pg_cron extension
  • Support for SQL/JSON path language
  • B-tree indexes performance improvements, rebuild indexes concurrently.
  • Inline common table expressions
13
  • Improved performance for aggregates or partitioned tables
  • Space savings and performance gains in B-tree indexes
  • Extended statistics for query planning

In-place upgrade vs. side-by-side upgrade approaches

There are two options to upgrade the major versions: in-place and side-by-side. Choosing one option over the other primarily depends on how much downtime your business can afford and complexity involved in the upgrade process. An in-place upgrade can be done using an in-built feature but typically needs downtime during the upgrade process. In contrast, a side-by-side upgrade reduces the downtime because the upgrade happens on the copy of the database, but it takes several steps to configure continuous replication with the new database instance until the cutover happens.

In-place upgrade

Both Amazon RDS and Aurora support in-place upgrades, which use the pg_upgrade utility, which is safest. This option is less complex 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 completes the following steps:

  1. Take a pre-upgrade snapshot (if configured for backups). 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. Choose this option if your business can afford the downtime with the fewest administration tasks.

To learn more about major version upgrades, see How to perform a major version upgrade.

Side-by-side upgrade

The following are the high-level steps for side-by-side 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 with the new version.
  2. Configure continuous replication by using any synchronization methods between the source and new instance with the major upgraded version.
  3. During the cutoff window, after both source and target database instances are in sync with replication, point the application to the target database instance.

The following table shows the synchronization methods available for Amazon RDS and Aurora. Choosing one method over the other primarily depends on the product (Amazon RDS or Aurora) and the duration and complexity involved in configuring synchronization.

Method Amazon RDS Aurora
PgLogical Yes No
AWS DMS with CDC Yes No
Bucardo Yes Yes

Option A: Logical replication (Amazon RDS for PostgreSQL only)

Amazon RDS for PostgreSQL version 9.6 supports 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 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: AWS DMS with CDC (Amazon RDS for PostgreSQL 9.6 only)

AWS Database Migration Service (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:

  • Downtime during the upgrade can be reduced
  • Continuous replication

However, it has the following disadvantages:

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

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

Bucardo is asynchronous trigger-based replication with primary-secondary architecture. Bucardo requires a dedicated database and runs as a Perl daemon that communicates with this database and all the databases involved in 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)

Summary

Now is the time for you to upgrade all your PostgreSQL 9.6 instances. You can initiate upgrades at any time from now to before the auto upgrade date (February 22, 2022, for Amazon RDS and February 15, 2022, for Aurora) whether or not they are in a maintenance window to allow time to test your applications.

Depending on the approaches taken, (in-place or side-by-side 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.

About the Author

Chandra Pathivada is a Senior Database Specialist Solutions Architect with Amazon Web Services. He works with AWS RDS team, focusing on Opensource database engines like RDS PostgreSQL and Aurora PostgreSQL. He enjoys working with customers to help design, deploy, and optimize relational database workloads on AWS.