search

Altinity / clickhouse-backup

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

clickhouse-backup(on k8s) is not working, only the metadata. #480

Closed myabcc17 closed 2 years ago

myabcc17 commented 2 years ago

i'm install clickhouse-backup using https://github.com/AlexAkulov/clickhouse-backup/blob/master/Examples.md#how-to-use-clickhouse-backup-in-kubernetes

clickhouse.yaml is below

apiVersion: "clickhouse.altinity.com/v1"
kind: "ClickHouseInstallation"
metadata:
  name: "builder"
spec:
  configuration:
    users:
        backup/networks/ip: 0.0.0.0/0      
        backup/password_sha256_hex: eb94c11d77f46a0290ba8c4fca1a7fd315b72e1e6c83146e42117c568cc3ea4d
    zookeeper:
      nodes:
        - host: zookeeper.zoo3ns
          port: 2181
    clusters:
      - name: "builder"
        layout:
          shardsCount: 3
          replicasCount: 2
        templates:
          podTemplate: clickhouse-stable
  templates:
    podTemplates:
      - name: clickhouse-stable
        spec:
          securityContext:
            runAsUser: 101
            runAsGroup: 101
            fsGroup: 101
          containers:
          - name: clickhouse
            image: altinity/clickhouse-server:21.8.10.1.altinitystable
            command:
              - clickhouse-server
              - --config-file=/etc/clickhouse-server/config.xml
            volumeMounts:
              - name: storage-vc-template
                mountPath: /var/lib/clickhouse
          - name: clickhouse-backup
            image: altinity/clickhouse-backup:master
            imagePullPolicy: Always
            command:
              - bash
              - -xc
              - "/bin/clickhouse-backup server"
            env:
              - name: LOG_LEVEL
                value: "debug"
              - name: ALLOW_EMPTY_BACKUPS
                value: "true"
              - name: API_LISTEN
                value: "0.0.0.0:7171"
              # INSERT INTO system.backup_actions to execute backup
              - name: API_CREATE_INTEGRATION_TABLES
                value: "true"
              - name: BACKUPS_TO_KEEP_REMOTE
                value: "3"
              - name: RESTORE_SCHEMA_ON_CLUSTER
                value: "builder"
              # change it for production S3
              - name: REMOTE_STORAGE
                value: "s3"
              - name: S3_ACL
                value: "private"
              - name: S3_ENDPOINT
                value: ****
              - name: S3_BUCKET
                value: clickhouse
              - name: S3_PATH
                value: backup
              - name: S3_ACCESS_KEY
                value: openbuilder
              - name: ****
                value: ****
              - name: S3_FORCE_PATH_STYLE
                value: "true"
              # remove it for production S3
              - name: S3_DISABLE_SSL
                value: "true"
              - name: S3_DEBUG
                value: "true"
            # require to avoid double scraping clickhouse and clickhouse-backup containers
            ports:
              - name: backup-rest
                containerPort: 7171
    volumeClaimTemplates:
      - name: storage-vc-template
        spec:
          accessModes:
            - ReadWriteOnce
          resources:
            requests:
              storage: 1Gi

I want to backup a test table and only the metadata gets backed up.

This is the config in use:

general:
  remote_storage: s3
  max_file_size: 0
  disable_progress_bar: true
  backups_to_keep_local: 0
  backups_to_keep_remote: 3
  log_level: debug
  allow_empty_backups: true
  download_concurrency: 4
  upload_concurrency: 4
  restore_schema_on_cluster: builder
  upload_by_part: true
  download_by_part: true
clickhouse:
  username: default
  password: ""
  host: localhost
  port: 9000
  disk_mapping: {}
  skip_tables:
  - system.*
  - INFORMATION_SCHEMA.*
  - information_schema.*
  timeout: 5m
  freeze_by_part: false
  freeze_by_part_where: ""
  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
  check_replicas_before_attach: false
  tls_key: ""
  tls_cert: ""
  tls_ca: ""
  debug: false
s3:
  access_key: *****
  secret_key: *****
  bucket: clickhouse
  endpoint: ****
  region: us-east-1
  acl: private
  assume_role_arn: ""
  force_path_style: true
  path: backup
  disable_ssl: true
  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: true

