Dedicated clickhouse-backup server restores only the schema, not the data

[LVH-27]

I am running a single ClickHouse deployment in Kubernetes, with no sharding or replication. I installed clickhouse-backup on that pod to try it out. Managed to create a backup and restore the data just fine, no issues.

I then tried to deploy a dedicated Kubernetes deployment for clickhouse-backup, using this Docker image, hoping to have a standalone system for managing backups. After some initial hiccups, I managed to configure the clickhouse-backup deployment to communicate with the ClickHouse server deployment - cURLing /backup/tables returned the list of tables in the database.

I also "managed to create" a backup. But when I deleted a table and tried restoring it, it only restored the table schema, not the data itself. The behavior was the same regardless of whether I did it through the API or through the shell client.

Backup creation:

bash-5.0# clickhouse-backup create --tables=test_backup.* test_backup
2021/10/18 06:14:58  warn can't get create database query: code: 107, message: Cannot openfile /var/lib/clickhouse/metadata/default.sql, errno: 2, strerror: No such file or directory
2021/10/18 06:14:58  info done                      backup=test_backup operation=create table=test_backup.Table1
2021/10/18 06:14:58  info done                      backup=test_backup operation=create table=test_backup.Table2
2021/10/18 06:14:58  info done                      backup=test_backup duration=253ms operation=create

Other than the warning, everything seems okay. And the warning itself seems to be handled in the code.

Restoration attempt (after having deleted Table1):

bash-5.0# clickhouse-backup restore --tables=test_backup.Table1 test_backup
2021/10/18 06:20:41  info done                      backup=test_backup operation=restore table=test_backup.Table1
2021/10/18 06:20:41  info done                      backup=test_backup duration=114ms operation=restore
2021/10/18 06:20:41  info done                      backup=test_backup operation=restore

The metadata/ directory is present, but it seems the shadow/ directory is just not being created. Am I doing something wrong?

deployment spec yaml:

spec:
      containers:
        - name: clickhouse-backup
          image: alexakulov/clickhouse-backup:1.1.0
          args: ["server"]
          env:
          # env vars: CH and clickhouse-backup API server credentials
          volumeMounts:
          - name: data
            mountPath: /var/lib/clickhouse/backup
          - name: config
            mountPath: /etc/clickhouse-backup
          ports:
          - containerPort: 7171
            name: http
            protocol: TCP
      volumes:
        - name: data
          persistentVolumeClaim:
            claimName: myClaim
        - name: config
          configMap:
            name: clickhouse-backup-config

config.yml:

    clickhouse:
      host: my.host.name
      port: 9000
    api:
      listen: "0.0.0.0:7171"

[Slach]

@LVH-27 did you define deployment.spec.template.spec.volumes.persistentVolumeClaim in your ClickHouse Deployment and deployment.spec.template.spec.containers.volumeMounts where mount /var/lib/clickhouse? clickhouse-backup container need access to /var/lib/clickhouse inside clickhouse-server container

[Slach]

You should define spec.containers with two containers first clickhouse-backup:1.1.0 and yandex/clickhouse-server

[LVH-27]

Hi @Slach, thanks for your input and sorry for the late reply. I mounted ClickHouse server's data PVC (in that deployment it's mounted on /var/lib/clickhouse) onto /var/lib/clickhouse in the clickhouse-backup deployment and then it worked. Silly of me to expect it to work locally without mounting the data partition.

However, it is also a bit silly to keep the backups on the same volume as the actual data, so I am now trying to get an SFTP remote storage working. I added a container running an SFTP server to the same pod as clickhouse-backup and managed to connect the two through the shell:

apk add lftp openssh-client
ssh my_user@localhost  # done to get the host SSH key; localhost works because the two containers are in the same pod
lftp sftp://my_user@localhost

I also modified the config.yml to add an SFTP remote storage instance. The file now has the following contents:

general:
  remote: sftp
  log_level: debug                                                                                                                                                                                                 clickhouse:
  host: my.host.name
  port: 9000
api:
  listen: "0.0.0.0:7171"                                                                                                                                                                                           sftp:
  address: localhost
  username: my_user
  password: my_pass
  path: /uploads 

However, when I try to upload the backup using clickhouse-backup upload test_backup, I get the following error:

Upload aborted: RemoteStorage set to "none"

I tried specifying the config file using the --config flag (even though the correct config file is clearly applied because I wouldn't be able to communicate with the database otherwise), but that didn't help either. I finally got it to work by setting the environment variable $REMOTE_STORAGE to sftp. I also managed to restore it, so it seems my system works.

So, why did the remote storage not work as defined in config.yml and only when I set the envvar? Did I mess something up or is it a bug?

[Slach]

when you starts clickhouse-backup server it load /etc/clickhouse-backup/config.yml and if environment variables exists it ovveride config values

please use

general:
  remote_storage: sftp

instead of

general:
  remote: sftp

also, try to use altinity/clickhouse-backup:1.1.1 image

[LVH-27]

I triple-checked if I missed something silly and then I proceed to mess up typing remote_storage. Very sorry to waste your time like that.

In the meantime I hit the error using $SFTP_PASSWORD instead of setting the password in the config (#274 ). I see that using the newer image resolved that issue and everything works as expected now. Thanks a lot for suggesting the new image!