ObjectDisks + CLICKHOUSE_USE_EMBEDDED_BACKUP_RESTORE: true - shall skip upload object disk content

[oliverwoe-avl]

When using CLICKHOUSE_USE_EMBEDDED_BACKUP_RESTORE with AZBLOB I get the following log output when trying to upload the backup:

info done                      backup=mybackup duration=43.566s logger=backuper operation=create_embedded
info SELECT count() AS is_macros_exists FROM system.tables WHERE database='system' AND name='macros'  SETTINGS empty_result_for_aggregation_by_empty_set=0 logger=clickhouse
info SELECT macro, substitution FROM system.macros logger=clickhouse
info SELECT metadata_path FROM system.tables WHERE database = 'system' AND metadata_path!='' LIMIT 1; logger=clickhouse
warn b.cleanRemoteEmbedded return error: backups is not present in object_disk.DisksCredentials logger=RemoveBackupLocal
error metrics.ExecuteWithMetrics(create) return error: backups is not present in object_disk.DisksCredentials logger=metrics
error API /backup/create error: backups is not present in object_disk.DisksCredentials logger=server

This is the clickhouse setting I use to create the backup (clickhouse operator is used):

<yandex>
    <backups>
        <allowed_disk>backups</allowed_disk>
        <allowed_path>/var/lib/clickhouse/backup/</allowed_path>
    </backups>
    <storage_configuration>
        <disks>
            <backups>
                <path>/var/lib/clickhouse/backup/</path>
                <type>local</type>
            </backups>
        </disks>
    </storage_configuration>
</yandex>

I can only find the warning appearing when trying to delete the remote backup. Do I have to use an azblob disk type in this case?

Thanks in advance!

[Slach]

You can't use backups disk with type local currently, because local actually is not backup
You can't restore data if you lose the whole server

[oliverwoe-avl]

So the culprid is the storage configuration...
Would the backup and restore procedure work with azure_blob_storage type, as mentioned here: ClickHouse/ClickHouse#29430?

Without CLICKHOUSE_USE_EMBEDDED_BACKUP_RESTORE the backup and restore procedure works, but unfortunately it skip tables of type tinylog...

[Slach]

Yes it should work, look to configuration examples
https://github.com/Altinity/clickhouse-backup/blob/master/test/integration/config-azblob-embedded.yml#L22-L23
and
https://github.com/Altinity/clickhouse-backup/blob/master/test/integration/dynamic_settings.sh#L280-L288

[oliverwoe-avl]

Thank you, I applied your configuration and the upload seems to work now. But I have still one Issue and one question left regarding the upload process:

1. While upload I get this message (but the upload works fine, it seems)

warn open /var/lib/clickhouse/disks/backups_azure/mybackup-135946/data/backup_result/Table1: no such file or directory logger=getTableListByPatternLocal
warn open /var/lib/clickhouse/disks/backups_azure/mybackup-135946/data/backup_result/Table2: no such file or directory logger=getTableListByPatternLocal

2. I also noticed that a lot (> 1000) of weird files get upload to azure blob storage as well (see screenshot), when using the embedded option. After the upload has been finished clickhouse-backup tries to delete every file (which takes a lot of time):

info done                      backup=kmhkemaivttpeezhbmmpatphqmkckwog duration=72ms location=remote logger=azure operation=RemoveOldBackups
warn can't deleteKey nncuaverfqrqyfotvnqiyxnplmwbkxet return error : -> github.com/Azure/azure-storage-blob-go/azblob.newStorageError, /home/runner/go/pkg/mod/github.com/!azure/azure-storage-blob-go@v0.15.0/azblob/zc_storage_error.go:42
===== RESPONSE ERROR (ServiceCode=OperationNotAllowedInCurrentState) =====
Description=The requested operation is not allowed in the current state of the entity.
   Code: OperationNotAllowedInCurrentState
   DELETE https://container.blob.core.windows.net/backups/nncuaverfqrqyfotvnqiyxnplmwbkxet/nncuaverfqrqyfotvnqiyxnplmwbkxet?timeout=901
   RESPONSE Status: 409 The requested operation is not allowed in the current state of the entity.
   Content-Length: [276]
   Content-Type: [application/xml]
   Server: [Windows-Azure-Blob/1.0 Microsoft-HTTPAPI/2.0]

