Replicated* table engines

If the local set of data differs too much from the expected one, a safety mechanism is triggered. The server enters this in the log and refuses to launch. The reason for this is that this case may indicate a configuration error, such as if a replica on a shard was accidentally configured like a replica on a different shard. However, the thresholds for this mechanism are set fairly low, and this situation might occur during normal failure recovery. In this case, data is restored semi-automatically - by "pushing a button".

To start recovery, create the node `/path_to_table/replica_name/flags/force_restore_data` in ClickHouse Keeper with any content, or run the command to restore all replicated tables:

```
sudo -u clickhouse touch /var/lib/clickhouse/flags/force_restore_data
```

Then restart the server. On start, the server deletes these flags and starts recovery.

## Recovery after complete data loss[​](#recovery-after-complete-data-loss "Direct link to Recovery after complete data loss")

If all data and metadata disappeared from one of the servers, follow these steps for recovery:

1. Install ClickHouse on the server. Define substitutions correctly in the config file that contains the shard identifier and replicas, if you use them.
2. If you had unreplicated tables that must be manually duplicated on the servers, copy their data from a replica (in the directory `/var/lib/clickhouse/data/db_name/table_name/`).
3. Copy table definitions located in `/var/lib/clickhouse/metadata/` from a replica. If a shard or replica identifier is defined explicitly in the table definitions, correct it so that it corresponds to this replica. (Alternatively, start the server and make all the `ATTACH TABLE` queries that should have been in the .sql files in `/var/lib/clickhouse/metadata/`.)
4. To start recovery, create the ClickHouse Keeper node `/path_to_table/replica_name/flags/force_restore_data` with any content, or run the command to restore all replicated tables: `sudo -u clickhouse touch /var/lib/clickhouse/flags/force_restore_data`

Then start the server (restart, if it is already running). Data will be downloaded from replicas.

An alternative recovery option is to delete information about the lost replica from ClickHouse Keeper (`/path_to_table/replica_name`), then create the replica again as described in "[Creating replicated tables](#creating-replicated-tables)".

There is no restriction on network bandwidth during recovery. Keep this in mind if you are restoring many replicas at once.

## Converting from MergeTree to ReplicatedMergeTree[​](#converting-from-mergetree-to-replicatedmergetree "Direct link to Converting from MergeTree to ReplicatedMergeTree")

We use the term `MergeTree` to refer to all table engines in the `MergeTree family`, the same as for `ReplicatedMergeTree`.

If you had a `MergeTree` table that was manually replicated, you can convert it to a replicated table. You might need to do this if you have already collected a large amount of data in a `MergeTree` table and now you want to enable replication.

[ATTACH TABLE ... AS REPLICATED](/docs/sql-reference/statements/attach#attach-mergetree-table-as-replicatedmergetree) statement allows to attach detached `MergeTree` table as `ReplicatedMergeTree`.

`MergeTree` table can be automatically converted on server restart if `convert_to_replicated` flag is set at the table's data directory (`/store/xxx/xxxyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy/` for `Atomic` database).
Create empty `convert_to_replicated` file and the table will be loaded as replicated on next server restart.

This query can be used to get the table's data path. If table has many data paths, you have to use the first one.

```
SELECT data_paths FROM system.tables WHERE table = 'table_name' AND database = 'database_name';
```

Note that ReplicatedMergeTree table will be created with values of `default_replica_path` and `default_replica_name` settings.
To create a converted table on other replicas, you will need to explicitly specify its path in the first argument of the `ReplicatedMergeTree` engine. The following query can be used to get its path.

```
SELECT zookeeper_path FROM system.replicas WHERE table = 'table_name';
```

There is also a manual way to do this.

If the data differs on various replicas, first sync it, or delete this data on all the replicas except one.

Rename the existing MergeTree table, then create a `ReplicatedMergeTree` table with the old name.
Move the data from the old table to the `detached` subdirectory inside the directory with the new table data (`/var/lib/clickhouse/data/db_name/table_name/`).
Then run `ALTER TABLE ATTACH PARTITION` on one of the replicas to add these data parts to the working set.

## Converting from ReplicatedMergeTree to MergeTree[​](#converting-from-replicatedmergetree-to-mergetree "Direct link to Converting from ReplicatedMergeTree to MergeTree")

Use [ATTACH TABLE ... AS NOT REPLICATED](/docs/sql-reference/statements/attach#attach-mergetree-table-as-replicatedmergetree) statement to attach detached `ReplicatedMergeTree` table as `MergeTree` on a single server.

Another way to do this involves server restart. Create a MergeTree table with a different name. Move all the data from the directory with the `ReplicatedMergeTree` table data to the new table's data directory. Then delete the `ReplicatedMergeTree` table and restart the server.

If you want to get rid of a `ReplicatedMergeTree` table without launching the server:

- Delete the corresponding `.sql` file in the metadata directory (`/var/lib/clickhouse/metadata/`).
- Delete the corresponding path in ClickHouse Keeper (`/path_to_table/replica_name`).

After this, you can launch the server, create a `MergeTree` table, move the data to its directory, and then restart the server.

## Recovery when metadata in the ClickHouse Keeper cluster is lost or damaged[​](#recovery-when-metadata-in-the-zookeeper-cluster-is-lost-or-damaged "Direct link to Recovery when metadata in the ClickHouse Keeper cluster is lost or damaged")

If the data in ClickHouse Keeper was lost or damaged, you can save data by moving it to an unreplicated table as described above.

**See Also**

- [background\_schedule\_pool\_size](/docs/operations/server-configuration-parameters/settings#background_schedule_pool_size)
- [background\_fetches\_pool\_size](/docs/operations/server-configuration-parameters/settings#background_fetches_pool_size)
- [execute\_merges\_on\_single\_replica\_time\_threshold](/docs/operations/settings/merge-tree-settings#execute_merges_on_single_replica_time_threshold)
- [max\_replicated\_fetches\_network\_bandwidth](/docs/operations/settings/merge-tree-settings#max_replicated_fetches_network_bandwidth)
- [max\_replicated\_sends\_network\_bandwidth](/docs/operations/settings/merge-tree-settings#max_replicated_sends_network_bandwidth)

- [Creating replicated tables](#creating-replicated-tables)
  - [Replicated\*MergeTree parameters](#replicatedmergetree-parameters)- [Recovery after failures](#recovery-after-failures)- [Recovery after complete data loss](#recovery-after-complete-data-loss)- [Converting from MergeTree to ReplicatedMergeTree](#converting-from-mergetree-to-replicatedmergetree)- [Converting from ReplicatedMergeTree to MergeTree](#converting-from-replicatedmergetree-to-mergetree)- [Recovery when metadata in the ClickHouse Keeper cluster is lost or damaged](#recovery-when-metadata-in-the-zookeeper-cluster-is-lost-or-damaged)

Was this page helpful?