Physical Cluster Replication Monitoring

You can monitor a stream using:

SHOW VIRTUAL CLUSTER... WITH REPLICATION STATUS in the SQL shell.
The on the DB Console.
Prometheus and Alertmanager to track and alert on replication metrics.

When you complete a , there will be a gap in the primary cluster’s metrics whether you are monitoring via the DB Console or Prometheus. The standby cluster will also require separate monitoring to ensure observability during the failover period. You can use the DB console to track the relevant metrics, or you can use a tool like to create two separate dashboards, one for each cluster, or a single dashboard with data from both clusters.

SQL Shell

In the standby cluster’s SQL shell, you can query SHOW VIRTUAL CLUSTER... WITH REPLICATION STATUS for detail on status and timestamps for planning :

SHOW VIRTUAL CLUSTER main WITH REPLICATION STATUS;

Refer to Responses for a description of each field.

id | name | source_tenant_name |              source_cluster_uri                 |         retained_time         |    replicated_time           | replication_lag | failover_time                   |   status
---+------+--------------------+-------------------------------------------------+-------------------------------+------------------------------+-----------------+--------------------------------+--------------
3  | main | main               | postgresql://user@hostname or IP:26257?redacted | 2023-09-28 16:09:04.327473+00 | 2023-09-28 17:41:18.03092+00 | 00:00:19.602682 | 1695922878030920020.0000000000 | replicating
(1 row)

Responses

Field	Response
`id`	The ID of a virtual cluster.
`name`	The name of the standby (destination) virtual cluster.
`data\_state`	The state of the data on a virtual cluster. This can show one of the following: `initializing replication`, `ready`, `replicating`, `replication paused`, `replication pending failover`, `replication failing over`, `replication error`. Refer to Data state for more detail on each response.
`service\_mode`	The service mode shows whether a virtual cluster is ready to accept SQL requests. This can show `none` or `shared`. When `shared`, a virtual cluster’s SQL connections will be served by the same nodes that are serving the system virtual cluster.
`source\_tenant\_name`	The name of the primary (source) virtual cluster.
`source\_cluster\_uri`	The URI of the primary (source) cluster. The standby cluster connects to the primary cluster using this URI when .
`replicated\_time`	The latest timestamp at which the standby cluster has consistent data — that is, the latest time you can fail over to. This time advances automatically as long as the replication proceeds without error. `replicated\_time` is updated periodically (every `30s`).
`retained\_time`	The earliest timestamp at which the standby cluster has consistent data — that is, the earliest time you can fail over to.
`replication\_lag`	The time between the most up-to-date replicated time and the actual time. Refer to the for more detail.
`failover\_time`	The time at which the failover will begin. This can be in the past or the future. Refer to .
`status`	The status of the replication stream. This can show one of the following: `initializing replication`, `ready`, `replicating`, `replication paused`, `replication pending failover`, `replication failing over`, `replication error`. Refer to Data state for more detail on each response.
`capability\_name`	The name.
`capability\_value`	Whether the is enabled for a virtual cluster.

Data state

State	Description
`add`	() The is waiting for the PCR job’s initial scan to complete, then `readonly` will be available for read queries.
`initializing replication`	The replication job is completing the initial scan of data from the primary cluster before it starts replicating data in real time.
`ready`	A virtual cluster’s data is ready for use. The `readonly` virtual cluster is ready to serve read queries.
`replicating`	The replication job has started and is replicating data.
`replication paused`	The replication job is paused due to an error or a manual request with .
`replication pending failover`	The replication job is running and the failover time has been set. Once the the replication reaches the failover time, the failover will begin automatically.
`replication failing over`	The job has started failing over. The failover time can no longer be changed. Once failover is complete, a virtual cluster will be available for use with .
`replication error`	An error has occurred. You can find more detail in the error message and the . Note: A PCR job will retry for 3 minutes before failing.

DB Console

You can use the of the standby cluster’s to monitor:

Prometheus

You can use Prometheus and Alertmanager to track and alert on PCR metrics. Refer to the tutorial for steps to set up Prometheus. We recommend tracking the following metrics:

physical_replication.logical_bytes: The logical bytes (the sum of all keys and values) ingested by all PCR jobs.
physical_replication.replicated_time_seconds: The of the physical replication stream in seconds since the Unix epoch.

Data verification

This feature is in and subject to change. To share feedback and/or issues, contact Support.

The SHOW EXPERIMENTAL_FINGERPRINTS statement verifies that the data transmission and ingestion is working as expected while a replication stream is running. Any checksum mismatch likely represents corruption or a bug in CockroachDB. SHOW EXPERIMENTAL_FINGERPRINTS is only to verify data. Should you encounter such a mismatch, contact Support. To verify that the data at a certain point in time is correct on the standby cluster, you can use the from the replication job information to run a point-in-time fingerprint on both the primary and standby clusters. This will verify that the transmission and ingestion of the data on the standby cluster, at that point in time, is correct.

Retrieve the current replicated time of the replication job on the standby cluster with :

SELECT replicated_time FROM [SHOW VIRTUAL CLUSTER standbymain WITH REPLICATION STATUS];

     replicated_time
----------------------------
2024-01-09 16:15:45.291575+00
(1 row)

For detail on connecting to the standby cluster, refer to .

From the primary cluster’s system virtual cluster, specify a timestamp at or earlier than the current replicated_time to retrieve the fingerprint. This example uses the current replicated_time:

SELECT * FROM [SHOW EXPERIMENTAL_FINGERPRINTS FROM VIRTUAL CLUSTER main] AS OF SYSTEM TIME '2024-01-09 16:15:45.291575+00';

tenant_name |             end_ts             |     fingerprint
------------+--------------------------------+----------------------
main        | 1704816945291575000.0000000000 | 2646132238164576487
(1 row)

For detail on connecting to the primary cluster, refer to .

From the standby cluster’s system virtual cluster, specify the same timestamp used on the primary cluster to retrieve the standby cluster’s fingerprint:

SELECT * FROM [SHOW EXPERIMENTAL_FINGERPRINTS FROM VIRTUAL CLUSTER standbymain] AS OF SYSTEM TIME '2024-01-09 16:15:45.291575+00';

    tenant_name     |             end_ts             |     fingerprint
--------------------+--------------------------------+----------------------
standbymain         | 1704816945291575000.0000000000 | 2646132238164576487
(1 row)

Compare the fingerprints of the primary and standby clusters to verify the data. The same value for the fingerprints indicates the data is correct.

​SQL Shell

​Responses

​Data state

​DB Console

​Prometheus

​Data verification

​See also

SQL Shell

Responses

Data state

DB Console

Prometheus

Data verification

See also