Upgrading the PostgreSQL DB engine for Amazon RDS - Amazon Relational Database Service

Upgrading the PostgreSQL DB engine for Amazon RDS

There are two types of upgrades that you can manage for your PostgreSQL database:

  • Operating system updates – Occasionally, Amazon RDS might need to update the underlying operating system of your database to apply security fixes or OS changes. You can decide when Amazon RDS applies OS updates by using the RDS console, AWS Command Line Interface (AWS CLI), or RDS API. For more information about OS updates, see Applying updates for a DB instance.

  • Database engine upgrades – When Amazon RDS supports a new version of a database engine, you can upgrade your databases to the new version.

A database in this context is an RDS for PostgreSQL DB instance or Multi-AZ DB cluster.

There are two kinds of engine upgrades for PostgreSQL databases: major version upgrades and minor version upgrades.

Major version upgrades

Major version upgrades can contain database changes that are not backward-compatible with existing applications. As a result, you must manually perform major version upgrades of your databases. You can initiate a major version upgrade by modifying your DB instance or Multi-AZ DB cluster. Before you perform a major version upgrade, we recommend that you follow the steps described in Choosing a major version upgrade for PostgreSQL.

If you're upgrading a DB instance that has in-Region read replicas, Amazon RDS upgrades the replicas along with the primary DB instance.

Amazon RDS doesn't upgrade Multi-AZ DB cluster read replicas. If you perform a major version upgrade of a Multi-AZ DB cluster, then the replication state of its read replicas changes to terminated. You must manually delete and recreate the read replicas after the upgrade completes.

Tip

You can minimize the downtime required for a major version upgrade by using a blue/green deployment. For more information, see Using Amazon RDS Blue/Green Deployments for database updates.

Minor version upgrades

In contrast, minor version upgrades include only changes that are backward-compatible with existing applications. You can initiate a minor version upgrade manually by modifying your database. Or, you can enable the Auto minor version upgrade option when creating or modifying a database. Doing so means that Amazon RDS automatically upgrades your database after testing and approving the new version. If your PostgreSQL database uses read replicas, you must first upgrade all of the read replicas before upgrading the source instance or cluster.

If your database is a Multi-AZ DB instance deployment, Amazon RDS simultaneously upgrades the primary and any standby instances. Therefore, your database might not be available until the upgrade completes. If your database is a Multi-AZ DB cluster deployment, Amazon RDS upgrades the reader DB instances one at a time. Then, one of the reader DB instances switches to be the new writer DB instance. Amazon RDS then upgrades the old writer instance (which is now a reader instance).

Note

The downtime for a minor version upgrade of a Multi-AZ DB instance deployment can last for several minutes. Multi-AZ DB clusters typically reduce the downtime of minor version upgrades to approximately 35 seconds. When used with RDS Proxy, you can further reduce downtime to one second or less. For more information, see Using Amazon RDS Proxy. Alternately, you can use an open source database proxy such as ProxySQL,PgBouncer, or the AWS JDBC Driver for MySQL.

For more information, see Automatic minor version upgrades for PostgreSQL. For information about manually performing a minor version upgrade, see Manually upgrading the engine version.

For more information about database engine versions and the policy for deprecating database engine versions, see Database Engine Versions in the Amazon RDS FAQs.

Overview of upgrading PostgreSQL

To safely upgrade your databases, Amazon RDS uses the pg_upgrade utility described in the PostgreSQL documentation.

When you use the AWS Management Console to upgrade a database, it shows the valid upgrade targets for the database. You can also use the following AWS CLI command to identify the valid upgrade targets for a database:

For Linux, macOS, or Unix:

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

For Windows:

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

For example, to identify the valid upgrade targets for a PostgreSQL version 12.13 database, run the following AWS CLI command:

For Linux, macOS, or Unix:

aws rds describe-db-engine-versions \ --engine postgres \ --engine-version 12.13 \ --query "DBEngineVersions[*].ValidUpgradeTarget[*].{EngineVersion:EngineVersion}" --output text

For Windows:

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

