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

empty disk path during CopyToDetachedFolder, makes hardlink to relative folder, and later error Detached part "all_10009_10012_1" not found #453

Closed metaIhead closed 2 years ago

metaIhead commented 2 years ago

I try sudo ./clickhouse-backup -c config.yml restore last_backup_09_06 and after error was occured.

error can't attach partitions for table database.table1': code: 233, message: Detached part "all_10009_10012_1" not found

what i am doing wrong?

clickhouse-backup --version
Version:     1.4.0
Git Commit:  e549da93400f9101ea3d88861d94fea63000acf8
Build Date:  2022-05-17

ClickHouse server version 22.2.2.1.
LOG_LEVEL=debug  sudo ./clickhouse-backup -c config.yml restore last_backup_09_06
2022/06/09 19:20:21.033423  info SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2022/06/09 19:20:21.038369  info SELECT * FROM system.disks;
2022/06/09 19:20:21.040397  info CREATE DATABASE IF NOT EXISTS my_database
ENGINE = Atomic
2022/06/09 19:20:21.042290  info SELECT engine FROM system.databases WHERE name = 'my_database'
2022/06/09 19:20:21.043965  info DROP TABLE IF EXISTS `my_database`.`my_table1` NO DELAY
2022/06/09 19:20:21.147078  info SELECT engine FROM system.databases WHERE name = 'configservice'
2022/06/09 19:20:21.150646  info DROP TABLE IF EXISTS `my_database`.`my_table2` NO DELAY
2022/06/09 19:20:21.153606  info CREATE DATABASE IF NOT EXISTS `configservice`
2022/06/09 19:20:21.155484  info CREATE TABLE my_database.my_table1
(
    `instance_object_id` UInt32,
    `config` String,
    `config_checksum` String,
    `config_size` UInt32,
    `added_lines_number` UInt16,
    `deleted_lines_number` UInt16,
    `updated_lines_number` UInt16,
    `total_lines_number` UInt16,
    `statistic_time` DateTime
)
ENGINE = ReplacingMergeTree(statistic_time)
ORDER BY (instance_object_id, statistic_time)
TTL statistic_time + toIntervalYear(5)
SETTINGS index_granularity = 8192
2022/06/09 19:20:21.161205  info CREATE DATABASE IF NOT EXISTS `my_database'
2022/06/09 19:20:21.162321  info CREATE TABLE my_database.my_table2
(
    `instance_object_id` UInt32,
    `config` String,
    `config_checksum` String,
    `config_size` UInt32,
    `added_lines_number` UInt16,
    `deleted_lines_number` UInt16,
    `updated_lines_number` UInt16,
    `total_lines_number` UInt16,
    `statistic_time` DateTime
)
ENGINE = ReplacingMergeTree(statistic_time)
ORDER BY instance_object_id
SETTINGS index_granularity = 8192
2022/06/09 19:20:21.169482  info SELECT count() FROM system.settings WHERE name = 'show_table_uuid_in_table_create_query_if_not_nil'
2022/06/09 19:20:21.173357  info SELECT name FROM system.databases WHERE engine IN ('MySQL','PostgreSQL')
2022/06/09 19:20:21.176126  info 
        SELECT 
            countIf(name='data_path') is_data_path_present, 
            countIf(name='data_paths') is_data_paths_present, 
            countIf(name='uuid') is_uuid_present, 
            countIf(name='create_table_query') is_create_table_query_present, 
            countIf(name='total_bytes') is_total_bytes_present 
        FROM system.columns WHERE database='system' AND table='tables'

2022/06/09 19:20:21.179964  info SELECT database, name, engine   FROM system.tables WHERE is_temporary = 0 SETTINGS show_table_uuid_in_table_create_query_if_not_nil=1
2022/06/09 19:20:21.188208  info SELECT sum(bytes_on_disk) as size FROM system.parts WHERE active AND database='my_database' AND table='my_table1' GROUP BY database, table
2022/06/09 19:20:21.191827  info SELECT sum(bytes_on_disk) as size FROM system.parts WHERE active AND database='my_database' AND table='my_table2' GROUP BY database, table
2022/06/09 19:20:21.194829  info ALTER TABLE `my_database`.`my_table1` ATTACH PART 'all_10009_10012_1'
2022/06/09 19:20:21.197860 error can't attach partitions for table 'my_database.my_table1': code: 233, message: Detached part "all_10009_10012_1" not found
config.yml

clickhouse:
  username: username              
  password: "somepass"   
  host: localhost                      
  port: 9123                           
  skip_tables:                        
    - system.*
    - INFORMATION_SCHEMA.*
    - information_schema.*
Slach commented 2 years ago

How do you create your backup? Do you have one clickhouse server or multiple servers?

Slach commented 2 years ago

could you share results of ls -la /var/lib/backups/last_backup_09_06/shadow/my_database/my_table1/all_10009_10012_1 from source and destination clickhouse server?

metaIhead commented 2 years ago

I have just one server. And create another, and i want to copy data from first server. Create backup like that sudo ./clickhouse-backup -c config.yml create last_config Afret copy file from /vat/lib/clickhouse/backup to another vm, in the same path

metaIhead commented 2 years ago

Destination

ls -la  /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1

total 40
drwxr-x--- 2 clickhouse clickhouse 4096 Jun  8 22:09 .
drwxr-x--- 7 clickhouse clickhouse 4096 Jun  8 22:09 ..
-r--r----- 2 clickhouse clickhouse  214 Jun  8 22:09 checksums.txt
-r--r----- 2 clickhouse clickhouse  269 Jun  8 22:09 columns.txt
-r--r----- 2 clickhouse clickhouse    1 Jun  8 22:09 count.txt
-r--r----- 2 clickhouse clickhouse  582 Jun  8 22:09 data.bin
-r--r----- 2 clickhouse clickhouse  304 Jun  8 22:09 data.mrk3
-r--r----- 2 clickhouse clickhouse   10 Jun  8 22:09 default_compression_codec.txt
-r--r----- 2 clickhouse clickhouse   16 Jun  8 22:09 primary.idx
-r--r----- 2 clickhouse clickhouse   67 Jun  8 22:09 ttl.txt
metaIhead commented 2 years ago

Source 

ls -la /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/
total 32
drwxr-x--- 2 root       root       167 июн  8 20:22 .
drwxr-x--- 7 clickhouse clickhouse 130 июн  8 20:22 ..
-r--r----- 1 clickhouse clickhouse 214 июн  8 14:27 checksums.txt
-r--r----- 1 clickhouse clickhouse 269 июн  8 14:27 columns.txt
-r--r----- 1 clickhouse clickhouse   1 июн  8 14:27 count.txt
-r--r----- 1 clickhouse clickhouse 582 июн  8 14:27 data.bin
-r--r----- 1 clickhouse clickhouse 304 июн  8 14:27 data.mrk3
-r--r----- 1 clickhouse clickhouse  10 июн  8 14:27 default_compression_codec.txt
-r--r----- 1 clickhouse clickhouse  16 июн  8 14:27 primary.idx
-r--r----- 1 clickhouse clickhouse  67 июн  8 14:27 ttl.txt
Slach commented 2 years ago

looks weird on destination server

ls -la /var/lib/clickhouse/shadow//my_database/my_table1/all_10009_10012_1/

metaIhead commented 2 years ago

i have no this path on dst server 

ls -la /var/lib/clickhouse/shadow/
total 12
drwxr-x---  2 clickhouse clickhouse 4096 May 30 21:57 .
drwx------ 16 clickhouse clickhouse 4096 Jun  8 22:07 ..
-rw-r-----  1 clickhouse clickhouse    2 May 30 21:57 increment.txt
metaIhead commented 2 years ago

what exactly looks weird?

Slach commented 2 years ago

during restore clickhouse-backup should make hardlinks in /var/lib/clickhouse/shadow/ folder to /var/lib/clickhouse/backups/

could you share sudo ./clickhouse-backup -c config.yml print-config ?

metaIhead commented 2 years ago 
general:
  remote_storage: none
  max_file_size: 0
  disable_progress_bar: true
  backups_to_keep_local: 0
  backups_to_keep_remote: 0
  log_level: info
  allow_empty_backups: false
  download_concurrency: 2
  upload_concurrency: 2
  restore_schema_on_cluster: ""
  upload_by_part: true
  download_by_part: true
clickhouse:
  username: username
  password: somepass
  host: localhost
  port: 9123
  disk_mapping: {}
  skip_tables:
  - system.*
  - INFORMATION_SCHEMA.*
  - information_schema.*
  timeout: 5m
  freeze_by_part: false
  secure: false
  skip_verify: false
  sync_replicated_tables: false
  log_sql_queries: true
  config_dir: /etc/clickhouse-server/
  restart_command: systemctl restart clickhouse-server
  ignore_not_exists_error_during_freeze: true
  tls_key: ""
  tls_cert: ""
  tls_ca: ""
  debug: false
s3:
  access_key: ""
  secret_key: ""
  bucket: ""
  endpoint: ""
  region: us-east-1
  acl: private
  assume_role_arn: ""
  force_path_style: false
  path: ""
  disable_ssl: false
  compression_level: 1
  compression_format: tar
  sse: ""
  disable_cert_verification: false
  storage_class: STANDARD
  concurrency: 1
  part_size: 0
  max_parts_count: 10000
  allow_multipart_download: false
  debug: false
gcs:
  credentials_file: ""
  credentials_json: ""
  bucket: ""
  path: ""
  compression_level: 1
  compression_format: tar
  debug: false
  endpoint: ""
cos:
  url: ""
  timeout: 2m
  secret_id: ""
  secret_key: ""
  path: ""
  compression_format: tar
  compression_level: 1
  debug: false
api:
  listen: localhost:7171
  enable_metrics: true
  enable_pprof: false
  username: ""
  password: ""
  secure: false
  certificate_file: ""
  private_key_file: ""
  create_integration_tables: false
  allow_parallel: false
ftp:
  address: ""
  timeout: 2m
  username: ""
  password: ""
  tls: false
  path: ""
  compression_format: tar
  compression_level: 1
  concurrency: 2
  debug: false
sftp:
  address: ""
  port: 22
  username: ""
  password: ""
  key: ""
  path: ""
  compression_format: tar
  compression_level: 1
  concurrency: 1
  debug: false
azblob:
  endpoint_suffix: core.windows.net
  account_name: ""
  account_key: ""
  sas: ""
  use_managed_identity: false
  container: ""
  path: ""
  compression_level: 1
  compression_format: tar
  sse_key: ""
  buffer_size: 0
  buffer_count: 3`
