How do I troubleshoot issues with major version upgrades in Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible?

Short description

Major version upgrades contain database changes that aren't backward-compatible with existing applications. The upgrades might change the internal format of system tables, data files, and data storage. Amazon RDS uses pg_upgrade to perform major version upgrades. For more information, see pg_upgrade on the PostgreSQL website.

During a major version upgrade, Amazon RDS runs a precheck procedure that identifies issues that might cause the upgrade to fail. It checks for potential incompatible conditions across all databases. If Amazon RDS identifies an issue during the precheck process, then it creates a log event for the failed precheck. The log events include the file name, timestamp, and reasons for the upgrade failure. For information about the precheck process for all databases, check the pg_upgrade_precheck.log log. For engine-specific issues, you must check the database log files for Amazon RDS for PostgreSQL or Aurora PostgreSQL- Compatible.

Resolution

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.

The pg_upgrade utility that performs major version upgrades produces the pg_upgrade_internal.log log and the pg_upgrade_server.log log. Amazon RDS appends a timestamp to the file name for the logs. Review the logs to get more information about the issues and errors that you encounter during the upgrade. For more information, see Monitoring Amazon RDS log files or Monitoring Amazon Aurora log files.

Long-running upgrades

Check for pending maintenance activities

Amazon RDS automatically uses engine version upgrades to apply pending maintenance activities, such as an operating system (OS) patch on your Amazon RDS instance. Amazon RDS applies the pending activity first, and then upgrades the engine version. If Amazon RDS must perform OS maintenance activities, then the upgrade takes longer.

If your Amazon RDS instance is in a Multi-AZ deployment, then OS maintenance results in a failover. When you set up your instance in a Multi-AZ environment, Amazon RDS typically creates the backup of the instance on the secondary instance. In a failover, Amazon RDS creates a backup on a new secondary instance after the upgrade. This backup on the new secondary instance might not be the latest backup, so Amazon RDS completes a full backup instead of an incremental backup. A full backup can take a long time, especially when the database is large.

To avoid this issue, search for pending maintenance activities for your RDS DB instance or your Aurora DB instance. Or, run the following describe-pending-maintenance-actions AWS CLI command on your instance:

aws rds describe-pending-maintenance-actions --resource-identifier example-arn

Note: Replace example-arn with you DB instance ARN.

Complete pending maintenance activities before you perform the database engine version upgrades.

Create a snapshot before you upgrade

Before you upgrade the version, it's a best practice to create a snapshot of the RDS DB instance or Aurora DB cluster. If you already turned on backups for your instance, then Amazon RDS automatically creates a snapshot as part of the upgrade process. Snapshots reduce the upgrade process time because Amazon RDS must only create an incremental backup for the upgrade.

Wait for the read replica to upgrade

When you perform a major version upgrade of your primary DB instance, Amazon RDS automatically upgrades all the read replicas in the same AWS Region. After the upgrade workflow starts, the read replicas wait for pg_upgrade to successfully complete on the primary DB instance. Then, the primary DB instance upgrade waits for the read replica upgrades to complete. The DB instance shuts down until all upgrades are complete. If the downtime window for the upgrade is small, then promote or drop your replica instance. Then, recreate the read replicas after the upgrade completes.

For Aurora DB clusters, pg_upgrade upgrades the writer instance first. Then, each reader DB instance shuts down when pg_upgrade is upgrading it to the new major version.

Note: If you upgrade an Aurora global database, then you must follow additional requirements and processes.

Resolve long-running transactions or high workloads before the upgrade

Long-running transactions or high workloads can increase the time Amazon RDS takes to shut down the database and upgrade the database engine.

To identify long-running transactions, run the following query:

SQL>SELECT pid, datname, application_name, state, age(query_start, clock_timestamp()), usename, query 
FROM pg_stat_activity 
WHERE query NOT ILIKE '%pg_stat_activity%' AND 
usename!='rdsadmin' 
ORDER BY query_start desc;

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

Make sure that you have enough compute capacity

The pg_upgrade utility can be compute intensive. To check compute, memory, and free storage capacity, it's a best practice to perform a test upgrade before you upgrade your production databases. The test upgrade also checks whether you might encounter precheck or upgrade errors. You can restore the production instance's snapshot and perform a test with the same instance class as the production database's instance class.

Upgrade failures

Check for unsupported DB instance classes and engine versions