If your backup retention period is greater than 0, Amazon RDS takes two DB snapshots during the upgrade process. The first DB snapshot is of the database before any upgrade changes have been made. If the upgrade fails for your databases, you can restore this snapshot to create a database running the old version. The second DB snapshot is taken after the upgrade completes.

Note

Amazon RDS takes DB snapshots during the upgrade process only if you have set the backup retention period for your database to a number greater than 0. To change the backup retention period for a DB instance, see Modifying an Amazon RDS DB instance. You can't configure a custom backup retention period for a Multi-AZ DB cluster.

When you perform a major version upgrade of a DB instance, any in-Region read replicas are also automatically upgraded. After the upgrade workflow starts, the read replicas wait for the pg_upgrade to complete successfully on the primary DB instance. Then the primary DB instance upgrade waits for the read replica upgrades to complete. You experience an outage until the upgrade is complete. When you perform a major version upgrade of a Multi-AZ DB cluster, the replication state of its read replicas changes to terminated.

After an upgrade is complete, you can't revert to the previous version of the DB engine. If you want to return to the previous version, restore the DB snapshot that was taken before the upgrade to create a new database.

PostgreSQL version numbers

The version numbering sequence for the PostgreSQL database engine is as follows:

  • For PostgreSQL versions 10 and later, the engine version number is in the form major.minor. The major version number is the integer part of the version number. The minor version number is the fractional part of the version number.

    A major version upgrade increases the integer part of the version number, such as upgrading from 10.minor to 11.minor.

  • For PostgreSQL versions earlier than 10, the engine version number is in the form major.major.minor. The major engine version number is both the integer and the first fractional part of the version number. For example, 9.6 is a major version. The minor version number is the third part of the version number. For example, for version 9.6.12, the 12 is the minor version number.

    A major version upgrade increases the major part of the version number. For example, an upgrade from 9.6.12 to 11.14 is a major version upgrade, where 9.6 and 11 are the major version numbers.

For information about RDS Extended Support version numbering, see Amazon RDS Extended Support version naming.

RDS version number

RDS version numbers use the major.minor.patch naming scheme. An RDS patch version includes important bug fixes added to a minor version after its release. For information about RDS Extended Support version numbering, see Amazon RDS Extended Support version naming.

To identify the Amazon RDS version number of your database, you must first create the rds_tools extension by using the following command:

CREATE EXTENSION rds_tools;

Starting with the release of PostgreSQL version 15.2-R2, you can find out the RDS version number of your RDS for PostgreSQL database with the following SQL query:

postgres=> SELECT rds_tools.rds_version();

For example, querying an RDS for PostgreSQL 15.2 database returns the following:

rds_version ---------------- 15.2.R2 (1 row)

Choosing a major version upgrade for PostgreSQL

Major version upgrades can contain changes that are not backward-compatible with previous versions of the database. New functionality can cause your existing applications to stop working correctly. For this reason, Amazon RDS doesn't apply major version upgrades automatically. To perform a major version upgrade, you modify your database manually. Make sure that you thoroughly test any upgrade to verify that your applications work correctly before applying the upgrade to your production databases. When you do a PostgreSQL major version upgrade, we recommend that you follow the steps described in How to perform a major version upgrade.

When you upgrade a PostgreSQL Single-AZ DB instance or Multi-AZ DB instance deployment to its next major version, any read replicas associated with the database are also upgraded to that next major version. In some cases, you can skip to a higher major version when upgrading. If your upgrade skips a major version, the read replicas are also upgraded to that target major version. Upgrades to version 11 that skip other major versions have certain limitations. You can find the details in the steps described in How to perform a major version upgrade.

Most PostgreSQL extensions aren't upgraded during a PostgreSQL engine upgrade. These must be upgraded separately. For more information, see Upgrading PostgreSQL extensions.

You can find out which major versions are available for your RDS for PostgreSQL database by running the following AWS CLI query:

aws rds describe-db-engine-versions --engine postgres --engine-version your-version --query "DBEngineVersions[*].ValidUpgradeTarget[*].{EngineVersion:EngineVersion}" --output text