Should I open a new issue for that?

[Slach]

could you share

[Slach]

could you share following queries results from your clickhouse server?

SELECT * FROM system.disks;
SELECT * FROM system.storage_policies;

[Slach]

I also noticed that a lot (> 1000) of weird files

actually keys looks like content of object disk

Looks like you have a wrong configuration
could you share clickhouse-backup print-config without sensitive credentials?

[oliverwoe-avl]

could you share following queries results from your clickhouse server?

SELECT * FROM system.disks;
SELECT * FROM system.storage_policies;

SOURCE
name         |path                                    |free_space          |total_space         |unreserved_space    |keep_free_space|type              |is_encrypted|is_read_only|is_write_once|is_remote|is_broken|cache_path|
-------------+----------------------------------------+--------------------+--------------------+--------------------+---------------+------------------+------------+------------+-------------+---------+---------+----------+
backups_azure|/var/lib/clickhouse/disks/backups_azure/|18446744073709551615|18446744073709551615|18446744073709551615|              0|azure_blob_storage|           0|           0|            0|        1|        0|          |
backups_local|/var/lib/clickhouse/backup/             |        521720692736|        536870912000|        521720692736|              0|local             |           0|           0|            0|        0|        0|          |
default      |/var/lib/clickhouse/                    |        521720692736|        536870912000|        521720692736|              0|local             |           0|           0|            0|        0|        0|          |

policy_name|volume_name|volume_priority|disks      |volume_type|max_data_part_size|move_factor|prefer_not_to_merge|perform_ttl_move_on_insert|load_balancing|
-----------+-----------+---------------+-----------+-----------+------------------+-----------+-------------------+--------------------------+--------------+
default    |default    |              1|['default']|JBOD       |                 0|        0.0|                  0|                         1|ROUND_ROBIN   |


DESTINATION
name         |path                                    |free_space          |total_space         |unreserved_space    |keep_free_space|type              |is_encrypted|is_read_only|is_write_once|is_remote|is_broken|cache_path|
-------------+----------------------------------------+--------------------+--------------------+--------------------+---------------+------------------+------------+------------+-------------+---------+---------+----------+
backups_azure|/var/lib/clickhouse/disks/backups_azure/|18446744073709551615|18446744073709551615|18446744073709551615|              0|azure_blob_storage|           0|           0|            0|        1|        0|          |
backups_local|/var/lib/clickhouse/backup/             |       2026805198848|       2199023255552|       2026805198848|              0|local             |           0|           0|            0|        0|        0|          |
default      |/var/lib/clickhouse/                    |       2026805198848|       2199023255552|       2026805198848|              0|local             |           0|           0|            0|        0|        0|          |

policy_name|volume_name|volume_priority|disks      |volume_type|max_data_part_size|move_factor|prefer_not_to_merge|perform_ttl_move_on_insert|load_balancing|
-----------+-----------+---------------+-----------+-----------+------------------+-----------+-------------------+--------------------------+--------------+
default    |default    |              1|['default']|JBOD       |                 0|        0.0|                  0|                         1|ROUND_ROBIN   |

I also noticed that a lot (> 1000) of weird files

actually keys looks like content of object disk

Looks like you have a wrong configuration could you share clickhouse-backup print-config without sensitive credentials?

I watched now the backup procedure and these files are already uploaded when the backup process is started (via API endpoint /backups/create) (no upload was initiated yet). These are the params I create the backup with:

params={'tables': 'db_name.*', 'name': 'mybackup'}

This is the clickhouse-backup configuration I use on source and destination host:

general:
    remote_storage: azblob
    max_file_size: 0
    disable_progress_bar: true
    backups_to_keep_local: -1
    backups_to_keep_remote: 5
    log_level: info
    allow_empty_backups: true
    download_concurrency: 1
    upload_concurrency: 1
    use_resumable_state: true
    restore_schema_on_cluster: ""
    upload_by_part: true
    download_by_part: true
    restore_database_mapping: {}
    retries_on_failure: 3
    retries_pause: 30s
    watch_interval: 1h
    full_interval: 24h
    watch_backup_name_template: shard{shard}-{type}-{time:20060102150405}
    sharded_operation_mode: ""
    cpu_nice_priority: 15
    io_nice_priority: idle
    retriesduration: 30s
    watchduration: 1h0m0s
    fullduration: 24h0m0s