If the instance class of your DB instance isn't compatible with the PostgreSQL version that you're upgrading to, then the upgrade fails. Check the compatibility of the engine version with the instance class for Amazon RDS or Aurora.

To check the engine versions that are compatible for an engine version upgrade, run the following describe-db-engine-versions command:

aws rds describe-db-engine-versions --engine postgres --engine-version your-version --query "DBEngineVersions[].ValidUpgradeTarget[].{EngineVersion:EngineVersion}" --output text

Note: Replace your-version with your engine version.

If your current version isn't compatible, then it's a best practice to upgrade to the newest minor version. Or, you can upgrade to one of the other available upgrade versions. For information about engine version upgrades, see Choosing a major version for an RDS for PostgreSQL upgrade.

Check for open prepared transactions

If there are open prepared transactions on the database, then the upgrade fails. You receive the "There are uncommitted prepared transactions" error in the pg_upgrade.log file. Before you start the upgrade, commit or roll back all open prepared transactions.

To check for open prepared transactions on your instance, run the following query:

SELECT count(*) FROM pg_catalog.pg_prepared_xacts;

Use a supported data type

You can upgrade the version only for regclass, regrole, and regtype data types. The pg_upgrade utility can't upgrade databases that include table columns that use the reg* object identifier (OID) referencing types. If you use a regcollation, regconfig, regdictionary, regnamespace, regoper, regoperator, regproc, or regprocedure data type, then the upgrade fails.

To resolve this issue, remove all uses of reg* data types except for regclass, regrole, and regtype, before you upgrade the data engine. To check for unsupported reg* data types in your tables, run the following 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');

Check for logical replication slots

If your instance has logical replication slots, then you can't upgrade the instance and receive the following error message in the pg_upgrade.log file:

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

You typically use logical replication slots for AWS Database Migration Service (AWS DMS) migrations. You also use them to replicate tables from databases to data lakes, business intelligence tools, and other destinations. Make sure that you know the purpose of the logical replication slots that you're using to determine whether you can delete them. If the logical replication slots are in use, then don't delete them. You must wait to upgrade the version before you can delete the logical replication slots.

If you don't need the logical replication slots, then run the following commands to delete them:

SELECT * FROM pg_replication_slots;
SELECT pg_drop_replication_slot(slot_name);

Note: Replace slot_name with the logical replication slot's name.

Check for storage issues

If the instance runs out of space when the pg_upgrade script runs, then the upgrade fails and you receive the following error message:

"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 keyword" left on device"

To resolve this issue, make sure that the instance has sufficient free storage before you start the upgrade.

Check for unknown data types

You can't use unknown data types in PostgreSQL versions 10 and later. For example, if a PostgreSQL version 9.6 database uses the unknown data type, then you receive the following error message when you upgrade to version 10:

"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."

To resolve this issue, manually remove columns that use the unknown data type or modify them to a supported data type.

To find the columns in your database that use the unknown data type, run the following query:

SELECT DISTINCT data_type FROM information_schema.columns WHERE data_type ILIKE 'unknown';

(RDS for PostgreSQL only) Check for a read replica upgrade failure

If the PostgreSQL instance has read replicas, then read replica upgrade failures might cause your primary instance upgrade to get stuck or fail. Amazon RDS sets a failed read replica to the incompatible-restore state, and then stops replication on the DB instance.

A read replica upgrade might fail for one of the following reasons:

• The read replica can't catch up with the primary DB instance even after the wait time.
• The read replica is in a terminal or incompatible lifecycle state, such as storage-full or incompatible-restore.
• When the primary DB instance upgrade starts, a separate minor version upgrade is running on the read replica.
• The read replica uses incompatible parameters.
• The read replica can't communicate with the primary DB instance to synchronize the data folder.

To resolve this issue, delete the read replica. Then, create a new read replica based on the upgraded primary instance after the upgrade.

Make sure that your primary username is accurate

If the primary username starts with pg_, then the upgrade fails, and you receive 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 issue, create another user with the rds_superuser role that doesn't start with pg_.

Check for incompatible parameters

The "incompatible parameters" error occurs when the value of a memory-related parameter, such as shared_buffer or work_memory, is too high for your configuration. This error causes the upgrade script to fail. To resolve the issue, reduce the values of the parameters, and then perform the upgrade again.

Update your extensions before you upgrade

Major version upgrades don't upgrade PostgreSQL extensions. If you don't update the extensions before a major version upgrade, then you might receive the following error message in the pg_upgrade.log file:

