How do I minimize the downtime and optimize the performance of an Amazon RDS for PostgreSQL major version upgrade?

Short description

Note: Major version upgrades might contain database changes that aren't compatible with your existing applications. Before you upgrade, make sure to thoroughly test the upgrade in a non-production environment. For more information, see How to perform a major version upgrade for RDS for PostgreSQL.

To upgrade your database (DB) instance, use one of the following methods:

• Snapshot restore
• Manual upgrade
• Blue/green deployment
• AWS Database Migration Service (AWS DMS)

Prerequisites:

• AWS Identity and Access Management (IAM) permissions to modify RDS instances
• A maintenance window for potential downtime
• A database backup

Resolution

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

Optimize upgrade performance

To minimize potential issues when you perform the major engine version, take the following actions:

• Create a DB snapshot.
• Check and resolve pending maintenance activities.
• Check the read replica limitations with PostgreSQL.
• Resolve long-running transactions.

To identify transactions that are in the running state for longer than 10 minutes, run the following command:

SELECT * FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled','active')
AND state_change < current_timestamp - INTERVAL '10' MINUTE
AND usename != 'rdsadmin';

If you identify a long-running transaction, then use pg_cancel_backend or pg_terminate_backend to end the transaction. For more information, see Server signaling functions on the PostgreSQL website.

Upgrade with a snapshot restore

Create a DB snapshot of your DB instance, upgrade the snapshot to a later major version, and then restore it to a new DB instance.

Note: You must use a different name for the new DB instance, and your application must stop during the upgrade process. Also, you must update the application endpoints after the upgrade completes.

Manually upgrade your DB instance

To manually upgrade your DB instance, see Manually upgrading the engine version.

Note: Manual database upgrades require downtime. To minimize downtime, use a blue/green deployment.

Use a blue/green deployment upgrade

To create a blue/green deployment, see Creating a blue/green deployment. When you're in the green environment, specify a later engine version.

For more information about blue/green deployments, see Limitations and considerations for Amazon RDS blue/green deployments.

Use AWS DMS to upgrade

Use AWS Database Migration Service (AWS DMS) to migrate data from your current DB instance to a new instance with the upgraded version. AWS DMS supports change data capture (CDC) for continuous replication.

Upgrade the pg_statistics table

After you complete the major engine version upgrade, run the ANALYZE VERBOSE; command on all databases to upgrade the pg_statistics table. For more information, see ANALYZE on the PostgreSQL website.