What are the limitations to use ZDP when I perform a minor version upgrade of my Aurora PostgreSQL-Compatible DB cluster?

Short description

Before you upgrade your cluster, check for and resolve the following factors that can prevent the use of ZDP:

• Long-running queries
• Active data definition language (DDL) operations
• Temporary tables
• Active table locks
• Sessions that listen on notification channels
• Incompatible TLS version

For a full list of limitations, see Limitations of zero-downtime patching.

For a list of Aurora PostgreSQL-Compatible versions that support ZDP, see Minor release upgrades and zero-downtime patching. To identify your current Aurora PostgreSQL-Compatible version, see Identifying versions of Amazon Aurora PostgreSQL.

Note: Your cluster must use supported instance class db.r, db.t, db.x, or db.serverless.

Resolution

Identify and resolve long-running queries

Complete the following steps:

1. Connect to your Aurora PostgreSQL-Compatible cluster.

2. To identify long-running queries, run the following command:

   SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
   FROM pg_stat_activity
   WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
   AND state = 'active';

3. To stop long-running queries, run the following command:

   SELECT pg_terminate_backend(PID);

   Note: Replace PID with your process ID.

4. To verify that you stopped all long-running queries, run the query from step 2 again.

Check for and complete DDL operations

You can wait until DDL operations complete, or you can stop them. 

Complete the following steps:

1. To check for active DDL operations, run the following command:

   SELECT pid, query, state, now() - query_start AS duration
   FROM pg_stat_activity
   WHERE query ILIKE '%CREATE%' OR query ILIKE '%ALTER%' OR query ILIKE '%DROP%'
   AND state = 'active';

2. To stop a DDL operation, run the following command:

   SELECT pg_terminate_backend(PID);

   Note: Replace PID with your process ID.

3. To confirm that you stopped your DDL operation, run the query from step 1 again.

Identify and close temporary tables

Complete the following steps:

1. To identify sessions with temporary tables, run the following command:

   SELECT schemaname, tablename, pidFROM pg_stat_user_tablesWHERE schemaname LIKE 'pg_temp_%';

2. To close sessions with temporary tables, run the following command:

   SELECT pg_terminate_backend(PID);

   Note: Replace PID with your process ID.

3.  To verify that you closed all temporary tables, run the query from step 1 again.

Check for and release table locks

Complete the following steps:

1. To check for active table locks, run the following command:

   SELECT l.locktype, l.database, l.relation, l.page, l.tuple, l.pid, a.queryFROM pg_locks lJOIN pg_stat_activity a ON l.pid = a.pidWHERE l.granted = true AND l.locktype = 'relation';

2. To release the locks, run the following command to stop the holding sessions:

   SELECT pg_terminate_backend(PID);

   Note: Replace PID with your process ID.

3. To verify that you released all locks, run the query from step 1 again.

Check for and stop notification channel listeners

Complete the following steps:

1. To identify sessions that listen on notification channels, run the following command:

   SELECT pid, query, stateFROM pg_stat_activityWHERE query ILIKE '%LISTEN%' AND state = 'idle';

2. To stop notification channel listeners, connect to each session and run the following command:

   UNLISTEN *;

   Or, run the pg_terminate_backend command to stop the sessions:

   SELECT pg_terminate_backend(PID);

Check your TLS version

ZDP supports TLSv1.3 connections.

To check the TLS version that you're currently using, run the following command:

SELECT pid, ssl, ssl_versionFROM pg_stat_sslJOIN pg_stat_activity USING (pid)
WHERE ssl = true;

If you're using a version of TLS that's earlier than 1.3, then update your version.

Upgrade your engine and monitor the upgrade

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.

To upgrade your DB cluster, see Upgrading the Aurora PostgreSQL engine to a new minor version.

To monitor upgrade events, run the following describe-events AWS CLI command:

aws rds describe-events --source-identifier CLUSTER-NAME --source-type db-cluster --start-time START-TIME

Note: Replace CLUSTER-NAME with your cluster name and START-TIME with the upgrade start time in ISO format.

Analyze the ZDP results

To check ZDP results in your database logs, connect to your cluster and then run the following command:

SELECT * FROM pg_stat_activity WHERE query LIKE '%zero downtime%';

If the ZDP operation succeeded, then you see log a event that's similar to the following one:

"Attempt to upgrade the database instance ###-### with zero downtime finished. The process took 3485 ms, 378 connections preserved, 0 connections dropped."

If the ZDP operation didn't succeed, then you see a log event that's similar to the following one:

"Database cluster engine minor version upgrade complete. Previous version: 13.12.9. New version: 13.18.4. Total time offline: 21.0 seconds. Taking database cluster offline while the primary instance completes the patch/upgrade process."

Note: ZDP operates on a best effort basis. Success depends on the completion of all preparation and database activity at the time of the upgrade.

Related information

Upgrading Amazon Aurora PostgreSQL DB clusters

Best practices with Amazon Aurora PostgreSQL

Working with Amazon Aurora PostgreSQL