Skip to content

Amazon RDS upgrade process

Jump to bottom
Carlo Costino edited this page Oct 4, 2016 · 3 revisions

Overview

This page describes the process for upgrading our Amazon RDS instances to a new version of PostgreSQL. It covers how to prepare for the upgrades, what steps to take, and any caveats to bear in mind. We'll cover the following:

  1. Major vs. minor upgrades
  2. Preparing for a major upgrade
  3. Performing the upgrade
  4. Known caveats

For more information, you can also read about PostgreSQL upgrades in the Amazon RDS documentation and the PostgreSQL Documentation.

There is also a GitHub issue in this repo that has some conversation around this process.

At this point in time, Amazon RDS currently only supports PostgreSQL up to version 9.5.4, but PostgreSQL 9.6 is the latest stable release. We are currently running on 9.5.4 in Amazon RDS for all of our instances.

Major vs. minor upgrades

Major upgrades

A major version upgrade is a change between the first or second number in a PostgreSQL release. For example, if you are currently running PostgreSQL 9.4 in RDS and you want to upgrade to a 9.5 release, that is a major version upgrade. For the moment we are in the 9.x series and it might be a while before we see a shift to a 10 series, so at this time all major upgrades can be considered a move from 9.x to 9.y (e.g., 9.4 to 9.5, not accounting for the last number, also known as a patch level number).

Furthermore, if you have any read replicas associated with the database, those will have to be dropped and recreated.

Minor upgrades

A minor version upgrade is a change of just the last number in a PostgreSQL release, also known as the patch level. An example would be upgrading a RDS instance from 9.5.2 to 9.5.4. This is not a major version change and generally doesn't have any potential impacts on the underlying storage of the data in the database. The PostgreSQL Global Development Group (the governing body of PostgreSQL development and releases) does its best to keep those kinds of changes only between major releases.

Replicas do support minor version upgrades. If you have any associated with the source/parent database, you must upgrade them first before you can update the source/parent database.

Preparing for a major upgrade

If you are performing just a minor upgrade, you may skip these steps and start at the next section. Whenever you are ready to perform a major upgrade, please run through these steps first:

  1. Perform a VACUUM operation. The upgrade documentation doesn't say anything about performing a full VACUUM or running VACUUM ANALYZE. Running just VACUUM does not obtain an exclusive lock on the tables that it processes, so read/write operations continue to occur normally.

  2. Check for prepared transactions that are still open and either commit them or roll them back - we can check for any with this query:

SELECT count(*) FROM pg_catalog.pg_prepared_xacts;

This count should return 0 for a result.

  1. Remove all reg* data types - we can check for any with this query:
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');

This count should return 0 for a result.

Once the vacuum operation is done and the two checks are performed, you are ready to perform an upgrade.

Performing the upgrade

In the Amazon RDS console, find the database instance that you would like to upgrade. Open the details by clicking on the drop down arrow at the left of the instance name and a small pane will appear with three buttons on the bottom. Click on the Instance Actions button to open a menu and then click on Modify Instance in it.

A new screen will open and the very first option listed is the PostgreSQL version. Click in the menu to select the new version you want to upgrade to, and a few other options further down in the form should automatically adjust to account for the selected version (option group and/or default parameter group I believe). Scroll down to the bottom and check the Apply Immediately checkbox. Now click on the Modify Instance button at the very bottom.

You'll be taken to a confirmation page, so take one more look to make sure everything is correct and then click on the Modify Instance button. This will start the upgrade process immediately.

Final steps after a major version upgrade

If you just finished performing a major version upgrade, you will need to reboot the database instance one more time to have the parameter group be in sync with the new version configuration.

Known caveats

  1. If you have read replicas associated with any source/parent database, you can only perform minor upgrades with them. Major upgrades require you to delete and recreate them. Furthermore, you must also upgrade them first before you can upgrade the source/parent database. RDS will inform you of this if you try to upgrade the source/parent first.

  2. If you have database traffic going to a read replica, you will want to shut it off and have all traffic go back to the source/parent database first before starting the upgrade. Unlike the source/parent upgrade, it seems that the read replica is taken completely offline and/or becomes out of sync for the duration of the process. Once the upgrade is complete, then you can return traffic to it and being working on the source/parent database.

Clone this wiki locally