Slach commented 2 years ago

try 1.4.2 and 

sudo  bash -c 'LOG_LEVEL=debug ./clickhouse-backup -c config.yml restore last_backup_09_06'
metaIhead commented 2 years ago

Unfortunately the error persisted.

sudo  bash -c 'LOG_LEVEL=debug ./clickhouse-backup -c config.yml restore last_backup_09_06'
2022/06/09 20:51:04.358005  info SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2022/06/09 20:51:04.363271  info SELECT * FROM system.disks;
2022/06/09 20:51:04.367576  info CREATE DATABASE IF NOT EXISTS my_database
ENGINE = Atomic
2022/06/09 20:51:04.369315  info SELECT engine FROM system.databases WHERE name = 'my_database'
2022/06/09 20:51:04.371918  info DROP TABLE IF EXISTS `my_database`.`my_table1` NO DELAY
2022/06/09 20:51:04.373584  info SELECT engine FROM system.databases WHERE name = 'my_database'
2022/06/09 20:51:04.376377  info DROP TABLE IF EXISTS `my_database`.`my_table2` NO DELAY
2022/06/09 20:51:04.378646  info CREATE DATABASE IF NOT EXISTS `my_database`
2022/06/09 20:51:04.381145  info CREATE TABLE my_database.my_table1
(
    `instance_object_id` UInt32,
    `config` String,
    `config_checksum` String,
    `config_size` UInt32,
    `added_lines_number` UInt16,
    `deleted_lines_number` UInt16,
    `updated_lines_number` UInt16,
    `total_lines_number` UInt16,
    `statistic_time` DateTime
)
ENGINE = ReplacingMergeTree(statistic_time)
ORDER BY (instance_object_id, statistic_time)
TTL statistic_time + toIntervalYear(5)
SETTINGS index_granularity = 8192
2022/06/09 20:51:04.386722  info CREATE DATABASE IF NOT EXISTS `my_database`
2022/06/09 20:51:04.389555  info CREATE TABLE my_database.my_table2
(
    `instance_object_id` UInt32,
    `config` String,
    `config_checksum` String,
    `config_size` UInt32,
    `added_lines_number` UInt16,
    `deleted_lines_number` UInt16,
    `updated_lines_number` UInt16,
    `total_lines_number` UInt16,
    `statistic_time` DateTime
)
ENGINE = ReplacingMergeTree(statistic_time)
ORDER BY instance_object_id
SETTINGS index_granularity = 8192
2022/06/09 20:51:04.396993 debug found 2 tables with data in backup backup=last_backup_09_06 operation=restore
2022/06/09 20:51:04.397037  info SELECT count() FROM system.settings WHERE name = 'show_table_uuid_in_table_create_query_if_not_nil'
2022/06/09 20:51:04.401099  info SELECT name FROM system.databases WHERE engine IN ('MySQL','PostgreSQL')
2022/06/09 20:51:04.405041  info 
        SELECT 
            countIf(name='data_path') is_data_path_present, 
            countIf(name='data_paths') is_data_paths_present, 
            countIf(name='uuid') is_uuid_present, 
            countIf(name='create_table_query') is_create_table_query_present, 
            countIf(name='total_bytes') is_total_bytes_present 
        FROM system.columns WHERE database='system' AND table='tables'

