Executing Replica Failover DDL
When a ReplicatedMergeTree table loses its Keeper session, ClickHouse silently flips the replica to read-only and stops accepting inserts — the fix is a small set of SYSTEM DDL commands that re-establish the session, rebuild lost metadata, and promote a healthy replica back into the ring. This page is the operational runbook for SYSTEM RESTART REPLICA, SYSTEM RESTORE REPLICA, and SYSTEM SYNC REPLICA, including how to read system.replicas to decide which one you actually need and how to apply the fix across every replica with ON CLUSTER.
Prerequisites
The failover decision path
Every replica failover starts by classifying the fault, because the three recovery commands are not interchangeable. A dropped session needs RESTART; wiped Keeper metadata needs RESTORE; a lagging replica needs SYNC. The two columns in system.replicas that drive the decision are is_session_expired (the Keeper session dropped and has not re-connected) and is_readonly (the replica cannot accept writes because it lost its metadata path). Reading them first stops you from running a destructive RESTORE when a cheap RESTART would have fixed it.
Step 1 — Read replica state before touching anything
Do not guess. Pull the health columns for every replicated table on the node and let the values pick the command for you.
SELECT
database,
table,
is_leader,
is_readonly,
is_session_expired,
future_parts,
parts_to_check,
queue_size,
absolute_delay,
last_queue_update_exception,
zookeeper_exception
FROM system.replicas
ORDER BY is_readonly DESC, absolute_delay DESC;
Expected output on a degraded node:
┌─database──┬─table──────┬─is_readonly─┬─is_session_expired─┬─queue_size─┬─absolute_delay─┐
│ analytics │ events_repl│ 1 │ 1 │ 42 │ 318 │
└───────────┴────────────┴─────────────┴────────────────────┴────────────┴────────────────┘
is_session_expired = 1 with is_readonly = 1 and a non-empty zookeeper_exception almost always means the Keeper session dropped. If queue_size is large but is_readonly = 0, the replica is merely behind — that is a SYNC, not a RESTART.
Step 2 — Re-establish the Keeper session with RESTART REPLICA
SYSTEM RESTART REPLICA tears down and re-initializes the in-memory replica state and re-opens the ZooKeeper/Keeper session for one table. It is non-destructive: it re-reads metadata from Keeper and re-queues any pending log entries. This is the correct first move after a transient network partition or a Keeper leader election.
-- Single table on this node
SYSTEM RESTART REPLICA analytics.events_repl;
If many tables dropped their sessions at once (a Keeper failover typically expires all of them), restart every replica on the node in one statement rather than looping:
SYSTEM RESTART REPLICAS;
After the command returns, re-run the Step 1 query. is_session_expired should be back to 0 and is_readonly should clear within a few seconds as the replica re-registers.
Step 3 — Rebuild lost metadata with RESTORE REPLICA
If Keeper actually lost the table’s metadata path — an ensemble that was rebuilt from an empty snapshot, or a rm -rf on the Keeper data dir — the replica stays is_readonly = 1 no matter how many times you restart it, because there is no path in Keeper to re-attach to. SYSTEM RESTORE REPLICA recreates that metadata in Keeper by scanning the replica’s local parts and re-registering them.
-- Run on ONE surviving replica that still holds the data locally.
SYSTEM RESTORE REPLICA analytics.events_repl;
If the Keeper root for the table is entirely gone, create the table’s replica path first, then restore:
SYSTEM DROP REPLICA 'replica_02' FROM TABLE analytics.events_repl; -- clear the stale record
SYSTEM RESTORE REPLICA analytics.events_repl;
Run RESTORE on exactly one replica. Once it has recreated the Keeper metadata, the other replicas re-attach by pulling from it — running RESTORE on several replicas simultaneously races them against each other for the same Keeper path.
Step 4 — Promote and re-sync the healthy replica
With metadata present again, force the recovering replica to pull every outstanding log entry before you route reads back to it. SYSTEM SYNC REPLICA blocks until the local replication queue is drained, so a subsequent SELECT sees a fully caught-up table.
-- Block until this replica has applied all entries from the shared log.
SYSTEM SYNC REPLICA analytics.events_repl STRICT;
The STRICT modifier waits for the queue to be genuinely empty rather than merely for the current entries to finish. Once it returns, this replica is a valid target for the load-balancing policies described in load balancing reads across replicas.
Step 5 — Apply the fix cluster-wide with ON CLUSTER
When a schema-level repair (not just a session reset) must reach every node — for example re-attaching a partition or re-issuing DDL after a split-brain — use ON CLUSTER so the DDL is written once to the distributed DDL queue and executed on every host in the named cluster.
-- Re-run a metadata-affecting statement on every node in the cluster.
SYSTEM RESTART REPLICA analytics.events_repl ON CLUSTER analytics_cluster;
-- Watch the distributed DDL queue drain across hosts.
SELECT host, status, cluster, query
FROM system.distributed_ddl_queue
WHERE cluster = 'analytics_cluster'
ORDER BY entry DESC
LIMIT 20;
Every host should reach status = 'Finished'. A host stuck on Active for longer than distributed_ddl_task_timeout is unreachable or itself read-only — resolve that node with Steps 2–3 before re-issuing the ON CLUSTER statement.
Verification
Confirm the whole ring is healthy and no replica is lagging or read-only:
SELECT
hostName() AS host,
database,
table,
is_readonly,
is_session_expired,
queue_size,
absolute_delay,
total_replicas,
active_replicas
FROM clusterAllReplicas('analytics_cluster', system.replicas)
WHERE database = 'analytics'
ORDER BY host, table;
A recovered cluster shows is_readonly = 0 and is_session_expired = 0 on every row, active_replicas = total_replicas, and absolute_delay trending to zero. Cross-check that the shared log is being consumed rather than piling up:
SELECT database, table, type, count() AS entries
FROM system.replication_queue
WHERE database = 'analytics'
GROUP BY database, table, type
ORDER BY entries DESC;
A persistently growing queue_size after a successful SYNC points to a repeated last_queue_update_exception — inspect that column for the underlying merge or fetch error.
Gotchas and edge cases
RESTORE REPLICAtrusts local parts as the source of truth. It re-registers whatever parts exist on the disk of the node you run it on. If that node was itself missing recent data, restoring from it publishes an incomplete part set to Keeper and the other replicas will converge on the gap. Always runRESTOREon the replica with the most complete local data, and verify part counts against a known-good peer first.RESTART REPLICAdoes not fix a wrongreplica_name. If two nodes were accidentally configured with the samereplica_namemacro, they fight over one Keeper path and repeatedly expire each other’s sessions. Restarting only masks it; fix the macro inconfig.xmland useSYSTEM DROP REPLICAto clear the duplicate registration.ON CLUSTERneeds a writable Keeper. The distributed DDL queue is itself stored in Keeper, so during a quorum lossON CLUSTERstatements silently queue and never execute. Recover the ensemble first — see recovering from ClickHouse Keeper quorum loss — then issue theON CLUSTERDDL.- A read-only replica still answers
SELECT. Becauseis_readonlyblocks writes but not reads, a degraded replica keeps serving stale query results and quietly poisons dashboards. Route reads away from any replica withis_readonly = 1until Step 4 confirms it is caught up.
Related
- Load Balancing Reads Across Replicas — where a freshly recovered replica re-enters the read-routing pool.
- Recovering from ClickHouse Keeper Quorum Loss — the ensemble-level recovery that must precede any
ON CLUSTERfailover DDL. - Implementing DNS-Based Fallback Routing for Analytics — moving client traffic off a node while you run failover DDL on it.
- Monitoring Replication Lag with system.replicas — the alerting layer that tells you a failover is needed before users do.