Upgrading Amazon Aurora PostgreSQL DB clusters

Amazon Aurora makes new versions of the PostgreSQL database engine available in AWS Regions only after extensive testing. You can upgrade your Aurora PostgreSQL DB clusters to the new version when it's available in your Region.

Depending on the version of Aurora PostgreSQL that your DB cluster is currently running, an upgrade to the new release is either a minor upgrade or a major upgrade. For example, upgrading an Aurora PostgreSQL 11.15 DB cluster to Aurora PostgreSQL 13.6 is a major version upgrade. Upgrading an Aurora PostgreSQL 13.3 DB cluster to Aurora PostgreSQL 13.7 is a minor version upgrade. In the following topics, you can find information about how to perform both types of upgrades.

Overview of the Aurora PostgreSQL upgrade processes

The differences between major and minor version upgrades are as follows:

Aurora PostgreSQL DB clusters occasionally require operating system updates. These updates might include a newer version of glibc library. During such updates, we recommend you to follow the guidelines as described in Collations supported in Aurora PostgreSQL.

Getting a list of available versions in your AWS Region

You can get a list of all engine versions available as upgrade targets for your Aurora PostgreSQL DB cluster by querying your AWS Region using the describe-db-engine-versions AWS CLI command, as follows.

For Linux, macOS, or Unix:

aws rds describe-db-engine-versions \
  --engine aurora-postgresql \
  --engine-version version-number \
  --query 'DBEngineVersions[*].ValidUpgradeTarget[*].{EngineVersion:EngineVersion}' \
  --output text

For Windows:

aws rds describe-db-engine-versions ^
  --engine aurora-postgresql ^
  --engine-version version-number ^
  --query "DBEngineVersions[*].ValidUpgradeTarget[*].{EngineVersion:EngineVersion}" ^
  --output text

For example, to identify the valid upgrade targets for an Aurora PostgreSQL version 12.10 DB cluster, run the following AWS CLI command:

For Linux, macOS, or Unix:

aws rds describe-db-engine-versions \
  --engine aurora-postgresql \
  --engine-version 12.10 \
  --query 'DBEngineVersions[*].ValidUpgradeTarget[*].{EngineVersion:EngineVersion}' \
  --output text

For Windows:

aws rds describe-db-engine-versions ^
  --engine aurora-postgresql ^
  --engine-version 12.10 ^
  --query "DBEngineVersions[*].ValidUpgradeTarget[*].{EngineVersion:EngineVersion}" ^
  --output text

In this table, you can find both major and minor version upgrade targets that are available for various Aurora PostgreSQL DB versions.

For any version that you're considering, always check the availability of your cluster's DB instance class. For example, db.r4 isn't supported for Aurora PostgreSQL 13. If your Aurora PostgreSQL DB cluster currently uses a db.r4 instance class, you need to move to db.r5 before trying to upgrade. For more information about DB instance classes, including which ones are Graviton2-based and which ones are Intel-based, see Aurora DB instance classes.

How to perform a major version upgrade

Major version upgrades might contain database changes that are not backward-compatible with previous versions of the database. New functionality in a new version can cause your existing applications to stop working correctly. To avoid issues, Amazon Aurora doesn't apply major version upgrades automatically. Rather, we recommend that you carefully plan for a major version upgrade by following these steps:

You can get a list of engine versions available as major version upgrade targets for your Aurora PostgreSQL DB cluster by querying your AWS Region using the describe-db-engine-versions AWS CLI command, as follows.

For Linux, macOS, or Unix:

aws rds describe-db-engine-versions \
  --engine aurora-postgresql \
  --engine-version version-number \
  --query 'DBEngineVersions[].ValidUpgradeTarget[?IsMajorVersionUpgrade == `true`].{EngineVersion:EngineVersion}' \
  --output text

For Windows:

aws rds describe-db-engine-versions ^
  --engine aurora-postgresql ^
  --engine-version version-number ^
  --query "DBEngineVersions[].ValidUpgradeTarget[?IsMajorVersionUpgrade == `true`].{EngineVersion:EngineVersion}" ^
  --output text

In some cases, the version that you want to upgrade to isn't a target for your current version. In such cases, use the information in the versions table to perform minor version upgrades until your cluster is at a version that has your chosen target in its row of targets.

Testing an upgrade of your production DB cluster to a new major version

Each new major version includes enhancements to the query optimizer that are designed to improve performance. However, your workload might include queries that result in a worse performing plan in the new version. That's why we recommend that you test and review performance before upgrading in production. You can manage query plan stability across versions by using the Query Plan Management (QPM) extension, as detailed in Ensuring plan stability after a major version upgrade.

Before upgrading your production Aurora PostgreSQL DB clusters to a new major version, we strongly recommend that you test the upgrade to verify that all your applications work correctly:

Post-upgrade recommendations

After you complete a major version upgrade, we recommend the following:

Upgrading the Aurora PostgreSQL engine to a new major version

