Upgrade Amazon RDS to major and minor versions of PostgreSQL

Open-source PostgreSQL occasionally releases new minor and major versions that include fixes for frequently encountered bugs, security issues, and data corruption problems. Generally, Amazon RDS aims to support new engine versions within five months of their availability. You also must upgrade your RDS PostgreSQL instances when a particular version is no longer supported. In this case, RDS sends emails suggesting that you upgrade your database instances. You can upgrade your instances by using the RDS console or the AWS CLI command modify-db-instance. You can also upgrade instances to suitable minor versions by enabling Auto Minor Version Upgrades.

Although RDS manages upgrades, you should be aware of common issues, steps involved, and best practices to upgrade with the least amount of impact on your business. This post discusses upgrading your RDS PostgreSQL database engine, including the following topics:

• What happens during major and minor version upgrades
• Common issues during upgrades
• Understanding the Auto Minor Version Upgrades feature
• Preparing for an upgrade

Major and minor version upgrades

Starting with PostgreSQL 10, an increase in the first digit of its version number indicates a new major version, for example, 10 to 11. The second digit indicates a minor version, for example, 10.4 to 10.9. Before PostgreSQL 10, the second digit could also indicate a major version, such as 9.5 to 9.6, while a third digit denoted a minor version, for example, 9.6.5 to 9.6.10.

Minor versions patch security vulnerabilities, fix bugs, and generally do not add new functionality. Minor releases never change the internal storage format and are always compatible with earlier and later minor releases of the same major version number. For example, version 10.4 is compatible with version 10.1 and version 10.6. Similarly, 9.5.3 is compatible with 9.5.0, 9.5.1, and 9.5.6. To update between compatible versions, RDS replaces the binaries while the server is down and restarts the server. The data directory remains unchanged. This is the reason minor upgrades are quicker compared to major upgrades.

For major releases of PostgreSQL, the internal format of system tables, data files, and internal data storage format also change. This complicates the upgrades. RDS uses the PostgreSQL utility pg_upgrade for performing major upgrades.

In major version upgrades, RDS completes the following steps:

1. Takes a pre-upgrade snapshot (if configured for backups). You can use this snapshot for rollbacks.
2. Shuts down the instance and prepares it for the upgrade.
3. Uses the pg_upgrade utility to run the upgrade job on the instance.
4. Takes a post-upgrade snapshot. Networking is now reconfigured on the instance.

When RDS initiates Step 1, the instance’s status changes from Available to Upgrading. After Step 4, it returns to Available.

The following table summarizes the significant differences in minor and major upgrade steps:

	Minor upgrade	Major upgrade
Can upgrade Replica	Yes	Yes
Needs new custom parameter group for upgraded instance	No	Yes
Upgrades automatically (provided RDS is configured with Auto Minor Version Upgrades)	Yes	No
Updates database data files	No	Yes
Copies table statistics to upgraded instance	Yes	No
Is always backward compatible	Yes	No

Common issues during an upgrade

Sometimes RDS Postgres upgrade encounters certain issues. These issues are related to unsupported datatypes and database objects. The database log file pg_upgrade.log contains details of these issues. Some of the issues are mentioned below:

INCOMPATIBLE_PARAMETER

This error occurs if a memory-related parameter such as shared_buffer or work_memory was set too large, and caused the pg_upgrade script to fail. To fix the issue, you should reduce the values and try the upgrade again.

STORAGE_FULL

While the pg_upgrade script runs, the instance can run out of space. This causes the script to fail. You see an error message similar to the following:

pg_restore: [archiver (db)] Error while PROCESSING TOC: 
pg_restore: [archiver (db)] could not execute query: ERROR: could not create file "base/12345/12345678": No space left on device 

To resolve this issue, make sure while upgrading that the instance has sufficient free storage depending on the number of databases and data files.

Logical replication slots

If the database is using logical replication slots, the major version upgrade fails and shows the following message:

PreUpgrade checks failed: The instance could not be upgraded because one or more databases have logical replication slots. Please drop all logical replication slots and try again.

To resolve the issue, stop any running DMS or logical replication jobs and drop any existing replication slots. See the following code:

SELECT * FROM pg_replication_slots;
SELECT pg_drop_replication_slot(slot_name);

Release date dependency

If the release date of the target version is older than the release date of the current version, you can’t upgrade the instance. You see an error message similar to the following:

Cannot upgrade postgres from 9.5.12 to 9.6.6 (Service: AmazonRDS; Status Code: 400; Error Code: InvalidParameterCombination; Request ID: 12345ab-12345ab-12345-ab)

In the preceding example, the release date of 9.5.12 is March 1, 2018, while the release date of 9.6.6 is November 9, 2017. To fix this issue, see the PostgreSQL official release notes for the release date and find the latest minor version that is available.

Master user name

If master user name starts with pg_, the upgrade fails and gives the following error message:

PreUpgrade checks failed: The instance could not be upgraded because one or more role names start with 'pg_'. Please rename all roles with names that start with 'pg_' and try again

To resolve this, create another user with the rds_superuser role. You also must contact AWS Premium Support to update this user to the new master user.

Understanding the Auto Minor Version Upgrades feature

You can configure your RDS PostgreSQL instance for the Auto Minor Version Upgrades setting, which allows a minor upgrade automatically whenever RDS make a version available for an auto upgrade. For example, if your RDS PostgreSQL instance is currently version 10.5 and you enable Auto Minor Version Upgrades, it upgrades to 10.6 version automatically during the next maintenance window. It doesn’t upgrade to any later minor versions automatically unless RDS makes it available.

Not all minor versions are available for auto upgrades. To find the available versions for auto upgrades, enter the following CLI command:

[ec2-user@ip- ~]$ aws rds describe-db-engine-versions --engine postgres | grep -A 1 AutoUpgrade| grep -A 2 true |grep PostgreSQL | sort --unique | sed -e 's/"Description": "//g' | sed -e 's/",//g'
PostgreSQL 10.6-R1
PostgreSQL 9.4.20-R1
PostgreSQL 9.5.15-R1
PostgreSQL 9.6.11-R1

Preparing for a minor version upgrade

For a minor version upgrade, complete the following steps beforehand:

1. Review the official release notes to understand the changes introduced in the new version.
2. Find the suitable next minor version as per the upgrade path.
   You can use an AWS CLI command to find the available higher RDS PostgreSQL minor versions. For example, to search for higher minor versions for an instance currently at version for 9.5.12, enter the following CLI command:[ec2-user@ip-~]$ aws rds describe-db-engine-versions --engine postgres --engine-version 9.5.12 | grep -A 500 "ValidUpgradeTarget"| grep "EngineVersion"| grep 9.5| sed -e 's/"//g' |sed -e 's/EngineVersion: /PostgreSQL /g' PostgreSQL 9.5.13 PostgreSQL 9.5.14 PostgreSQL 9.5.15 PostgreSQL 9.5.16 PostgreSQL 9.5.18 PostgreSQL 9.5.19
3. Test your applications and workload on the new minor version to estimate its expected outage and performance.
   To test the upgrade, take a snapshot of the production instance, restore it in a test environment, and upgrade it to the new minor version. To limit the chances of an outage during upgrade, close all existing connections and take a manual snapshot before running the upgrade, so the pre-upgrade snapshot is quicker.
   You can also use Read Replica to minimize the outage during a minor version upgrade. You must create a Read Replica and a minor upgrade the Replica. When the Replica is in sync with the source instance, promote it and point the application to the new master.

Preparing for a major version upgrade

For major version upgrades, complete the following steps beforehand:

