Product Documentation

Appearance

sys.replica_status ​

This system view records current replication activity, per cycle, on the source system. See also sys.log_replica_status.

Column NameData TypeDescription
database_idbigintUnique ID of the source database.
replica_idbigintUnique ID of the replica.
snapshot_idvarchar(256)Unique ID of the backup snapshot.
elapsed_msbigintElapsed time for the replication cycle (in milliseconds).
remaining_msbigintEstimated remaining time for the replication cycle (in milliseconds), based on an estimation of the size of the backup that is being restored. (This column always returns 0 in sys.log_replica_status.)
start_timetimestamptzWhen the replication cycle started.
end_timetimestamptzWhen the replication cycle ended.
statevarchar(256)Status of replication for this replica, such as RUNNING, PAUSED, NOT_STARTED.
error_stringvarchar(256)Error message, if there was a failure.
backup_session_keyvarchar(256)Unique session key for the backup used for replication.
restore_session_keyvarchar(256)Unique session key for the restore used for replication.
sent_bytesbigintNumber of bytes sent over the network.
write_bytesbigintNumber of bytes written to the replica database.

Examples ​

After starting replication on an empty database, when nothing has been replicated except the system catalog, you will see an entry like this:

premdb=# select * from sys.replica_status;
-[ RECORD 1 ]-------+-----------------------------------------------------------------
database_id         | 16391
replica_id          | 16415
snapshot_id         | premdb_replica1_19_12_12_16_58_14
elapsed_ms          | 37868
remaining_ms        | 0
start_time          | 2019-12-12 16:58:14-08
end_time            | [NULL]
state               | CATALOG_COMPLETED
error_string        | [NULL]
backup_session_key  | A1pETqXv8ndvwu8z-I2_b_HjCqXKbO-tAr5Dav7M0QpUx5k2H25yEd2jMmWDof5N
restore_session_key | CmeN0iX2HD-t--YfAm9huHsjkraIYlpw9dZeZPT9-xFFSqwzswEsd8ZPlNXMAb-6
sent_bytes          | 0
write_bytes         | 0

The snapshot ID identifies a backup that is used for replication on this database. The following records show replication in flight based on data captured in new snapshots:

premdb=# select * from sys.replica_status;
-[ RECORD 1 ]-------+-----------------------------------------------------------------
database_id         | 16391
replica_id          | 16415
snapshot_id         | premdb_replica1_19_12_12_17_14_58
elapsed_ms          | 8784
remaining_ms        | 0
start_time          | 2019-12-12 17:14:58-08
end_time            | [NULL]
state               | USER_DATA_RUNNING
error_string        | [NULL]
backup_session_key  | DDK-d7UVx8UMmxKPl9iPO65La7lvj-X207JY1QaWi1KIZoLbbYpnfFno3Gc-HVt0
restore_session_key | DC4O8zCY6sFB1SSQILTUvZOfGMZFiOgj_XxbA6__gi9SM4ixM7xGViFp26XoxOD1
sent_bytes          | 0
write_bytes         | 0
premdb=# select * from sys.replica_status;
-[ RECORD 1 ]-------+-----------------------------------------------------------------
database_id         | 16391
replica_id          | 16415
snapshot_id         | premdb_replica1_19_12_12_17_28_18
elapsed_ms          | 11162
remaining_ms        | 0
start_time          | 2019-12-12 17:28:18-08
end_time            | [NULL]
state               | USER_DATA_RUNNING
error_string        | [NULL]
backup_session_key  | C1XW4jcEnywf8LQKycQLIW-_6RyYEiVUENQtqQmvnwEILJU7AJxcpeuuUaI-HSHy
restore_session_key | DH6janEMJMwUgCn3qtlS6030IGO_DdTefLrHin9BdbZHVdQSYL5aTF9Ki5U9DFOv
sent_bytes          | 24731019
write_bytes         | 24731019
premdb=# select * from sys.replica_status;
-[ RECORD 1 ]-------+-----------------------------------------------------------------
database_id         | 16391
replica_id          | 16415
snapshot_id         | premdb_replica1_19_12_12_17_29_13
elapsed_ms          | 34215
remaining_ms        | 0
start_time          | 2019-12-12 17:29:13-08
end_time            | [NULL]
state               | USER_DATA_RUNNING
error_string        | [NULL]
backup_session_key  | AeL5WMy1W36EBiv0ea0BKhYxW0sV2Xlmrvzg0ucLUIKcg2JxlsU4zFnlBfIBWcbM
restore_session_key | CWW8BbTi2bi9KWq-GdupA9fYok_B1anAGy5fuA8OcsDlPD_GHN01FRftqet0_zFF
sent_bytes          | 138412896
write_bytes         | 138412896
premdb=# select * from sys.replica_status;
-[ RECORD 1 ]-------+-----------------------------------------------------------------
database_id         | 16391
replica_id          | 16415
snapshot_id         | premdb_replica1_19_12_12_17_34_37
elapsed_ms          | 207203
remaining_ms        | 0
start_time          | 2019-12-12 17:34:37-08
end_time            | [NULL]
state               | USER_DATA_RUNNING
error_string        | [NULL]
backup_session_key  | DmabhirRiu7r_o0DEH4YguDjg0A0oBnESEKjvH40CXVP8W4Ug1ODvtFD-2YVbjnd
restore_session_key | AxtTYw1nAb0qqRFi93rSEA0PWU9K4O-d2RRGm2fNkZg34qyHwHZ5s23wevDI51iI
sent_bytes          | 5580363831
write_bytes         | 5580363831

Note that the replica database is in HOT_STANDBY mode and must remain in that mode while replication is running:

premdb=# \l
                                          List of databases
        Name          |    Owner    | Encoding |      Access privileges      | Read-Only | Hot Standby 
-----------------------+-------------+----------+-----------------------------+-----------+-------------
 premdb                | os_user     | LATIN9   |                             | f         | f
 premdb_replica1_db    | yellowbrick | LATIN9   |                             | f         | t
...

Parent topic:System Views