rds_tools functions reference - Amazon Aurora
rds_tools.role_password_encryption_typerds_tools.postgres_get_av_diag
Services or capabilities described in AWS documentation might vary by Region. To see the differences applicable to the AWS European Sovereign Cloud Region, see the AWS European Sovereign Cloud User Guide.

rds_tools functions reference

The rds_tools extension provides a set of utility functions for managing and monitoring your Aurora PostgreSQL DB instance. Using these functions, you can diagnose replication, vacuum, and configuration issues directly from SQL, without separate monitoring tools. These functions are in addition to the standard PostgreSQL functions. For more information about standard PostgreSQL functions, see Functions and Operators in the PostgreSQL documentation.

This section describes the rds_tools functions available on Aurora PostgreSQL. Each function description notes the rds_tools extension version in which the function became available.

To create the extension, run the following command.

postgres=> CREATE EXTENSION rds_tools;
CREATE EXTENSION

rds_tools.role_password_encryption_type

Returns the password encryption method used for each database role. This set-returning function excludes RDS-protected system roles from the output. Available starting with rds_tools extension version 1.0.

For an example of using this function to review password encryption before migrating database users to SCRAM, see Using SCRAM for PostgreSQL password encryption.

Function prototype

rds_tools.role_password_encryption_type()

Arguments

None.

Output

  • rolname – The name of the database role.

  • encryption_type – The password encryption method used, such as md5 or scram-sha-256.

Usage example

=> SELECT * FROM rds_tools.role_password_encryption_type();
    rolname    | encryption_type
---------------+-----------------
 postgres      | md5
 app_user      | md5
 readonly_user | scram-sha-256
(3 rows)

rds_tools.postgres_get_av_diag

Retrieves diagnostic information about autovacuum processes that are blocking or lagging behind, along with suggestions for resolving each issue. Run this set-returning function in the database with the oldest transaction ID for accurate results. For more information, see Identify and resolve aggressive vacuum blockers.

Available starting with rds_tools extension version 1.9.

Function prototype

rds_tools.postgres_get_av_diag()

Arguments

None.

Output

  • blocker – The category of database activity blocking the vacuum. The value is one of the following:

    • Active statement

    • Idle in transaction

    • Prepared transaction

    • Logical replication slot

    • Read replica with physical replication slot

    • Read replica with streaming replication

    • Temporary tables

  • database – The name of the database where the blocking activity is occurring.

  • blocker_identifier – The identifier of the blocking activity. The value depends on the blocker type:

    • For an active statement or idle-in-transaction session, the process ID with its SQL statement.

    • For a prepared transaction, the prepared transaction name.

    • For a read replica, the IP address of the replica.

    • For a replication slot, the replication slot name.

  • wait_event – The wait event of the blocking session, where applicable.

  • autovacuum_lagging_by – The number of transactions that autovacuum is lagging behind for the category.

  • suggestion – A suggestion to resolve the blocker, including the database name, process ID, and the action to take.

  • suggested_action – An array of the actions to take to resolve the blocker.

Usage example

=> SELECT *
   FROM rds_tools.postgres_get_av_diag()
   ORDER BY autovacuum_lagging_by DESC;

blocker               | Active statement
database              | my_database
blocker_identifier    | SELECT pg_sleep(20000);
wait_event            | Timeout:PgSleep
autovacuum_lagging_by | 568600871
suggestion            | Connect to database "my_database", review carefully, and consider terminating the process using suggested_action.
suggested_action      | {"SELECT pg_terminate_backend(29621);"}