Product Documentation

Appearance

sys.log_replica_status ​

This system view records the history of replication activity on the source system.

See the sys.replica_status description for details about the columns in the sys.log_replica_status view.

Examples ​

Return records for replication cycles that have sent bytes from the source system to the target system:

premdb=# select * from sys.log_replica_status where sent_bytes >0 order by sent_bytes desc;
-[ RECORD 1 ]-------+-----------------------------------------------------------------
database_id         | 16391
replica_id          | 16415
snapshot_id         | premdb_replica1_19_12_12_17_34_37
elapsed_ms          | 223745
remaining_ms        | 0
start_time          | 2019-12-12 17:34:37.123-08
end_time            | 2019-12-12 17:38:20.868-08
state               | DONE
error_string        | [NULL]
backup_session_key  | DmabhirRiu7r_o0DEH4YguDjg0A0oBnESEKjvH40CXVP8W4Ug1ODvtFD-2YVbjnd
restore_session_key | AxtTYw1nAb0qqRFi93rSEA0PWU9K4O-d2RRGm2fNkZg34qyHwHZ5s23wevDI51iI
sent_bytes          | 5815083399
write_bytes         | 5815083399
retries             | 0
-[ RECORD 2 ]-------+-----------------------------------------------------------------
database_id         | 16391
replica_id          | 16415
snapshot_id         | premdb_replica1_19_12_12_17_29_59
elapsed_ms          | 47561
remaining_ms        | 0
start_time          | 2019-12-12 17:29:59.848-08
end_time            | 2019-12-12 17:30:47.409-08
state               | DONE
error_string        | [NULL]
backup_session_key  | Dn5KkhJ0_5Wt_7qahaFAFYVCCFDJ-3Y3dtBRsvqNsD1hzTNRdWjJo61VxDTzLbLD
restore_session_key | BAZOiGXIj8NtxayHGrUPtOZSBxsuKg0ykj7jtsTdCuQKUb-Yd7_tPH5gfm2a0m18
sent_bytes          | 968731450
write_bytes         | 968731450
retries             | 0
-[ RECORD 3 ]-------+-----------------------------------------------------------------
database_id         | 16391
replica_id          | 16415
snapshot_id         | premdb_replica1_19_12_12_17_29_13
elapsed_ms          | 46109
remaining_ms        | 0
start_time          | 2019-12-12 17:29:13.729-08
end_time            | 2019-12-12 17:29:59.838-08
state               | DONE
error_string        | [NULL]
backup_session_key  | AeL5WMy1W36EBiv0ea0BKhYxW0sV2Xlmrvzg0ucLUIKcg2JxlsU4zFnlBfIBWcbM
restore_session_key | CWW8BbTi2bi9KWq-GdupA9fYok_B1anAGy5fuA8OcsDlPD_GHN01FRftqet0_zFF
sent_bytes          | 413449071
write_bytes         | 413449071
retries             | 0
...

Return the five longest-running replication cycles for a replicated database named premdb_hot. This query joins three system views in order to return the source database name and the alias (target) database name.

premdb=# select d.database_id, d.name as srcdbname, r.replica_id, r.alias, elapsed_ms, sent_bytes 
from 
sys.log_replica_status lrs, 
sys.database d, 
sys.replica r
where lrs.database_id=d.database_id
and lrs.replica_id=r.replica_id
and r.alias='premdb_hot'
order by elapsed_ms desc 
limit 5;
 database_id | srcdbname | replica_id |   alias    | elapsed_ms | sent_bytes 
-------------+-----------+------------+------------+------------+------------
      16432 | premdb    |      16460 | premdb_hot |      48158 | 8433800688
      16432 | premdb    |      16460 | premdb_hot |      36261 | 6272871179
      16432 | premdb    |      16460 | premdb_hot |      13666 |    8210360
      16432 | premdb    |      16460 | premdb_hot |      12133 | 1956897451
      16432 | premdb    |      16460 | premdb_hot |       2138 |  120741116
(5 rows)

Parent topic:System Views