Clearing system.backup_actions table

[manasmulay]

Hello
We are running the clickhouse-backup tool in server mode, and with integration tables enabled. The problem we see is that after running it for a long time with lots of backups, queries like select status from system.backup_actions where command='create backup test' are returning errors in clickhouse.

Is there any way to clear the system.backup_actions table without restarting the clickhouse-backup server?

Sample error log

Code: 26. DB::ParsingException: Cannot parse JSON string: expected closing quote: (while reading the value of key command): While executing ParallelParsingBlockInputFormat: While executing URL: (in file/uri http://127.0.0.1:7171/backup/actions): (at row 2553)
. (CANNOT_PARSE_QUOTED_STRING) (version 23.8.11.29.altinitystable (altinity build))

[Slach]

duplicated with #1125 (comment)

do you also use /backup/list or system.backup_list to check backup status?

[manasmulay]

curl -vvv http://localhost:7171/backup/actions -o backup_actions.txt
* Host localhost:7171 was resolved.
* IPv6: ::1
* IPv4: 127.0.0.1
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0*   Trying [::1]:7171...
* connect to ::1 port 7171 from ::1 port 40356 failed: Connection refused
*   Trying 127.0.0.1:7171...
* Connected to localhost (127.0.0.1) port 7171
> GET /backup/actions HTTP/1.1
> Host: localhost:7171
> User-Agent: curl/8.8.0
> Accept: */*
>
* Request completely sent off
< HTTP/1.1 200 OK
< Cache-Control: no-store, no-cache, must-revalidate
< Content-Type: application/json; charset=UTF-8
< Pragma: no-cache
< Date: Mon, 28 Apr 2025 16:56:29 GMT
< Transfer-Encoding: chunked
<
{ [7991 bytes data]
100 5101k    0 5101k    0     0   176M      0 --:--:-- --:--:-- --:--:--  177M

The transfer size is ~5M for the entire payload, so I have only added the relevant parts from the response. Prefilter seems to have solved our issue.
For our backup workflow; we insert a create backup query into the backup_actions db and keep polling status every 5s to check failure / success. Post success we upload it to remote, poll every 5s for success and then delete local backup. All operations are done by inserting queries to the backup_actions db

[Slach]

total is 177M too much
could you try

curl http://localhost:7171/backup/actions -o backup_actions.jsonl
clickhouse-local -q "SELECT command, count() FROM file('backup_actions.jsonl') GROUP BY ALL"

[manasmulay]

It's again a pretty big file; 13015 rows and some create backup commands are retried so counts are > 1 in few places for the query. I dont think I can share it due to company data privacy policies. But does this give you the info you were looking for?

[Slach]

provide how often you execute backup related commands?

13015 rows is big number, do you run backup every hour? or separate backup for each table?

[manasmulay]

Absolutely! That’s spot on. We have a few tables that get data every hour, so we back up the data every hour too. And then there are some tables that get data every day, so we back up the data every day. The main reason we do this is because the data size is huge, and when we get new data, we drop the oldest data. So, keeping those backups doesn’t make much sense anymore. We may optimise this to reduce how frequently we trigger backups though.