clickhouse-client -q "SELECT count() FROM karon.test" --host "$SERVER" --port "$CLICKHOUSE_PORT" --user "$BACKUP_USER" --password "$BACKUP_PASSWORD";

shard0 data count: 3 shard1 data count: 1 shard2 data count: 1

bash-5.1# clickhouse-backup create test_backup
2022/07/26 08:42:33.048853  info SELECT name, engine FROM system.databases WHERE name NOT IN ('system', 'INFORMATION_SCHEMA', 'information_schema')
2022/07/26 08:42:33.061426  info SHOW CREATE DATABASE `default`
2022/07/26 08:42:33.067165  info SHOW CREATE DATABASE `karon`
2022/07/26 08:42:33.069795  info SELECT count() FROM system.settings WHERE name = 'show_table_uuid_in_table_create_query_if_not_nil'
2022/07/26 08:42:33.075770  info SELECT name FROM system.databases WHERE engine IN ('MySQL','PostgreSQL')
2022/07/26 08:42:33.079339  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/07/26 08:42:33.087631  info SELECT database, name, engine , data_paths , uuid , create_table_query , coalesce(total_bytes, 0) AS total_bytes   FROM system.tables WHERE is_temporary = 0 SETTINGS show_table_uuid_in_table_create_query_if_not_nil=1
2022/07/26 08:42:33.108717  info SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2022/07/26 08:42:33.116143  info SELECT * FROM system.disks;
2022/07/26 08:42:33.123278 debug create data               backup=test_backup operation=create table=karon.test
2022/07/26 08:42:33.123363  info ALTER TABLE `karon`.`test` FREEZE WITH NAME 'f9097bb12aa04ac6a21ac63e2e53f36c';
2022/07/26 08:42:42.164002 debug freezed                   backup=test_backup operation=create table=karon.test
2022/07/26 08:42:42.164137 debug done                      backup=test_backup operation=create table=karon.test
2022/07/26 08:42:42.164163 debug create metadata           backup=test_backup operation=create table=karon.test
2022/07/26 08:42:42.164965  info done                      backup=test_backup operation=create table=karon.test
2022/07/26 08:42:42.164993 debug create data               backup=test_backup operation=create table=karon.test_all
2022/07/26 08:42:42.165032 debug skip table backup         backup=test_backup engine=Distributed operation=create table=karon.test_all
2022/07/26 08:42:42.165047 debug create metadata           backup=test_backup operation=create table=karon.test_all
2022/07/26 08:42:42.165266  info done                      backup=test_backup operation=create table=karon.test_all
2022/07/26 08:42:42.165403  info SELECT toUInt8(count()) udf_presents FROM system.columns WHERE database='system' AND table='functions' AND name='create_query'
2022/07/26 08:42:42.174005  info SELECT value FROM `system`.`build_options` where name='VERSION_DESCRIBE'
2022/07/26 08:42:42.182381  info done                      backup=test_backup duration=9.141s operation=create

Backup and s3 uploading working well. but it is backup only metadata.

bash-5.1# pwd
/var/lib/clickhouse/backup
bash-5.1# ls -lR
.:
total 4
drwxr-s---    3 clickhou clickhou      4096 Jul 26 08:42 test_backup

./test_backup:
total 8
drwxr-s---    3 clickhou clickhou      4096 Jul 26 08:42 metadata
-rw-r-----    1 clickhou clickhou       660 Jul 26 08:42 metadata.json

./test_backup/metadata:
total 4
drwxr-s---    2 clickhou clickhou      4096 Jul 26 08:42 karon

./test_backup/metadata/karon:
total 8
-rw-r--r--    1 clickhou clickhou       287 Jul 26 08:42 test.json
-rw-r--r--    1 clickhou clickhou       205 Jul 26 08:42 test_all.json

so, i'm run clickhouse-backup restore test_backup. there is only created table without data.

how to fix this??

Slach commented 2 years ago

you miss mount /var/lib/clickhouse into clickhouse-backup container just add

  volumeMounts:
              - name: storage-vc-template
                mountPath: /var/lib/clickhouse

inside

  containers:
    - name: clickhouse-backup

the same way as you added to name: clickhouse container

clickhouse-backup require direct access to the same /var/lib/clickhouse as clickhouse-server

myabcc17 commented 2 years ago

@Slach Thank you!!!. I'll try it