The following table summarizes the results of this query for all available versions. An asterisk (*) on the version number means that version is deprecated. If your current version is deprecated, we recommend that you upgrade to the newest minor version upgrade target or to one of the other available upgrade targets for that version. For more information about the RDS for PostgreSQL version 9.6 deprecation, see Deprecation of PostgreSQL version 9.6. For more information about the RDS for PostgreSQL version 10 deprecation, see Deprecation of PostgreSQL version 10.

Current source version (* deprecated) Newest major version upgrade target Other available upgrade targets
16.1 16.2
15.6 16.2
15.5 16.2 16.1 15.6
15.4 16.2 16.1 15.6 15.5
15.3 16.2 16.1 15.6 15.5 15.4
15.2 16.2 16.1 15.6 15.5 15.4 15.3
14.11 16.2 15.6
14.10 16.1 15.6 15.5 14.11
14.9 15.6 15.5 15.4 14.11 14.10
14.8 15.6 15.5 15.4 15.3 14.11 14.10 14.9
14.7 15.6 15.5 15.4 15.3 15.2 14.11 14.10 14.9 14.8
14.6 15.6 15.5 15.4 15.3 15.2 14.11 14.10 14.9 14.8 14.7
14.5 15.6 15.5 15.4 15.3 15.2 14.11 14.10 14.9 14.8 14.7 14.6
14.4 15.6 15.5 15.4 15.3 15.2 14.11 14.10 14.9 14.8 14.7 14.6 14.5
14.3 15.6 15.5 15.4 15.3 15.2 14.11 14.10 14.9 14.8 14.7 14.6 14.5 14.4
14.2* 15.6 15.5 15.4 15.3 15.2 14.11 14.10 14.9 14.8 14.7 14.6 14.5 14.4 14.3
14.1* 15.6 15.5 15.4 15.3 15.2 14.11 14.10 14.9 14.8 14.7 14.6 14.5 14.4 14.3 14.2
13.14 16.2 15.6 14.11
13.13 16.1 15.5 14.11 14.10 13.14
13.12 15.4 14.11 14.10 14.9 13.14 13.13
13.11 15.3 14.11 14.10 14.9 14.8 13.14 13.13 13.12
13.10 15.2 14.11 14.10 14.9 14.8 14.7 13.14 13.13 13.12 13.11
13.9 14.11 14.10 14.9 14.8 14.7 14.6 13.14 13.13 13.12 13.11 13.10
13.8 14.11 14.10 14.9 14.8 14.7 14.6 14.5 13.14 13.13 13.12 13.11 13.10 13.9
13.7 14.11 14.10 14.9 14.8 14.7 14.6 14.5 14.4 14.3 13.14 13.13 13.11 13.10 13.9 13.8
13.6* 14.11 14.10 14.9 14.8 14.7 14.6 14.5 14.4 14.3 14.2 13.14 13.13 13.11 13.10 13.9 13.8 13.7
13.5* 14.11 14.10 14.9 14.8 14.7 14.6 14.5 14.4 14.3 14.2 14.1 13.14 13.13 13.11 13.10 13.9 13.8 13.7 13.6
13.4* 14.11 14.10 14.9 14.8 14.7 14.6 14.5 14.4 14.3 14.2 14.1 13.14 13.13 13.11 13.10 13.9 13.8 13.7 13.6 13.5
13.3* 14.11 14.10 14.9 14.8 14.7 14.6 14.5 14.4 14.3 14.2 14.1 13.14 13.13 13.11 13.10 13.9 13.8 13.7 13.6 13.5 13.4
13.2*, 13.1* 14.11 14.10 14.9 14.8 14.7 14.6 14.5 14.4 14.3 14.2 14.1 13.14 13.13 13.11 13.10 13.9 13.8 13.7 13.6 13.5 13.4
12.18 16.2 15.6 14.11 13.14
12.17 16.1 15.5 14.10 13.14 13.13 12.18
12.16 15.4 14.9 13.14 13.13 13.12 12.18 12.17
12.15 15.3 14.8 13.14 13.13 13.12 13.11 12.18 12.17 12.16
12.14 15.2 14.7 13.14 13.13 13.12 13.11 13.10 12.18 12.17 12.16 12.15
12.13 14.6 13.14 13.13 13.12 13.11 13.10 13.9 12.18 12.17 12.16 12.15 12.14
12.12 14.5 13.14 13.13 13.12 13.11 13.10 13.9 13.8 12.18 12.17 12.16 12.15 12.14 12.13
12.11 14.4 14.3 13.14 13.13 13.12 13.11 13.10 13.9 13.8 13.7 12.18 12.17 12.16 12.15 12.14 12.13 12.12
12.10* 14.2 13.14 13.13 13.12 13.11 13.10 13.9 13.8 13.7 13.6 12.18 12.17 12.16 12.15 12.14 12.13 12.12 12.11
12.9* 14.1 13.14 13.13 13.12 13.11 13.10 13.9 13.8 13.7 13.6 13.5 12.18 12.17 12.16 12.15 12.14 12.13 12.12 12.11 12.10
12.8* 13.14 13.13 13.12 13.11 13.10 13.9 13.8 13.7 13.6 13.5 13.4 12.18 12.17 12.16 12.15 12.14 12.13 12.12 12.11 12.10 12.9
12.7* 13.14 13.13 13.12 13.11 13.10 13.9 13.8 13.7 13.6 13.5 13.4 13.3 12.18 12.17 12.16 12.15 12.14 12.13 12.12 12.11 12.10 12.9 12.8
12.6*, 12.5*, 12.4*, 12.3*, 12.2* 13.14 13.13 13.12 13.11 13.7 13.6 12.18 12.17 12.16 12.15 12.14 12.13 12.12 12.11 12.10 12.9 12.8 12.7
11.22 16.1 15.5 14.10 13.13 12.17
11.21* 15.4 14.9 13.12 12.17 12.16 11.22
11.20* 15.3 14.8 13.11 12.17 12.16 12.15 11.22 11.21
11.19* 15.2 14.7 13.10 12.17 12.16 12.15 12.14 11.22 11.21 11.20
11.18* 14.6 13.9 12.17 12.16 12.15 12.14 12.13 11.22 11.21 11.20 11.19
11.17* 14.5 13.8 12.17 12.16 12.15 12.14 12.13 12.12 11.22 11.21 11.20 11.19 11.18
11.16* 14.4 14.3 13.7 12.17 12.16 12.15 12.14 12.13 12.12 12.11 11.22 11.21 11.20 11.19 11.18 11.17
11.15* 14.2 13.6 12.17 12.16 12.15 12.14 12.13 12.12 12.11 12.10 11.22 1121 11.20 11.19 11.18 11.17 11.16
11.14* 14.1 13.5 12.17 12.16 12.15 12.14 12.13 12.12 12.11 12.10 12.9 11.22 11.21 11.20 11.19 11.18 11.17 11.16 11.15
11.13* 13.4 12.17 12.16 12.15 12.14 12.13 12.12 12.11 12.10 12.9 12.8 11.22 11.21 11.20 11.19 11.18 11.17 11.16 11.15 11.14
11.12* 13.3 12.17 12.16 12.15 12.14 12.13 12.12 12.11 12.10 12.9 12.8 12.7 11.22 11.21 11.20 11.19 11.18 11.17 11.16 11.15 11.14 11.13
10.23* 14.6 13.9 12.13 11.22 11.21 11.20 11.19 11.18
10.22* 14.5 13.8 12.12 11.22 11.21 11.20 11.19 11.18 11.17 10.23
10.21* 14.4 14.3 13.7 12.11 11.22 11.21 11.20 11.19 11.18 11.17 11.16 10.23 10.22
10.20* 14.2 13.6 12.10 11.22 11.21 11.20 11.19 11.18 11.17 11.16 11.15 10.23 10.22 10.21
10.19* 14.1 13.5 12.9 11.22 11.21 11.20 11.19 11.18 11.17 11.16 11.15 11.14 10.23 10.22 10.21 10.20
10.18* 13.4 12.8 11.22 11.21 11.20 11.19 11.18 11.17 11.16 11.15 11.14 11.13 10.23 10.22 10.21 10.20 10.19
10.17* 13.3 12.7 11.22 11.21 11.20 11.19 11.18 11.17 11.16 11.15 11.14 11.13 11.12 10.23 10.22 10.21 10.20 10.19 10.18
9.6.24* 14.1 13.5 12.9 11.14 10.20 10.19
9.6.23* 13.4 12.8 11.13 10.20 10.19 10.18 9.6.24
9.6.22* 13.3 12.7 11.12 10.20 10.19 10.18 10.17 9.6.24 9.6.23
9.6.19*, 9.6.18*, 9.6.17*, 9.6.16*, 9.6.15*, 9.6.14*, 9.6.12*, 9.6.11*9.6.10*, 9.6.9*, 9.6.8*, 9.6.6*, 9.6.5*, 9.6.3*, 9.6.2*, 9.6.1* 9.6.24 14.1 13.5 12.9 11.14 10.20 10.19 9.6.23 9.6.22

