Restoring to newly created clickhouse environment using a replicatedmergetree backup.

[bunkiedc]

Hi,
I'm in a dev environment and testing how to use clickhouse-backup from an existing clickhouse cluster, which is using replicatedmergetree, and restoring it to a newly created clickhouse cluster.
I've tried restoring this backup on an existing cluster (using the same backup), and it works well. But to the new cluster, it only puts the restored tables onto the node that the backup is running on. I did check the metadata of each of the table backups to be sure these are listed as replicatedmergetree and not mergetree, which they are.

Does the restore have to occur on each clickhouse node, or do the original table schemas need to be replaced or what? I haven't been able to find any answers for this particular situation. I'm also not sharding yet, so it's a pretty straightforward config.

Also, not sure if it matters or not, but the clickhouse cluster and clickhouse-back are running in containers.

thanks!

[Slach]

could you provide more context

how did you execute clikchouse-backup?
provide full restore command

[bunkiedc]

Firs of all, just wanted to say thanks... I see your name all over the place helping out with detailed information, it's much appreciated!

I managed to get this completed by first recreating all the tables schema, then doing the restore. I guess I had assumed that since the backup "appeared" to have the tables as replicatedmergetree that it would all get recreated.

Is it correct then, to first recreate all the table schemas, then do the restore, or is there a better way of doing this?
thanks again!

[Slach]

Sorry, I still not understand what exactly do you try to achieve?
Which is your restore commands sequence? provide full commands
Why do you need "recreating all the tables schema"?

restore command by default re-create exists table with schema which contains in every backup

[bunkiedc]

sorry, my restore sequence is: "clickhouse-backup restore fileNameOfBackup", running on one of the clickhouse nodes, nothing very exotic.

This was a new installation of a 3 node clickhouse cluster that I was restoring to, so no databases, no tables. When I first tried, it only restored to the node that clickhouse-backup was running on. The other two nodes did not replicate. If I first create the databases and tables, and then do a restore, the data replicates across all the nodes.

[Slach]

"clickhouse-backup restore fileNameOfBackup"

what means fileNameOfBackup exactly? Something like fileName.tar.gz? or this is just backup_name?

did you use clickhouse-backup download backupName before execute restore command?

maybe you need read
https://github.com/Altinity/clickhouse-backup/blob/master/Examples.md#how-to-back-up--restore-a-sharded-cluster
and try to understand proper command sequence?

[bunkiedc]

Yes, the fileNameOfBackup is as you describe, just the backup name. In my case it is like this: 2025-01-13T23:50:27.
My backups are kept locally. I've read that link many times, and I don't think it pertains to my original question. All the restore links appear to address restroring to an existing clickhouse setup, not a newly created one.

I did notice there there are options to the restore; --schema & --data, which say only schema and only data respectively. So I assumed not using those options I would get both schema and data when I restored. Which I did, but it didn't replicate across all nodes.
The command I used was: clickhouse-backup restore 2025-01-13T23:50:27

I'm still puzzled at how to do this when starting with a new installation of clickouse and trying to bring in existing data to that new installation.

[bunkiedc]

going though the clickhouse-backup website, I found this note:
"If all replicas in a shard were to fail, or more commonly, data was corrupted, the entire shard must be restored from a backup as described above. Ideally, restore the backup to one replica, restore the schema to the others, and allow ClickHouse’s native replication take over."

It sounds like perhaps the process should be (and in my case this is a newly created clickhouse cluster):

1. run the restore to a node, say node1 (clickhouse-backup restore Filename), this restores the schema and data to node1
2. run the restore with just the schema option set ( clickhouse-backup restore --schema Filename) on node 2
3. run the restore with just the schema option set ( clickhouse-backup restore --schema Filename) on node 3

Clickhouse should then provide the replication across all nodes. Does this sound correct?

Based on what else the website states, the backup needs to be running in the same namespace as the clickhouse-server node. I'm running all this in containers, so that means the clickhouse-backup would have to run on that same node in order to get to the clickhouse-server's data, is that correct?

[Slach]

All the restore links appear to address restroring to an existing clickhouse setup, not a newly created one.

Doesn't matter will you restore on new setup or exists setup

restore command will do following SQL sequence:

• CREATE DATABASE IF NOT EXISTS
• DROP TABLE IF EXISTS
• CREATE TABLE
• multiple queries ALTER TABLE .. ATTACH PART

if your restore failed
provide full restore command and full restore logs

The command I used was: clickhouse-backup restore 2025-01-13T23:50:27
Which I did, but it didn't replicate across all nodes.

this is expected behavior, and properly command sequence to avoid it described on link which I shared above

another way is using following config options

clickhouse:
  restore_schema_on_cluster: "{cluster}"

In this case, the schema will restore on all nodes in the cluster (will add ON CLUSTER clause to each DROP and CREATE queries)
it requires clickhouse cluster with worked zookeeper/clickhouse-keeper

In any case, if you have multiple shards and separate backup for each shard, follow the command sequence which shared above

[Slach]

It sounds like perhaps the process should be (and in my case this is a newly created clickhouse cluster):

• run the restore to a node, say node1 (clickhouse-backup restore Filename), this restores the schema and data to node1
• run the restore with just the schema option set ( clickhouse-backup restore --schema Filename) on node 2
• run the restore with just the schema option set ( clickhouse-backup restore --schema Filename) on node 3

are you sure you read https://github.com/Altinity/clickhouse-backup/blob/master/Examples.md#how-to-back-up--restore-a-sharded-cluster?

restore schema first
then restore data on first replica in shard

Based on what else the website states, the backup needs to be running in the same namespace as the clickhouse-server node.

yes, it requires
this is the second line in README
https://github.com/Altinity/clickhouse-backup/#dont-run-clickhouse-backup-remotely

[Slach]

Clickhouse should then provide the replication across all nodes. Does this sound correct?

If table not exist on the node, how will we replicate data?

And not "to all nodes", but only to nodes where table exists and have the same replication path in Zookeeper

This is the root reason why use {shard} macro in replication_path parameter on `engine=ReplicatedMergeTree('replication_path','replica_name') for multi-sharded installations

[Slach]

I'm running all this in containers, so that means the clickhouse-backup would have to run on that same node in order to get to the clickhouse-server's data, is that correct?

Yes, the same node and requires same volumes with clickhouse-server container to allow properly execute ALTER TABLE ... ATTACH PART ...

[bunkiedc]

Okay, I think I have it now, thanks!