Restoring an incremental backup with mapping drops not specified dbs? #1182

ardabeyazoglu · 2025-07-10T12:19:35Z

ardabeyazoglu
Jul 10, 2025

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 · 2025-07-10T12:50:57Z

Slach
Jul 10, 2025
Maintainer

which clickhouse-backup version did you use?

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

0 replies

ardabeyazoglu · 2025-07-10T13:21:44Z

ardabeyazoglu
Jul 10, 2025
Author

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.

4 replies

Slach Jul 10, 2025
Maintainer

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 Jul 10, 2025
Author

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 Jul 10, 2025
Maintainer

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

Yes

Slach Jul 10, 2025
Maintainer

Feel free to provide more logs if something goes wrong.

Navigation Menu

Repository navigation

Restoring an incremental backup with mapping drops not specified dbs? #1182

Uh oh!

{{title}}

Uh oh!

Replies: 2 comments 4 replies

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Select a reply

Uh oh!

Restoring an incremental backup with mapping drops not specified dbs? #1182

Uh oh!

ardabeyazoglu Jul 10, 2025

Replies: 2 comments · 4 replies

Uh oh!

Slach Jul 10, 2025 Maintainer

Uh oh!

ardabeyazoglu Jul 10, 2025 Author

Uh oh!

Slach Jul 10, 2025 Maintainer

Uh oh!

ardabeyazoglu Jul 10, 2025 Author

Uh oh!

Slach Jul 10, 2025 Maintainer

Uh oh!

Slach Jul 10, 2025 Maintainer

ardabeyazoglu
Jul 10, 2025

Replies: 2 comments 4 replies

Slach
Jul 10, 2025
Maintainer

ardabeyazoglu
Jul 10, 2025
Author

Slach Jul 10, 2025
Maintainer

ardabeyazoglu Jul 10, 2025
Author

Slach Jul 10, 2025
Maintainer

Slach Jul 10, 2025
Maintainer