How to perform a major version upgrade

We recommend the following process when performing a major version upgrade on an Amazon RDS for PostgreSQL database:

  1. Have a version-compatible parameter group ready – If you are using a custom parameter group, you have two options. You can specify a default parameter group for the new DB engine version. Or you can create your own custom parameter group for the new DB engine version. For more information, see Working with parameter groups and Working with DB cluster parameter groups for Multi-AZ DB clusters.

  2. Check for unsupported database classes – Check that your database's instance class is compatible with the PostgreSQL version you are upgrading to. For more information, see Supported DB engines for DB instance classes.

  3. Check for unsupported usage:

    • Prepared transactions – Commit or roll back all open prepared transactions before attempting an upgrade.

      You can use the following query to verify that there are no open prepared transactions on your database.

      SELECT count(*) FROM pg_catalog.pg_prepared_xacts;
    • Reg* data types – Remove all uses of the reg* data types before attempting an upgrade. Except for regtype and regclass, you can't upgrade the reg* data types. The pg_upgrade utility can't persist this data type, which is used by Amazon RDS to do the upgrade.

      To verify that there are no uses of unsupported reg* data types, use the following query for each database.

      SELECT count(*) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n, pg_catalog.pg_attribute a WHERE c.oid = a.attrelid AND NOT a.attisdropped AND a.atttypid IN ('pg_catalog.regproc'::pg_catalog.regtype, 'pg_catalog.regprocedure'::pg_catalog.regtype, 'pg_catalog.regoper'::pg_catalog.regtype, 'pg_catalog.regoperator'::pg_catalog.regtype, 'pg_catalog.regconfig'::pg_catalog.regtype, 'pg_catalog.regdictionary'::pg_catalog.regtype) AND c.relnamespace = n.oid AND n.nspname NOT IN ('pg_catalog', 'information_schema');
  4. Handle logical replication slots – An upgrade can't occur if the database has any logical replication slots. Logical replication slots are typically used for AWS DMS migration and for replicating tables from the database to data lakes, BI tools, and other targets. Before upgrading, make sure that you know the purpose of any logical replication slots that are in use, and confirm that it's okay to delete them. If the logical replication slots are still being used, you shouldn't delete them, and you can't proceed with the upgrade.

    If the logical replication slots aren't needed, you can delete them using the following SQL:

    SELECT * FROM pg_replication_slots; SELECT pg_drop_replication_slot(slot_name);

    Logical replication setups that use the pglogical extension also need to have slots dropped for a successful major version upgrade. For information about how to identify and drop slots created using the pglogical extension, see Managing logical replication slots for RDS for PostgreSQL.

  5. Handle read replicas – An upgrade of a Single-AZ DB instance or Multi-AZ DB instance deployment also upgrades the in-Region read replicas along with the primary DB instance. Amazon RDS doesn't upgrade Multi-AZ DB cluster read replicas.

    You can't upgrade read replicas separately. If you could, it could lead to situations where the primary and replica databases have different PostgreSQL major versions. However, read replica upgrades might increase downtime on the primary DB instance. To prevent a read replica upgrade, promote the replica to a standalone instance or delete it before starting the upgrade process.

    The upgrade process recreates the read replica's parameter group based on the read replica's current parameter group. You can apply a custom parameter group to a read replica only after the upgrade completes by modifying the read replica. For more information about read replicas, see Working with read replicas for Amazon RDS for PostgreSQL.

  6. Perform a backup – We recommend that you perform a backup before performing the major version upgrade so that you have a known restore point for your database. If your backup retention period is greater than 0, the upgrade process creates DB snapshots of your database before and after upgrading. To change your backup retention period, see Modifying an Amazon RDS DB instance and Modifying a Multi-AZ DB cluster.

    To perform a backup manually, see Creating a DB snapshot for a Single-AZ DB instance and Creating a Multi-AZ DB cluster snapshot.

  7. Upgrade certain extensions before a major version upgrade – If you plan to skip a major version with the upgrade, you need to update certain extensions before performing the major version upgrade. For example, upgrading from versions 9.5.x or 9.6.x to version 11.x skips a major version. The extensions to update include PostGIS and related extensions for processing spatial data.

    • address_standardizer

    • address_standardizer_data_us

    • postgis_raster

    • postgis_tiger_geocoder

    • postgis_topology

    Run the following command for each extension that you're using:

    ALTER EXTENSION PostgreSQL-extension UPDATE TO 'new-version';

    For more information, see Upgrading PostgreSQL extensions. To learn more about upgrading PostGIS, see Step 6: Upgrade the PostGIS extension.

  8. Drop certain extensions before the major version upgrade – An upgrade that skips a major version to version 11.x doesn't support updating the pgRouting extension. Upgrading from versions 9.4.x, 9.5.x, or 9.6.x to versions 11.x skips a major version. It's safe to drop the pgRouting extension and then reinstall it to a compatible version after the upgrade. For the extension versions you can update to, see Supported PostgreSQL extension versions.

    The tsearch2 and chkpass extensions are no longer supported for PostgreSQL versions 11 or later. If you are upgrading to version 11.x, drop the tsearch2, and chkpass extensions before the upgrade.

  9. Drop unknown data types – Drop unknown data types depending on the target version.

    PostgreSQL version 10 stopped supporting the unknown data type. If a version 9.6 database uses the unknown data type, an upgrade to a version 10 shows an error message such as the following:

    Database instance is in a state that cannot be upgraded: PreUpgrade checks failed: The instance could not be upgraded because the 'unknown' data type is used in user tables. Please remove all usages of the 'unknown' data type and try again."

    To find the unknown data type in your database so you can remove the offending column or change it to a supported data type, use the following SQL:

    SELECT DISTINCT data_type FROM information_schema.columns WHERE data_type ILIKE 'unknown';
  10. Perform an upgrade dry run – We highly recommend testing a major version upgrade on a duplicate of your production database before attempting the upgrade on your production database. You can monitor the execution plans on the duplicate test database for any possible execution plan regressions and to evaluate its performance. To create a duplicate test instance, you can either restore your database from a recent snapshot or do a point-in-time restore of your database to its latest restorable time.

    For more information, see Restoring from a snapshot or Restoring a DB instance to a specified time. For Multi-AZ DB clusters, see Restoring from a snapshot to a Multi-AZ DB cluster or Restoring a Multi-AZ DB cluster to a specified time.

    For details on performing the upgrade, see Manually upgrading the engine version.

    In upgrading a version 9.6 database to version 10, be aware that PostgreSQL 10 enables parallel queries by default. You can test the impact of parallelism before the upgrade by changing the max_parallel_workers_per_gather parameter on your test database to 2.

    Note

    The default value for max_parallel_workers_per_gather parameter in the default.postgresql10 DB parameter group is 2.

    For more information, see Parallel Query in the PostgreSQL documentation. To disable parallelism on version 10, set the max_parallel_workers_per_gather parameter to 0.

    During the major version upgrade, the public and template1 databases and the public schema in every database are temporarily renamed. These objects appear in the logs with their original name and a random string appended. The string is appended so that custom settings such as locale and owner are preserved during the major version upgrade. After the upgrade completes, the objects are renamed back to their original names.

    Note

    During the major version upgrade process, you can't do a point-in-time restore of your DB instance or Multi-AZ DB cluster. After Amazon RDS performs the upgrade, it takes an automatic backup of the database. You can perform a point-in-time restore to times before the upgrade began and after the automatic backup of your database has completed.

  11. If an upgrade fails with precheck procedure errors, resolve the issues – During the major version upgrade process, Amazon RDS for PostgreSQL first runs a precheck procedure to identify any issues that might cause the upgrade to fail. The precheck procedure checks all potential incompatible conditions across all databases in the instance.

    If the precheck encounters an issue, it creates a log event indicating the upgrade precheck failed. The precheck process details are in an upgrade log named pg_upgrade_precheck.log for all the databases of a database. Amazon RDS appends a timestamp to the file name. For more information about viewing logs, see Monitoring Amazon RDS log files.

    If a read replica upgrade fails at precheck, replication on the failed read replica is broken and the read replica is put in the terminated state. Delete the read replica and recreate a new read replica based on the upgraded primary DB instance.

    Resolve all of the issues identified in the precheck log and then retry the major version upgrade. The following is an example of a precheck log.

    ------------------------------------------------------------------------ Upgrade could not be run on Wed Apr 4 18:30:52 2018 ------------------------------------------------------------------------- The instance could not be upgraded from 9.6.11 to 10.6 for the following reasons. Please take appropriate action on databases that have usage incompatible with the requested major engine version upgrade and try the upgrade again. * There are uncommitted prepared transactions. Please commit or rollback all prepared transactions.* One or more role names start with 'pg_'. Rename all role names that start with 'pg_'. * The following issues in the database 'my"million$"db' need to be corrected before upgrading:** The ["line","reg*"] data types are used in user tables. Remove all usage of these data types. ** The database name contains characters that are not supported by RDS for PostgreSQL. Rename the database. ** The database has extensions installed that are not supported on the target database version. Drop the following extensions from your database: ["tsearch2"]. * The following issues in the database 'mydb' need to be corrected before upgrading:** The database has views or materialized views that depend on 'pg_stat_activity'. Drop the views.
  12. If a read replica upgrade fails while upgrading the database, resolve the issue – A failed read replica is placed in the incompatible-restore state and replication is terminated on the database. Delete the read replica and recreate a new read replica based on the upgraded primary DB instance.

    Note

    Amazon RDS doesn't upgrade read replicas for Multi-AZ DB clusters. If you perform a major version upgrade on a Multi-AZ DB cluster, then the replication state of its read replicas changes to terminated.

    A read replica upgrade might fail for the following reasons:

    • It was unable to catch up with the primary DB instance even after a wait time.

    • It was in a terminal or incompatible lifecycle state such as storage-full, incompatible-restore, and so on.

    • When the primary DB instance upgrade started, there was a separate minor version upgrade running on the read replica.

    • The read replica used incompatible parameters.

    • The read replica was unable to communicate with the primary DB instance to synchronize the data folder.

  13. Upgrade your production database – When the dry-run major version upgrade is successful, you should be able to upgrade your production database with confidence. For more information, see Manually upgrading the engine version.

  14. Run the ANALYZE operation to refresh the pg_statistic table. You should do this for every database on all your PostgreSQL databases. Optimizer statistics aren't transferred during a major version upgrade, so you need to regenerate all statistics to avoid performance issues. Run the command without any parameters to generate statistics for all regular tables in the current database, as follows:

    ANALYZE VERBOSE;

    The VERBOSE flag is optional, but using it shows you the progress. For more information, see ANALYZE in the PostgreSQL documentation.

    Note

    Run ANALYZE on your system after the upgrade to avoid performance issues.

