How do I validate prerequisites before a major version upgrade on my Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible database?

Short description

To validate prerequisites before a major version upgrade, use the Major Version Upgrade (MVU) precheck tool on the GitHub website. The tool detects upgrade blockers, including configuration issues, incompatible extensions, unsupported data types, and replication requirements.

The MVU precheck tool is available in the following three versions:

• The shell version automates checks across all databases in a cluster or instance from a client machine. It supports batch execution across multiple instances or clusters with a CSV file.
• The SQL version is a .sql file that you run with psql against each database individually.
• The PL/pgSQL version creates a function in the public schema that you call with SELECT from a PostgreSQL client and run against each database.

Note: Supported target PostgreSQL versions are 11–18.

Resolution

Use the shell version

If you can run shell scripts on a client machine, such as an Amazon Elastic Compute Cloud (Amazon EC2) instance or your workstation, then use the shell version. The shell version produces an HTML or text report. It can also pull Amazon RDS configuration details with the AWS Command Line Interface (AWS CLI) and supports AWS Secrets Manager for credentials.

Prerequisites:

• bash 4.0 or later.
• psql client installed. To download the client, see PostgreSQL downloads on the PostgreSQL website.
• AWS CLI installed and configured.
• jq installed. To download the tool, see Download jq on the ./jq website.

Run prechecks on a single instance or cluster

Use the pg-major-version-upgrade-precheck.sh script to run prechecks against all databases in an Amazon RDS instance or Aurora cluster. To copy the script, see pg-major-version-upgrade-precheck.sh on the GitHub website.

The script supports interactive and non-interactive modes and sql run mode for only database checks, rds run mode for only Amazon RDS configuration checks, or both.

To run the precheck in interactive mode, run the following command:

./pg-major-version-upgrade-precheck.sh

To run the precheck in non-interactive mode with both sql and rds checks, run the following command:

./pg-major-version-upgrade-precheck.sh --non-interactive -m YOUR-RUN-MODE -r YOUR-REGION -i YOUR-INSTANCE-ID -p YOUR-PROFILE -h YOUR-ENDPOINT -P YOUR-PORT -d YOUR-DATABASE -u YOUR-USERNAME -w YOUR-PASSWORD

Note: In the preceding command, replace the following values with your values:

• YOUR-RUN-MODE with your run mode
• YOUR-REGION with your AWS Region
• YOUR-INSTANCE-ID with your RDS instance ID
• YOUR-PROFILE with your AWS CLI profile
• YOUR-ENDPOINT with your database (DB) endpoint
• YOUR-PORT with your port
• YOUR-DATABASE with your DB name
• YOUR-USERNAME with your username
• YOUR-PASSWORD with your password

For information about all options, blue/green checks, Secrets Manager support, environment variables, report format, and troubleshooting, see the Usage README on the GitHub website.

Run prechecks on multiple instances or clusters

Use the wrapper.sh script to run prechecks across multiple RDS instances or Aurora clusters in a single run. To copy the script, see wrapper.sh on the GitHub website.

The wrapper reads a CSV file that lists each target instance or cluster with its connection details. It calls pg-major-version-upgrade-precheck.sh for each entry and produces a consolidated summary.

You can use the wrapper to either discover RDS and Aurora instances in your AWS account and generate a CSV template. Or, run the prechecks for all entries in the CSV file.

To run the wrapper, run the following command:

./wrapper.sh

For the CSV format, column reference, security recommendations, password and Secrets Manager options, see the wrapper README on the GitHub website.

Use the SQL version

If you have psql access to the database but can't install additional client tools, such as AWS CLI or jq, then use the SQL version. The SQL version uses psql meta-commands, such as the -v variable, for the target version. You must run the script against each database individually.

To run the precheck against a database that targets PostgreSQL 16, run the following command:

psql "host=HOST port=PORT user=USER dbname=postgres sslmode=verify-full sslrootcert=global-bundle.pem" -v target_version=16 -f pg-major-version-upgrade-precheck-sql.sql

Note: Replace HOST with your DB endpoint, PORT with your port, USER with your username, and postgres with your DB name.

For an overview of the check categories, output interpretation, and required permissions, see the SQL README on the GitHub website.

Use the PL/pgSQL version

If you don't have shell access or you use a GUI client to connect, such as pgAdmin or DBeaver, then use the PL/pgSQL version.

Important: You must create and call the function in each database that you're checking.

Complete the following steps:

1. To load the function into your database, run the following command:

   psql "host=HOST port=PORT user=USER dbname=postgres sslmode=verify-full sslrootcert=global-bundle.pem" -f pg-major-version-upgrade-precheck-plpgsql.sql

   Note: Replace HOST with your DB endpoint, PORT with your port, and USER with your username.
2. To run the precheck for an upgrade to PostgreSQL 16, run the following command:

   SELECT * FROM public.pg_major_version_upgrade_precheck(16);

   Note: Replace 16 with your target PostgreSQL major version.
3. (Optional) To clean up the function after you use it, run the following command:

   DROP FUNCTION IF EXISTS public.pg_major_version_upgrade_precheck(integer);

For an overview of check categories, output columns, status meanings, and required privileges, see the PL/pgSQL README on the GitHub website.

Review the results

All three versions of the MVU precheck tool return the following statuses for each check:

• PASSED shows that the check passed with no issues found. No action is required.
• INFO shows information that you can review for awareness.
• WARNING shows issues that require attention. You must resolve the issues before you can upgrade.
• FAILED shows that the tool detected a critical upgrade blocker that you must resolve before you upgrade.
• SKIP shows that the check isn't applicable for the upgrade path. No action is required.

If a check returns a FAILED status, review the check output to identify the specific upgrade blocker. Resolve the blocker before you try the major version upgrade. For detailed resolutions for each check, see the README for the precheck tool version that you used.

Related information

How to perform a major version upgrade for RDS for PostgreSQL  

Performing a major version upgrade