Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

keep_backups_remote behavior shall respect for recursive incremental sequences #525

Closed
marknefedov opened this issue Oct 3, 2022 · 3 comments · Fixed by #541
Closed

keep_backups_remote behavior shall respect for recursive incremental sequences #525

marknefedov opened this issue Oct 3, 2022 · 3 comments · Fixed by #541
Assignees
@Slach
Milestone
2.1.0

Comments

@marknefedov
Copy link

marknefedov commented Oct 3, 2022 •
edited
Loading

Hi, I expect all remote backups before 2022-10-03T09-30-20 to be deleted.

2022/10/03 10:32:17.436629  info SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2022/10/03 10:32:17.439184  info SELECT * FROM system.disks;
2022/10/03 10:32:17.450417  info SELECT count() AS is_macros_exists FROM system.tables WHERE database='system' AND name='macros'
2022/10/03 10:32:17.456905  info SELECT * FROM system.macros
2022-10-03T10-11-15   152.26GiB   03/10/2022 10:11:16   local                           
2022-10-03T10-12-38   152.26GiB   03/10/2022 10:12:39   local                           
2022-10-03T10-12-57   152.26GiB   03/10/2022 10:12:58   local                           
2022-10-03T10-13-16   152.26GiB   03/10/2022 10:13:18   local                           
2022-10-03T10-15-32   152.26GiB   03/10/2022 10:15:33   local                           
2022-10-03T10-32-06   152.26GiB   03/10/2022 10:32:07   local                           
2022-09-01T05-00-01   150.42GiB   01/09/2022 08:11:00   remote                          tar
2022-09-01T21-00-03   56.29MiB    02/09/2022 00:00:00   remote   +2022-09-01T05-00-01   tar
2022-09-02T21-00-02   59.70MiB    03/09/2022 00:00:00   remote   +2022-09-01T21-00-03   tar
2022-09-03T21-00-03   57.51MiB    04/09/2022 00:00:00   remote   +2022-09-02T21-00-02   tar
2022-09-04T21-00-03   154.34MiB   05/09/2022 00:00:00   remote   +2022-09-03T21-00-03   tar
2022-09-05T21-00-03   167.68MiB   06/09/2022 00:00:00   remote   +2022-09-04T21-00-03   tar
2022-09-06T21-00-03   195.81MiB   07/09/2022 00:00:00   remote   +2022-09-05T21-00-03   tar
2022-09-07T21-00-03   147.14MiB   08/09/2022 00:00:00   remote   +2022-09-06T21-00-03   tar
2022-09-08T21-00-03   89.49MiB    09/09/2022 00:00:00   remote   +2022-09-07T21-00-03   tar
2022-09-09T21-00-03   197.16MiB   10/09/2022 00:00:00   remote   +2022-09-08T21-00-03   tar
2022-09-10T21-00-03   102.61MiB   11/09/2022 00:00:00   remote   +2022-09-09T21-00-03   tar
2022-09-11T21-00-03   185.30MiB   12/09/2022 00:00:00   remote   +2022-09-10T21-00-03   tar
2022-09-12T21-00-02   167.08MiB   13/09/2022 00:00:00   remote   +2022-09-11T21-00-03   tar
2022-09-13T21-00-03   492.04MiB   14/09/2022 00:00:00   remote   +2022-09-12T21-00-02   tar
2022-09-14T21-00-03   162.21MiB   15/09/2022 00:00:00   remote   +2022-09-13T21-00-03   tar
2022-10-03T09-30-20   152.27GiB   03/10/2022 12:37:00   remote                          tar
2022-10-03T09-39-37   3.68MiB     03/10/2022 12:39:00   remote   +2022-10-03T09-30-20   tar
2022-10-03T09-40-03   3.69MiB     03/10/2022 12:40:00   remote   +2022-10-03T09-39-37   tar
2022-10-03T09-41-31   3.80MiB     03/10/2022 12:41:00   remote   +2022-10-03T09-40-03   tar, regular
2022-10-03T09-52-12   7.35MiB     03/10/2022 12:52:00   remote   +2022-10-03T09-41-31   tar, regular
2022-10-03T10-11-15   5.70MiB     03/10/2022 13:11:00   remote   +2022-10-03T09-52-12   tar, regular
2022-10-03T10-12-57   1.93MiB     03/10/2022 13:12:00   remote   +2022-10-03T10-12-38   tar, regular
2022-10-03T10-12-38   1.89MiB     03/10/2022 13:12:00   remote   +2022-10-03T10-11-15   tar, regular
2022-10-03T10-13-16   2.01MiB     03/10/2022 13:13:00   remote   +2022-10-03T10-12-38   tar, regular
2022-10-03T10-15-32   2.18MiB     03/10/2022 13:15:00   remote   +2022-10-03T10-13-16   tar, regular