2022/06/09 20:51:04.409226  info SELECT database, name, engine   FROM system.tables WHERE is_temporary = 0 SETTINGS show_table_uuid_in_table_create_query_if_not_nil=1
2022/06/09 20:51:04.418065  info SELECT sum(bytes_on_disk) as size FROM system.parts WHERE active AND database='my_database' AND table='my_table1' GROUP BY database, table
2022/06/09 20:51:04.422613  info SELECT sum(bytes_on_disk) as size FROM system.parts WHERE active AND database='my_database' AND table='my_table2' GROUP BY database, table
2022/06/09 20:51:04.426193 debug MkDirAll detached/all_10009_10012_1 operation=CopyDataToDetached
2022/06/09 20:51:04.426370 debug MkDir detached/all_10009_10012_1 operation=CopyDataToDetached
2022/06/09 20:51:04.426410 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/checksums.txt -> detached/all_10009_10012_1/checksums.txt operation=CopyDataToDetached
2022/06/09 20:51:04.426446 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/columns.txt -> detached/all_10009_10012_1/columns.txt operation=CopyDataToDetached
2022/06/09 20:51:04.426481 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/count.txt -> detached/all_10009_10012_1/count.txt operation=CopyDataToDetached
2022/06/09 20:51:04.426508 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/data.bin -> detached/all_10009_10012_1/data.bin operation=CopyDataToDetached
2022/06/09 20:51:04.426549 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/data.mrk3 -> detached/all_10009_10012_1/data.mrk3 operation=CopyDataToDetached
2022/06/09 20:51:04.426579 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/default_compression_codec.txt -> detached/all_10009_10012_1/default_compression_codec.txt operation=CopyDataToDetached
2022/06/09 20:51:04.426611 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/primary.idx -> detached/all_10009_10012_1/primary.idx operation=CopyDataToDetached
2022/06/09 20:51:04.426739 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/ttl.txt -> detached/all_10009_10012_1/ttl.txt operation=CopyDataToDetached
2022/06/09 20:51:04.426764 debug MkDirAll detached/all_10013_10013_0 operation=CopyDataToDetached
2022/06/09 20:51:04.426852 debug MkDir detached/all_10013_10013_0 operation=CopyDataToDetached
2022/06/09 20:51:04.426892 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10013_10013_0/checksums.txt -> detached/all_10013_10013_0/checksums.txt operation=CopyDataToDetached
2022/06/09 20:51:04.426927 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10013_10013_0/columns.txt -> detached/all_10013_10013_0/columns.txt operation=CopyDataToDetached
2022/06/09 20:51:04.426959 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10013_10013_0/count.txt -> detached/all_10013_10013_0/count.txt operation=CopyDataToDetached
2022/06/09 20:51:04.426983 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10013_10013_0/data.bin -> detached/all_10013_10013_0/data.bin operation=CopyDataToDetached
2022/06/09 20:51:04.427007 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10013_10013_0/data.mrk3 -> detached/all_10013_10013_0/data.mrk3 operation=CopyDataToDetached
2022/06/09 20:51:04.427040 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10013_10013_0/default_compression_codec.txt -> detached/all_10013_10013_0/default_compression_codec.txt operation=CopyDataToDetached
2022/06/09 20:51:04.427065 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10013_10013_0/primary.idx -> detached/all_10013_10013_0/primary.idx operation=CopyDataToDetached
2022/06/09 20:51:04.427087 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10013_10013_0/ttl.txt -> detached/all_10013_10013_0/ttl.txt operation=CopyDataToDetached
2022/06/09 20:51:04.427121 debug MkDirAll detached/all_10014_10014_0 operation=CopyDataToDetached
2022/06/09 20:51:04.427196 debug MkDir detached/all_10014_10014_0 operation=CopyDataToDetached
2022/06/09 20:51:04.427222 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10014_10014_0/checksums.txt -> detached/all_10014_10014_0/checksums.txt operation=CopyDataToDetached
2022/06/09 20:51:04.427257 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10014_10014_0/columns.txt -> detached/all_10014_10014_0/columns.txt operation=CopyDataToDetached
2022/06/09 20:51:04.427281 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10014_10014_0/count.txt -> detached/all_10014_10014_0/count.txt operation=CopyDataToDetached
2022/06/09 20:51:04.427343 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10014_10014_0/data.bin -> detached/all_10014_10014_0/data.bin operation=CopyDataToDetached
2022/06/09 20:51:04.427380 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10014_10014_0/data.mrk3 -> detached/all_10014_10014_0/data.mrk3 operation=CopyDataToDetached
2022/06/09 20:51:04.427415 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10014_10014_0/default_compression_codec.txt -> detached/all_10014_10014_0/default_compression_codec.txt operation=CopyDataToDetached
2022/06/09 20:51:04.427444 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10014_10014_0/primary.idx -> detached/all_10014_10014_0/primary.idx operation=CopyDataToDetached
2022/06/09 20:51:04.427473 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10014_10014_0/ttl.txt -> detached/all_10014_10014_0/ttl.txt operation=CopyDataToDetached
2022/06/09 20:51:04.427503 debug MkDirAll detached/all_10015_10015_0 operation=CopyDataToDetached
2022/06/09 20:51:04.427583 debug MkDir detached/all_10015_10015_0 operation=CopyDataToDetached
2022/06/09 20:51:04.427602 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10015_10015_0/checksums.txt -> detached/all_10015_10015_0/checksums.txt operation=CopyDataToDetached
2022/06/09 20:51:04.427641 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10015_10015_0/columns.txt -> detached/all_10015_10015_0/columns.txt operation=CopyDataToDetached
2022/06/09 20:51:04.427669 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10015_10015_0/count.txt -> detached/all_10015_10015_0/count.txt operation=CopyDataToDetached
2022/06/09 20:51:04.427699 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10015_10015_0/data.bin -> detached/all_10015_10015_0/data.bin operation=CopyDataToDetached
2022/06/09 20:51:04.427735 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10015_10015_0/data.mrk3 -> detached/all_10015_10015_0/data.mrk3 operation=CopyDataToDetached
2022/06/09 20:51:04.427759 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10015_10015_0/default_compression_codec.txt -> detached/all_10015_10015_0/default_compression_codec.txt operation=CopyDataToDetached
2022/06/09 20:51:04.427795 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10015_10015_0/primary.idx -> detached/all_10015_10015_0/primary.idx operation=CopyDataToDetached
2022/06/09 20:51:04.427825 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10015_10015_0/ttl.txt -> detached/all_10015_10015_0/ttl.txt operation=CopyDataToDetached
2022/06/09 20:51:04.427851 debug MkDirAll detached/all_1_10008_7770 operation=CopyDataToDetached
2022/06/09 20:51:04.427955 debug MkDir detached/all_1_10008_7770 operation=CopyDataToDetached
2022/06/09 20:51:04.427980 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/added_lines_number.bin -> detached/all_1_10008_7770/added_lines_number.bin operation=CopyDataToDetached
2022/06/09 20:51:04.428010 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/added_lines_number.mrk2 -> detached/all_1_10008_7770/added_lines_number.mrk2 operation=CopyDataToDetached
2022/06/09 20:51:04.428045 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/checksums.txt -> detached/all_1_10008_7770/checksums.txt operation=CopyDataToDetached
2022/06/09 20:51:04.428071 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/columns.txt -> detached/all_1_10008_7770/columns.txt operation=CopyDataToDetached
2022/06/09 20:51:04.428107 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/config.bin -> detached/all_1_10008_7770/config.bin operation=CopyDataToDetached
2022/06/09 20:51:04.428132 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/config.mrk2 -> detached/all_1_10008_7770/config.mrk2 operation=CopyDataToDetached
2022/06/09 20:51:04.428159 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/config_checksum.bin -> detached/all_1_10008_7770/config_checksum.bin operation=CopyDataToDetached
2022/06/09 20:51:04.428194 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/config_checksum.mrk2 -> detached/all_1_10008_7770/config_checksum.mrk2 operation=CopyDataToDetached
2022/06/09 20:51:04.428223 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/config_size.bin -> detached/all_1_10008_7770/config_size.bin operation=CopyDataToDetached
2022/06/09 20:51:04.428265 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/config_size.mrk2 -> detached/all_1_10008_7770/config_size.mrk2 operation=CopyDataToDetached
2022/06/09 20:51:04.428293 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/count.txt -> detached/all_1_10008_7770/count.txt operation=CopyDataToDetached
2022/06/09 20:51:04.428336 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/default_compression_codec.txt -> detached/all_1_10008_7770/default_compression_codec.txt operation=CopyDataToDetached
2022/06/09 20:51:04.428363 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/deleted_lines_number.bin -> detached/all_1_10008_7770/deleted_lines_number.bin operation=CopyDataToDetached
2022/06/09 20:51:04.428391 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/deleted_lines_number.mrk2 -> detached/all_1_10008_7770/deleted_lines_number.mrk2 operation=CopyDataToDetached
2022/06/09 20:51:04.428428 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/instance_object_id.bin -> detached/all_1_10008_7770/instance_object_id.bin operation=CopyDataToDetached
2022/06/09 20:51:04.428455 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/instance_object_id.mrk2 -> detached/all_1_10008_7770/instance_object_id.mrk2 operation=CopyDataToDetached
2022/06/09 20:51:04.428488 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/primary.idx -> detached/all_1_10008_7770/primary.idx operation=CopyDataToDetached
2022/06/09 20:51:04.428517 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/statistic_time.bin -> detached/all_1_10008_7770/statistic_time.bin operation=CopyDataToDetached
2022/06/09 20:51:04.428560 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/statistic_time.mrk2 -> detached/all_1_10008_7770/statistic_time.mrk2 operation=CopyDataToDetached
2022/06/09 20:51:04.428589 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/total_lines_number.bin -> detached/all_1_10008_7770/total_lines_number.bin operation=CopyDataToDetached
2022/06/09 20:51:04.428615 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/total_lines_number.mrk2 -> detached/all_1_10008_7770/total_lines_number.mrk2 operation=CopyDataToDetached
2022/06/09 20:51:04.428651 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/ttl.txt -> detached/all_1_10008_7770/ttl.txt operation=CopyDataToDetached
2022/06/09 20:51:04.428678 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/updated_lines_number.bin -> detached/all_1_10008_7770/updated_lines_number.bin operation=CopyDataToDetached
2022/06/09 20:51:04.428718 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/updated_lines_number.mrk2 -> detached/all_1_10008_7770/updated_lines_number.mrk2 operation=CopyDataToDetached
2022/06/09 20:51:04.428743 debug done                      duration=3ms operation=CopyDataToDetached
2022/06/09 20:51:04.428759 debug copied data to 'detached' backup=last_backup_09_06 operation=restore table=my_database.my_table1
2022/06/09 20:51:04.428775  info ALTER TABLE `my_database`.`my_table1` ATTACH PART 'all_10009_10012_1'
2022/06/09 20:51:04.431436 error can't attach partitions for table 'my_database.my_table1': code: 233, message: Detached part "all_10009_10012_1" not found
Slach commented 2 years ago

