Restoring an incremental backup with mapping drops not specified dbs?

[ardabeyazoglu]

Hi,

I was testing the tool and created full and incremental backups. Then i tried restoring a single db with a different name using "--restore-database-mapping" in the same running instance. This dropped and recreated all other databases i have, thus causing data loss.

Is this intentional and why ? Or am i doing something wrong ?

[Slach]

which clickhouse-backup version did you use?

could you share full command which you executed
and logs for this command?

[ardabeyazoglu]

Hi,

Version is v2.6.26. I was tinkering so i didnt save/organize logs but i will try to share as much as possible.

List of backup before:

test-incr                               10/07/2025 11:43:08   local    +shard{shard}-increment-20250710092755   all:76.74MiB,data:76.73MiB,arch:0B,obj:9.15KiB,meta:0B,rbac:3.69KiB,conf:0B          regular
test-incr2                              10/07/2025 11:48:32   local    +test-incr                               all:14.09KiB,data:654B,arch:0B,obj:9.76KiB,meta:0B,rbac:3.69KiB,conf:0B              regular
shard{shard}-increment-20250710092755   10/07/2025 11:13:35   remote   +shard{shard}-full-20250709195113        all:40.32GiB,data:40.31GiB,arch:40.32GiB,obj:0B,meta:12.59KiB,rbac:6.50KiB,conf:0B   tar, regular
test-incr                               10/07/2025 11:43:20   remote   +shard{shard}-increment-20250710092755   all:77.50MiB,data:76.73MiB,arch:77.48MiB,obj:0B,meta:9.66KiB,rbac:6.50KiB,conf:0B    tar, regular
test-incr2                              10/07/2025 11:48:37   remote   +test-incr                               all:25.32KiB,data:654B,arch:9.00KiB,obj:0B,meta:9.82KiB,rbac:6.50KiB,conf:0B         tar, regular

Then i run this: clickhouse-backup -c config-gcs.yml restore_remote --restore-database-mapping yeni:backup test-incr2