After the major version upgrade is complete, we recommend the following:

  • A PostgreSQL upgrade doesn't upgrade any PostgreSQL extensions. To upgrade extensions, see Upgrading PostgreSQL extensions.

  • Optionally, use Amazon RDS to view two logs that the pg_upgrade utility produces. These are pg_upgrade_internal.log and pg_upgrade_server.log. Amazon RDS appends a timestamp to the file name for these logs. You can view these logs as you can any other log. For more information, see Monitoring Amazon RDS log files.

    You can also upload the upgrade logs to Amazon CloudWatch Logs. For more information, see Publishing PostgreSQL logs to Amazon CloudWatch Logs.

  • To verify that everything works as expected, test your application on the upgraded database with a similar workload. After the upgrade is verified, you can delete this test instance.

Automatic minor version upgrades for PostgreSQL

If you enable the Auto minor version upgrade option when creating or modifying a DB instance or Multi-AZ DB cluster, you can have your database automatically upgraded.

For each RDS for PostgreSQL major version, one minor version is designated by RDS as the automatic upgrade version. After a minor version has been tested and approved by Amazon RDS, the minor version upgrade occurs automatically during your maintenance window. RDS doesn't automatically set newer released minor versions as the automatic upgrade version. Before RDS designates a newer automatic upgrade version, several criteria are considered, such as the following:

  • Known security issues

  • Bugs in the PostgreSQL community version

  • Overall fleet stability since the minor version was released