ls -la /var/lib/clickhouse/data/my_database/my_table1/detached/all_10009_10012_1

in logs i see

2022/06/09 20:51:04.426410 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/checksums.txt -> detached/all_10009_10012_1/checksums.txt operation=CopyDataToDetached
2022/06/09 20:51:04.426446 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/columns.txt -> detached/all_10009_10012_1/columns.txt operation=CopyDataToDetached
2022/06/09 20:51:04.426481 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/count.txt -> detached/all_10009_10012_1/count.txt operation=CopyDataToDetached
2022/06/09 20:51:04.426508 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/data.bin -> detached/all_10009_10012_1/data.bin operation=CopyDataToDetached
2022/06/09 20:51:04.426549 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/data.mrk3 -> detached/all_10009_10012_1/data.mrk3 operation=CopyDataToDetached
2022/06/09 20:51:04.426579 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/default_compression_codec.txt -> detached/all_10009_10012_1/default_compression_codec.txt operation=CopyDataToDetached
2022/06/09 20:51:04.426611 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/primary.idx -> detached/all_10009_10012_1/primary.idx operation=CopyDataToDetached
2022/06/09 20:51:04.426739 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/ttl.txt -> detached/all_10009_10012_1/ttl.txt operation=CopyDataToDetached
metaIhead commented 2 years ago

no dir all_10009_10012_1

ls -la /var/lib/clickhouse/data/my_database/my_table1/detached/
total 8
drwxr-x--- 2 clickhouse clickhouse 4096 Jun  9 21:12 .
drwxr-x--- 3 clickhouse clickhouse 4096 Jun  9 21:12 ..
metaIhead commented 2 years ago

directory detached/all_10009_10012_1 is located next to the script clickhouse-backup

ls -la /home/user/build/linux/amd64
total 39220
39212 -rwxr-xr-x 1 i.kolomin  i.kolomin  40150852 Jun  8 16:27 clickhouse-backup
    4 -rw-r--r-- 1 root       root            446 Jun  9 20:48 config.yml
    4 drwxr-x--- 7 clickhouse clickhouse     4096 Jun  9 20:51 detached
ls -la /home/user/build/linux/amd64/detached/all_10009_10012_1
total 40
drwxr-x--- 2 clickhouse clickhouse 4096 Jun  9 20:51 .
drwxr-x--- 7 clickhouse clickhouse 4096 Jun  9 20:51 ..
-r--r----- 3 clickhouse clickhouse  214 Jun  8 22:09 checksums.txt
-r--r----- 3 clickhouse clickhouse  269 Jun  8 22:09 columns.txt
-r--r----- 3 clickhouse clickhouse    1 Jun  8 22:09 count.txt
-r--r----- 3 clickhouse clickhouse  582 Jun  8 22:09 data.bin
-r--r----- 3 clickhouse clickhouse  304 Jun  8 22:09 data.mrk3
-r--r----- 3 clickhouse clickhouse   10 Jun  8 22:09 default_compression_codec.txt
-r--r----- 3 clickhouse clickhouse   16 Jun  8 22:09 primary.idx
-r--r----- 3 clickhouse clickhouse   67 Jun  8 22:09 ttl.txt
.
└── detached
    ├── all_10009_10012_1
    ├── all_10013_10013_0
    ├── all_10014_10014_0
    ├── all_10015_10015_0
    └── all_1_10008_7770
Slach commented 2 years ago

How do you install clickhouse-backup on destination server?

is clickhouse-server run inside docker?

metaIhead commented 2 years ago 
wget https://github.com/AlexAkulov/clickhouse-backup/releases/download/v1.4.2/clickhouse-backup-linux-amd64.tar.gz
tar -xf clickhouse-backup-linux-amd64.tar.gz
touch build/linux/amd64/config.yml

After, fill config.yml and run sudo build/linux/amd64/clickhouse-backup -c config.yaml

clickhouse-server NOT in docker

metaIhead commented 2 years ago

something wrong in installation?

Slach commented 2 years ago

could you share

SELECT * FROM system.disks

from source and from destination server?

metaIhead commented 2 years ago

DST

SELECT * FROM system.disks;

SELECT *
FROM system.disks

Query id: f5e68ea8-7078-41df-84c3-46caeede42b4

┌─name────┬─path─────────────────┬─free_space─┬─total_space─┬─keep_free_space─┬─type──┐
│ default │ /var/lib/clickhouse/ │ 4672471040 │ 14374207488 │               0 │ local │
└─────────┴──────────────────────┴────────────┴─────────────┴─────────────────┴───────┘

SRC

SELECT * FROM system.disks;

SELECT *
FROM system.disks

Query id: 10e932d4-6cd2-4b8d-81e8-86ed217672b5

┌─name────┬─path─────────────────┬──free_space─┬─total_space─┬─keep_free_space─┬─type──┐
│ default │ /var/lib/clickhouse/ │ 24787795968 │ 61184409600 │               0 │ local │
└─────────┴──────────────────────┴─────────────┴─────────────┴─────────────────┴───────┘
Slach commented 2 years ago

