--- alias: operation-guide-clickhouse-highavailability tags: - database - ClickHouse - high availability description: "Operate a replicated ClickHouse cluster, run and verify the migration to replicated tables, and keep Data Platform available when a server fails" --- # ClickHouse High Availability From version 12.0, Critical Manufacturing MES supports ClickHouse running as a replicated cluster. A replicated cluster keeps a copy of the data on more than one server, so that Data Platform stays available when a server fails. This page describes how to operate a replicated cluster after installation. For the requirements, see [[system-requirements-clickhouse-data-platform#high-availability|ClickHouse Requirements]]. For the setup steps, see [[installation-guide-planning-and-installation-guides-clickhouse#setting-up-a-replicated-cluster|ClickHouse Connection Setup]]. ## Benefits of Replication On a replicated cluster, every server in the shard holds a full copy of the data. ClickHouse Keeper coordinates the replicas and keeps them in agreement about which parts each one holds. Replication gives you two things: - **Availability** - When a ClickHouse server fails, the remaining replicas still hold the data. With a load balancer in front of them, the connections move to a surviving replica. - **Durability across servers** - A disk failure on one server does not lose the data, because another replica holds the same parts. !!! warning Replication is not a backup. A statement that drops or corrupts data runs on every replica. Continue to back up the databases as described in [[operation-guide-clickhouse-backupandrestoreoverview]]. ## Deployment Options Replication behaves differently in each supported deployment: - **ClickHouse in the MES stack** - A single instance, with no replication. It is not supported in Staging or Production environments. - **External self-managed ClickHouse** - Replication through the `Replicated*MergeTree` engine family. It requires ClickHouse Keeper and, in most deployments, a load balancer. Everything on this page applies. - **ClickHouse Cloud** - The service manages replication internally. You do not set a cluster name, and the sections on the migration and on the load balancer do not apply. For the requirements of each option, see [[system-requirements-clickhouse-data-platform#high-availability|ClickHouse Requirements]]. ## How the Migration Runs When `Cluster Name` holds a value, the installation converts the existing databases and tables to the cluster at the end of the installation. You do not convert them yourself. The conversion runs in these cases: - On an upgrade to version 12.0 of an environment that already holds data. - On a clean installation, where it creates the objects across the cluster from the start. - On the restore of a generated backup package, even when the restore does not change the version. When `Cluster Name` is empty, the conversion does not run. The log records that the parameter is not configured and that the migration does not run. The MES keeps the single-instance behavior of earlier versions. !!! note The conversion to a cluster is not the ClickHouse migration process of version 12.0.0. That process rebuilds tables in the background after an upgrade, and it runs regardless of the value of `Cluster Name`. On a cluster, HouseKeeper runs the rebuild with `ON CLUSTER`, so it rebuilds each table on every replica. For more information, see [ClickHouse Migration Process](https://developer.criticalmanufacturing.com/explore/guides/migration/version-specific/1200/data-platform/clickhouse_migration_process/). ### What the Migration Does The conversion works on the metadata of each object. It does not copy or rewrite the data: 1. Creates the database on the cluster. 2. Creates the table on the cluster, with the engine rewritten to the `Replicated*MergeTree` family. 3. Detaches the existing table, then attaches it again as a replicated table. 4. Restores the replica and attaches the partitions that already exist on disk. Views only need the create statement, because a view holds no data of its own. After a successful run, the MES records a patch entry named `High availability - Enable ClickHouse Replication` against each converted database. This entry shows that the conversion already ran. ### Re-Running the Migration If the installation fails partway through the conversion, run it again. The conversion continues from where it stopped, and it does not repeat the work it already completed. On a second run, the conversion checks the state of each table and issues only the steps that are still outstanding: - A table that is already replicated does not go through the detach and attach steps again. - The conversion restores a replica only when that replica is read-only or its session expired. - The conversion attaches the partitions only when parts remain in the detached directory. - The conversion also picks up a table that a failed run left detached. A detached table no longer appears in `system.tables`, so the conversion finds it in `system.detached_tables`. When a replica recovers on its own between the check and the conversion, the conversion skips the restore and records the reason in the log. ### How Long the Migration Takes The conversion takes about one second per table, on average. The size of a table does not change this, because the conversion changes metadata rather than copying data. The total duration depends on the number of tables in the environment, not on the volume of data in them. !!! note Count the tables in the environment to estimate the duration. A 200 GB database and a 20 GB database with the same number of tables take about the same time to convert. ## Verifying a Replicated Cluster Run these checks after an installation or an upgrade to confirm that the data reaches every replica. ### Confirm That the Cluster Is Visible ```sql SELECT cluster, shard_num, replica_num, host_name FROM system.clusters WHERE cluster = ''; ``` The result lists one row per replica. If the result is empty, the cluster name in the ClickHouse server configuration does not match the value of `Cluster Name`. ### Confirm That the Tables Use a Replicated Engine ```sql SELECT database, name, engine FROM system.tables WHERE database LIKE '%' AND engine LIKE '%MergeTree' AND engine NOT LIKE 'Replicated%'; ``` The result should be empty. Any table listed is still a single-instance table, and its data does not replicate. ### Confirm That the Replicas Are in Agreement ```sql SELECT database, table, is_readonly, absolute_delay, total_replicas, active_replicas FROM system.replicas WHERE active_replicas < total_replicas OR is_readonly = 1; ``` The result should be empty. A row with `is_readonly` set to `1` means the table lost its connection to ClickHouse Keeper. A row where `active_replicas` is lower than `total_replicas` means a replica is down or is still catching up. ### Confirm That the Conversion Ran Check the patches of each Data Platform database for the `High availability - Enable ClickHouse Replication` entry. When the entry is missing and the tables are not replicated, the conversion did not run, which means `Cluster Name` was empty during the installation. ## Load Balancer A load balancer in front of the replicas is not required, but it is recommended. The MES connects to the load balancer rather than to a single replica, so the connection stays up when a replica fails. Without a load balancer, the MES connects to one replica directly. If that replica goes down, the MES loses connectivity, and it needs manual reconfiguration and a restart. Configure the load balancer for failover, so that all connections reach the primary replica and another replica takes over only when the primary fails. If your load balancer cannot do failover, configure sticky sessions instead, so that one session stays on one replica. Failover also keeps reads consistent. The Aggregation Engine uses dbt, which reads back the data it has just written. If a replica that has not caught up serves that read, the aggregation fails. For the configuration steps, see [[installation-guide-planning-and-installation-guides-clickhouse#load-balancer|ClickHouse Connection Setup]]. ## Adding a Replica to an Existing Cluster ClickHouse Keeper replicates only the objects it already knows about. It does not watch the cluster for new replicas, so a replica added to a running cluster does not start to replicate on its own. The databases, tables, and views have to exist on the new replica first. After they exist, ClickHouse Keeper recognizes that replication is needed and starts to synchronize. To create those objects, redeploy the packages that hold the migration handler, `Cmf.Database.ClickHouse` and `Cmf.Database.ClickHouse.Mes`, through the DevOps Center. Every statement that the packages issue is guarded with `IF NOT EXISTS ... ON CLUSTER`, so they create only what the new replica is missing. You do not need a manual script. 1. Install ClickHouse on the new replica. 2. Add the replica to the cluster configuration on every ClickHouse server and every ClickHouse Keeper instance, and configure the macros. 3. Restart ClickHouse and ClickHouse Keeper, so they read the new configuration. 4. Redeploy the `Cmf.Database.ClickHouse` and `Cmf.Database.ClickHouse.Mes` packages. 5. Update the load balancer last, so that it starts to send connections to the new replica. !!! warning Update the load balancer only after the redeploy completes. The migration handler cannot create objects on a replica that is not part of the cluster configuration yet, so the new replica holds no data until step 4 runs. While the load balancer holds no configuration for the new replica, it cannot send connections to it. The change needs a short downtime window for the configuration to apply. Redeploying the package is faster than reinstalling the application, because a full installation only completes after replication finishes. The environment may run slower while the replicas catch up. If the primary replica fails before the migration completes, restart the installation.