How do I calculate downtime when my RDS for PostgreSQL instance has a major version upgrade?

Short description

When you perform a major version upgrade for an Amazon RDS for a PostgreSQL DB instance, the following occurs:

1. A pre-upgrade snapshot is taken for backups and rollbacks.
2. The instance is shut down to prepare for the upgrade.
3. The pg_upgrade utility is used to run the upgrade job on the instance.
4. A post-upgrade snapshot is taken and networking is re-configured on the instance.

Because major version upgrades include changes to the internal data storage format, you can expect downtime without an estimated time of completion. The pre-upgrade backup is based on the incremental changes, the pg_upgrade database size, and the number of databases and database objects such as tables.

Resolution

To calculate downtime before you perform an instance major version upgrade, test a separate instance that simulates your current instance setup and data. To test a separate instance, complete the following steps:

Note: The following steps provide an estimate of your major version upgrade process and the time required for the upgrade.

1. Create a manual snapshot of your current DB instance.
   Note: After you create a manual snapshot, allow your instance to finish lazy loading or complete a pre-warm up of the instance.
2. To create a test instance, restore the snapshot. Make sure that the test instance configuration is identical to your production instance for accurate downtime estimates.
3. Upgrade the test instance to the desired version.
4. To refresh the pg_statistic table, run ANALYZE.
5. Compare your results, and then choose your production upgrade.

Downtime for Amazon RDS for PostgreSQL instance upgrades with read replicas

When you perform a major version upgrade, Amazon RDS upgrades your read replicas simultaneously within your AWS Region and the primary DB instance. Read replicas might extend the upgrade process time on the primary instance.

To prevent a replica upgrade, promote the replica to a standalone instance before you start the upgrade process. This allows you to reduce your upgrade downtime and have an accurate time for upgrade completion.

Downtime for Amazon RDS for PostgreSQL instance upgrades with large objects

When you perform a major version upgrade, all objects inside the database are scanned. The number of objects inside the database determine the upgrade process time. Also, large objects (pg_largeobject) in Amazon RDS for PostgreSQL can significantly increase your upgrade downtime.

To reduce the upgrade downtime on an instance that has large objects, review your large objects to determine whether your large objects can be removed. Also, use the vacuumlo utility tool to identify and remove orphaned large objects. The vacuumlo utility tool can be downloaded and run locally or on an Amazon Elastic Compute Cloud (Amazon EC2) instance. For more information, see vacuumlo on the PostgreSQL website.

Related information

Best practices for upgrading Amazon RDS to major and minor versions of PostgreSQL

Upgrading a DB instance engine version