looks really weird according to https://github.com/AlexAkulov/clickhouse-backup/blob/master/pkg/filesystemhelper/filesystemhelper.go#L116

relative "detached" folder will use only if map dstDataPaths doesn't contain key backupDisk.Name

could you share from destination server:

cat /var/lib/clickhouse/backups/last_backup_09_06/metadata/my_database/my_table1.json
metaIhead commented 2 years ago

cat /var/lib/clickhouse/backup/last_backup_09_06/metadata/my_database/my_table1.json

{
 "table": "my_table1",
 "database": "my_database",
 "parts": {
  "default": [
   {
    "name": "all_10009_10012_1"
   },
   {
    "name": "all_10013_10013_0"
   },
   {
    "name": "all_10014_10014_0"
   },
   {
    "name": "all_10015_10015_0"
   },
   {
    "name": "all_1_10008_7770"
   }
  ]
 },
 "query": "CREATE TABLE my_database.my_table1\n(\n    `instance_object_id` UInt32,\n    `config` String,\n    `config_checksum` String,\n    `config_size` UInt32,\n    `added_lines_number` UInt16,\n    `deleted_lines_number` UInt16,\n    `updated_lines_number` UInt16,\n    `total_lines_number` UInt16,\n    `statistic_time` DateTime\n)\nENGINE = ReplacingMergeTree(statistic_time)\nORDER BY (instance_object_id, statistic_time)\nTTL statistic_time + toIntervalYear(5)\nSETTINGS index_granularity = 8192",
 "size": {
  "default": 1290363
 },
 "total_bytes": 1287333,
 "metadata_only": false
Slach commented 2 years ago

ok. let's try to setup config.yml more explicitly

clickhouse:
  disk_mapping:
    default: /var/lib/clickhouse

and try to run LOG_LEVEL=debug clickhouse-backup restore --rm last_backup_09_06 again and share the log

metaIhead commented 2 years ago

cat config.yml

clickhouse: 
  disk_mapping: 
    default: /var/lib/clickhouse
  username: username
  host: localhost
  password: somepass
  port: 9123
  skip_tables: 
    - system.*
    - INFORMATION_SCHEMA.*
    - information_schema.*

LOG_LEVEL=debug sudo ./clickhouse-backup -c config.yml restore --rm last_backup_09_06

2022/06/10 09:47:45.578800  info SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2022/06/10 09:47:45.583418  info SELECT * FROM system.disks;
2022/06/10 09:47:45.585990  info CREATE DATABASE IF NOT EXISTS my_database
ENGINE = Atomic
2022/06/10 09:47:45.587888  info SELECT engine FROM system.databases WHERE name = 'my_database'
2022/06/10 09:47:45.589667  info DROP TABLE IF EXISTS `my_database`.`my_table1` NO DELAY
2022/06/10 09:47:45.593551  info SELECT engine FROM system.databases WHERE name = 'my_database'
2022/06/10 09:47:45.597134  info DROP TABLE IF EXISTS `my_database`.`my_table2` NO DELAY
2022/06/10 09:47:45.599598  info CREATE DATABASE IF NOT EXISTS `my_database`
2022/06/10 09:47:45.600824  info CREATE TABLE my_database.my_table1
(
    `instance_object_id` UInt32,
    `config` String,
    `config_checksum` String,
    `config_size` UInt32,
    `added_lines_number` UInt16,
    `deleted_lines_number` UInt16,
    `updated_lines_number` UInt16,
    `total_lines_number` UInt16,
    `statistic_time` DateTime
)
ENGINE = ReplacingMergeTree(statistic_time)
ORDER BY (instance_object_id, statistic_time)
TTL statistic_time + toIntervalYear(5)
SETTINGS index_granularity = 8192
2022/06/10 09:47:45.608578  info CREATE DATABASE IF NOT EXISTS `my_database`
2022/06/10 09:47:45.610393  info CREATE TABLE my_database.my_table2
(
    `instance_object_id` UInt32,
    `config` String,
    `config_checksum` String,
    `config_size` UInt32,
    `added_lines_number` UInt16,
    `deleted_lines_number` UInt16,
    `updated_lines_number` UInt16,
    `total_lines_number` UInt16,
    `statistic_time` DateTime
)
ENGINE = ReplacingMergeTree(statistic_time)
ORDER BY instance_object_id
SETTINGS index_granularity = 8192
2022/06/10 09:47:45.616351  info SELECT count() FROM system.settings WHERE name = 'show_table_uuid_in_table_create_query_if_not_nil'
2022/06/10 09:47:45.620502  info SELECT name FROM system.databases WHERE engine IN ('MySQL','PostgreSQL')
2022/06/10 09:47:45.623975  info 
        SELECT 
            countIf(name='data_path') is_data_path_present, 
            countIf(name='data_paths') is_data_paths_present, 
            countIf(name='uuid') is_uuid_present, 
            countIf(name='create_table_query') is_create_table_query_present, 
            countIf(name='total_bytes') is_total_bytes_present 
        FROM system.columns WHERE database='system' AND table='tables'

2022/06/10 09:47:45.628269  info SELECT database, name, engine   FROM system.tables WHERE is_temporary = 0 SETTINGS show_table_uuid_in_table_create_query_if_not_nil=1
2022/06/10 09:47:45.635939  info SELECT sum(bytes_on_disk) as size FROM system.parts WHERE active AND database='my_database' AND table='my_table1' GROUP BY database, table
2022/06/10 09:47:45.638730  info SELECT sum(bytes_on_disk) as size FROM system.parts WHERE active AND database='my_database' AND table='my_table2' GROUP BY database, table
2022/06/10 09:47:45.642033  info ALTER TABLE `my_database`.`my_table1` ATTACH PART 'all_10009_10012_1'
2022/06/10 09:47:45.644045 error can't attach partitions for table 'my_database.my_table1': code: 233, message: Detached part "all_10009_10012_1" not found
Slach commented 2 years ago

shared log is not debug log doesnt matter, i will try to reproduce locally, need time for it, thanks a lot for reporting

metaIhead commented 2 years ago

Thank you very much for answering) I can hope for your answer here based on the result?

Slach commented 2 years ago

yep

Slach commented 2 years ago

can't reproduce locally

clickhouse-backup-race.zip could you unpack this file

run

sudo bash -c 'LOG_LEVEL=debug ./clickhouse-backup-race -c config.yml restore --tables=my_database.my_table1 --rm last_backup_09_06'

and share results?

