search

Altinity / clickhouse-backup

Tool for easy backup and restore for ClickHouse® using object storage for backup files.
https://altinity.com
Other
1.25k stars 225 forks source link

Failed to restore INFORMATION_SCHEMA and information_schema backups or data #321

Closed kishorer00 closed 2 years ago

kishorer00 commented 2 years ago

Hi Alex,

OS version :CentOS Linux release 7.6 (Core)

ClickHouse version: ClickhouseDB version 2.11

Backup tool version: v1.2.2 from the release versions: https://github.com/AlexAkulov/clickhouse-backup/releases .

Able to take the Clickhouse data backup and restore it.

But getting the following Error message while restoring the Clickhouse backup.

++++++++++++++++++++++++++++++++++++++++++++++++++++++

2021/12/15 10:18:46 warn can't create table 'INFORMATION_SCHEMA.SCHEMATA': code: 80, message: Incorrect ATTACH TABLE query for Atomic database engine. Use one of the following queries instead:

  1. ATTACH TABLE SCHEMATA;
  2. CREATE TABLE SCHEMATA ;
  3. ATTACH TABLE SCHEMATA FROM '/path/to/data/'
    4. ;
  4. ATTACH TABLE SCHEMATA UUID ''
    5. ;, will try again backup=v122-backup operation=restore

    2021/12/15 10:18:46 warn can't create table 'INFORMATION_SCHEMA.VIEWS': code: 80, message: Incorrect ATTACH TABLE query for Atomic database engine. Use one of the following queries instead:

    1. ATTACH TABLE VIEWS;
    2. CREATE TABLE VIEWS
    ;
  5. ATTACH TABLE VIEWS FROM '/path/to/data/'
    6. ;
  6. ATTACH TABLE VIEWS UUID ''
    7. ;, will try again backup=v122-backup operation=restore +++++++++++++++++++++++++++++++++++++++++++++++++

    Any fix for this ? Appreciated your help on this ..

    Thanks in advance !!!.

    Slach commented 2 years ago

    Please add to config.yaml

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

    or use CLICKHOUSE_SKIP_TABLES=system.*,INFORMATION_SCHEMA.*,information_schema.* environment variable

    kishorer00 commented 2 years ago

    Thanks for the quick reply...

    Few queries after referring to the documentation: https://clickhouse.com/docs/en/operations/system-tables/information_schema/ ...

    • INFORMATION_SCHEMA (information_schema) is a system database that contains views ==> If we skip, will that impact my system views ? Hence it's a partial data
    • These views read data from the columns of the system.columns, system.databases and system.tables system tables. ==> Any issues with tables and schema's, after we skip INFORMATION_SCHEMA ?

    If you can answer, that helps in understanding more on INFORMATION_SCHEMA.

    Slach commented 2 years ago

    INFORMATION_SCHEMA (information_schema) is a system database that contains views ==> If we skip, will that impact my system views ? Hence it's a partial data

    No, it creates on every clickhouse-server start and it should be skipped during backup we store SHOW CREATE TABLE metadata for all talbes which backuped

    kishorer00 commented 2 years ago

    It worked !!!. Thanks a lot ...

    • © Githubissues.
    • Githubissues is a development platform for aggregating issues.