Upgrading from Amazon RDS for PostgreSQL version 9.5

Updated on 02/18/2021 with more details about the automatic upgrade and its 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. The next major version to reach EOL is 9.5, which is happening on February 11, 2021.

Preparing to upgrade

Amazon Relational Database Service (Amazon RDS) for PostgreSQL relies on the open-source community to fix critical security, availability and data correctness issues in the database engine. After the community stops supporting a major version, you’re at the risk of being exposed to one of these high – severity bugs. Therefore, it’s important to retire unsupported major versions in tandem with the community, and we encourage you to upgrade to newer versions as early as possible and validate your applications before automatic upgrades begin.

The PostgreSQL community version 9.5 will reach EOL on February 11, 2021. To encourage the use of newer versions of PostgreSQL, on February 16, 2020, Amazon RDS for PostgreSQL will stop supporting the creation of new PostgreSQL 9.5 instances from the AWS Management Console or AWS Command Line Interface (AWS CLI). If you do not upgrade your databases before February 16, 2021, RDS will upgrade your PostgreSQL 9.5 databases to PostgreSQL 12 during a scheduled maintenance window between Feb 16, 2021 00:00:01 UTC and March 16, 2021 00:00:01 UTC. On March 16, 2021 00:00:01 AM UTC, any PostgreSQL 9.5 databases that remain will be upgraded to version 12 regardless of whether the instances are in a maintenance window or not.

We highly recommend upgrading any Amazon RDS for PostgreSQL 9.5 databases before March 16, 2021, to allow time to test your applications. To find out if you have any 9.5 instances in a given Region, on the Amazon RDS console, navigate to the Depreciated major versions section on the Recommendations page.

You can also get the list of all valid upgrade targets using the describe-db-engine-versions– CLI command. For example, see the following code:

export REGION=AWS-Region                         
export ENDPOINT=https://rds.AWS-Region.amazonaws.com

aws rds describe-db-engine-versions --engine postgres --region $REGION --endpoint $ENDPOINT --output text --query '*[].ValidUpgradeTarget[?IsMajorVersionUpgrade==`true`].{EngineVersion:EngineVersion}' 

If any PostgreSQL 9.5 instances are in your account, you can upgrade them before March 16, 2021. For instructions, see Upgrading the PostgreSQL DB Engine for Amazon RDS.

Features in new major versions

PostgreSQL 9.5 had significant features in its release. The most prominent were the introduction of row – level security and Block Range Indexes (BRIN). PostgreSQL 9.5 also introduced INSERT ON CONFLICT (or UPSERT) and did substantial performance improvements for sorting and for multi-CPU machines. These great features and many more are available in newer major versions of PostgreSQL.

Amazon RDS recommends that all RDS for PostgreSQL 9.5 instances be upgraded 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 are not yet certified on PostgreSQL 12 and must move to a slightly earlier version. When manually upgrading ahead of the automatic upgrades starting on March 16, 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. The following table summarizes the major features of current versions.

In addition to the many new features available in each major version, Amazon RDS PostgreSQL continues to increase performance year over year. Newer PostgreSQL versions also give you access to newer instance types. A simple benchmark such as in the following visualization shows the performance of each major version with the default configuration increasing with the same application workload.

The year-over-year performance increases coming from new major versions of PostgreSQL is a compelling reason to stay current with the latest major version. However, some queries or operations in your application may experience performance regressions, so be sure to test for performance in addition to compatibility with your application before you upgrade.

Summary

The PostgreSQL community is retiring PostgreSQL 9.5 on February 11, 2021. Amazon will reach end of life for PostgreSQL 9.5 on March 16, 2021. Now is the time for you to upgrade all your PostgreSQL 9.5 instances. You can initiate upgrades to PostgreSQL 12 or higher at any time from until March 16, 2021. After March 16, Amazon RDS for PostgreSQL will automatically upgrade any remaining PostgreSQL 9.5 instances to version 12 whether or not they are in a maintenance window. Restoration of Amazon RDS for PostgreSQL 9.5 database snapshots will result in an automatic upgrade of the restored database to a still-supported version at the time.

About the Author

Vivek Kumar is a Solutions Architect at AWS based out of New York. He works with customers providing technical assistance and architectural guidance on various AWS services. Prior to AWS, Vivek worked for over 23 years in software engineering and architecture roles for various large-scale enterprises.

Aditya Badhwar is a Solutions Architect at AWS. He works with digital native business customers providing architectural guidance on AWS services.