# Aurora PostgreSQL functions reference
Following, you can find a list of Aurora PostgreSQL functions that are available for your Aurora DB clusters that run the Aurora PostgreSQL-Compatible Edition DB engine. These Aurora PostgreSQL functions are in addition to the standard PostgreSQL functions. For more information about standard PostgreSQL functions, see [PostgreSQL–Functions and Operators](https://www.postgresql.org/docs/current/functions.html).
## Overview
You can use the following functions for Amazon RDS DB instances running Aurora PostgreSQL:
+ [aurora\$1db\$1instance\$1identifier](aurora_db_instance_identifier.md)
+ [aurora\$1ccm\$1status](aurora_ccm_status.md)
+ [aurora\$1global\$1db\$1instance\$1status](aurora_global_db_instance_status.md)
+ [aurora\$1global\$1db\$1status](aurora_global_db_status.md)
+ [aurora\$1list\$1builtins](aurora_list_builtins.md)
+ [aurora\$1replica\$1status](aurora_replica_status.md)
+ [aurora\$1stat\$1activity](aurora_stat_activity.md)
+ [aurora\$1stat\$1backend\$1waits](aurora_stat_backend_waits.md)
+ [aurora\$1stat\$1bgwriter](aurora_stat_bgwriter.md)
+ [aurora\$1stat\$1database](aurora_stat_database.md)
+ [aurora\$1stat\$1dml\$1activity](aurora_stat_dml_activity.md)
+ [aurora\$1stat\$1get\$1db\$1commit\$1latency](aurora_stat_get_db_commit_latency.md)
+ [aurora\$1stat\$1logical\$1wal\$1cache](aurora_stat_logical_wal_cache.md)
+ [aurora\$1stat\$1memctx\$1usage](aurora_stat_memctx_usage.md)
+ [aurora\$1stat\$1optimized\$1reads\$1cache](aurora_stat_optimized_reads_cache.md)
+ [aurora\$1stat\$1plans](aurora_stat_plans.md)
+ [aurora\$1stat\$1reset\$1wal\$1cache](aurora_stat_reset_wal_cache.md)
+ [aurora\$1stat\$1statements](aurora_stat_statements.md)
+ [aurora\$1stat\$1system\$1waits](aurora_stat_system_waits.md)
+ [aurora\$1stat\$1wait\$1event](aurora_stat_wait_event.md)
+ [aurora\$1stat\$1wait\$1type](aurora_stat_wait_type.md)
+ [aurora\$1version](aurora_version.md)
+ [aurora\$1volume\$1logical\$1start\$1lsn](aurora_volume_logical_start_lsn.md)
+ [aurora\$1wait\$1report](aurora_wait_report.md)
# aurora\$1db\$1instance\$1identifier
Reports the name of the DB instance name to which you're connected.
## Syntax
```
aurora_db_instance_identifier()
```
## Arguments
None
## Return type
VARCHAR string
## Usage notes
This function displays the name of Aurora PostgreSQL-Compatible Edition cluster's DB instance for your database client or application connection.
This function is available starting with the release of Aurora PostgreSQL versions 13.7, 12.11, 11.16, 10.21 and for all other later versions.
## Examples
The following example shows results of calling the `aurora_db_instance_identifier` function.
```
=> SELECT aurora_db_instance_identifier();
aurora_db_instance_identifier
-------------------------------
test-my-instance-name
```
You can join the results of this function with the `aurora_replica_status` function to obtain details about the DB instance for your connection. The [aurora\$1replica\$1status](aurora_replica_status.md) alone doesn't provide show you which DB instance you're using. The following example shows you how.
```
=> SELECT *
FROM aurora_replica_status() rt,
aurora_db_instance_identifier() di
WHERE rt.server_id = di;
-[ RECORD 1 ]----------------------+-----------------------
server_id | test-my-instance-name
session_id | MASTER_SESSION_ID
durable_lsn | 88492069
highest_lsn_rcvd |
current_read_lsn |
cur_replay_latency_in_usec |
active_txns |
is_current | t
last_transport_error | 0
last_error_timestamp |
last_update_timestamp | 2022-06-03 11:18:25+00
feedback_xmin |
feedback_epoch |
replica_lag_in_msec |
log_stream_speed_in_kib_per_second | 0
log_buffer_sequence_number | 0
oldest_read_view_trx_id |
oldest_read_view_lsn |
pending_read_ios | 819
```
# aurora\$1ccm\$1status
Displays the status of cluster cache manager.
## Syntax
```
aurora_ccm_status()
```
## Arguments
None.
## Return type
SETOF record with the following columns:
+ `buffers_sent_last_minute` – The number of buffers sent to the designated reader in the past minute.
+ `buffers_found_last_minute` – The number of frequently accessed buffers identified during the past minute.
+ `buffers_sent_last_scan` – The number of buffers sent to the designated reader during the last complete scan of the buffer cache.
+ `buffers_found_last_scan` – The number of frequently accessed buffers sent during the last complete scan of the buffer cache. Buffers that are already cached on the designated reader aren't sent.
+ `buffers_sent_current_scan` – The number of buffers sent during the current scan.
+ `buffers_found_current_scan` – The number of frequently accessed buffers that were identified in the current scan.
+ `current_scan_progress` – The number of buffers visited so far during the current scan.
## Usage notes
You can use this function to check and monitor the cluster cache management (CCM) feature. This function works only if CCM is active on your Aurora PostgreSQL DB cluster. To use this function you connect to the Write DB instance on your Aurora PostgreSQL DB cluster.
You turn on CCM for an Aurora PostgreSQL DB cluster by setting the `apg_ccm_enabled` to 1 in the cluster's custom DB cluster parameter group. To learn how, see [Configuring cluster cache management](AuroraPostgreSQL.cluster-cache-mgmt.md#AuroraPostgreSQL.cluster-cache-mgmt.Configure).
Cluster cache management is active on an Aurora PostgreSQL DB cluster when the cluster has an Aurora Reader instance configured as follows:
+ The Aurora Reader instance uses same DB instance class type and size as the cluster's Writer instance.
+ The Aurora Reader instance is configured as Tier-0 for the cluster. If the cluster has more than one Reader, this is its only Tier-0 Reader.
Setting more than one Reader to Tier-0 disables CCM. When CCM is disabled, calling this function returns the following error message:
```
ERROR: Cluster Cache Manager is disabled
```
You can also the PostgreSQL pg\$1buffercache extension to analyze the buffer cache. For more information, see [pg\$1buffercache](https://www.postgresql.org/docs/current/pgbuffercache.html) in the PostgreSQL documentation.
For more information, see [Introduction to Aurora PostgreSQL cluster cache management](https://aws.amazon.com/blogs/database/introduction-to-aurora-postgresql-cluster-cache-management/).
## Examples
The following example shows the results of calling the `aurora_ccm_status` function. This first example shows CCM statistics.
```
=> SELECT * FROM aurora_ccm_status();
buffers_sent_last_minute | buffers_found_last_minute | buffers_sent_last_scan | buffers_found_last_scan | buffers_sent_current_scan | buffers_found_current_scan | current_scan_progress
--------------------------+---------------------------+------------------------+-------------------------+---------------------------+----------------------------+-----------------------
2242000 | 2242003 | 17920442 | 17923410 | 14098000 | 14100964 | 15877443
```
For more complete detail, you can use expanded display, as shown following:
```
\x
Expanded display is on.
SELECT * FROM aurora_ccm_status();
[ RECORD 1 ]-----------------------+---------
buffers_sent_last_minute | 2242000
buffers_found_last_minute | 2242003
buffers_sent_last_scan | 17920442
buffers_found_last_scan | 17923410
buffers_sent_current_scan | 14098000
buffers_found_current_scan | 14100964
current_scan_progress | 15877443
```
This example shows how to check warm rate and warm percentage.
```
=> SELECT buffers_sent_last_minute * 8/60 AS warm_rate_kbps,
100 * (1.0-buffers_sent_last_scan/buffers_found_last_scan) AS warm_percent
FROM aurora_ccm_status ();
warm_rate_kbps | warm_percent
----------------+--------------
16523 | 100.0
```
# aurora\$1global\$1db\$1instance\$1status
Displays the status of all Aurora instances, including replicas in an Aurora global DB cluster.
## Syntax
```
aurora_global_db_instance_status()
```
## Arguments
None
## Return type
SETOF record with the following columns:
+ `server_id` – The identifier of the DB instance.
+ `session_id` – A unique identifier for the current session. A value of `MASTER_SESSION_ID` identifies the Writer (primary) DB instance.
+ `aws_region` – The AWS Region in which this global DB instance runs. For a list of Regions, see [Region availability](Concepts.RegionsAndAvailabilityZones.md#Aurora.Overview.Availability).
+ `durable_lsn` – The log sequence number (LSN) made durable in storage. A log sequence number (LSN) is a unique sequential number that identifies a record in the database transaction log. LSNs are ordered such that a larger LSN represents a later transaction.
+ `highest_lsn_rcvd` – The highest LSN received by the DB instance from the writer DB instance.
+ `feedback_epoch` – The epoch that the DB instance uses when it generates hot standby information. A *hot standby* is a DB instance that supports connections and queries while the primary DB is in recovery or standby mode. The hot standby information includes the epoch (point in time) and other details about the DB instance that's being used as a hot standby. For more information, see [Hot Standby](https://www.postgresql.org/docs/current/hot-standby.html) in the PostgreSQL documentation.
+ `feedback_xmin` – The minimum (oldest) active transaction ID used by the DB instance.
+ `oldest_read_view_lsn` – The oldest LSN used by the DB instance to read from storage.
+ `visibility_lag_in_msec` – How far this DB instance is lagging behind the writer DB instance in milliseconds.
## Usage notes
This function shows replication statistics for an Aurora DB cluster. For each Aurora PostgreSQL DB instance in the cluster, the function shows a row of data that includes any cross-Region replicas in a global database configuration.
You can run this function from any instance in an Aurora PostgreSQL DB cluster or an Aurora PostgreSQL global database. The function returns details about lag for all replica instances.
To learn more about monitoring lag using this function (`aurora_global_db_instance_status`) or by using using `aurora_global_db_status`, see [Monitoring Aurora PostgreSQL-based global databases](aurora-global-database-monitoring.md#aurora-global-database-monitoring.postgres).
For more information about Aurora global databases, see [Overview of Amazon Aurora Global Database](aurora-global-database.md#aurora-global-database-overview).
To get started with Aurora global databases, see [Getting started with Amazon Aurora Global Database](aurora-global-database-getting-started.md) or see [Amazon Aurora FAQs](https://aws.amazon.com/rds/aurora/faqs/).
## Examples
This example shows cross-Region instance stats.
```
=> SELECT *
FROM aurora_global_db_instance_status();
server_id | session_id | aws_region | durable_lsn | highest_lsn_rcvd | feedback_epoch | feedback_xmin | oldest_read_view_lsn | visibility_lag_in_msec
------------------------------------------+--------------------------------------+--------------+-------------+------------------+----------------+---------------+----------------------+------------------------
db-119-001-instance-01 | MASTER_SESSION_ID | eu-west-1 | 2534560273 | [NULL] | [NULL] | [NULL] | [NULL] | [NULL]
db-119-001-instance-02 | 4ecff34d-d57c-409c-ba28-278b31d6fc40 | eu-west-1 | 2534560266 | 2534560273 | 0 | 19669196 | 2534560266 | 6
db-119-001-instance-03 | 3e8a20fc-be86-43d5-95e5-bdf19d27ad6b | eu-west-1 | 2534560266 | 2534560273 | 0 | 19669196 | 2534560266 | 6
db-119-001-instance-04 | fc1b0023-e8b4-4361-bede-2a7e926cead6 | eu-west-1 | 2534560266 | 2534560273 | 0 | 19669196 | 2534560254 | 23
db-119-001-instance-05 | 30319b74-3f08-4e13-9728-e02aa1aa8649 | eu-west-1 | 2534560266 | 2534560273 | 0 | 19669196 | 2534560254 | 23
db-119-001-global-instance-1 | a331ffbb-d982-49ba-8973-527c96329c60 | eu-central-1 | 2534560254 | 2534560266 | 0 | 19669196 | 2534560247 | 996
db-119-001-global-instance-1 | e0955367-7082-43c4-b4db-70674064a9da | eu-west-2 | 2534560254 | 2534560266 | 0 | 19669196 | 2534560247 | 14
db-119-001-global-instance-1-eu-west-2a | 1248dc12-d3a4-46f5-a9e2-85850491a897 | eu-west-2 | 2534560254 | 2534560266 | 0 | 19669196 | 2534560247 | 0
```
This example shows how to check global replica lag in milliseconds.
```
=> SELECT CASE
WHEN 'MASTER_SESSION_ID' = session_id THEN 'Primary'
ELSE 'Secondary'
END AS global_role,
aws_region,
server_id,
visibility_lag_in_msec
FROM aurora_global_db_instance_status()
ORDER BY 1, 2, 3;
global_role | aws_region | server_id | visibility_lag_in_msec
-------------+--------------+-----------------------------------------+------------------------
Primary | eu-west-1 | db-119-001-instance-01 | [NULL]
Secondary | eu-central-1 | db-119-001-global-instance-1 | 13
Secondary | eu-west-1 | db-119-001-instance-02 | 10
Secondary | eu-west-1 | db-119-001-instance-03 | 9
Secondary | eu-west-1 | db-119-001-instance-04 | 2
Secondary | eu-west-1 | db-119-001-instance-05 | 18
Secondary | eu-west-2 | db-119-001-global-instance-1 | 14
Secondary | eu-west-2 | db-119-001-global-instance-1-eu-west-2a | 13
```
This example shows how to check min, max and average lag per AWS Region from the global database configuration.
```
=> SELECT 'Secondary' global_role,
aws_region,
min(visibility_lag_in_msec) min_lag_in_msec,
max(visibility_lag_in_msec) max_lag_in_msec,
round(avg(visibility_lag_in_msec),0) avg_lag_in_msec
FROM aurora_global_db_instance_status()
WHERE aws_region NOT IN (SELECT aws_region
FROM aurora_global_db_instance_status()
WHERE session_id='MASTER_SESSION_ID')
GROUP BY aws_region
UNION ALL
SELECT 'Primary' global_role,
aws_region,
NULL,
NULL,
NULL
FROM aurora_global_db_instance_status()
WHERE session_id='MASTER_SESSION_ID'
ORDER BY 1, 5;
global_role | aws_region | min_lag_in_msec | max_lag_in_msec | avg_lag_in_msec
------------+--------------+-----------------+-----------------+-----------------
Primary | eu-west-1 | [NULL] | [NULL] | [NULL]
Secondary | eu-central-1 | 133 | 133 | 133
Secondary | eu-west-2 | 0 | 495 | 248
```
# aurora\$1global\$1db\$1status
Displays information about various aspects of Aurora global database lag, specifically, lag of the underlying Aurora storage (so called durability lag) and lag between the recovery point objective (RPO).
## Syntax
```
aurora_global_db_status()
```
## Arguments
None.
## Return type
SETOF record with the following columns:
+ `aws_region` – The AWS Region that this DB cluster is in. For a complete listing of AWS Regions by engine, see [Regions and Availability Zones](Concepts.RegionsAndAvailabilityZones.md).
+ `highest_lsn_written` – The highest log sequence number (LSN) that currently exists on this DB cluster. A log sequence number (LSN) is a unique sequential number that identifies a record in the database transaction log. LSNs are ordered such that a larger LSN represents a later transaction.
+ `durability_lag_in_msec` – The difference in the timestamp values between the `highest_lsn_written` on a secondary DB cluster and the `highest_lsn_written` on the primary DB cluster. A value of -1 identifies the primary DB cluster of the Aurora global database.
+ `rpo_lag_in_msec` – The recovery point objective (RPO) lag. The RPO lag is the time it takes for the most recent user transaction COMMIT to be stored on a secondary DB cluster after it's been stored on the primary DB cluster of the Aurora global database. A value of -1 denotes the primary DB cluster (and thus, lag isn't relevant).
In simple terms, this metric calculates the recovery point objective for each Aurora PostgreSQL DB cluster in the Aurora global database, that is, how much data might be lost if there were an outage. As with lag, RPO is measured in time.
+ `last_lag_calculation_time` – The timestamp that specifies when values were last calculated for `durability_lag_in_msec` and `rpo_lag_in_msec`. A time value such as `1970-01-01 00:00:00+00` means this is the primary DB cluster.
+ `feedback_epoch` – The epoch that the secondary DB cluster uses when it generates hot standby information. A *hot standby* is a DB instance that supports connections and queries while the primary DB is in recovery or standby mode. The hot standby information includes the epoch (point in time) and other details about the DB instance that's being used as a hot standby. For more information, see [Hot Standby](https://www.postgresql.org/docs/current/hot-standby.html) in the PostgreSQL documentation.
+ `feedback_xmin` – The minimum (oldest) active transaction ID used by a secondary DB cluster.
## Usage notes
All currently available Aurora PostgreSQL versions support this function. This function shows replication statistics for an Aurora global database. It shows one row for each DB cluster in an Aurora PostgreSQL global database. You can run this function from any instance in your Aurora PostgreSQL global database.
To evaluate Aurora global database replication lag, which is the visible data lag, see [aurora\$1global\$1db\$1instance\$1status](aurora_global_db_instance_status.md).
To learn more about using `aurora_global_db_status` and `aurora_global_db_instance_status` to monitor Aurora global database lag, see [Monitoring Aurora PostgreSQL-based global databases](aurora-global-database-monitoring.md#aurora-global-database-monitoring.postgres). For more information about Aurora global databases, see [Overview of Amazon Aurora Global Database](aurora-global-database.md#aurora-global-database-overview).
## Examples
This example shows how to display cross-region storage statistics.
```
=> SELECT CASE
WHEN '-1' = durability_lag_in_msec THEN 'Primary'
ELSE 'Secondary'
END AS global_role,
*
FROM aurora_global_db_status();
global_role | aws_region | highest_lsn_written | durability_lag_in_msec | rpo_lag_in_msec | last_lag_calculation_time | feedback_epoch | feedback_xmin
-------------+------------+---------------------+------------------------+-----------------+----------------------------+----------------+---------------
Primary | eu-west-1 | 131031557 | -1 | -1 | 1970-01-01 00:00:00+00 | 0 | 0
Secondary | eu-west-2 | 131031554 | 410 | 0 | 2021-06-01 18:59:36.124+00 | 0 | 12640
Secondary | eu-west-3 | 131031554 | 410 | 0 | 2021-06-01 18:59:36.124+00 | 0 | 12640
```
# aurora\$1list\$1builtins
Lists all available Aurora PostgreSQL built-in functions, along with brief descriptions and function details.
## Syntax
```
aurora_list_builtins()
```
## Arguments
None
## Return type
SETOF record
## Examples
The following example shows results from calling the `aurora_list_builtins` function.
```
=> SELECT *
FROM aurora_list_builtins();
Name | Result data type | Argument data types | Type | Volatility | Parallel | Security | Description
-----------------------------------+------------------+----------------------------------------------------------+------+------------+------------+----------+---------------------------------------------------------------------
aurora_version | text | | func | stable | safe | invoker | Amazon Aurora PostgreSQL-Compatible Edition version string
aurora_stat_wait_type | SETOF record | OUT type_id smallint, OUT type_name text | func | volatile | restricted | invoker | Lists all supported wait types
aurora_stat_wait_event | SETOF record | OUT type_id smallint, OUT event_id integer, OUT event_na.| func | volatile | restricted | invoker | Lists all supported wait events
| |.me text | | | | |
aurora_list_builtins | SETOF record | OUT "Name" text, OUT "Result data type" text, OUT "Argum.| func | stable | safe | invoker | Lists all Aurora built-in functions
| |.ent data types" text, OUT "Type" text, OUT "Volatility" .| | | | |
| |.text, OUT "Parallel" text, OUT "Security" text, OUT "Des.| | | | |
| |.cription" text | | | | |
.
.
.
aurora_stat_file | SETOF record | OUT filename text, OUT allocated_bytes bigint, OUT used_.| func | stable | safe | invoker | Lists all files present in Aurora storage
| |.bytes bigint | | | | |
aurora_stat_get_db_commit_latency | bigint | oid | func | stable | restricted | invoker | Per DB commit latency in microsecs
```
# aurora\$1replica\$1status
Displays the status of all Aurora PostgreSQL reader nodes.
## Syntax
```
aurora_replica_status()
```
## Arguments
None
## Return type
SETOF record with the following columns:
+ `server_id` – The DB instance ID (identifier).
+ `session_id` – A unique identifier for the current session, returned for primary instance and reader instances as follows:
+ For the primary instance, `session_id` is always ``MASTER_SESSION_ID’`.
+ For reader instances, `session_id` is always the `UUID` (universally unique identifier) of the reader instance.
+ `durable_lsn` – The log sequence number (LSN) that's been saved in storage.
+ For the primary volume, the primary volume durable LSN (VDL) that's currently in effect.
+ For any secondary volumes, the VDL of the primary up to which the secondary has successfully been applied.
**Note**
A log sequence number (LSN) is a unique sequential number that identifies a record in the database transaction log. LSNs are ordered such that a larger LSN represents a transaction that's occurred later in the sequence.
+ `highest_lsn_rcvd` – The highest (most recent) LSN received by the DB instance from the writer DB instance.
+ `current_read_lsn` – The LSN of the most recent snapshot that's been applied to all readers.
+ `cur_replay_latency_in_usec` – The number of microseconds that it's expected to take to replay the log on the secondary.
+ `active_txns` – The number of currently active transactions.
+ `is_current` – Not used.
+ `last_transport_error` – Last replication error code.
+ `last_error_timestamp` – Timestamp of last replication error.
+ `last_update_timestamp` – Timestamp of last update to replica status. From Aurora PostgreSQL 13.9, the `last_update_timestamp` value for the DB instance that you are connected to is set to `NULL`.
+ `feedback_xmin` – The hot standby feedback\$1xmin of the replica. The minimum (oldest) active transaction ID used by the DB instance.
+ `feedback_epoch` – The epoch the DB instance uses when it generates hot standby information.
+ `replica_lag_in_msec` – Time that reader instance lags behind writer instance, in milliseconds.
+ `log_stream_speed_in_kib_per_second` – The log stream throughput in kilobytes per second.
+ `log_buffer_sequence_number` – The log buffer sequence number.
+ `oldest_read_view_trx_id` – Not used.
+ `oldest_read_view_lsn` – The oldest LSN used by the DB instance to read from storage.
+ `pending_read_ios` – The outstanding page reads that are still pending on replica.
+ `read_ios` – The total number of page reads on replica.
+ `iops` – Not used.
+ `cpu` – CPU usage of the Aurora Storage Daemon for each node in the cluster. For information about CPU usage by the instance, see [Instance-level metrics for Amazon Aurora](Aurora.AuroraMonitoring.Metrics.md#Aurora.AuroraMySQL.Monitoring.Metrics.instances).
## Usage notes
All currently available Aurora PostgreSQL versions support this function. The `aurora_replica_status` function returns values from an Aurora PostgreSQL DB cluster's replica status manager. You can use this function to obtain information about the status of replication on your Aurora PostgreSQL DB cluster, including metrics for all DB instances in your Aurora DB cluster. For example, you can do the following:
+ **Get information about the type of instance (writer, reader) in the Aurora PostgreSQL DB cluster** – You can obtain this information by checking the values of the following columns:
+ `server_id` – Contains the name of the instance that you specified when you created the instance. In some cases, such as for the primary (writer) instance, the name is typically created for you by appending *-instance-1* to the name that you create for your Aurora PostgreSQL DB cluster.
+ `session_id` – The `session_id` field indicates whether the instance is a reader or a writer. For a writer instance, `session_id` is always set to `"MASTER_SESSION_ID"`. For a reader instance, `session_id` is set to the `UUID` of the specific reader.
+ **Diagnose common replication issues, such as replica lag** – Replica lag is the time in milliseconds that the page cache of a reader instance is behind that of the writer instance. This lag occurs because Aurora clusters use asynchronous replication, as described in [Replication with Amazon Aurora](Aurora.Replication.md). It's shown in the `replica_lag_in_msec` column in the results returned by this function. Lag can also occur when a query is cancelled due to conflicts with recovery on a standby server. You can check `pg_stat_database_conflicts()` to verify that such a conflict is causing the replica lag (or not). For more information, see [The Statistics Collector](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-CONFLICTS-VIEW) in the *PostgreSQL documentation*. To learn more about high availability and replication, see [Amazon Aurora FAQs](https://aws.amazon.com/rds/aurora/faqs/#High_Availability_and_Replication).
Amazon CloudWatch stores `replica_lag_in_msec` results over time, as the `AuroraReplicaLag` metric. For information about using CloudWatch metrics for Aurora, see [Monitoring Amazon Aurora metrics with Amazon CloudWatch](monitoring-cloudwatch.md)
To learn more about troubleshooting Aurora read replicas and restarts, see [Why did my Amazon Aurora read replica fall behind and restart?](https://aws.amazon.com/premiumsupport/knowledge-center/aurora-read-replica-restart/) in the [AWS Support Center](https://console.aws.amazon.com/support/home#/).
## Examples
The following example shows how to get the replication status of all instances in an Aurora PostgreSQL DB cluster:
```
=> SELECT *
FROM aurora_replica_status();
```
The following example shows the writer instance in the `docs-lab-apg-main` Aurora PostgreSQL DB cluster:
```
=> SELECT server_id,
CASE
WHEN 'MASTER_SESSION_ID' = session_id THEN 'writer'
ELSE 'reader'
END AS instance_role
FROM aurora_replica_status()
WHERE session_id = 'MASTER_SESSION_ID';
server_id | instance_role
------------------------+---------------
db-119-001-instance-01 | writer
```
The following example example lists all reader instances in a cluster:
```
=> SELECT server_id,
CASE
WHEN 'MASTER_SESSION_ID' = session_id THEN 'writer'
ELSE 'reader'
END AS instance_role
FROM aurora_replica_status()
WHERE session_id <> 'MASTER_SESSION_ID';
server_id | instance_role
------------------------+---------------
db-119-001-instance-02 | reader
db-119-001-instance-03 | reader
db-119-001-instance-04 | reader
db-119-001-instance-05 | reader
(4 rows)
```
The following example lists all instances, how far each instance is lagging behind the writer, and how long since the last update:
```
=> SELECT server_id,
CASE
WHEN 'MASTER_SESSION_ID' = session_id THEN 'writer'
ELSE 'reader'
END AS instance_role,
replica_lag_in_msec AS replica_lag_ms,
round(extract (epoch FROM (SELECT age(clock_timestamp(), last_update_timestamp))) * 1000) AS last_update_age_ms
FROM aurora_replica_status()
ORDER BY replica_lag_in_msec NULLS FIRST;
server_id | instance_role | replica_lag_ms | last_update_age_ms
------------------------+---------------+----------------+--------------------
db-124-001-instance-03 | writer | [NULL] | 1756
db-124-001-instance-01 | reader | 13 | 1756
db-124-001-instance-02 | reader | 13 | 1756
(3 rows)
```
# aurora\$1stat\$1activity
Returns one row per server process, showing information related to the current activity of that process.
## Syntax
```
aurora_stat_activity();
```
## Arguments
None
## Return type
Returns one row per server process. In additional to `pg_stat_activity` columns, the following field is added:
+ planid – plan identifier
## Usage notes
A supplementary view to `pg_stat_activity` returning the same columns with an additional `plan_id` column which shows the current query execution plan.
`aurora_compute_plan_id` must be enabled for the view to return a plan\$1id.
This function is available from Aurora PostgreSQL versions 14.10, 15.5, and for all other later versions.
## Examples
The example query below aggregates the top load by query\$1id and plan\$1id.
```
db1=# select count(*), query_id, plan_id
db1-# from aurora_stat_activity() where state = 'active'
db1-# and pid <> pg_backend_pid()
db1-# group by query_id, plan_id
db1-# order by 1 desc;
count | query_id | plan_id
-------+----------------------+-------------
11 | -5471422286312252535 | -2054628807
3 | -6907107586630739258 | -815866029
1 | 5213711845501580017 | 300482084
(3 rows)
```
If the plan used for query\$1id changes, a new plan\$1id will be reported by aurora\$1stat\$1activity.
```
count | query_id | plan_id
-------+----------------------+-------------
10 | -5471422286312252535 | 1602979607
1 | -6907107586630739258 | -1809935983
1 | -2446282393000597155 | -207532066
(3 rows)
```
# aurora\$1stat\$1backend\$1waits
Displays statistics for wait activity for a specific backend process.
## Syntax
```
aurora_stat_backend_waits(pid)
```
## Arguments
`pid` – The ID for the backend process. You can obtain process IDs by using the `pg_stat_activity` view.
## Return type
SETOF record with the following columns:
+ `type_id` – A number that denotes the type of wait event, such as `1` for a lightweight lock (`LWLock`), `3` for a lock, or `6` for a client session, to name some examples. These values become meaningful when you join the results of this function with columns from `aurora_stat_wait_type` function, as shown in the [Examples](#aurora_stat_backend_waits-examples).
+ `event_id` – An identifying number for the wait event. Join this value with the columns from `aurora_stat_wait_event` to obtain meaningful event names.
+ `waits` – A count of the number of waits accumulated for the specified process ID.
+ `wait_time` – Wait time in milliseconds.
## Usage notes
You can use this function to analyze specific backend (session) wait events that occurred since a connection opened. To get more meaningful information about wait event names and types, you can combine this function `aurora_stat_wait_type` and `aurora_stat_wait_event`, by using JOIN as shown in the examples.
## Examples
This example shows all waits, types, and event names for a backend process ID 3027.
```
=> SELECT type_name, event_name, waits, wait_time
FROM aurora_stat_backend_waits(3027)
NATURAL JOIN aurora_stat_wait_type()
NATURAL JOIN aurora_stat_wait_event();
type_name | event_name | waits | wait_time
-----------+------------------------+-------+------------
LWLock | ProcArrayLock | 3 | 27
LWLock | wal_insert | 423 | 16336
LWLock | buffer_content | 11840 | 1033634
LWLock | lock_manager | 23821 | 5664506
Lock | tuple | 10258 | 152280165
Lock | transactionid | 78340 | 1239808783
Client | ClientRead | 34072 | 17616684
IO | ControlFileSyncUpdate | 2 | 0
IO | ControlFileWriteUpdate | 4 | 32
IO | RelationMapRead | 2 | 795
IO | WALWrite | 36666 | 98623
IO | XactSync | 4867 | 7331963
```
This example shows current and cumulative wait types and wait events for all active sessions (`pg_stat_activity state <> 'idle'`) (but without the current session that's invoking the function (`pid <> pg_backend_pid()`).
```
=> SELECT a.pid,
a.usename,
a.app_name,
a.current_wait_type,
a.current_wait_event,
a.current_state,
wt.type_name AS wait_type,
we.event_name AS wait_event,
a.waits,
a.wait_time
FROM (SELECT pid,
usename,
left(application_name,16) AS app_name,
coalesce(wait_event_type,'CPU') AS current_wait_type,
coalesce(wait_event,'CPU') AS current_wait_event,
state AS current_state,
(aurora_stat_backend_waits(pid)).*
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND state <> 'idle') a
NATURAL JOIN aurora_stat_wait_type() wt
NATURAL JOIN aurora_stat_wait_event() we;
pid | usename | app_name | current_wait_type | current_wait_event | current_state | wait_type | wait_event | waits | wait_time
-------+----------+----------+-------------------+--------------------+---------------+-----------+------------------------+-------+-----------
30099 | postgres | pgbench | Lock | transactionid | active | LWLock | wal_insert | 1937 | 29975
30099 | postgres | pgbench | Lock | transactionid | active | LWLock | buffer_content | 22903 | 760498
30099 | postgres | pgbench | Lock | transactionid | active | LWLock | lock_manager | 10012 | 223207
30099 | postgres | pgbench | Lock | transactionid | active | Lock | tuple | 20315 | 63081529
.
.
.
30099 | postgres | pgbench | Lock | transactionid | active | IO | WALWrite | 93293 | 237440
30099 | postgres | pgbench | Lock | transactionid | active | IO | XactSync | 13010 | 19525143
30100 | postgres | pgbench | Lock | transactionid | active | LWLock | ProcArrayLock | 6 | 53
30100 | postgres | pgbench | Lock | transactionid | active | LWLock | wal_insert | 1913 | 25450
30100 | postgres | pgbench | Lock | transactionid | active | LWLock | buffer_content | 22874 | 778005
.
.
.
30109 | postgres | pgbench | IO | XactSync | active | LWLock | ProcArrayLock | 3 | 71
30109 | postgres | pgbench | IO | XactSync | active | LWLock | wal_insert | 1940 | 27741
30109 | postgres | pgbench | IO | XactSync | active | LWLock | buffer_content | 22962 | 776352
30109 | postgres | pgbench | IO | XactSync | active | LWLock | lock_manager | 9879 | 218826
30109 | postgres | pgbench | IO | XactSync | active | Lock | tuple | 20401 | 63581306
30109 | postgres | pgbench | IO | XactSync | active | Lock | transactionid | 50769 | 211645008
30109 | postgres | pgbench | IO | XactSync | active | Client | ClientRead | 89901 | 44192439
```
This example shows current and the top three (3) cumulative wait type and wait events for all active sessions (`pg_stat_activity state <> 'idle'`) excluding current session (`pid <>pg_backend_pid()`).
```
=> SELECT top3.*
FROM (SELECT a.pid,
a.usename,
a.app_name,
a.current_wait_type,
a.current_wait_event,
a.current_state,
wt.type_name AS wait_type,
we.event_name AS wait_event,
a.waits,
a.wait_time,
RANK() OVER (PARTITION BY pid ORDER BY a.wait_time DESC)
FROM (SELECT pid,
usename,
left(application_name,16) AS app_name,
coalesce(wait_event_type,'CPU') AS current_wait_type,
coalesce(wait_event,'CPU') AS current_wait_event,
state AS current_state,
(aurora_stat_backend_waits(pid)).*
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND state <> 'idle') a
NATURAL JOIN aurora_stat_wait_type() wt
NATURAL JOIN aurora_stat_wait_event() we) top3
WHERE RANK <=3;
pid | usename | app_name | current_wait_type | current_wait_event | current_state | wait_type | wait_event | waits | wait_time | rank
-------+----------+----------+-------------------+--------------------+---------------+-----------+-----------------+---------+------------+------
20567 | postgres | psql | CPU | CPU | active | LWLock | wal_insert | 25000 | 67512003 | 1
20567 | postgres | psql | CPU | CPU | active | IO | WALWrite | 3071758 | 1016961 | 2
20567 | postgres | psql | CPU | CPU | active | IO | BufFileWrite | 20750 | 184559 | 3
27743 | postgres | pgbench | Lock | transactionid | active | Lock | transactionid | 237350 | 1265580011 | 1
27743 | postgres | pgbench | Lock | transactionid | active | Lock | tuple | 93641 | 341472318 | 2
27743 | postgres | pgbench | Lock | transactionid | active | Client | ClientRead | 417556 | 204796837 | 3
.
.
.
27745 | postgres | pgbench | IO | XactSync | active | Lock | transactionid | 238068 | 1265816822 | 1
27745 | postgres | pgbench | IO | XactSync | active | Lock | tuple | 93210 | 338312247 | 2
27745 | postgres | pgbench | IO | XactSync | active | Client | ClientRead | 419157 | 207836533 | 3
27746 | postgres | pgbench | Lock | transactionid | active | Lock | transactionid | 237621 | 1264528811 | 1
27746 | postgres | pgbench | Lock | transactionid | active | Lock | tuple | 93563 | 339799310 | 2
27746 | postgres | pgbench | Lock | transactionid | active | Client | ClientRead | 417304 | 208372727 | 3
```
# aurora\$1stat\$1bgwriter
`aurora_stat_bgwriter` is a statistics view showing information about Optimized Reads cache writes.
## Syntax
```
aurora_stat_bgwriter()
```
## Arguments
None
## Return type
SETOF record with all `pg_stat_bgwriter` columns and the following additional columns. For more information on `pg_stat_bgwriter` columns, see [https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-BGWRITER-VIEW](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-BGWRITER-VIEW).
You can reset stats for this function using `pg_stat_reset_shared("bgwriter")`.
+ `orcache_blks_written` – Total number of optimized reads cache data blocks written.
+ `orcache_blk_write_time` – If `track_io_timing` is enabled, it tracks the total time spent writing optimized reads cache data file blocks, in milliseconds. For more information, see [track\$1io\$1timing](https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-IO-TIMING).
## Usage notes
This function is available in the following Aurora PostgreSQL versions:
+ 15.4 and higher 15 versions
+ 14.9 and higher 14 versions
## Examples
```
=> select * from aurora_stat_bgwriter();
-[ RECORD 1 ]-----------------+-----------
orcache_blks_written | 246522
orcache_blk_write_time | 339276.404
```
# aurora\$1stat\$1database
It carries all columns of pg\$1stat\$1database and adds new columns in the end.
## Syntax
```
aurora_stat_database()
```
## Arguments
None
## Return type
SETOF record with all `pg_stat_database` columns and the following additional columns. For more information on `pg_stat_database` columns, see [https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW).
+ `storage_blks_read` – Total number of shared blocks read from aurora storage in this database.
+ `orcache_blks_hit` – Total number of optimized reads cache hits in this database.
+ `local_blks_read` – Total number of local blocks read in this database.
+ `storage_blk_read_time` – If `track_io_timing` is enabled, it tracks the total time spent reading data file blocks from aurora storage, in milliseconds, otherwise the value is zero. For more information, see [track\$1io\$1timing](https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-IO-TIMING).
+ `local_blk_read_time` – If `track_io_timing` is enabled, it tracks the total time spent reading local data file blocks, in milliseconds, otherwise the value is zero. For more information, see [track\$1io\$1timing](https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-IO-TIMING).
+ `orcache_blk_read_time` – If `track_io_timing` is enabled, it tracks the total time spent reading data file blocks from optimized reads cache, in milliseconds, otherwise the value is zero. For more information, see [track\$1io\$1timing](https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-IO-TIMING).
**Note**
The value of `blks_read` is the sum of `storage_blks_read`, `orcache_blks_hit`, and `local_blks_read`.
The value of `blk_read_time` is the sum of `storage_blk_read_time`, `orcache_blk_read_time`, and `local_blk_read_time`.
## Usage notes
This function is available in the following Aurora PostgreSQL versions:
+ 15.4 and higher 15 versions
+ 14.9 and higher 14 versions
## Examples
The following example shows how it carries all the `pg_stat_database` columns and appends 6 new columns in the end:
```
=> select * from aurora_stat_database() where datid=14717;
-[ RECORD 1 ]------------+------------------------------
datid | 14717
datname | postgres
numbackends | 1
xact_commit | 223
xact_rollback | 4
blks_read | 1059
blks_hit | 11456
tup_returned | 27746
tup_fetched | 5220
tup_inserted | 165
tup_updated | 42
tup_deleted | 91
conflicts | 0
temp_files | 0
temp_bytes | 0
deadlocks | 0
checksum_failures |
checksum_last_failure |
blk_read_time | 3358.689
blk_write_time | 0
session_time | 1076007.997
active_time | 3684.371
idle_in_transaction_time | 0
sessions | 10
sessions_abandoned | 0
sessions_fatal | 0
sessions_killed | 0
stats_reset | 2023-01-12 20:15:17.370601+00
orcache_blks_hit | 425
orcache_blk_read_time | 89.934
storage_blks_read | 623
storage_blk_read_time | 3254.914
local_blks_read | 0
local_blk_read_time | 0
```
# aurora\$1stat\$1dml\$1activity
Reports cumulative activity for each type of data manipulation language (DML) operation on a database in an Aurora PostgreSQL cluster.
## Syntax
```
aurora_stat_dml_activity(database_oid)
```
## Arguments
*database\$1oid*
The object ID (OID) of the database in the Aurora PostgreSQL cluster.
## Return type
SETOF record
## Usage notes
The `aurora_stat_dml_activity` function is only available with Aurora PostgreSQL release 3.1 compatible with PostgreSQL engine 11.6 and later.
Use this function on Aurora PostgreSQL clusters with a large number of databases to identify which databases have more or slower DML activity, or both.
The `aurora_stat_dml_activity` function returns the number of times the operations ran and the cumulative latency in microseconds for SELECT, INSERT, UPDATE, and DELETE operations. The report includes only successful DML operations.
You can reset this statistic by using the PostgreSQL statistics access function `pg_stat_reset`. You can check the last time this statistic was reset by using the `pg_stat_get_db_stat_reset_time` function. For more information about PostgreSQL statistics access functions, see [The Statistics Collector](https://www.postgresql.org/docs/9.1/monitoring-stats.html) in the PostgreSQL documentation.
## Examples
The following example shows how to report DML activity statistics for the connected database.
```
––Define the oid variable from connected database by using \gset
=> SELECT oid,
datname
FROM pg_database
WHERE datname=(select current_database()) \gset
=> SELECT *
FROM aurora_stat_dml_activity(:oid);
select_count | select_latency_microsecs | insert_count | insert_latency_microsecs | update_count | update_latency_microsecs | delete_count | delete_latency_microsecs
--------------+--------------------------+--------------+--------------------------+--------------+--------------------------+--------------+--------------------------
178957 | 66684115 | 171065 | 28876649 | 519538 | 1454579206167 | 1 | 53027
–– Showing the same results with expanded display on
=> SELECT *
FROM aurora_stat_dml_activity(:oid);
-[ RECORD 1 ]------------+--------------
select_count | 178957
select_latency_microsecs | 66684115
insert_count | 171065
insert_latency_microsecs | 28876649
update_count | 519538
update_latency_microsecs | 1454579206167
delete_count | 1
delete_latency_microsecs | 53027
```
The following example shows DML activity statistics for all databases in the Aurora PostgreSQL cluster. This cluster has two databases, `postgres` and `mydb`. The comma-separated list corresponds with the `select_count`, `select_latency_microsecs`, `insert_count`, `insert_latency_microsecs`, `update_count`, `update_latency_microsecs`, `delete_count`, and `delete_latency_microsecs` fields.
Aurora PostgreSQL creates and uses a system database named `rdsadmin` to support administrative operations such as backups, restores, health checks, replication, and so on. These DML operations have no impact on your Aurora PostgreSQL cluster.
```
=> SELECT oid,
datname,
aurora_stat_dml_activity(oid)
FROM pg_database;
oid | datname | aurora_stat_dml_activity
-------+----------------+-----------------------------------------------------------------
14006 | template0 | (,,,,,,,)
16384 | rdsadmin | (2346623,1211703821,4297518,817184554,0,0,0,0)
1 | template1 | (,,,,,,,)
14007 | postgres | (178961,66716329,171065,28876649,519538,1454579206167,1,53027)
16401 | mydb | (200246,64302436,200036,107101855,600000,83659417514,0,0)
```
The following example shows DML activity statistics for all databases, organized in columns for better readability.
```
SELECT db.datname,
BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 1), '()') AS select_count,
BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 2), '()') AS select_latency_microsecs,
BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 3), '()') AS insert_count,
BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 4), '()') AS insert_latency_microsecs,
BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 5), '()') AS update_count,
BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 6), '()') AS update_latency_microsecs,
BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 7), '()') AS delete_count,
BTRIM(SPLIT_PART(db.asdmla::TEXT, ',', 8), '()') AS delete_latency_microsecs
FROM (SELECT datname,
aurora_stat_dml_activity(oid) AS asdmla
FROM pg_database
) AS db;
datname | select_count | select_latency_microsecs | insert_count | insert_latency_microsecs | update_count | update_latency_microsecs | delete_count | delete_latency_microsecs
----------------+--------------+--------------------------+--------------+--------------------------+--------------+--------------------------+--------------+--------------------------
template0 | | | | | | | |
rdsadmin | 4206523 | 2478812333 | 7009414 | 1338482258 | 0 | 0 | 0 | 0
template1 | | | | | | | |
fault_test | 66 | 452099 | 0 | 0 | 0 | 0 | 0 | 0
db_access_test | 1 | 5982 | 0 | 0 | 0 | 0 | 0 | 0
postgres | 42035 | 95179203 | 5752 | 2678832898 | 21157 | 441883182488 | 2 | 1520
mydb | 71 | 453514 | 0 | 0 | 1 | 190 | 1 | 152
```
The following example shows the average cumulative latency (cumulative latency divided by count) for each DML operation for the database with the OID `16401`.
```
=> SELECT select_count,
select_latency_microsecs,
select_latency_microsecs/NULLIF(select_count,0) select_latency_per_exec,
insert_count,
insert_latency_microsecs,
insert_latency_microsecs/NULLIF(insert_count,0) insert_latency_per_exec,
update_count,
update_latency_microsecs,
update_latency_microsecs/NULLIF(update_count,0) update_latency_per_exec,
delete_count,
delete_latency_microsecs,
delete_latency_microsecs/NULLIF(delete_count,0) delete_latency_per_exec
FROM aurora_stat_dml_activity(16401);
-[ RECORD 1 ]------------+-------------
select_count | 451312
select_latency_microsecs | 80205857
select_latency_per_exec | 177
insert_count | 451001
insert_latency_microsecs | 123667646
insert_latency_per_exec | 274
update_count | 1353067
update_latency_microsecs | 200900695615
update_latency_per_exec | 148478
delete_count | 12
delete_latency_microsecs | 448
delete_latency_per_exec | 37
```
# aurora\$1stat\$1get\$1db\$1commit\$1latency
Gets the cumulative commit latency in microseconds for Aurora PostgreSQL databases. *Commit latency* is measured as the time between when a client submits a commit request and when it receives the commit acknowledgement.
## Syntax
```
aurora_stat_get_db_commit_latency(database_oid)
```
## Arguments
*database\$1oid*
The object ID (OID) of the Aurora PostgreSQL database.
## Return type
SETOF record
## Usage notes
Amazon CloudWatch uses this function to calculate the average commit latency. For more information about Amazon CloudWatch metrics and how to troubleshoot high commit latency, see [Viewing metrics in the Amazon RDS console](USER_Monitoring.md) and [Making better decisions about Amazon RDS with Amazon CloudWatch metrics](https://aws.amazon.com/blogs/database/making-better-decisions-about-amazon-rds-with-amazon-cloudwatch-metrics/).
You can reset this statistic by using the PostgreSQL statistics access function `pg_stat_reset`. You can check the last time this statistic was reset by using the `pg_stat_get_db_stat_reset_time` function. For more information about PostgreSQL statistics access functions, see [The Statistics Collector](https://www.postgresql.org/docs/9.1/monitoring-stats.html) in the PostgreSQL documentation.
## Examples
The following example gets the cumulative commit latency for each database in the `pg_database` cluster.
```
=> SELECT oid,
datname,
aurora_stat_get_db_commit_latency(oid)
FROM pg_database;
oid | datname | aurora_stat_get_db_commit_latency
-------+----------------+-----------------------------------
14006 | template0 | 0
16384 | rdsadmin | 654387789
1 | template1 | 0
16401 | mydb | 229556
69768 | postgres | 22011
```
The following example gets the cumulative commit latency for the currently connected database. Before calling the `aurora_stat_get_db_commit_latency` function, the example first uses `\gset` to define a variable for the `oid` argument and sets its value from the connected database.
```
––Get the oid value from the connected database before calling aurora_stat_get_db_commit_latency
=> SELECT oid
FROM pg_database
WHERE datname=(SELECT current_database()) \gset
=> SELECT *
FROM aurora_stat_get_db_commit_latency(:oid);
aurora_stat_get_db_commit_latency
-----------------------------------
1424279160
```
The following example gets the cumulative commit latency for the `mydb` database in the `pg_database` cluster. Then, it resets this statistic by using the `pg_stat_reset` function and shows the results. Finally, it uses the `pg_stat_get_db_stat_reset_time` function to check the last time this statistic was reset.
```
=> SELECT oid,
datname,
aurora_stat_get_db_commit_latency(oid)
FROM pg_database
WHERE datname = 'mydb';
oid | datname | aurora_stat_get_db_commit_latency
-------+-----------+-----------------------------------
16427 | mydb | 3320370
=> SELECT pg_stat_reset();
pg_stat_reset
---------------
=> SELECT oid,
datname,
aurora_stat_get_db_commit_latency(oid)
FROM pg_database
WHERE datname = 'mydb';
oid | datname | aurora_stat_get_db_commit_latency
-------+-----------+-----------------------------------
16427 | mydb | 6
=> SELECT *
FROM pg_stat_get_db_stat_reset_time(16427);
pg_stat_get_db_stat_reset_time
--------------------------------
2021-04-29 21:36:15.707399+00
```
# aurora\$1stat\$1logical\$1wal\$1cache
Shows logical write-ahead log (WAL) cache usage per slot.
## Syntax
```
SELECT * FROM aurora_stat_logical_wal_cache()
```
## Arguments
None
## Return type
SETOF record with the following columns:
+ `name` – The name of the replication slot.
+ `active_pid` – ID of the walsender process.
+ `cache_hit` – The total number of wal cache hits since last reset.
+ `cache_miss` – The total number of wal cache misses since last reset.
+ `blks_read` – The total number of wal cache read requests.
+ `hit_rate` – The WAL cache hit rate (cache\$1hit / blks\$1read).
+ `last_reset_timestamp` – Last time that the counter was reset.
## Usage notes
This function is available for the following Aurora PostgreSQL versions.
+ 15.2 and all higher versions
+ 14.7 and higher versions
+ 13.8 and higher versions
+ 12.12 and higher versions
+ 11.17 and higher versions
## Examples
The following example shows two replication slots with only one active. `aurora_stat_logical_wal_cache` function.
```
=> SELECT *
FROM aurora_stat_logical_wal_cache();
name | active_pid | cache_hit | cache_miss | blks_read | hit_rate | last_reset_timestamp
------------+------------+-----------+------------+-----------+----------+-------------------------------
test_slot1 | 79183 | 24 | 0 | 24 | 100.00% | 2022-08-05 17:39:56.830635+00
test_slot2 | | 1 | 0 | 1 | 100.00% | 2022-08-05 17:34:04.036795+00
(2 rows)
```
# aurora\$1stat\$1memctx\$1usage
Reports the memory context usage for each PostgreSQL process.
## Syntax
```
aurora_stat_memctx_usage()
```
## Arguments
None
## Return type
SETOF record with the following columns:
+ `pid` – The ID of the process.
+ `name` – The name of the memory context.
+ `allocated` – The number of bytes obtained from the underlying memory subsystem by the memory context.
+ `used` – The number of bytes committed to clients of the memory context.
+ `instances` – The count of currently existing contexts of this type.
## Usage notes
This function displays the memory context usage for each PostgreSQL process. Some processes are labeled `anonymous`. The processes aren't exposed because they contain restricted keywords.
This function is available starting with the following Aurora PostgreSQL versions:
+ 15.3 and higher 15 versions
+ 14.8 and higher 14 versions
+ 13.11 and higher 13 versions
+ 12.15 and higher 12 versions
+ 11.20 and higher 11 versions
## Examples
The following example shows the results of calling the `aurora_stat_memctx_usage` function.
```
=> SELECT *
FROM aurora_stat_memctx_usage();
pid| name | allocated | used | instances
-------+---------------------------------+-----------+---------+-----------
123864 | Miscellaneous | 19520 | 15064 | 3
123864 | Aurora File Context | 8192 | 616 | 1
123864 | Aurora WAL Context | 8192 | 296 | 1
123864 | CacheMemoryContext | 524288 | 422600 | 1
123864 | Catalog tuple context | 16384 | 13736 | 1
123864 | ExecutorState | 32832 | 28304 | 1
123864 | ExprContext | 8192 | 1720 | 1
123864 | GWAL record construction | 1024 | 832 | 1
123864 | MdSmgr | 8192 | 296 | 1
123864 | MessageContext | 532480 | 353832 | 1
123864 | PortalHeapMemory | 1024 | 488 | 1
123864 | PortalMemory | 8192 | 576 | 1
123864 | printtup | 8192 | 296 | 1
123864 | RelCache hash table entries | 8192 | 8152 | 1
123864 | RowDescriptionContext | 8192 | 1344 | 1
123864 | smgr relation context | 8192 | 296 | 1
123864 | Table function arguments | 8192 | 352 | 1
123864 | TopTransactionContext | 8192 | 632 | 1
123864 | TransactionAbortContext | 32768 | 296 | 1
123864 | WAL record construction | 50216 | 43904 | 1
123864 | hash table | 65536 | 52744 | 6
123864 | Relation metadata | 191488 | 124240 | 87
104992 | Miscellaneous | 9280 | 7728 | 3
104992 | Aurora File Context | 8192 | 376 | 1
104992 | Aurora WAL Context | 8192 | 296 | 1
104992 ||Autovacuum Launcher | 8192 | 296 | 1
104992 | Autovacuum database list | 16384 | 744 | 2
104992 | CacheMemoryContext | 262144 | 140288 | 1
104992 | Catalog tuple context | 8192 | 296 | 1
104992 | GWAL record construction | 1024 | 832 | 1
104992 | MdSmgr | 8192 | 296 | 1
104992 | PortalMemory | 8192 | 296 | 1
104992 | RelCache hash table entries | 8192 | 296 | 1
104992 | smgr relation context | 8192 | 296 | 1
104992 | Autovacuum start worker (tmp) | 8192 | 296 | 1
104992 | TopTransactionContext | 16384 | 592 | 2
104992 | TransactionAbortContext | 32768 | 296 | 1
104992 | WAL record construction | 50216 | 43904 | 1
104992 | hash table | 49152 | 34024 | 4
(39 rows)
```
Some restricted keywords will be hidden and the output will look as follows:
```
postgres=>SELECT *
FROM aurora_stat_memctx_usage();
pid| name | allocated | used | instances
-------+---------------------------------+-----------+---------+-----------
5482 | anonymous | 8192 | 456 | 1
5482 | anonymous | 8192 | 296 | 1
```
# aurora\$1stat\$1optimized\$1reads\$1cache
This function shows tiered cache stats.
## Syntax
```
aurora_stat_optimized_reads_cache()
```
## Arguments
None
## Return type
SETOF record with the following columns:
+ `total_size` – Total optimized reads cache size.
+ `used_size` – Used page size in optimized reads cache.
## Usage notes
This function is available in the following Aurora PostgreSQL versions:
+ 15.4 and higher 15 versions
+ 14.9 and higher 14 versions
## Examples
The following example shows the output on a r6gd.8xlarge instance :
```
=> select pg_size_pretty(total_size) as total_size, pg_size_pretty(used_size)
as used_size from aurora_stat_optimized_reads_cache();
total_size | used_size
-----------+-----------
1054 GB | 975 GB
```
# aurora\$1stat\$1plans
Returns a row for every tracked execution plan.
## Syntax
```
aurora_stat_plans(
showtext
)
```
## Arguments
+ showtext – Show the query and plan text. Valid values are NULL, true or false. True will show the query and plan text.
## Return type
Returns a row for each tracked plan that contains all the columns from `aurora_stat_statements` and the following additional columns.
+ planid – plan identifier
+ explain\$1plan – explain plan text
+ plan\$1type:
+ `no plan` - no plan was captured
+ `estimate` - plan captured with estimated costs
+ `actual` - plan captured with EXPLAIN ANALYZE
+ plan\$1captured\$1time – last time a plan was captured
## Usage notes
`aurora_compute_plan_id` must be enabled and `pg_stat_statements` must be in `shared_preload_libraries` for the plans to be tracked.
The number of plans available is controlled by the value set in the `pg_stat_statements.max` parameter. When `aurora_compute_plan_id` is enabled, you can track the plans up to this specified value in `aurora_stat_plans`.
This function is available from Aurora PostgreSQL versions 14.10, 15.5, and for all other later versions.
## Examples
In the example below, the two plans that are for the query identifier -5471422286312252535 are captured and the statements statistics are tracked by the planid.
```
db1=# select calls, total_exec_time, planid, plan_captured_time, explain_plan
db1-# from aurora_stat_plans(true)
db1-# where queryid = '-5471422286312252535'
calls | total_exec_time | planid | plan_captured_time | explain_plan
---------+--------------------+-------------+-------------------------------+------------------------------------------------------------------
1532632 | 3209846.097107853 | 1602979607 | 2023-10-31 03:27:16.925497+00 | Update on pgbench_branches +
| | | | -> Bitmap Heap Scan on pgbench_branches +
| | | | Recheck Cond: (bid = 76) +
| | | | -> Bitmap Index Scan on pgbench_branches_pkey +
| | | | Index Cond: (bid = 76)
61365 | 124078.18012200127 | -2054628807 | 2023-10-31 03:20:09.85429+00 | Update on pgbench_branches +
| | | | -> Index Scan using pgbench_branches_pkey on pgbench_branches+
| | | | Index Cond: (bid = 17)
```
# aurora\$1stat\$1reset\$1wal\$1cache
Resets the counter for logical wal cache.
## Syntax
To reset a specific slot
```
SELECT * FROM aurora_stat_reset_wal_cache('slot_name')
```
To reset all slots
```
SELECT * FROM aurora_stat_reset_wal_cache(NULL)
```
## Arguments
`NULL` or `slot_name`
## Return type
Status message, text string
+ Reset the logical wal cache counter – Success message. This text is returned when the function succeeds.
+ Replication slot not found. Please try again. – Failure message. This text is returned when the function doesn't succeed.
## Usage notes
This function is available for the following versions.
+ Aurora PostgreSQL 14.5 and higher
+ Aurora PostgreSQL version 13.8 and higher
+ Aurora PostgreSQL version 12.12 and higher
+ Aurora PostgreSQLversion 11.17 and higher
## Examples
The following example uses the `aurora_stat_reset_wal_cache` function to reset a slot named `test_results`, and then tries to reset a slot that doesn't exist.
```
=> SELECT *
FROM aurora_stat_reset_wal_cache('test_slot');
aurora_stat_reset_wal_cache
--------------------------------------
Reset the logical wal cache counter.
(1 row)
=> SELECT *
FROM aurora_stat_reset_wal_cache('slot-not-exist');
aurora_stat_reset_wal_cache
-----------------------------------------------
Replication slot not found. Please try again.
(1 row)
```
# aurora\$1stat\$1resource\$1usage
Reports the real-time resource utilization which consists of backend resource metrics and cpu usage for all Aurora PostgreSQL backend processes.
## Syntax
```
aurora_stat_resource_usage()
```
## Arguments
None
## Return type
SETOF record with columns:
+ pid - Process identifier
+ allocated\$1memory - Total memory allocated by process in bytes
+ used\$1memory - Actually used memory by process in bytes
+ cpu\$1usage\$1percent - CPU usage percentage of the process
## Usage notes
This function displays the backend resource usage for each Aurora PostgreSQL backend process.
This function is available starting with the following Aurora PostgreSQL versions:
+ Aurora PostgreSQL 17.5 and higher 17 versions
+ Aurora PostgreSQL 16.9 and higher 16 versions
+ Aurora PostgreSQL 15.13 and higher 15 versions
+ Aurora PostgreSQL 14.18 and higher 14 versions
+ Aurora PostgreSQL 13.21 and higher 13 versions
## Examples
The following example shows the output of the `aurora_stat_resource_usage` function.
```
=> select * from aurora_stat_resource_usage();
pid | allocated_memory | used_memory | cpu_usage_percent
------+------------------+-------------+-----------------------
666 | 1074032 | 333544 | 0.00729274882897963
667 | 787312 | 287360 | 0.0029263928146372746
668 | 3076776 | 1563488 | 0.006013116835953961
684 | 803744 | 307480 | 0.002226855426881142
2401 | 1232992 | 943144 | 0
647 | 8000 | 944 | 0.48853387812429855
659 | 319344 | 243000 | 0.0004135602076683591
663 | 262000 | 185736 | 0.008181301476644002
664 | 9024 | 1216 | 0.10992313082653653
(9 rows)
```
# aurora\$1stat\$1statements
Displays all `pg_stat_statements` columns and adds more columns in the end.
## Syntax
```
aurora_stat_statements(showtext boolean)
```
## Arguments
*showtext boolean*
## Return type
SETOF record with all `pg_stat_statements` columns and the following additional columns. For more information on `pg_stat_statements` columns, see [https://www.postgresql.org/docs/current/pgstatstatements.html](https://www.postgresql.org/docs/current/pgstatstatements.html).
You can reset stats for this function using `pg_stat_statements_reset()`.
+ `storage_blks_read` – Total number of shared blocks read from aurora storage by this statement.
+ `orcache_blks_hit` – Total number of optimized reads cache hits by this statement.
+ `storage_blk_read_time` – If `track_io_timing` is enabled, it tracks the total time the statement spent reading shared blocks from aurora storage, in milliseconds, otherwise the value is zero. For more information, see [track\$1io\$1timing](https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-IO-TIMING).
+ `local_blk_read_time` – If `track_io_timing` is enabled, it tracks the total time the statement spent reading local blocks, in milliseconds, otherwise the value is zero. For more information, see [track\$1io\$1timing](https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-IO-TIMING).
+ `orcache_blk_read_time` – If `track_io_timing` is enabled, it tracks the total time the statement spent reading shared blocks from optimized reads cache, in milliseconds, otherwise the value is zero. For more information, see [track\$1io\$1timing](https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-IO-TIMING).
+ `total_plan_peakmem` – Total sum of peak memory values during planning phase for all calls to this statement. To see the average peak memory during planning for the statement, divide this value by the number of calls.
+ `min_plan_peakmem` – Smallest peak memory value seen during planning across all calls to this statement.
+ `max_plan_peakmem` – Largest peak memory value during planning seen across all calls to this statement.
+ `total_exec_peakmem` – Total sum of peak memory values during execution phase for all calls to this statement. To see the average peak memory during execution for the statement, divide this value by the number of calls.
+ `min_exec_peakmem` –Smallest peak memory value, in bytes, seen during execution across all calls to this statement.
+ `max_exec_peakmem` – Largest peak memory value, in bytes, seen during execution across all calls to this statement.
**Note**
`total_plan_peakmen`, `min_plan_peakmem`, and `max_plan_peakmem` are only monitored when the setting `pg_stat_statements.track_planning` is turned on.
## Usage notes
To use the aurora\$1stat\$1statements() function, you must include `pg_stat_statements` extension in the `shared_preload_libraries` parameter.
This function is available in the following Aurora PostgreSQL versions:
+ 15.4 and higher 15 versions
+ 14.9 and higher 14 versions
The columns showing peak memory are available from the following versions:
+ 16.3 and higher versions
+ 15.7 and higher versions
+ 14.12 and higher versions
## Examples
The following example shows how it carries all the pg\$1stat\$1statements columns and append 11 new columns in the end:
```
=> select * from aurora_stat_statements(true) where query like 'with window_max%';
-[ RECORD 1 ]----------+------------------------------------------------------------------------------------------------
userid | 16409
dbid | 5
toplevel | t
queryid | -8347523682669847482
query | with window_max as (select custid, max(scratch) over (order by scratch rows between $1 preceding
and $2 following) wmax from ts) select sum(wmax), max(custid) from window_max
plans | 0
total_plan_time | 0
min_plan_time | 0
max_plan_time | 0
mean_plan_time | 0
stddev_plan_time | 0
calls | 4
total_exec_time | 254.105121
min_exec_time | 57.503164000000005
max_exec_time | 68.687418
mean_exec_time | 63.52628025
stddev_exec_time | 5.150765359979643
rows | 4
shared_blks_hit | 200192
shared_blks_read | 0
shared_blks_dirtied | 0
shared_blks_written | 0
local_blks_hit | 0
local_blks_read | 0
local_blks_dirtied | 0
local_blks_written | 0
temp_blks_read | 0
temp_blks_written | 0
blk_read_time | 0
blk_write_time | 0
temp_blk_read_time | 0
temp_blk_write_time | 0
wal_records | 0
wal_fpi | 0
wal_bytes | 0
jit_functions | 0
jit_generation_time | 0
jit_inlining_count | 0
jit_inlining_time | 0
jit_optimization_count | 0
jit_optimization_time | 0
jit_emission_count | 0
jit_emission_time | 0
storage_blks_read | 0
orcache_blks_hit | 0
storage_blk_read_time | 0
local_blk_read_time | 0
orcache_blk_read_time | 0
total_plan_peakmem | 0
min_plan_peakmem | 0
max_plan_peakmem | 0
total_exec_peakmem | 6356224
min_exec_peakmem | 1589056
max_exec_peakmem | 1589056
```
# aurora\$1stat\$1system\$1waits
Reports wait event information for the Aurora PostgreSQL DB instance.
## Syntax
```
aurora_stat_system_waits()
```
## Arguments
None
## Return type
SETOF record
## Usage notes
This function returns the cumulative number of waits and cumulative wait time for each wait event generated by the DB instance that you're currently connected to.
The returned recordset includes the following fields:
+ `type_id` – The ID of the type of wait event.
+ `event_id` – The ID of the wait event.
+ `waits` – The number of times the wait event occurred.
+ `wait_time` – The total amount of time in microseconds spent waiting for this event.
Statistics returned by this function are reset when a DB instance restarts.
## Examples
The following example shows results from calling the `aurora_stat_system_waits` function.
```
=> SELECT *
FROM aurora_stat_system_waits();
type_id | event_id | waits | wait_time
---------+-----------+-----------+--------------
1 | 16777219 | 11 | 12864
1 | 16777220 | 501 | 174473
1 | 16777270 | 53171 | 23641847
1 | 16777271 | 23 | 319668
1 | 16777274 | 60 | 12759
.
.
.
10 | 167772231 | 204596 | 790945212
10 | 167772232 | 2 | 47729
10 | 167772234 | 1 | 888
10 | 167772235 | 2 | 64
```
The following example shows how you can use this function together with `aurora_stat_wait_event` and `aurora_stat_wait_type` to produce more readable results.
```
=> SELECT type_name,
event_name,
waits,
wait_time
FROM aurora_stat_system_waits()
NATURAL JOIN aurora_stat_wait_event()
NATURAL JOIN aurora_stat_wait_type();
type_name | event_name | waits | wait_time
-----------+------------------------+----------+--------------
LWLock | XidGenLock | 11 | 12864
LWLock | ProcArrayLock | 501 | 174473
LWLock | buffer_content | 53171 | 23641847
LWLock | rdsutils | 2 | 12764
Lock | tuple | 75686 | 2033956052
Lock | transactionid | 1765147 | 47267583409
Activity | AutoVacuumMain | 136868 | 56305604538
Activity | BgWriterHibernate | 7486 | 55266949471
Activity | BgWriterMain | 7487 | 1508909964
.
.
.
IO | SLRURead | 3 | 11756
IO | WALWrite | 52544463 | 388850428
IO | XactSync | 187073 | 597041642
IO | ClogRead | 2 | 47729
IO | OutboundCtrlRead | 1 | 888
IO | OutboundCtrlWrite | 2 | 64
```
# aurora\$1stat\$1wait\$1event
Lists all supported wait events for Aurora PostgreSQL. For information about Aurora PostgreSQL wait events, see [Amazon Aurora PostgreSQL wait events](AuroraPostgreSQL.Reference.Waitevents.md).
## Syntax
```
aurora_stat_wait_event()
```
## Arguments
None
## Return type
SETOF record with the following columns:
+ type\$1id – The ID of the type of wait event.
+ event\$1id – The ID of the wait event.
+ type\$1name – Wait type name
+ event\$1name – Wait event name
## Usage notes
To see event names with event types instead of IDs, use this function together with other functions such as `aurora_stat_wait_type` and `aurora_stat_system_waits`. Wait event names returned by this function are the same as those returned by the `aurora_wait_report` function.
## Examples
The following example shows results from calling the `aurora_stat_wait_event` function.
```
=> SELECT *
FROM aurora_stat_wait_event();
type_id | event_id | event_name
---------+-----------+-------------------------------------------
1 | 16777216 |
1 | 16777217 | ShmemIndexLock
1 | 16777218 | OidGenLock
1 | 16777219 | XidGenLock
.
.
.
9 | 150994945 | PgSleep
9 | 150994946 | RecoveryApplyDelay
10 | 167772160 | BufFileRead
10 | 167772161 | BufFileWrite
10 | 167772162 | ControlFileRead
.
.
.
10 | 167772226 | WALInitWrite
10 | 167772227 | WALRead
10 | 167772228 | WALSync
10 | 167772229 | WALSyncMethodAssign
10 | 167772230 | WALWrite
10 | 167772231 | XactSync
.
.
.
11 | 184549377 | LsnAllocate
```
The following example joins `aurora_stat_wait_type` and `aurora_stat_wait_event` to return type names and event names for improved readability.
```
=> SELECT *
FROM aurora_stat_wait_type() t
JOIN aurora_stat_wait_event() e
ON t.type_id = e.type_id;
type_id | type_name | type_id | event_id | event_name
---------+-----------+---------+-----------+-------------------------------------------
1 | LWLock | 1 | 16777216 |
1 | LWLock | 1 | 16777217 | ShmemIndexLock
1 | LWLock | 1 | 16777218 | OidGenLock
1 | LWLock | 1 | 16777219 | XidGenLock
1 | LWLock | 1 | 16777220 | ProcArrayLock
.
.
.
3 | Lock | 3 | 50331648 | relation
3 | Lock | 3 | 50331649 | extend
3 | Lock | 3 | 50331650 | page
3 | Lock | 3 | 50331651 | tuple
.
.
.
10 | IO | 10 | 167772214 | TimelineHistorySync
10 | IO | 10 | 167772215 | TimelineHistoryWrite
10 | IO | 10 | 167772216 | TwophaseFileRead
10 | IO | 10 | 167772217 | TwophaseFileSync
.
.
.
11 | LSN | 11 | 184549376 | LsnDurable
```
# aurora\$1stat\$1wait\$1type
Lists all supported wait types for Aurora PostgreSQL.
## Syntax
```
aurora_stat_wait_type()
```
## Arguments
None
## Return type
SETOF record with the following columns:
+ type\$1id – The ID of the type of wait event.
+ type\$1name – Wait type name.
## Usage notes
To see wait event names with wait event types instead of IDs, use this function together with other functions such as `aurora_stat_wait_event` and `aurora_stat_system_waits`. Wait type names returned by this function are the same as those returned by the `aurora_wait_report` function.
## Examples
The following example shows results from calling the `aurora_stat_wait_type` function.
```
=> SELECT *
FROM aurora_stat_wait_type();
type_id | type_name
---------+-----------
1 | LWLock
3 | Lock
4 | BufferPin
5 | Activity
6 | Client
7 | Extension
8 | IPC
9 | Timeout
10 | IO
11 | LSN
```
# aurora\$1version
Returns the string value of the Amazon Aurora PostgreSQL-Compatible Edition version number.
## Syntax
```
aurora_version()
```
## Arguments
None
## Return type
CHAR or VARCHAR string
## Usage notes
This function displays the version of the Amazon Aurora PostgreSQL-Compatible Edition database engine. The version number is returned as a string formatted as *major*.*minor*.*patch*. For more information about Aurora PostgreSQL version numbers, see [Aurora version number](AuroraPostgreSQL.Updates.md#AuroraPostgreSQL.Updates.Versions.AuroraNumber).
You can choose when to apply minor version upgrades by setting the maintenance window for your Aurora PostgreSQL DB cluster. To learn how, see [Maintaining an Amazon Aurora DB cluster](USER_UpgradeDBInstance.Maintenance.md).
Starting with the release of Aurora PostgreSQL versions 13.3, 12.8, 11.13, 10.18, and for all other later versions, Aurora version numbers follow PostgreSQL version numbers. For more information about all Aurora PostgreSQL releases, see [Amazon Aurora PostgreSQL updates](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Updates.html) in the *Release Notes for Aurora PostgreSQL*.
## Examples
The following example shows the results of calling the `aurora_version` function on an Aurora PostgreSQL DB cluster running [PostgreSQL 12.7, Aurora PostgreSQL release 4.2](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Updates.html#AuroraPostgreSQL.Updates.20180305.42) and then running the same function on a cluster running [Aurora PostgreSQL version 13.3](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Updates.html#AuroraPostgreSQL.Updates.20180305.133X).
```
=> SELECT * FROM aurora_version();
aurora_version
----------------
4.2.2
SELECT * FROM aurora_version();
aurora_version
----------------
13.3.0
```
This example shows how to use the function with various options to get more detail about the Aurora PostgreSQL version. This example has an Aurora version number that's distinct from the PostgreSQL version number.
```
=> SHOW SERVER_VERSION;
server_version
----------------
12.7
(1 row)
=> SELECT * FROM aurora_version();
aurora_version
----------------
4.2.2
(1 row)
=> SELECT current_setting('server_version') AS "PostgreSQL Compatiblility";
PostgreSQL Compatiblility
---------------------------
12.7
(1 row)
=> SELECT version() AS "PostgreSQL Compatiblility Full String";
PostgreSQL Compatiblility Full String
-------------------------------------------------------------------------------------------------------------
PostgreSQL 12.7 on aarch64-unknown-linux-gnu, compiled by aarch64-unknown-linux-gnu-gcc (GCC) 7.4.0, 64-bit
(1 row)
=> SELECT 'Aurora: '
|| aurora_version()
|| ' Compatible with PostgreSQL: '
|| current_setting('server_version') AS "Instance Version";
Instance Version
------------------------------------------------
Aurora: 4.2.2 Compatible with PostgreSQL: 12.7
(1 row)
```
This next example uses the function with the same options in the previous example. This example doesn't have an Aurora version number that's distinct from the PostgreSQL version number.
```
=> SHOW SERVER_VERSION;
server_version
----------------
13.3
=> SELECT * FROM aurora_version();
aurora_version
----------------
13.3.0
=> SELECT current_setting('server_version') AS "PostgreSQL Compatiblility";
PostgreSQL Compatiblility
---------------------------
13.3
=> SELECT version() AS "PostgreSQL Compatiblility Full String";
PostgreSQL Compatiblility Full String
-------------------------------------------------------------------------------------------------
PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (GCC) 7.4.0, 64-bit
=> SELECT 'Aurora: '
|| aurora_version()
|| ' Compatible with PostgreSQL: '
|| current_setting('server_version') AS "Instance Version";
Instance Version
-------------------------------------------------------
Aurora: 13.3.0 Compatible with PostgreSQL: 13.3
```
# aurora\$1volume\$1logical\$1start\$1lsn
Returns the log sequence number (LSN) used for identifying the beginning of a record in the logical write-ahead log (WAL) stream of the Aurora cluster volume.
## Syntax
```
aurora_volume_logical_start_lsn()
```
## Arguments
None
## Return type
`pg_lsn`
## Usage notes
This function identifies the beginning of the record in the logical WAL stream for a given Aurora cluster volume. You can use this function while performing major version upgrade using logical replication and Aurora fast cloning to determine the LSN at which a snapshot or database clone is taken. You can then use logical replication to continuously stream the newer data recorded after the LSN and synchronize the changes from publisher to subscriber.
For more information on using logical replication for a major version upgrade, see [Using logical replication to perform a major version upgrade for Aurora PostgreSQL](AuroraPostgreSQL.MajorVersionUpgrade.md).
This function is available on the following versions of Aurora PostgreSQL:
+ 15.2 and higher 15 versions
+ 14.3 and higher 14 versions
+ 13.6 and higher 13 versions
+ 12.10 and higher 12 versions
+ 11.15 and higher 11 versions
+ 10.20 and higher 10 versions
## Examples
You can obtain the log sequence number (LSN) using the following query:
```
postgres=> SELECT aurora_volume_logical_start_lsn();
aurora_volume_logical_start_lsn
---------------
0/402E2F0
(1 row)
```
# aurora\$1wait\$1report
This function shows wait event activity over a period of time.
## Syntax
```
aurora_wait_report([time])
```
## Arguments
*time (optional) *
The time in seconds. Default is 10 seconds.
## Return type
SETOF record with the following columns:
+ type\$1name – Wait type name
+ event\$1name – Wait event name
+ wait – Number of waits
+ wait\$1time – Wait time in milliseconds
+ ms\$1per\$1wait – Average milliseconds by the number of an wait
+ waits\$1per\$1xact – Average waits by the number of one transaction
+ ms\$1per\$1xact – Average milliseconds by the number of transactions
## Usage notes
This function is available as of Aurora PostgreSQL release 1.1 compatible with PostgreSQL 9.6.6 and higher versions.
To use this function, you need to first create the Aurora PostgreSQL `aurora_stat_utils` extension, as follows:
```
=> CREATE extension aurora_stat_utils;
CREATE EXTENSION
```
For more information about available Aurora PostgreSQL extension versions, see [Extension versions for Amazon Aurora PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html) in *Release Notes for Aurora PostgreSQL*.
This function calculates the instance-level wait events by comparing two snapshots of statistics data from aurora\$1stat\$1system\$1waits() function and pg\$1stat\$1database PostgreSQL Statistics Views.
For more information about `aurora_stat_system_waits()` and `pg_stat_database`, see [The Statistics Collector](https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-DATABASE-VIEW) in the *PostgreSQL documentation*.
When run, this function takes an initial snapshot, waits the number of seconds specified, and then takes a second snapshot. The function compares the two snapshots and returns the difference. This difference represents the instance's activity for that time interval.
On the writer instance, the function also displays the number of committed transactions and TPS (transactions per second). This function returns information at the instance level and includes all databases on the instance.
## Examples
This example shows how to create aurora\$1stat\$1utils extension to be able to use aurora\$1wait\$1report function.
```
=> CREATE extension aurora_stat_utils;
CREATE EXTENSION
```
This example shows how to check wait report for 10 seconds.
```
=> SELECT *
FROM aurora_wait_report();
NOTICE: committed 34 transactions in 10 seconds (tps 3)
type_name | event_name | waits | wait_time | ms_per_wait | waits_per_xact | ms_per_xact
-----------+-------------------+-------+-----------+-------------+----------------+-------------
Client | ClientRead | 26 | 30003.00 | 1153.961 | 0.76 | 882.441
Activity | WalWriterMain | 50 | 10051.32 | 201.026 | 1.47 | 295.627
Timeout | PgSleep | 1 | 10049.52 | 10049.516 | 0.03 | 295.574
Activity | BgWriterHibernate | 1 | 10048.15 | 10048.153 | 0.03 | 295.534
Activity | AutoVacuumMain | 18 | 9941.66 | 552.314 | 0.53 | 292.402
Activity | BgWriterMain | 1 | 201.09 | 201.085 | 0.03 | 5.914
IO | XactSync | 15 | 25.34 | 1.690 | 0.44 | 0.745
IO | RelationMapRead | 12 | 0.54 | 0.045 | 0.35 | 0.016
IO | WALWrite | 84 | 0.21 | 0.002 | 2.47 | 0.006
IO | DataFileExtend | 1 | 0.02 | 0.018 | 0.03 | 0.001
```
This example shows how to check wait report for 60 seconds.
```
=> SELECT *
FROM aurora_wait_report(60);
NOTICE: committed 1544 transactions in 60 seconds (tps 25)
type_name | event_name | waits | wait_time | ms_per_wait | waits_per_xact | ms_per_xact
-----------+------------------------+---------+-----------+-------------+----------------+-------------
Lock | transactionid | 6422 | 477000.53 | 74.276 | 4.16 | 308.938
Client | ClientRead | 8265 | 270752.99 | 32.759 | 5.35 | 175.358
Activity | CheckpointerMain | 1 | 60100.25 | 60100.246 | 0.00 | 38.925
Timeout | PgSleep | 1 | 60098.49 | 60098.493 | 0.00 | 38.924
Activity | WalWriterMain | 296 | 60010.99 | 202.740 | 0.19 | 38.867
Activity | AutoVacuumMain | 107 | 59827.84 | 559.139 | 0.07 | 38.749
Activity | BgWriterMain | 290 | 58821.83 | 202.834 | 0.19 | 38.097
IO | XactSync | 1295 | 55220.13 | 42.641 | 0.84 | 35.764
IO | WALWrite | 6602259 | 47810.94 | 0.007 | 4276.07 | 30.966
Lock | tuple | 473 | 29880.67 | 63.173 | 0.31 | 19.353
LWLock | buffer_mapping | 142 | 3540.13 | 24.930 | 0.09 | 2.293
Activity | BgWriterHibernate | 290 | 1124.15 | 3.876 | 0.19 | 0.728
IO | BufFileRead | 7615 | 618.45 | 0.081 | 4.93 | 0.401
LWLock | buffer_content | 73 | 345.93 | 4.739 | 0.05 | 0.224
LWLock | lock_manager | 62 | 191.44 | 3.088 | 0.04 | 0.124
IO | RelationMapRead | 72 | 5.16 | 0.072 | 0.05 | 0.003
LWLock | ProcArrayLock | 1 | 2.01 | 2.008 | 0.00 | 0.001
IO | ControlFileWriteUpdate | 2 | 0.03 | 0.013 | 0.00 | 0.000
IO | DataFileExtend | 1 | 0.02 | 0.018 | 0.00 | 0.000
IO | ControlFileSyncUpdate | 1 | 0.00 | 0.000 | 0.00 | 0.000
```