2025-07-10 14:51:18.357 INF pkg/clickhouse/clickhouse.go:131 > clickhouse connection prepared: tcp://localhost:9000 run ping
2025-07-10 14:51:18.359 INF pkg/clickhouse/clickhouse.go:134 > clickhouse connection success: tcp://localhost:9000
2025-07-10 14:51:18.360 INF pkg/clickhouse/clickhouse.go:1170 > SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2025-07-10 14:51:18.363 INF pkg/clickhouse/clickhouse.go:1170 > SELECT countIf(name='type') AS is_disk_type_present, countIf(name='object_storage_type') AS is_object_storage_type_present, countIf(name='free_space') AS is_free_space_present, countIf(name='disks') AS is_storage_policy_present FROM system.columns WHERE database='system' AND table IN ('disks','storage_policies') 
2025-07-10 14:51:18.369 INF pkg/clickhouse/clickhouse.go:1170 > SELECT d.path AS path, any(d.name) AS name, any(lower(if(d.type='ObjectStorage',d.object_storage_type,d.type))) AS type, min(d.free_space) AS free_space, groupUniqArray(s.policy_name) AS storage_policies FROM system.disks AS d  LEFT JOIN (SELECT policy_name, arrayJoin(disks) AS disk FROM system.storage_policies) AS s ON s.disk = d.name GROUP BY d.path
2025-07-10 14:51:18.375 INF pkg/clickhouse/clickhouse.go:336 > clickhouse connection closed
2025-07-10 14:51:18.375 INF pkg/clickhouse/clickhouse.go:131 > clickhouse connection prepared: tcp://localhost:9000 run ping
2025-07-10 14:51:18.376 INF pkg/clickhouse/clickhouse.go:134 > clickhouse connection success: tcp://localhost:9000
2025-07-10 14:51:18.376 INF pkg/clickhouse/clickhouse.go:1170 > SELECT countIf(name='type') AS is_disk_type_present, countIf(name='object_storage_type') AS is_object_storage_type_present, countIf(name='free_space') AS is_free_space_present, countIf(name='disks') AS is_storage_policy_present FROM system.columns WHERE database='system' AND table IN ('disks','storage_policies') 
2025-07-10 14:51:18.381 INF pkg/clickhouse/clickhouse.go:1170 > SELECT d.path AS path, any(d.name) AS name, any(lower(if(d.type='ObjectStorage',d.object_storage_type,d.type))) AS type, min(d.free_space) AS free_space, groupUniqArray(s.policy_name) AS storage_policies FROM system.disks AS d  LEFT JOIN (SELECT policy_name, arrayJoin(disks) AS disk FROM system.storage_policies) AS s ON s.disk = d.name GROUP BY d.path
2025-07-10 14:51:18.388 INF pkg/clickhouse/clickhouse.go:1168 > CREATE DATABASE IF NOT EXISTS `cdp` ENGINE = Atomic with args []interface {}{[]interface {}(nil)}
2025-07-10 14:51:18.388 INF pkg/clickhouse/clickhouse.go:1168 > CREATE DATABASE IF NOT EXISTS `default` ENGINE = Atomic with args []interface {}{[]interface {}(nil)}
2025-07-10 14:51:18.389 INF pkg/clickhouse/clickhouse.go:1168 > CREATE DATABASE IF NOT EXISTS `newjitsu` ENGINE = Atomic with args []interface {}{[]interface {}(nil)}
2025-07-10 14:51:18.390 INF pkg/clickhouse/clickhouse.go:1168 > CREATE DATABASE IF NOT EXISTS `backup` ENGINE = Atomic with args []interface {}{[]interface {}(nil)}
2025-07-10 14:51:18.396 INF pkg/clickhouse/clickhouse.go:1170 > DROP TABLE IF EXISTS `cdp`.`events` NO DELAY
2025-07-10 14:51:22.222 INF pkg/clickhouse/clickhouse.go:1170 > DROP TABLE IF EXISTS `cdp`.`events_crm` NO DELAY
2025-07-10 14:51:22.751 INF pkg/clickhouse/clickhouse.go:1170 > DROP TABLE IF EXISTS `cdp`.`test` NO DELAY
2025-07-10 14:51:22.755 INF pkg/clickhouse/clickhouse.go:1170 > DROP TABLE IF EXISTS `backup`.`tablo` NO DELAY
2025-07-10 14:51:22.755 INF pkg/clickhouse/clickhouse.go:1170 > CREATE DATABASE IF NOT EXISTS `cdp` ENGINE=Atomic
2025-07-10 14:51:22.756 INF pkg/clickhouse/clickhouse.go:1170 > CREATE TABLE cdp.events UUID 'ce5dda84-cf6c-4f3b-b324-d9798f2750a8' (`context_traits_email` String, `context_traits_mobile` String, `context_library_name` LowCardinality(String), `context_library_version` LowCardinality(String), `context_library_env` LowCardinality(String), `context_user_agent` String, `context_locale` LowCardinality(String), `context_page_path` String, `context_page_referrer` String, `context_page_referring_domain` String, `context_page_host` String, `context_page_search` String, `context_page_title` String, `context_page_url` String, `context_page_encoding` LowCardinality(String), `context_campaign_source` String, `context_campaign_medium` String, `context_campaign_name` String, `context_campaign_content` String, `context_ip` String, `title` String, `url` String, `path` String, `hash` String, `search` String, `brand` LowCardinality(String), `city` LowCardinality(String), `type` LowCardinality(String), `user_id` String, `anonymous_id` String, `timestamp` DateTime64(6), `sent_at` DateTime64(6), `message_id` String, `write_key` LowCardinality(String), `request_ip` String, `received_at` DateTime64(6), `event` LowCardinality(String), `model` String, `has_email_or_mobile` UInt8 MATERIALIZED if(((context_traits_email IS NOT NULL) AND (context_traits_email != '')) OR ((context_traits_mobile IS NOT NULL) AND (context_traits_mobile != '')), 1, 0), `_unmapped_data` JSON, `brand_id` Int64, `referrer` String, `context_campaign_term` String, `duration` Int64, `context_campaign_targeting` String, `context_campaign_id` String, `context_campaign_ad` String, `context_campaign_publisher` String, `context_client_ids_fbp` String, `context_client_ids_ga4_client_id` String, `context_client_ids_ga4_session_ids` String, `context_client_ids_fbc` String) ENGINE = ReplacingMergeTree PARTITION BY toYYYYMM(timestamp) ORDER BY (toDate(timestamp), message_id) TTL toStartOfDay(toDateTime(timestamp)) + toIntervalMonth(24) SETTINGS index_granularity = 8192
2025-07-10 14:51:22.775 INF pkg/clickhouse/clickhouse.go:1170 > CREATE TABLE cdp.events_crm UUID 'f01a1c73-3379-469e-b433-004a3aceddb9' (`id` UUID, `record_owner` UInt64, `created_on` DateTime64(6), `created_by` UInt64, `last_modified_on` Nullable(DateTime64(6)), `last_modified_by` Nullable(UInt64), `source_id` String, `source_type` LowCardinality(String), `source_model` LowCardinality(String), `source_record_id` UInt64, `rowdata_id` UInt64, `context_data` JSON, `user_contact_id` UInt64, `user_lead_id` UInt64, `user_email` String, `user_mobile` String, `user_tax_number` String, `dealer_id` UInt64, `dealer` LowCardinality(String), `brand_id` UInt64, `brand` LowCardinality(String), `model` LowCardinality(String), `models` Array(LowCardinality(String)), `model_master` LowCardinality(String), `chassis_no` String, `plate` String, `city` LowCardinality(String), `facebook_source_id` LowCardinality(String), `event_start_date` Nullable(DateTime64(6)), `event_status` LowCardinality(String), `event_result` LowCardinality(String), `event_location` String, `calendar` LowCardinality(String), `lead_source` LowCardinality(String), `lead_request` LowCardinality(String), `negative_event_result` LowCardinality(String), `positive_event_result` LowCardinality(String), `external_lead_source` LowCardinality(String)) ENGINE = ReplacingMergeTree(created_on) PARTITION BY toYear(created_on) ORDER BY (toStartOfDay(created_on), source_id) TTL toStartOfDay(created_on) + toIntervalYear(5) SETTINGS index_granularity = 8192
2025-07-10 14:51:22.786 INF pkg/clickhouse/clickhouse.go:1170 > CREATE TABLE cdp.test UUID '221f6fab-5f78-440f-a9c8-81e92601de06' (`id` UInt32, `email` String) ENGINE = MergeTree ORDER BY id SETTINGS index_granularity = 8192
2025-07-10 14:51:22.793 INF pkg/clickhouse/clickhouse.go:1170 > CREATE DATABASE IF NOT EXISTS `backup` ENGINE=Atomic
2025-07-10 14:51:22.794 INF pkg/clickhouse/clickhouse.go:1170 > CREATE TABLE backup.tablo UUID '30c7a582-5d84-11f0-a482-00505601425e' (`id` UInt32, `email` String) ENGINE = MergeTree ORDER BY id SETTINGS index_granularity = 8192
2025-07-10 14:51:22.803 INF pkg/backup/restore.go:954 > done backup=test-incr2 duration=4.407s operation=restore_schema
2025-07-10 14:51:22.804 INF pkg/clickhouse/clickhouse.go:1170 > SELECT count() AS is_macros_exists FROM system.tables WHERE database='system' AND name='macros'  SETTINGS empty_result_for_aggregation_by_empty_set=0
2025-07-10 14:51:22.808 INF pkg/clickhouse/clickhouse.go:1170 > SELECT macro, substitution FROM system.macros
2025-07-10 14:51:22.810 INF pkg/clickhouse/clickhouse.go:1168 > SELECT name, count(*) as is_present FROM system.settings WHERE name IN (?, ?) GROUP BY name with args []interface {}{"show_table_uuid_in_table_create_query_if_not_nil", "display_secrets_in_show_and_select"}
2025-07-10 14:51:22.814 INF pkg/clickhouse/clickhouse.go:1170 > SELECT name FROM system.databases WHERE engine IN ('MySQL','PostgreSQL','MaterializedPostgreSQL')
2025-07-10 14:51:22.816 INF pkg/clickhouse/clickhouse.go:1170 >    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'  
2025-07-10 14:51:22.820 INF pkg/clickhouse/clickhouse.go:1170 > SELECT database, name, engine , data_paths , uuid , create_table_query , coalesce(total_bytes, 0) AS total_bytes   FROM system.tables WHERE is_temporary = 0 AND match(concat(database,'.',name),'^.*$|^backup\..*$')  ORDER BY total_bytes DESC SETTINGS show_table_uuid_in_table_create_query_if_not_nil=1
2025-07-10 14:51:22.920 INF pkg/clickhouse/clickhouse.go:1170 > SELECT data_path AS metadata_path FROM system.databases WHERE name = 'system' LIMIT 1
2025-07-10 14:51:22.923 INF pkg/clickhouse/clickhouse.go:1170 > SELECT sum(bytes_on_disk) as size FROM system.parts WHERE active AND database='cdp' AND table='test' GROUP BY database, table2025-07-10 14:51:22.926 INF pkg/clickhouse/clickhouse.go:1170 > SELECT sum(bytes_on_disk) as size FROM system.parts WHERE active AND database='cdp' AND table='events_crm' GROUP BY database, table
2025-07-10 14:51:22.930 INF pkg/clickhouse/clickhouse.go:1170 > SELECT sum(bytes_on_disk) as size FROM system.parts WHERE active AND database='cdp' AND table='events' GROUP BY database, table
2025-07-10 14:51:22.933 INF pkg/clickhouse/clickhouse.go:1170 > SELECT sum(bytes_on_disk) as size FROM system.parts WHERE active AND database='backup' AND table='tablo' GROUP BY database, table
2025-07-10 14:51:22.936 INF pkg/backup/restore.go:1513 > download object_disks start table=backup.tablo
2025-07-10 14:51:22.936 INF pkg/backup/restore.go:1520 > download object_disks finish duration=0s size=0B
2025-07-10 14:51:22.936 INF pkg/clickhouse/clickhouse.go:1170 > ALTER TABLE `backup`.`tablo` ATTACH PART 'all_1_1_0'
2025-07-10 14:51:22.944 INF pkg/backup/restore.go:1473 > done database=backup duration=8ms operation=restoreDataRegular progress=4/4 table=tablo
2025-07-10 14:51:22.944 INF pkg/clickhouse/clickhouse.go:336 > clickhouse connection closed
2025-07-10 14:51:22.944 FTL cmd/clickhouse-backup/main.go:742 > error="one of restoreDataRegular go-routine return error: can't copy data to detached `cdp`.`events`: error during filepath.Walk for part '202311_2390_3169_17_2983': lstat /var/lib/clickhouse/backup/test-incr2/shadow/cdp/events/default/202311_2390_3169_17_2983: no such file or directory"