You can use the following AWS CLI command to determine the current automatic minor upgrade target version for a specified PostgreSQL minor version in a specific AWS Region.

For Linux, macOS, or Unix:

aws rds describe-db-engine-versions \ --engine postgres \ --engine-version minor-version \ --region region \ --query "DBEngineVersions[*].ValidUpgradeTarget[*].{AutoUpgrade:AutoUpgrade,EngineVersion:EngineVersion}" \ --output text

For Windows:

aws rds describe-db-engine-versions ^ --engine postgres ^ --engine-version minor-version ^ --region region ^ --query "DBEngineVersions[*].ValidUpgradeTarget[*].{AutoUpgrade:AutoUpgrade,EngineVersion:EngineVersion}" ^ --output text

For example, the following AWS CLI command determines the automatic minor upgrade target for PostgreSQL minor version 12.13 in the US East (Ohio) AWS Region (us-east-2).

For Linux, macOS, or Unix:

aws rds describe-db-engine-versions \ --engine postgres \ --engine-version 12.13 \ --region us-east-2 \ --query "DBEngineVersions[*].ValidUpgradeTarget[*].{AutoUpgrade:AutoUpgrade,EngineVersion:EngineVersion}" \ --output table

For Windows:

aws rds describe-db-engine-versions ^ --engine postgres ^ --engine-version 12.13 ^ --region us-east-2 ^ --query "DBEngineVersions[*].ValidUpgradeTarget[*].{AutoUpgrade:AutoUpgrade,EngineVersion:EngineVersion}" ^ --output table