metaIhead commented 2 years ago 
sudo bash -c 'LOG_LEVEL=debug ./clickhouse-backup-race -c config.yml restore --tables=my_database.my_table1 --rm last_backup_09_06'
2022/06/10 15:51:06.648268  info SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2022/06/10 15:51:06.653652  info SELECT * FROM system.disks;
2022/06/10 15:51:06.659966  info CREATE DATABASE IF NOT EXISTS my_database
ENGINE = Atomic
2022/06/10 15:51:06.668580  info SELECT engine FROM system.databases WHERE name = 'my_database'
2022/06/10 15:51:06.672586  info DROP TABLE IF EXISTS `my_database`.`my_table1` NO DELAY
2022/06/10 15:51:06.675022  info CREATE DATABASE IF NOT EXISTS `my_database`
2022/06/10 15:51:06.678318  info CREATE TABLE my_database.my_table1
(
    `instance_object_id` UInt32,
    `config` String,
    `config_checksum` String,
    `config_size` UInt32,
    `added_lines_number` UInt16,
    `deleted_lines_number` UInt16,
    `updated_lines_number` UInt16,
    `total_lines_number` UInt16,
    `statistic_time` DateTime
)
ENGINE = ReplacingMergeTree(statistic_time)
ORDER BY (instance_object_id, statistic_time)
TTL statistic_time + toIntervalYear(5)
SETTINGS index_granularity = 8192
2022/06/10 15:51:06.690327 debug found 1 tables with data in backup backup=last_backup_09_06 operation=restore
2022/06/10 15:51:06.690531  info SELECT count() FROM system.settings WHERE name = 'show_table_uuid_in_table_create_query_if_not_nil'
2022/06/10 15:51:06.696462  info SELECT name FROM system.databases WHERE engine IN ('MySQL','PostgreSQL')
2022/06/10 15:51:06.702299  info 
        SELECT 
            countIf(name='data_path') is_data_path_present, 
            countIf(name='data_paths') is_data_paths_present, 
            countIf(name='uuid') is_uuid_present, 
            countIf(name='create_table_query') is_create_table_query_present, 
            countIf(name='total_bytes') is_total_bytes_present 
        FROM system.columns WHERE database='system' AND table='tables'

2022/06/10 15:51:06.706890  info SELECT database, name, engine   FROM system.tables WHERE is_temporary = 0 AND match(concat(database,'.',name),'my_database\.my_table1')  SETTINGS show_table_uuid_in_table_create_query_if_not_nil=1
2022/06/10 15:51:06.716183  info SELECT sum(bytes_on_disk) as size FROM system.parts WHERE active AND database='my_database' AND table='my_table1' GROUP BY database, table
2022/06/10 15:51:06.723303 debug GetDisksByPaths disks=[{default /var/lib/clickhouse/ local}] dataPaths=[]
2022/06/10 15:51:06.723426 debug GetDisksByPaths result=map[]
2022/06/10 15:51:06.723533 debug MkDirAll detached/all_10009_10012_1 operation=CopyDataToDetached
2022/06/10 15:51:06.723887 debug MkDir detached/all_10009_10012_1 operation=CopyDataToDetached
2022/06/10 15:51:06.724059 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/checksums.txt -> detached/all_10009_10012_1/checksums.txt operation=CopyDataToDetached
2022/06/10 15:51:06.724224 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/columns.txt -> detached/all_10009_10012_1/columns.txt operation=CopyDataToDetached
2022/06/10 15:51:06.724372 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/count.txt -> detached/all_10009_10012_1/count.txt operation=CopyDataToDetached
2022/06/10 15:51:06.724532 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/data.bin -> detached/all_10009_10012_1/data.bin operation=CopyDataToDetached
2022/06/10 15:51:06.724673 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/data.mrk3 -> detached/all_10009_10012_1/data.mrk3 operation=CopyDataToDetached
2022/06/10 15:51:06.724831 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/default_compression_codec.txt -> detached/all_10009_10012_1/default_compression_codec.txt operation=CopyDataToDetached
2022/06/10 15:51:06.724993 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/primary.idx -> detached/all_10009_10012_1/primary.idx operation=CopyDataToDetached
2022/06/10 15:51:06.725149 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10009_10012_1/ttl.txt -> detached/all_10009_10012_1/ttl.txt operation=CopyDataToDetached
2022/06/10 15:51:06.725283 debug MkDirAll detached/all_10013_10013_0 operation=CopyDataToDetached
2022/06/10 15:51:06.725563 debug MkDir detached/all_10013_10013_0 operation=CopyDataToDetached
2022/06/10 15:51:06.725724 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10013_10013_0/checksums.txt -> detached/all_10013_10013_0/checksums.txt operation=CopyDataToDetached
2022/06/10 15:51:06.725904 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10013_10013_0/columns.txt -> detached/all_10013_10013_0/columns.txt operation=CopyDataToDetached
2022/06/10 15:51:06.726058 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10013_10013_0/count.txt -> detached/all_10013_10013_0/count.txt operation=CopyDataToDetached
2022/06/10 15:51:06.726209 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10013_10013_0/data.bin -> detached/all_10013_10013_0/data.bin operation=CopyDataToDetached
2022/06/10 15:51:06.726352 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10013_10013_0/data.mrk3 -> detached/all_10013_10013_0/data.mrk3 operation=CopyDataToDetached
2022/06/10 15:51:06.726510 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10013_10013_0/default_compression_codec.txt -> detached/all_10013_10013_0/default_compression_codec.txt operation=CopyDataToDetached
2022/06/10 15:51:06.726663 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10013_10013_0/primary.idx -> detached/all_10013_10013_0/primary.idx operation=CopyDataToDetached
2022/06/10 15:51:06.726808 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10013_10013_0/ttl.txt -> detached/all_10013_10013_0/ttl.txt operation=CopyDataToDetached
2022/06/10 15:51:06.726947 debug MkDirAll detached/all_10014_10014_0 operation=CopyDataToDetached
2022/06/10 15:51:06.727220 debug MkDir detached/all_10014_10014_0 operation=CopyDataToDetached
2022/06/10 15:51:06.727385 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10014_10014_0/checksums.txt -> detached/all_10014_10014_0/checksums.txt operation=CopyDataToDetached
2022/06/10 15:51:06.727545 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10014_10014_0/columns.txt -> detached/all_10014_10014_0/columns.txt operation=CopyDataToDetached
2022/06/10 15:51:06.727694 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10014_10014_0/count.txt -> detached/all_10014_10014_0/count.txt operation=CopyDataToDetached
2022/06/10 15:51:06.727839 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10014_10014_0/data.bin -> detached/all_10014_10014_0/data.bin operation=CopyDataToDetached
2022/06/10 15:51:06.727994 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10014_10014_0/data.mrk3 -> detached/all_10014_10014_0/data.mrk3 operation=CopyDataToDetached
2022/06/10 15:51:06.728140 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10014_10014_0/default_compression_codec.txt -> detached/all_10014_10014_0/default_compression_codec.txt operation=CopyDataToDetached
2022/06/10 15:51:06.728289 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10014_10014_0/primary.idx -> detached/all_10014_10014_0/primary.idx operation=CopyDataToDetached
2022/06/10 15:51:06.728444 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10014_10014_0/ttl.txt -> detached/all_10014_10014_0/ttl.txt operation=CopyDataToDetached
2022/06/10 15:51:06.728587 debug MkDirAll detached/all_10015_10015_0 operation=CopyDataToDetached
2022/06/10 15:51:06.728851 debug MkDir detached/all_10015_10015_0 operation=CopyDataToDetached
2022/06/10 15:51:06.729067 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10015_10015_0/checksums.txt -> detached/all_10015_10015_0/checksums.txt operation=CopyDataToDetached
2022/06/10 15:51:06.729290 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10015_10015_0/columns.txt -> detached/all_10015_10015_0/columns.txt operation=CopyDataToDetached
2022/06/10 15:51:06.729511 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10015_10015_0/count.txt -> detached/all_10015_10015_0/count.txt operation=CopyDataToDetached
2022/06/10 15:51:06.729696 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10015_10015_0/data.bin -> detached/all_10015_10015_0/data.bin operation=CopyDataToDetached
2022/06/10 15:51:06.729850 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10015_10015_0/data.mrk3 -> detached/all_10015_10015_0/data.mrk3 operation=CopyDataToDetached
2022/06/10 15:51:06.730007 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10015_10015_0/default_compression_codec.txt -> detached/all_10015_10015_0/default_compression_codec.txt operation=CopyDataToDetached
2022/06/10 15:51:06.730168 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10015_10015_0/primary.idx -> detached/all_10015_10015_0/primary.idx operation=CopyDataToDetached
2022/06/10 15:51:06.730312 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_10015_10015_0/ttl.txt -> detached/all_10015_10015_0/ttl.txt operation=CopyDataToDetached
2022/06/10 15:51:06.730431 debug MkDirAll detached/all_1_10008_7770 operation=CopyDataToDetached
2022/06/10 15:51:06.730748 debug MkDir detached/all_1_10008_7770 operation=CopyDataToDetached
2022/06/10 15:51:06.730910 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/added_lines_number.bin -> detached/all_1_10008_7770/added_lines_number.bin operation=CopyDataToDetached
2022/06/10 15:51:06.731065 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/added_lines_number.mrk2 -> detached/all_1_10008_7770/added_lines_number.mrk2 operation=CopyDataToDetached
2022/06/10 15:51:06.731210 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/checksums.txt -> detached/all_1_10008_7770/checksums.txt operation=CopyDataToDetached
2022/06/10 15:51:06.731346 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/columns.txt -> detached/all_1_10008_7770/columns.txt operation=CopyDataToDetached
2022/06/10 15:51:06.731477 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/config.bin -> detached/all_1_10008_7770/config.bin operation=CopyDataToDetached
2022/06/10 15:51:06.731620 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/config.mrk2 -> detached/all_1_10008_7770/config.mrk2 operation=CopyDataToDetached
2022/06/10 15:51:06.731759 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/config_checksum.bin -> detached/all_1_10008_7770/config_checksum.bin operation=CopyDataToDetached
2022/06/10 15:51:06.731911 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/config_checksum.mrk2 -> detached/all_1_10008_7770/config_checksum.mrk2 operation=CopyDataToDetached
2022/06/10 15:51:06.732048 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/config_size.bin -> detached/all_1_10008_7770/config_size.bin operation=CopyDataToDetached
2022/06/10 15:51:06.732187 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/config_size.mrk2 -> detached/all_1_10008_7770/config_size.mrk2 operation=CopyDataToDetached
2022/06/10 15:51:06.732343 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/count.txt -> detached/all_1_10008_7770/count.txt operation=CopyDataToDetached
2022/06/10 15:51:06.732488 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/default_compression_codec.txt -> detached/all_1_10008_7770/default_compression_codec.txt operation=CopyDataToDetached
2022/06/10 15:51:06.732620 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/deleted_lines_number.bin -> detached/all_1_10008_7770/deleted_lines_number.bin operation=CopyDataToDetached
2022/06/10 15:51:06.732770 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/deleted_lines_number.mrk2 -> detached/all_1_10008_7770/deleted_lines_number.mrk2 operation=CopyDataToDetached
2022/06/10 15:51:06.732913 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/instance_object_id.bin -> detached/all_1_10008_7770/instance_object_id.bin operation=CopyDataToDetached
2022/06/10 15:51:06.733052 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/instance_object_id.mrk2 -> detached/all_1_10008_7770/instance_object_id.mrk2 operation=CopyDataToDetached
2022/06/10 15:51:06.733186 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/primary.idx -> detached/all_1_10008_7770/primary.idx operation=CopyDataToDetached
2022/06/10 15:51:06.733323 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/statistic_time.bin -> detached/all_1_10008_7770/statistic_time.bin operation=CopyDataToDetached
2022/06/10 15:51:06.733458 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/statistic_time.mrk2 -> detached/all_1_10008_7770/statistic_time.mrk2 operation=CopyDataToDetached
2022/06/10 15:51:06.733624 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/total_lines_number.bin -> detached/all_1_10008_7770/total_lines_number.bin operation=CopyDataToDetached
2022/06/10 15:51:06.733761 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/total_lines_number.mrk2 -> detached/all_1_10008_7770/total_lines_number.mrk2 operation=CopyDataToDetached
2022/06/10 15:51:06.733915 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/ttl.txt -> detached/all_1_10008_7770/ttl.txt operation=CopyDataToDetached
2022/06/10 15:51:06.734058 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/updated_lines_number.bin -> detached/all_1_10008_7770/updated_lines_number.bin operation=CopyDataToDetached
2022/06/10 15:51:06.734203 debug Link /var/lib/clickhouse/backup/last_backup_09_06/shadow/my_database/my_table1/default/all_1_10008_7770/updated_lines_number.mrk2 -> detached/all_1_10008_7770/updated_lines_number.mrk2 operation=CopyDataToDetached
2022/06/10 15:51:06.734319 debug done                      duration=11ms operation=CopyDataToDetached
2022/06/10 15:51:06.734433 debug copied data to 'detached' backup=last_backup_09_06 operation=restore table=my_database.my_table1
2022/06/10 15:51:06.734575  info ALTER TABLE `my_database`.`my_table1` ATTACH PART 'all_10009_10012_1'
2022/06/10 15:51:06.739301 error can't attach partitions for table 'my_database.my_table1': code: 233, message: Detached part "all_10009_10012_1" not found
Slach commented 2 years ago

