aurora_replica_status - Amazon Aurora
SyntaxArgumentsReturn typeUsage notesExamples
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

aurora_replica_status

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

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. 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 in the PostgreSQL documentation. To learn more about high availability and replication, see Amazon Aurora FAQs.

    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

To learn more about troubleshooting Aurora read replicas and restarts, see Why did my Amazon Aurora read replica fall behind and restart? in the Amazon Web Services Support Center.

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)