User Migration : Migrate users with roles to another CH

[akshayparseja]

Hi, we plan on selfhosting another CH for scale-test purpose and want to migrate all users to the new CH aswell, we have been using api calls to create users which do not add into the config maps , hence passwords for users are not available. Is there a way to migrate all users using a query to another CH with the roles they have.

[alex-zaitsev]

@akshayparseja , yes, you can do it with https://github.com/Altinity/clickhouse-backup/, see --rbac-only option.

If you are using operator, you need to deploy clickhouse-backup as a sidecar. See podTemplate example here: https://github.com/Altinity/clickhouse-operator/blob/master/tests/e2e/manifests/chit/tpl-clickhouse-backups.yaml

[akshayparseja]

Hi, thanks for your reply, i already have a clickhouse backup running, i am skipping tables like system and information, if i want to perform the users migration , i shall back those aswell right? or a DB backup includes user roles and credentials to migrate

[alex-zaitsev]

Users are not stored in system tables. System tables are more like 'views'. Users can be stored in configuration (in this case it is a part of CHI spec), local storage or replicated storage (Zoo)Keeper.

In latter cases DB backup includes all RBAC data, including users and roles. And you can backup only RBAC (no data), using --rbac-only flag.

[akshayparseja]

I did a complete remote_restore into another clickhouse, the restore of tables views looked fine but when i checked with show users, i did not see any user being restored. Also tried logging in with one of test user from the actual clickhouse which backup I used to restore into the new one. Gave me Auth failed error. So not sure how to do a user migration along with its data. .

There were no errors in logs for clickhouse-backup when i performed restrore .

@alex-zaitsev

[Slach]

@akshayparseja which clickhouse-backup version do you use? how did you create users via SQL or via YAML / XML?

[akshayparseja]

@Slach Hi, I am using latest image

            image: altinity/clickhouse-backup:latest

Also the users i am trying to migrate were created via the yaml of clickhouseinstallation . Under the Users: Section But in my actual use case we have users created via sql aswell as yaml so we need to migrate all users irrespective .

This check is being done between two dummy clickhouses with data and a few users.

Adding on , here i had done a whole cluster backup and restore but at the moment i want to just migrate rbac to another clickhouse sine tables and data already persist.

Either ways it didnt work and no users were migrated

[Slach]

Also the users i am trying to migrate were created via the yaml of clickhouseinstallation

Is your destination clickhouse cluster also managed by clickhouse-operator?

[akshayparseja]

yes, both my clusters are manged by operators and both have clickhouse-backup running as a container along with the actual clickhouse.

[Slach]

In this case, clickhouse-backup allows only restore data.

You need just copy / paste YAML users definition from source kind: ClickHouseInstallation to destination kind: ClickHouseInstallation manifest

[akshayparseja]

on usig --rbac-only command, why would it still download 660gb of backup from gcs. is there a better way to do this?

[Slach]

--rbac and --rbac-only applicable only for SQL created RBAC objects, not for YAML

Which command you executed?

restore_remote --rbac-only? or download + restore --rbac-only ?

you need to create_remote --rbac-only if you want backup only RBAC objects

[Slach]

you didn't read my comments --rbac-only will not applicable for your use case, cause you create users via .spec.users

about 660gb look https://github.com/Altinity/clickhouse-backup/issues/1042

[akshayparseja]

Hi, there were a few users which were created via sql , so I migrated those using the clickhouse-backup and it worked fine but ended up downloading 600gb in its local and then it took rbac only restoration. I ran

restore_remote --rbac-only thinking it would download a few kbs or mbs since its rbac only .

Maybe thats because my orignal backup was a full backup of data+users leading to this but it would be better if we could restore users only from a whole data backup (i.e backup which is not created with --rbac-only)

Thanks for all the assistance :D , was able to complete the migration

[Slach]

try to use create_remote --rbac-only + restore_remote --rbac-only