"The Logs indicates that the RDS instance ''abcd'' has older version of PostGIS extension or its dependent extensions (address_standardizer,address_standardizer_data_us, postgis_tiger_geocoder, postgis_topology, postgis_raster) installed as against the current version required for the upgrade."

The preceding example error message shows an issue with the PostGIS extension. To resolve this issue, run the following query to check the default and installed versions for PostGIS and its dependent extensions:

SELECT name, default_version, installed_versionFROM pg_available_extensions WHERE installed_version IS NOT NULL ANDname LIKE 'postgis%' OR name LIKE 'address%';

Note: Replace postgis% with your extension.

If the value for installed_version is lower than the value for default_version, then you must update PostGIS to the default version. To upgrade your extension, run the following command:

ALTER EXTENSION extension_name UPDATE TO 'default_version_number';

Note: Replace default_version_number with the default_version value.

For more information, see Upgrades of the RDS for PostgreSQL DB engine or Upgrading Amazon Aurora PostgreSQL-Compatible DB clusters.

Check for changes in the system catalog of the version that causes issues in views

Columns in certain views vary across different PostgreSQL versions. For example, you might receive an error message that's similar to the following one:

"pg_restore: error: could not execute query: ERROR: column reference 'backend_type' is ambiguous"

This error occurs when you try to upgrade the database from version 12.x to 13.x, and pg_stat_activity has different structures on version 12.x and 13.x.

To resolve this issue, complete the following steps:

1. Run the following command to query the view definition:

   SELECT pg_get_viewdef('pg_stat_activity_allusers', true);

2. Run the following command to drop the view:

   DROP VIEW pg_stat_activity_allusers;

3. Upgrade the engine version.

4. Run the following command to recreate the view:

   CREATE VIEW pg_stat_activity_allusers AS SELECT * FROM get_sa(); 
   GRANT SELECT ON pg_stat_activity_allusers TO public;

Or, you might receive an error message that's similar to the following one:

"pg_restore: from TOC entry abc; abc abcd VIEW sys_user_constraints art pg_restore: error: could not execute query: ERROR: column c.consrc does not exist LINE 18: 'c'.'consrc' AS 'search_condition', ^ HINT: Perhaps you meant to reference the column 'c.conkey' or the column 'c.conbin'."

This error occurs when the structure of the pg_constraint catalog changed in PostgreSQL version 12.

To resolve this issue, drop the views based on the system catalogs of the destination version.

Important: Before you drop the views, it's a best practice to use pgdump to back up your views or capture the definition of the views. When you drop a view, you or your database administrator must manually recreate the view after the version upgrade.

Check for foreign tables with a prefetch value

Use the oracle_fdw extension to prefetch between 0–10,240 rows from Oracle databases. Set the prefetch parameter in the foreign table's OPTIONS clause. Higher values improve performance but use more memory on the PostgreSQL server. To reduce memory usage, it's a best practice to set prefetch during the precheck phase of a major version upgrade.

If the prefetch value is greater than 1,000, then you might receive the following error message:

"pg_restore: error: could not execute query: ERROR: invalid value for option 'prefetch'"

Note: Valid values in this context are integers between 0–1000.

To resolve the issue, set the prefetch values between 1–1,000 on all foreign tables, and then retry the upgrade.

To alter the foreign tables, run the following command:

ALTER FOREIGN TABLE dwh_ddl_sync.fdw_all_tab_cols OPTIONS (SET prefetch '999');

To list all foreign tables, run the following command:

SELECT * from information_schema.foreign_tables;

To check the prefetch value of the tables, run the following command:

SELECT * FROM pg_foreign_table;

Verify that you upgraded your Babelfish for Aurora PostgreSQL-Compatible cluster

When you perform a major engine version upgrade on a Babelfish for Aurora PostgreSQL-Compatible cluster, you might receive an error message that's similar to the following one:

"You can't perform a multi major version upgrade on a Babelfish for Aurora PostgreSQL DB cluster 13.20 and lower versions."

To resolve this issue, upgrade the cluster to a version that supports the major version upgrade and then upgrade to the next major version.

After upgrade completion

After the upgrade is complete, run the ANALYZE command on all user databases to update the pg_statistics table. Major version upgrades don't migrate pg_statistics table contents to the new version. If you don't migrate the contents, then you might experience slow-running queries.

Related information

Overview of the Aurora PostgreSQL upgrade processes

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