root cause 

2022/06/10 15:51:06.723303 debug GetDisksByPaths disks=[{default /var/lib/clickhouse/ local}] dataPaths=[]

dataPaths shall be not empty after table creation

could you share SELECT version() from source and destination clickhouse-server?

metaIhead commented 2 years ago

source

┌─version()─┐
│ 21.2.2.8  │
└───────────┘

destination

┌─version()─┐
│ 22.2.2.1  │
└───────────┘
metaIhead commented 2 years ago

I need same server versions to backup?

Slach commented 2 years ago

no, you can restore on different version

could you share

SELECT 
            countIf(name='data_path') is_data_path_present, 
            countIf(name='data_paths') is_data_paths_present, 
            countIf(name='uuid') is_uuid_present, 
            countIf(name='create_table_query') is_create_table_query_present, 
            countIf(name='total_bytes') is_total_bytes_present 
        FROM system.columns WHERE database='system' AND table='tables'
metaIhead commented 2 years ago

Dst

SELECT
    countIf(name = 'data_path') AS is_data_path_present,
    countIf(name = 'data_paths') AS is_data_paths_present,
    countIf(name = 'uuid') AS is_uuid_present,
    countIf(name = 'create_table_query') AS is_create_table_query_present,
    countIf(name = 'total_bytes') AS is_total_bytes_present
FROM system.columns
WHERE (database = 'system') AND (table = 'tables')

Query id: f1b313f0-271f-4bed-a934-ce0b11e628af

┌─is_data_path_present─┬─is_data_paths_present─┬─is_uuid_present─┬─is_create_table_query_present─┬─is_total_bytes_present─┐
│                    0 │                     0 │               0 │                             0 │                      0 │
└──────────────────────┴───────────────────────┴─────────────────┴───────────────────────────────┴────────────────────────┘
Slach commented 2 years ago

Will try to fix in Monday

could you share from destination server?

SHOW CREATE TABLE system.tables;
SELECT count() FROM system.columns WHERE database='system';
Slach commented 2 years ago

@metaIhead looks strange i tried version 22.2.2.1 and

SELECT
    countIf(name = 'data_path') AS is_data_path_present,
    countIf(name = 'data_paths') AS is_data_paths_present,
    countIf(name = 'uuid') AS is_uuid_present,
    countIf(name = 'create_table_query') AS is_create_table_query_present,
    countIf(name = 'total_bytes') AS is_total_bytes_present