Your output is similar to the following.

---------------------------------- | DescribeDBEngineVersions | +--------------+-----------------+ | AutoUpgrade | EngineVersion | +--------------+-----------------+ | True | 12.14 | | False | 12.15 | | False | 13.9 | | False | 13.10 | | False | 13.11 | | False | 14.6 | +--------------+-----------------+

In this example, the AutoUpgrade value is True for PostgreSQL version 12.14. So, the automatic minor upgrade target is PostgreSQL version 12.14, which is highlighted in the output.

A PostgreSQL database is automatically upgraded during your maintenance window if the following criteria are met:

  • The database has the Auto minor version upgrade option enabled.

  • The database is running a minor DB engine version that is less than the current automatic upgrade minor version.

For more information, see Automatically upgrading the minor engine version.

Note

A PostgreSQL upgrade doesn't upgrade PostgreSQL extensions. To upgrade extensions, see Upgrading PostgreSQL extensions.

Upgrading PostgreSQL extensions

A PostgreSQL engine upgrade doesn't upgrade most PostgreSQL extensions. To update an extension after a version upgrade, use the ALTER EXTENSION UPDATE command.

Note

For information about updating the PostGIS extension, see Managing spatial data with the PostGIS extension (Step 6: Upgrade the PostGIS extension).

To update the pg_repack extension, drop the extension and then create the new version in the upgraded database. For more information, see pg_repack installation in the pg_repack documentation.

To upgrade an extension, use the following command.

ALTER EXTENSION extension_name UPDATE TO 'new_version';

For the list of supported versions of PostgreSQL extensions, see Supported PostgreSQL extension versions.

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;