When you initiate the upgrade process to a new major version, Aurora PostgreSQL takes a snapshot of the Aurora DB cluster before it makes any changes to your cluster. This snapshot is created for major version upgrades only, not minor version upgrades. When the upgrade process completes, you can find this snapshot among the manual snapshots listed under Snapshots in the RDS console. The snapshot name includes preupgrade as its prefix, the name of your Aurora PostgreSQL DB cluster, the source version, the target version, and the date and timestamp, as shown in the following example.

preupgrade-docs-lab-apg-global-db-12-8-to-13-6-2022-05-19-00-19

After the upgrade completes, you can use the snapshot that Aurora created and stored in your manual snapshot list to restore the DB cluster to its previous version, if necessary.

During the major version upgrade process, Aurora allocates a volume and clones the source Aurora PostgreSQL DB cluster. If the upgrade fails for any reason, Aurora PostgreSQL uses the clone to roll back the upgrade. After more than 15 clones of a source volume are allocated, subsequent clones become full copies and take longer. This can cause the upgrade process also to take longer. If Aurora PostgreSQL rolls back the upgrade, be aware of the following:

To safely upgrade the DB instances that make up your cluster, Aurora PostgreSQL uses the pg_upgrade utility. After the writer upgrade completes, each reader instance experiences a brief outage while it's upgraded to the new major version. To learn more about this PostgreSQL utility, see pg_upgrade in the PostgreSQL documentation.

You can upgrade your Aurora PostgreSQL DB cluster to a new version by using the AWS Management Console, the AWS CLI, or the RDS API.

aws rds modify-db-cluster \
    --db-cluster-identifier mydbcluster \
    --engine-version new_version \
    --allow-major-version-upgrade \
    --no-apply-immediately

aws rds modify-db-cluster ^
    --db-cluster-identifier mydbcluster ^
    --engine-version new_version ^
    --allow-major-version-upgrade ^
    --no-apply-immediately

Major upgrades for global databases

For an Aurora global database cluster, the upgrade process upgrades all DB clusters that make up your Aurora global database at the same time. It does so to ensure that each runs the same Aurora PostgreSQL version. It also ensures that any changes to system tables, data file formats, and so on, are automatically replicated to all secondary clusters.

To upgrade a global database cluster to a new major version of Aurora PostgreSQL, we recommend that you test your applications on the upgraded version, as detailed in Testing an upgrade of your production DB cluster to a new major version. Be sure to prepare your DB cluster parameter group and DB parameter group settings for each AWS Region in your Aurora global database before the upgrade as detailed in step 1. of Testing an upgrade of your production DB cluster to a new major version.

If your Aurora PostgreSQL global database cluster has a recovery point objective (RPO) set for its rds.global_db_rpo parameter, make sure to reset the parameter before upgrading. The major version upgrade process doesn't work if the RPO is turned on. By default, this parameter is turned off. For more information about Aurora PostgreSQL global databases and RPO, see Managing RPOs for Aurora PostgreSQL–based global databases.

If you verify that your applications can run as expected on the trial deployment of the new version, you can start the upgrade process. To do so, see Upgrading the Aurora PostgreSQL engine to a new major version. Be sure to choose the top-level item from the Databases list in the RDS console, Global database, as shown in the following image.

As with any modification, you can confirm that you want the process to proceed when prompted.

Rather than using the console, you can start the upgrade process by using the AWS CLI or the RDS API. As with the console, you operate on the Aurora global database cluster rather than any of its constituents, as follows:

Before performing a minor version upgrade

We recommend that you perform the following actions to reduce the downtime during a minor version upgrade:

How to perform minor version upgrades and apply patches

Minor version upgrades and patches become available in AWS Regions only after rigorous testing. Before releasing upgrades and patches, Aurora PostgreSQL tests to ensure that known security issues, bugs, and other issues that emerge after the release of the minor community version don't disrupt overall Aurora PostgreSQL fleet stability.

As Aurora PostgreSQL makes new minor versions available, the instances that make up your Aurora PostgreSQL DB cluster can be automatically upgraded during your specified maintenance window. For this to happen, your Aurora PostgreSQL DB cluster must have the Enable auto minor version upgrade option turned on. All DB instances that make up your Aurora PostgreSQL DB cluster must have the automatic minor version upgrade (AmVU) option turned on so that the minor upgrade to be applied throughout the cluster.

You can check the value of the Enable auto minor version upgrade option for all your Aurora PostgreSQL DB clusters by using the describe-db-instances AWS CLI command with the following query.

aws rds describe-db-instances \
  --query '*[].{DBClusterIdentifier:DBClusterIdentifier,DBInstanceIdentifier:DBInstanceIdentifier,AutoMinorVersionUpgrade:AutoMinorVersionUpgrade}'

This query returns a list of all Aurora DB clusters and their instances with a true or false value for the status of the AutoMinorVersionUpgrade setting. The command as shown assumes that you have your AWS CLI configured to target your default AWS Region.

For more information about the AmVU option and how to modify your Aurora DB cluster to use it, see Automatic minor version upgrades for Aurora DB clusters.