FROM system.columns
WHERE (database = 'system') AND (table = 'tables')

return different results

┌─is_data_path_present─┬─is_data_paths_present─┬─is_uuid_present─┬─is_create_table_query_present─┬─is_total_bytes_present─┐
│                    0 │                     1 │               1 │                             1 │                      1 │
└──────────────────────┴───────────────────────┴─────────────────┴───────────────────────────────┴────────────────────────┘

could you share queries which i asked here https://github.com/AlexAkulov/clickhouse-backup/issues/453#issuecomment-1157896182 ?

Slach commented 2 years ago

which settings you changed in your destination server?

Slach commented 2 years ago

@metaIhead is issue still actual for you?

metaIhead commented 2 years ago

yes, issue still actual for me.

metaIhead commented 2 years ago

which settings you changed in your destination server?

Can you explain question?

Slach commented 2 years ago

You have abnormal behavior when system.tables is not represented inside system.columns, so I try to figure out how it possible, cause i have different behavior on the same clickhouse-server version https://github.com/AlexAkulov/clickhouse-backup/issues/453#issuecomment-1160042619

please share

SHOW CREATE TABLE system.tables;
SELECT count() FROM system.columns WHERE database='system';
SELECT * FROM system.settings WHERE changed FORMAT Vertical;
Slach commented 2 years ago

@metaIhead any news?

metaIhead commented 2 years ago

From DST server

SHOW CREATE TABLE system.tables

Query id: fc698842-cd29-4a51-971a-24ce422b5c4b

0 rows in set. Elapsed: 0.018 sec. 

Received exception from server (version 22.2.2):
Code: 497. DB::Exception: Received from localhost:9123. DB::Exception: my_database: Not enough privileges. To execute this query it's necessary to have grant SHOW COLUMNS ON system.tables. (ACCESS_DENIED)
SELECT count()
FROM system.columns
WHERE database = 'system'

Query id: c7ffb91b-c66c-4c0c-8c08-fd41ea04bfc8

┌─count()─┐
│       0 │
└─────────┘
SELECT *
FROM system.settings
WHERE changed
FORMAT Vertical

Query id: 16f14ff4-96be-4351-be34-4e633bb8d99b

Row 1:
──────
name:        use_uncompressed_cache
value:       0
changed:     1
description: Whether to use the cache of uncompressed blocks.
min:         ᴺᵁᴸᴸ
max:         ᴺᵁᴸᴸ
readonly:    0
type:        Bool

Row 2:
──────
name:        load_balancing
value:       random
changed:     1
description: Which replicas (among healthy replicas) to preferably send a query to (on the first attempt) for distributed processing.
min:         ᴺᵁᴸᴸ
max:         ᴺᵁᴸᴸ
readonly:    0
type:        LoadBalancing

Row 3:
──────
name:        max_memory_usage
value:       10000000000
changed:     1
description: Maximum memory usage for processing of single query. Zero means unlimited.
min:         ᴺᵁᴸᴸ
max:         ᴺᵁᴸᴸ
readonly:    0
type:        UInt64

Row 4:
──────
name:        max_partitions_per_insert_block
value:       100
changed:     1
description: Limit maximum number of partitions in single INSERTed block. Zero means unlimited. Throw exception if the block contains too many partitions. This setting is a safety threshold, because using large number of partitions is a common misconception.
min:         ᴺᵁᴸᴸ
max:         ᴺᵁᴸᴸ
readonly:    0
type:        UInt64
Slach commented 2 years ago

ok. looks like you changed default profile access grants and root cause is not related to clickhouse itself

could you share

ls -la /var/lib/clichouse/access
ls -la /etc/clickhouse-server/users.d/
metaIhead commented 2 years ago 
ls -la /var/lib/clichouse/access
ls: cannot access '/var/lib/clichouse/access': No such file or directory


ls -la /etc/clickhouse-server/users.d/
total 8
dr-x------ 2 clickhouse clickhouse 4096 May 27 14:45 .
drwx------ 5 clickhouse clickhouse 4096 Jun 23 12:23 ..
Slach commented 2 years ago

sorry, clichouse was wrong

ls -la /var/lib/clickhouse/access and cat /var/lib/clickhouse/preprocessed_configs/users.xml without sensitive credentials?

metaIhead commented 2 years ago 
cat /var/lib/clickhouse/preprocessed_configs/users.xml
<!-- This file was generated automatically.
     Do not edit it: it is likely to be discarded and generated again before it's read next time.
     Files used to generate this file:
       /etc/clickhouse-server/users.xml      -->

<!-- Ansible managed: modified on 2022-05-30 11:46:18 --><yandex>
   <profiles>
    <!-- Profiles of settings. -->
    <!-- Default profiles. -->
        <default>
            <max_memory_usage>10000000000</max_memory_usage>
            <use_uncompressed_cache>0</use_uncompressed_cache>
            <load_balancing>random</load_balancing>
            <max_partitions_per_insert_block>100</max_partitions_per_insert_block>
        </default>
        <readonly>
            <readonly>1</readonly>
        </readonly>
        <!-- Default profiles end. -->
    <!-- Custom profiles. -->
        <!-- Custom profiles end. -->
    </profiles>

    <!-- Users and ACL. -->
    <users>
    <!-- Default users. -->
            <!-- Default user for login if user not defined -->
        <default>
                <password/>
                <networks incl="networks" replace="replace">
                <ip>::1</ip>
                <ip>127.0.0.1</ip>
                </networks>
        <profile>default</profile>
        <quota>default</quota>
            </default>
            <!-- Example of user with readonly access -->
        <readonly>
                <password/>
                <networks incl="networks" replace="replace">
                <ip>::1</ip>
                <ip>127.0.0.1</ip>
                </networks>
        <profile>default</profile>
        <quota>default</quota>
            </readonly>
        <!-- Custom users. -->
            <my_user>
                <password>my_pass</password>
                <networks incl="networks" replace="replace">
                <ip>0.0.0.0/0</ip>
                </networks>
        <profile>default</profile>
        <quota>default</quota>
                 <allow_databases>
                    <database>my_database</database>
                </allow_databases>
            </my_user>
        </users>

    <!-- Quotas. -->
    <quotas>
        <!-- Default quotas. -->
        <default>
            <duration>3600</duration>
        <queries>0</queries>
        <errors>0</errors>
        <result_rows>0</result_rows>
        <read_rows>0</read_rows>
        <execution_time>0</execution_time>
        </default>
            </quotas>

ls -la /var/lib/clickhouse/access
ls: cannot access '/var/lib/clickhouse/access': No such file or directory
Slach commented 2 years ago

Do you try to restore data with my_user? It restricted with 

              <allow_databases>
                    <database>my_database</database>
                </allow_databases>

change your /etc/clickhouse-backup/config.yml clickhouse section credentials to

clickhouse:
  user: default
  password: ""

and try to restore

metaIhead commented 2 years ago

Do you try to restore data with my_user? It restricted with

              <allow_databases>
                    <database>my_database</database>
                </allow_databases>

change your /etc/clickhouse-backup/config.yml clickhouse section credentials to

clickhouse:
  user: default
  password: ""

and try to restore

Yes, i try to restore with "my_user"

Slach commented 2 years ago

is restore sucessfull with default user as i mentioned on https://github.com/AlexAkulov/clickhouse-backup/issues/453#issuecomment-1169818298 ?