About backup table to s3 (Atomic database engine)

[yangshike]

I have a 3 node 3 replica cluster. The uuid used when creating tables is used( Atomic database engine). I use the backup table statement to back up data to S3. The backup table creation statement is also used. When I use restore to restore, the table creation statement does not have on cluster, resulting in recovery failure
#Code: 62. DB::Exception: Received from 127.0.0.1:9000. DB::Exception: Macro 'uuid' and empty arguments of ReplicatedMergeTree are supported only for ON CLUSTER queries with Atomic database engine: While creating table xxx. (SYNTAX_ERROR)

This problem also exists in local backup and recovery。
In other words, this backup function is only for non-clustered data？or only supports recovery to non-clustered environment？
However, the backup table creation statement is based on uuid, and it needs to be carried with (on cluster) when restoring

[yangshike]

When I manually add the backup file to the on cluster, then execute restore
：RESTORE TABLE xx as data3 FROM S3('xx/aa', 'xxx', 'xxx');
i got error:
Code: 608. DB::Exception: Received from 127.0.0.1:9000. DB::Exception: The table has a different definition:db.data3. (UNKNOWN_TABLE)

[yangshike]

I test using the Ordinal engine. When I use RESTORE TABLE xx.xx as xx.aaa, the prompt:
Code: 253. DB::Exception: Received from 127.0.0.1:9000. DB::Exception: Replica /clickhouse/tables/01-01/xx/xx /replicas/01-01-2 already exists:
so,Is there any best practice for backup S3

[vitlibar]

About the error The table has a different definition: ClickHouse shows that error if during RESTORE you already have a table with such name, but it's definition (i.e. create query) is different from what is stored inside the backup. The RESTORE command can insert into an existing table but that requires the table definition to be the same. This error is only for integrity and can be overcome with SETTINGS allow_different_table_def=false specified after RESTORE, but be careful if you use that.

I'll check the main issue. The backup functions are not only for non-clustered data, they are supposed to work everywhere.

[yangshike]

First,
use the Atomic engine for the cluster and database, TABLE ENGINE=ReplicatedReplacingMergeTree(). There is no on cluster option in the table creation statement saved through the backup table, but I will prompt when restoring:

DB::Exception: Macro 'uuid' and empty arguments of ReplicatedMergeTree are supported only for ON CLUSTER queries with Atomic database engine
Secondly:
use the database engine Atomic and TABLE ENGINE=ReplicatedReplacingMergeTree('/clickhouse/tables/{layer}-{shard}/database_name/table_name', '{replica}',)

When I restore the current cluster, I will be prompted: DB::Exception: Replica /clickhouse/tables/01-01/database_name/table_name /replicas/01-01-2 already exists:

[yangshike]

show create table xx.aa
CREATE TABLE xx.aa
(
id Int64,
customer_id Int64,
camp_date_id Nullable(Int64),
course_id Int64,
chapter_id Int64,
section_id Int64,
stay_time Int64,
os_name Nullable(String),
created_time DateTime,
modified_time Nullable(DateTime)
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}')
PARTITION BY toYYYYMM(created_time)
PRIMARY KEY id
ORDER BY id
SETTINGS storage_policy = 'all_jbod', index_granularity = 8192

[yangshike]

In addition, where can I find backup and restore parameters that can be configured in SETTING?

[yangshike]

Also, if the amount of table data is particularly large, it will prompt when backing up or restoring:
Timeout exceeded while receiving data from server. Waited for 300 seconds, timeout is 300 seconds.
Do I need to adjust the parameters receive_timeout and send_timeout to larger values?

[vitlibar]

I found a problem in the code related to using the {uuid} pattern in zookeeper path which is specified in the first argument of ReplicatedReplacingMergeTree. ON CLUSTER must not be saved inside a backup, and during restore it should recreate tables without ON CLUSTER as well, however {uuid} should be handled correctly by the code during RESTORE. Now it's not handled correctly so that error message is displayed. I'll fix this problem.

SETTINGS can be specified right after BACKUP or RESTORE command:

BACKUP ... SETTINGS ...

About timeouts - you can either adjust timeouts or specify ASYNC after your BACKUP/RESTORE command - that will make ClickHouse execute your command in background, and also you can use SELECT * FROM system.backups to understand when the backup is done.

[vitlibar]

Until I fix the issue with '{uuid}' you can restore your table manually by using the following workaround:

1. First recreate your table manually (without using RESTORE)

CREATE TABLE xx.aa ON CLUSTER ...

and then
2. Execute

RESTORE TABLE xx.aa FROM ... SETTINGS allow_different_table_def=true`

to append data from the backup to the table you created at step 1. That must work.

[vitlibar]

Replica /clickhouse/tables/01-01/database_name/table_name /replicas/01-01-2 already exists

That just means the specified path in ZooKeeper is already being used by another replica. It doesn't related to backup/restore.

[yangshike]

Replica /clickhouse/tables/01-01/database_name/table_name /replicas/01-01-2 already exists

That just means the specified path in ZooKeeper is already being used by another replica. It doesn't related to backup/restore.

Because the table is normal in using， I just want to restore the data from the backup to another table, but the table creation statement in the backup already exists to save the zookeeper address, so in this case, the restore statement cannot be executed normally

About settings, I want to know what can be added after setting

[vitlibar]

About settings, I want to know what can be added after setting

Some of the settings are documented at https://clickhouse.com/docs/en/operations/backup/

I just want to restore the data from the backup to another table

Try recreating your table with the same definition but with another name, and then restore the data:

CREATE TABLE xx.newaa ON CLUSTER '...'
(
id Int64,
customer_id Int64,
camp_date_id Nullable(Int64),
course_id Int64,
chapter_id Int64,
section_id Int64,
stay_time Int64,
os_name Nullable(String),
created_time DateTime,
modified_time Nullable(DateTime)
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}')
PARTITION BY toYYYYMM(created_time)
PRIMARY KEY id
ORDER BY id
SETTINGS storage_policy = 'all_jbod', index_granularity = 8192;

RESTORE xx.aa AS xx.newaa FROM S3(...) SETTINGS allow_different_table_def=true ASYNC;

[yangshike]

https://clickhouse.com/docs/en/operations/backup/

I looked at this address before, but I did not find allow_different_table_def in this document