The tool is probably ok, but I probably understood something wrong or missing an important information.

• Does the restore command always detach (or drop) all tables until restore operation finish ?
• I thought "--restore-database-mapping x:y" will only restore x db as "y" and leave others as it is. But I suppose this is not the case either.

[Slach]

You shared only final part of log, I don't see anything related to download

2025-07-10 14:51:22.944 FTL cmd/clickhouse-backup/main.go:742 > error="one of restoreDataRegular go-routine return error: can't copy data to detached cdp.events: error during filepath.Walk for part '202311_2390_3169_17_2983': lstat /var/lib/clickhouse/backup/test-incr2/shadow/cdp/events/default/202311_2390_3169_17_2983: no such file or directory"

do you run clickhouse-backup on the same machine where run clickhouse-server?
look details
https://github.com/Altinity/clickhouse-backup/?tab=readme-ov-file#dont-run-clickhouse-backup-remotely

clickhouse-backup -c config-gcs.yml restore_remote --restore-database-mapping yeni:backup test-incr2
I thought "--restore-database-mapping x:y" will only restore x db as "y" and leave others as it is. But I suppose this is not the case either.

If you want to restore only one database, then you need to define it with --tables=db.* parameter

properly command shall look something like that:
clickhouse-backup -c config-gcs.yml restore_remote --tables=yeni.* --restore-database-mapping yeni:backup test-incr2

[ardabeyazoglu]

Unfortunately i couldnt find any other log, but thanks. I will share more details if i encounter it again.

Yes, i use it in the same machine with the server.

If you want to restore only one database, then you need to define it with --tables=db.* parameter

So mapping is just for "mapping", it will still restore everything in the backup. However, if i specify --tables, then it will not touch other tables on the server. If i am correct, we can close the thread.

Thank you very much for your time.

[Slach]

However, if i specify --tables, then it will not touch other tables on the server.

Yes

[Slach]

Feel free to provide more logs if something goes wrong.