1. Review the PostgreSQL official release notes and familiarize yourself with changes in the target version.
2. Find the suitable target major version as per RDS upgrade path.
   For example, for an instance currently at version 9.6.12, use the following CLI command:[ec2-user@ip-~]$ aws rds describe-db-engine-versions --engine postgres --engine-version 9.6.12 | grep -A 200 "ValidUpgradeTarget"|grep "EngineVersion"| sed -e 's/"//g' |sed -e 's/EngineVersion: /PostgreSQL /g' PostgreSQL 9.6.14 PostgreSQL 9.6.15 PostgreSQL 10.7 PostgreSQL 10.9 PostgreSQL 10.10 PostgreSQL 11.2RDS PostgreSQL now supports multiple major version upgrades in a single step.
3. Drop any VIEW depending on system catalogs of the target version.
   For example, VIEW depending on pg_stat_activity fails to upgrade from 9.5 to 9.6 because the waiting column was replaced with wait_event_type and wait_event.
4. Drop any unknown data types depending on the target version.
   Similarly, version 10 stopped supporting the unknown data type. An unknown data type in 9.6 fails to upgrade from 9.6 to 10.6 and shows the following error message: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." Below are some of the customer casesYou can find the unknown data type in your database and remove the offending column or change to the supported data type with the following code:SELECT DISTINCT data_type FROM information_schema.columns where data_type ilike 'unknown';
5. Create a new RDS instance of the target major version and perform pg_dump/ pg_restore to copy data from the lower version to the higher version.
   As the part of major version upgrade, the pg_upgrade program copies the data files and restores the changes needed to support the new version. This step avoids the issues mentioned previously.
   During this test, if you encounter any errors, the upgrade likely encounters the same errors. To have a smooth upgrade, you need to resolve these issues.
6. Close all existing connections and take a manual snapshot before the upgrade.
   As part of a major upgrade, a snapshot is taken during the outage. As EBS snapshot is incremental, so taking a snapshot before the upgrade reduces the overall outage. You can use the CLI create-db-snapshot to take a snapshot of the RDS instance.
7. Have a custom parameter group ready while upgrading.
   If you are using a custom parameter group, you need a new parameter group for the target version. To apply the custom parameter group, you need to reboot the instance.
8. Upgrade your extensions with the ALTER EXTENSION UPDATE command.
   A major version upgrade doesn’t upgrade any PostgreSQL extensions. The following code example upgrades a PostGIS extension while upgrading the instance from 9.4 to 9.5:ALTER EXTENSION POSTGIS UPDATE TO '2.2.5';
9. During a major version upgrade, Amazon RDS also upgrades all of the in-Region read replicas along with the primary DB instance.
10. If necessary, perform scale storage to achieve 15%-20% free storage for a major version.
    Alternatively, enable RDS Storage autoscaling to mitigate any unforeseen space issues.
11. Stop any DMS tasks that are dependent on the RDS instance you are upgrading by setting the rds.logical_replication parameter to 0.
    When the upgrade is complete, upgrade the pg_statistics table by running ANALYZE on all user databases. A major upgrade doesn’t move the content of the pg_statistics table to the new version. Skipping this step can result in slow SQL queries.
    You can also perform a dry run upgrade before upgrading production databases. You can restore a snapshot of the production instance and perform a dry run. Consider testing your application on the upgraded database with a similar workload to verify that everything works as expected. After the upgrade is verified, you can delete this test instance.
    Multi-AZ configuration doesn’t help avoid an outage during a database engine upgrade. Multi-AZ reduces outage time during an instance scale compute, but because storage level changes are required during a database engine upgrade, both instances upgrade at the same time.
    If your database is sensitive towards an outage, you can use AWS DMS, logical replication, or the pglogical extension to set up replication between two different major versions. When both instances are in sync, cut over and point applications to the new master RDS instance. You can also rename the instance in the same Region and account so applications don’t need any changes.

credit: https://aws.amazon.com/blogs/database/best-practices-for-upgrading-amazon-rds-to-major-and-minor-versions-of-postgresql/