config

general:
  remote_storage: ftp
  max_file_size: 0
  disable_progress_bar: true
  backups_to_keep_local: 6
  backups_to_keep_remote: 6
  log_level: info
  allow_empty_backups: false
  download_concurrency: 16
  upload_concurrency: 16
  restore_schema_on_cluster: ""
  upload_by_part: true
  download_by_part: true
  restore_database_mapping: {}
clickhouse:
  username: ***
  password: ***
  host: localhost
  port: 9000
  disk_mapping: {}
  skip_tables:
  - system.*
  - INFORMATION_SCHEMA.*
  - information_schema.*
  - _temporary_and_external_tables.*
  timeout: 5m
  freeze_by_part: false
  freeze_by_part_where: ""
  use_embedded_backup_restore: false
  embedded_backup_disk: ""
  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: 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
  use_custom_storage_class: 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: ""
  storage_class: STANDARD
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
  integration_tables_host: ""
  allow_parallel: false
ftp:
  address: ***:21
  timeout: 2m
  username: ***
  password: ***
  tls: false
  path: ""
  compression_format: tar
  compression_level: 1
  concurrency: 16
  debug: false
sftp:
  address: ""
  port: 22
  username: ""
  password: ""
  key: ""
  path: ""
  compression_format: tar
  compression_level: 1
  concurrency: 1
  debug: false
azblob:
  endpoint_schema: https
  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
  max_parts_count: 10000
  timeout: 15m
custom:
  upload_command: ""
  download_command: ""
  list_command: ""
  delete_command: ""
  command_timeout: 4h

creating incremental backup:

Creating remote backup with base: 2022-10-03T10-32-06
2022/10/03 10:47:38.464177  info SELECT name, engine FROM system.databases WHERE name NOT IN ('system', 'INFORMATION_SCHEMA', 'information_schema', '_temporary_and_external_tables')
2022/10/03 10:47:38.467190  info SHOW CREATE DATABASE `***`
2022/10/03 10:47:38.469868  info SELECT count() FROM system.settings WHERE name = 'show_table_uuid_in_table_create_query_if_not_nil'
2022/10/03 10:47:38.475485  info SELECT name FROM system.databases WHERE engine IN ('MySQL','PostgreSQL')
2022/10/03 10:47:38.480939  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/10/03 10:47:38.485447  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/10/03 10:47:38.500725  info SELECT toUInt8(count()) udf_presents FROM system.columns WHERE database='system' AND table='functions' AND name='create_query'
2022/10/03 10:47:38.507139  info SELECT name, create_query FROM system.functions WHERE create_query!=''
2022/10/03 10:47:38.514122  info SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2022/10/03 10:47:38.516436  info SELECT * FROM system.disks;
2022/10/03 10:47:38.519799  info ALTER TABLE `***`.`***` FREEZE WITH NAME 'dd8eddd746784b639143ec556bc96fa0';
2022/10/03 10:47:38.696571  info done                      backup=2022-10-03T10-47-38 operation=create table=***.***
2022/10/03 10:47:38.696621  info ALTER TABLE `***`.`***` FREEZE WITH NAME '776bae1ac1cb4a69a9c417ff7fe98804';
2022/10/03 10:47:39.221377  info done                      backup=2022-10-03T10-47-38 operation=create table=***.***
2022/10/03 10:47:39.221439  info ALTER TABLE `***`.`***` FREEZE WITH NAME 'd05952cb12904e19a675195cdb625d3c';
2022/10/03 10:47:39.676156  info done                      backup=2022-10-03T10-47-38 operation=create table=***.***
2022/10/03 10:47:39.676394  info SELECT value FROM `system`.`build_options` where name='VERSION_DESCRIBE'
2022/10/03 10:47:39.683426  info done                      backup=2022-10-03T10-47-38 duration=1.223s operation=create
2022/10/03 10:47:39.866375  info done                      backup=2022-10-03T10-11-15 duration=179ms location=local operation=delete
2022/10/03 10:47:39.873350  info SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2022/10/03 10:47:39.877301  info SELECT * FROM system.disks;
2022/10/03 10:47:39.883100  info SELECT max(toInt64(bytes_on_disk * 1.02)) AS max_file_size FROM system.parts
2022/10/03 10:47:39.894086  info SELECT count() AS is_macros_exists FROM system.tables WHERE database='system' AND name='macros'
2022/10/03 10:47:39.899147  info SELECT * FROM system.macros
2022/10/03 10:47:40.058226  info done                      backup=2022-10-03T10-47-38 duration=120ms operation=upload size=1.19MiB table=***.***
2022/10/03 10:47:40.070698  info done                      backup=2022-10-03T10-47-38 duration=132ms operation=upload size=2.78MiB table=***.***
2022/10/03 10:47:40.073734  info done                      backup=2022-10-03T10-47-38 duration=135ms operation=upload size=227.85KiB table=***.***
2022/10/03 10:47:40.075423  info done                      backup=2022-10-03T10-47-38 duration=209ms operation=upload size=4.19MiB
2022/10/03 10:47:40.147451  info calculate backup list for delete duration=72ms operation=RemoveOldBackups
2022/10/03 10:47:40.191593  info done                      backup=2022-09-14T21-00-03 duration=44ms location=remote operation=RemoveOldBackups
2022/10/03 10:47:40.191625  info done                      duration=116ms operation=RemoveOldBackups
2022/10/03 10:47:40.196200  info SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
@Slach
Copy link
Collaborator

Slach commented Oct 3, 2022

i propose
backups_to_keep_local: 1
to avoid extra disk space allocation

which clickhouse-backup version do you use?

@Slach Slach self-assigned this Oct 3, 2022
@Slach Slach added this to the 2.1.0 milestone Oct 3, 2022
@marknefedov
Copy link
Author

from docker image.

Version:	 2.0.0
Git Commit:	 93f6c2f168b63db4ab7c8cd51fcc317884752b84
Build Date:	 2022-09-04

@Slach
Copy link
Collaborator

Slach commented Oct 3, 2022

backups didn't delete because you have incremental backup sequences

we avoid delete backup if some in current backup list contains reference to backup which should delete

looks

I think, we can improve current algorithm and try to resolve recursive incremental backup sequence
https://github.com/Altinity/clickhouse-backup/blob/master/pkg/storage/utils.go#L13-L49
don't hesitate to make merge request

@Slach Slach changed the title ftp remote is not being cleaned keep_backups_remote behavior shall respect for recursive incremental sequences Oct 13, 2022
@Slach Slach mentioned this issue Oct 20, 2022
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@Slach Slach

Labels
None yet
Projects
None yet
Milestone
2.1.0
Development

Successfully merging a pull request may close this issue.

Release 2.1.0
2 participants
@Slach @marknefedov