You can upgrade your Aurora PostgreSQL DB clusters to new minor versions either by responding to maintenance tasks, or by modifying the cluster to use the new version.

You can identify any available upgrades or patches for your Aurora PostgreSQL DB clusters by using the RDS console and opening the Recommendations menu. There, you can find a list of various maintenance issues such as Old minor versions. Depending on your production environment, you can choose to Schedule the upgrade or take immediate action, by choosing Apply now, as shown following.

To learn more about how to maintain an Aurora DB cluster, including how to manually apply patches and minor version upgrades, see Maintaining an Amazon Aurora DB cluster.

Minor release upgrades and zero-downtime patching

Upgrading an Aurora PostgreSQL DB cluster involves the possibility of an outage. During the upgrade process, the database is shut down as it's being upgraded. If you start the upgrade while the database is busy, you lose all connections and transactions that the DB cluster is processing. If you wait until the database is idle to perform the upgrade, you might have to wait a long time.

The zero-downtime patching (ZDP) feature improves the upgrading process. With ZDP, both minor version upgrades and patches can be applied with minimal impact to your Aurora PostgreSQL DB cluster. ZDP is used when applying patches or newer minor version upgrades to Aurora PostgreSQL versions and other higher releases of these minor versions and newer major versions. That is, upgrading to new minor versions from any of these releases onward uses ZDP.

The following table shows the Aurora PostgreSQL versions and DB instance classes where ZDP is available:

ZDP works by preserving current client connections to your Aurora PostgreSQL DB cluster throughout the Aurora PostgreSQL upgrade process. However, in the following cases, connections will be dropped for ZDP to complete:

During the upgrade process using ZDP, the database engine looks for a quiet point to pause all new transactions. This action safeguards the database during patches and upgrades. To make sure that your applications run smoothly with paused transactions, we recommend integrating retry logic into your code. This approach ensures that the system can manage any brief downtime without failing and can retry the new transactions after the upgrade.

When ZDP completes successfully, application sessions are maintained except for those with dropped connections, and the database engine restarts while the upgrade is still in progress. Although the database engine restart can cause a temporary drop in throughput, this typically lasts only for a few seconds or at most, approximately one minute.

In some cases, zero-downtime patching (ZDP) might not succeed. For example, parameter changes that are in a pending state on your Aurora PostgreSQL DB cluster or its instances interfere with ZDP.

You can find metrics and events for ZDP operations in Events page in the console. The events include the start of the ZDP upgrade and completion of the upgrade. In this event you can find how long the process took, and the numbers of preserved and dropped connections that occurred during the restart. You can find details in your database error log.

Upgrading the Aurora PostgreSQL engine to a new minor version

You can upgrade your Aurora PostgreSQL DB cluster to a new minor version by using the console, the AWS CLI, or the RDS API. Before performing the upgrade, we recommend that you follow the same best practice that we recommend for major version upgrades. As with new major versions, new minor versions can also have optimizer improvements, such as fixes, that can cause query plan regressions. To ensure plan stability, we recommend that you use the Query Plan Management (QPM) extension as detailed in Ensuring plan stability after a major version upgrade.

aws rds modify-db-cluster \
    --db-cluster-identifier mydbcluster \
    --engine-version new_version \
    --no-apply-immediately

aws rds modify-db-cluster ^
    --db-cluster-identifier mydbcluster ^
    --engine-version new_version ^
    --no-apply-immediately

Upgrading PostgreSQL extensions

Upgrading your Aurora PostgreSQL DB cluster to a new major or minor version doesn't upgrade the PostgreSQL extensions at the same time. For most extensions, you upgrade the extension after the major or minor version upgrade completes. However, in some cases, you upgrade the extension before you upgrade the Aurora PostgreSQL DB engine. For more information, see the list of extensions to update in Testing an upgrade of your production DB cluster to a new major version.

Installing PostgreSQL extensions requires rds_superuser privileges. Typically, an rds_superuser delegates permissions over specific extensions to relevant users (roles), to facilitate the management of a given extension. That means that the task of upgrading all the extensions in your Aurora PostgreSQL DB cluster might involve many different users (roles). Keep this in mind especially if you want to automate the upgrade process by using scripts. For more information about PostgreSQL privileges and roles, see Security with Amazon Aurora PostgreSQL.

To update an extension after an engine upgrade, use the ALTER EXTENSION UPDATE command.

ALTER EXTENSION extension_name UPDATE TO 'new_version';

To list your currently installed extensions, use the PostgreSQL pg_extension catalog in the following command.

SELECT * FROM pg_extension;

To view a list of the specific extension versions that are available for your installation, use the PostgreSQL pg_available_extension_versions view in the following command.

SELECT * FROM pg_available_extension_versions;

Alternative blue/green upgrade technique

In some situations, your top priority is to perform an immediate switchover from the old cluster to an upgraded one. In such situations, you can use a multistep process that runs the old and new clusters side-by-side. Here, you replicate data from the old cluster to the new one until you are ready for the new cluster to take over. For details, see Using Amazon RDS Blue/Green Deployments for database updates.