I can't able to restore by clickhouse backup file using clickhouse-backup tools

[sivasankarananth]

Hi,
-I have use the clickhouse-backup tools for taking backup of my clickhouse database.
-It is working fine.
-I tried to restore my backup db in another server at that Time am facing the below issues.
"error code: 336, message: Unknown database engine:
ENGINE = Memory()"

[Slach]

@sivasankarananth which clickhouse-server version do you use on the server where you try to restore?

could you share the results of the following command:

LOG_LEVEL=debug clickhouse-backup -c /path/to/your/config.yaml restore <your_backup_name>

?

[Slach]

@sivasankarananth is the issue still relevant for you? Did you try the latest releases 1.1.0+? Let's close issue during inactivity, feel free reopen if necessary

[makorne]

LOG_LEVEL=debug /usr/bin/clickhouse-backup restore db_back

2021/12/20 14:34:40 debug SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2021/12/20 14:34:40 debug SELECT * FROM system.disks;
2021/12/20 14:34:40 debug CREATE DATABASE IF NOT EXISTS INFORMATION_SCHEMA
ENGINE = Memory()
2021/12/20 14:34:40 error code: 36, message: Database engine Memory cannot have arguments

ClickHouse servers are the latest : version 21.12.2.17 (official build).

Centos 8 Linux 4.18.0-305.19.1.el8_4.x86_64 #1 SMP Wed Sep 15 15:39:39 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux

[makorne]

Ups...
/usr/bin/clickhouse-backup -v
Version: 1.0.0

Now I will check on 1.2:

/usr/bin/clickhouse-backup -v
Version: 1.2.2
Git Commit: 09d6131
Build Date: 2021-11-26

2021/12/20 15:01:33 debug SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2021/12/20 15:01:33 debug SELECT * FROM system.disks;
2021/12/20 15:01:33 debug CREATE DATABASE IF NOT EXISTS INFORMATION_SCHEMA
ENGINE = Memory()
2021/12/20 15:01:33 error code: 36, message: Database engine Memory cannot have arguments

Latest1.2.2 - the same...

[Slach]

@makorne do you try to restore backup which created with old versions of clickhouse-backup?

Could you try to add in your config.yaml

clickhouse:
  skip_tables:
    - system.*
    - INFORMATION_SCHEMA.*
    - information_schema.*

information_schema it's database which added on latest clickhouse-server and it not affect to backup process

[makorne]

No, I recreate backup with new version and copy it.

Ok, I tried recreate backup and restore with such config.yaml

The same...

2021/12/20 15:54:44 debug SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'                                                                                            
2021/12/20 15:54:44 debug SELECT * FROM system.disks;
2021/12/20 15:54:44 debug CREATE DATABASE IF NOT EXISTS INFORMATION_SCHEMA
ENGINE = Memory()
2021/12/20 15:54:44 error code: 36, message: Database engine Memory cannot have arguments

[makorne]

The same backup on a freshly installed server works well even without config,yaml

Do you have an idea how to restore backup on the old server?

[Slach]

it depends on many circumstates
i can suggest only one thing

add to /etc/clickhouse-backup/config.yaml
following section

clickhouse:
  skip_tables:
    - system.*
    - INFORMATION_SCHEMA.*
    - information_schema.*

after it run clickhouse-backup create<backup_name> on source server
copy /var/lib/clickhouse/backup/<backup_name> to destination server /var/lib/clickhouse/backup/<backup_name>(if you choose not use third server withremote_storage`)

run clickhouse-backup restore <backup_name> on destination server