clickhouse:
    username: user
    password: ********
    host: localhost
    port: 9000
    disk_mapping: {}
    skip_tables:
        - system.*
        - INFORMATION_SCHEMA.*
        - information_schema.*
        - _temporary_and_external_tables.*
    skip_table_engines: []
    timeout: 24h
    freeze_by_part: false
    freeze_by_part_where: ""
    use_embedded_backup_restore: true
    embedded_backup_disk: backups_azure
    backup_mutations: true
    restore_as_attach: false
    check_parts_columns: true
    secure: false
    skip_verify: false
    sync_replicated_tables: false
    log_sql_queries: true
    config_dir: /etc/clickhouse-server/
    restart_command: exec:systemctl restart clickhouse-server
    ignore_not_exists_error_during_freeze: true
    check_replicas_before_attach: 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: ""
    object_disk_path: ""
    disable_ssl: false
    compression_level: 1
    compression_format: tar
    sse: ""
    sse_kms_key_id: ""
    sse_customer_algorithm: ""
    sse_customer_key: ""
    sse_customer_key_md5: ""
    sse_kms_encryption_context: ""
    disable_cert_verification: false
    use_custom_storage_class: false
    storage_class: STANDARD
    custom_storage_class_map: {}
    concurrency: 49
    part_size: 0
    max_parts_count: 5000
    allow_multipart_download: false
    object_labels: {}
    request_payer: ""
    debug: false
gcs:
    credentials_file: ""
    credentials_json: ""
    credentials_json_encoded: ""
    bucket: ""
    path: ""
    object_disk_path: ""
    compression_level: 1
    compression_format: tar
    debug: false
    force_http: false
    endpoint: ""
    storage_class: STANDARD
    object_labels: {}
    custom_storage_class_map: {}
    client_pool_size: 144
cos:
    url: ""
    timeout: 2m
    secret_id: ""
    secret_key: ""
    path: ""
    compression_format: tar
    compression_level: 1
    debug: false
api:
    listen: 0.0.0.0:7171
    enable_metrics: true
    enable_pprof: false
    username: user
    password: ********
    secure: false
    certificate_file: ""
    private_key_file: ""
    ca_cert_file: ""
    ca_key_file: ""
    create_integration_tables: true
    integration_tables_host: ""
    allow_parallel: false
    complete_resumable_after_restart: true
ftp:
    address: ""
    timeout: 2m
    username: ""
    password: ""
    tls: false
    skip_tls_verify: false
    path: ""
    object_disk_path: ""
    compression_format: tar
    compression_level: 1
    concurrency: 49
    debug: false
sftp:
    address: ""
    port: 22
    username: ""
    password: ""
    key: ""
    path: ""
    object_disk_path: ""
    compression_format: tar
    compression_level: 1
    concurrency: 49
    debug: false
azblob:
    endpoint_schema: https
    endpoint_suffix: core.windows.net
    account_name: accountname
    account_key: ********
    sas: ""
    use_managed_identity: false
    container: backups
    path: ""
    object_disk_path: ""
    compression_level: 1
    compression_format: none
    sse_key: ""
    buffer_size: 0
    buffer_count: 3
    max_parts_count: 5000
    timeout: 15m
custom:
    upload_command: ""
    download_command: ""
    list_command: ""
    delete_command: ""
    command_timeout: 4h
    commandtimeoutduration: 4h0m0s

[Slach]

try to change /etc/clickhouse-backup/config.yml

azblob:
    path: "/clickhouse_backup_data/"
    object_disk_path: "/clickhouse_backup_object_disks_data/"

pseudo-random files on screenshot is a content of embedded backup which created during execute BACKUP SQL statement

[oliverwoe-avl]

Still no change when creating the backup on clickhouse. Additionally, when uploading the backup I now get this error:

warn b.cleanRemoteEmbedded return error: -> github.com/Azure/azure-pipeline-go/pipeline.NewError, /home/runner/go/pkg/mod/github.com/!azure/azure-pipeline-go@v0.2.3/pipeline/error.go:157
HTTP request failed

Put "https://accountname.blob.accountname.blob.core.windows.net/backups3?restype=container&timeout=901": dial tcp: lookup accountname.blob.accountname.blob.core.windows.net on 10.96.0.10:53: no such host
 logger=RemoveBackupLocal
2023/12/18 08:26:28.677012  info clickhouse connection closed logger=clickhouse
2023/12/18 08:26:28.677049  info clickhouse connection closed logger=clickhouse
2023/12/18 08:26:28.677110 error metrics.ExecuteWithMetrics(create) return error: -> github.com/Azure/azure-pipeline-go/pipeline.NewError, /home/runner/go/pkg/mod/github.com/!azure/azure-pipeline-go@v0.2.3/pipeline/error.go:157
HTTP request failed

Note the double accountname.blob in the url.

This is the new setting:

azblob:
    endpoint_schema: https
    endpoint_suffix: core.windows.net
    account_name: accountname
    account_key: ...
    sas: ""
    use_managed_identity: false
    container: backups
    path: backup_data/
    object_disk_path: /backup_object_disk_data/
    compression_level: 1
    compression_format: none
    sse_key: ""
    buffer_size: 0
    buffer_count: 3
    max_parts_count: 5000
    timeout: 15m

I also noticed that the first / is removed from the azblob.path setting.

Additionally, tried this setting, with the same result:

    - name: AZBLOB_PATH
      value: "/var/lib/clickhouse/backup_data/"
    - name: AZBLOB_OBJECT_DISK_PATH
      value: "/var/lib/clickhouse/backup_object_disk_data/"

[Slach]

warn b.cleanRemoteEmbedded return error

Please share the full upload log
with environment variable LOG_LEVEL=debug
??

[oliverwoe-avl]

2023/12/18 11:05:26.898249  warn b.cleanRemoteEmbedded return error: -> github.com/Azure/azure-pipeline-go/pipeline.NewError, /home/runner/go/pkg/mod/github.com/!azure/azure-pipeline-go@v0.2.3/pipeline/error.go:157
HTTP request failed

Put "https://containername.blob.containername.blob.core.windows.net/backups4?restype=container&timeout=901": dial tcp: lookup containername.blob.containername.blob.core.windows.net on 10.96.0.10:53: no such host
 logger=RemoveBackupLocal
2023/12/18 11:05:26.898373  info clickhouse connection closed logger=clickhouse
2023/12/18 11:05:26.898396  info clickhouse connection closed logger=clickhouse
2023/12/18 11:05:26.898439 error metrics.ExecuteWithMetrics(create) return error: -> github.com/Azure/azure-pipeline-go/pipeline.NewError, /home/runner/go/pkg/mod/github.com/!azure/azure-pipeline-go@v0.2.3/pipeline/error.go:157
HTTP request failed

Put "https://containername.blob.containername.blob.core.windows.net/backups4?restype=container&timeout=901": dial tcp: lookup containername.blob.containername.blob.core.windows.net on 10.96.0.10:53: no such host
 logger=metrics
2023/12/18 11:05:26.898472 error API /backup/create error: -> github.com/Azure/azure-pipeline-go/pipeline.NewError, /home/runner/go/pkg/mod/github.com/!azure/azure-pipeline-go@v0.2.3/pipeline/error.go:157
HTTP request failed

Put "https://containername.blob.containername.blob.core.windows.net/backups4?restype=container&timeout=901": dial tcp: lookup containername.blob.containername.blob.core.windows.net on 10.96.0.10:53: no such host
 logger=server
2023/12/18 11:05:26.898525 debug api.status.stop -> status.commands[0] == {ActionRowStatus:{Command:create backup_result_20231218-120154 Status:error Start:2023-12-18 11:01:54 Finish:2023-12-18 11:05:26 Error:-> github.com/Azure/azure-pipeline-go/pipeline.NewError, /home/runner/go/pkg/mod/github.com/!azure/azure-pipeline-go@v0.2.3/pipeline/error.go:157
HTTP request failed

Put "https://containername.blob.containername.blob.core.windows.net/backups4?restype=container&timeout=901": dial tcp: lookup containername.blob.containername.blob.core.windows.net on 10.96.0.10:53: no such host
} Ctx:<nil> Cancel:<nil>} logger=status
2023/12/18 11:05:30.056172  info API call GET /backup/status logger=server

There is not much information added by the debug logging..