To change datafile location for existing database

[lavanya.lvn. chinthanippu]

Hi,

I want to change the datafile location for my existing database.

I tried changing the path value in config.xml but it is reflecting for only system and default databases but not my database.

Please suggest on to change the current datafile location for my existing database.

-Thanks 

[Denis Zhuravlev]

It should work for your database as well.

Please answer questions:

1. Clickhouse version?  select version();

2. select * from system.disks ; select * from system.storage_policies;

3. show create table your_table;

4. did you move a folder with your database to a new location?

[lavanya.lvn. chinthanippu]

Hi Denis,

Thanks for replying. 

Please find my response below,

1. Clickhouse version?  select version(); -->  20.10.3.30

2. select * from system.disks ; 

┌─name -┬─path──────── ┬─free_space─┬─total_space─┬─keep_free_space─┬─type──┐

│ default │ /var/lib/clickhouse/ │ 3320283136 │  8259014656 │               0 │ local 

select * from system.storage_policies;

─policy_name┬─volume_name─┬─volume_priority─┬─disks───────┬─volume_type─┬─max_data_part_size─┬─move_factor─┐

│ default          │ default     │               1 │ ['default'] │ JBOD        │                  0 │           0 

3. show create table your_table;

SHOW CREATE TABLE Resources

┌─statement────────────────────┐

│ CREATE TABLE test.Resources

(

    `EMPID` UInt64,

    `EMPNAME` String

)

ENGINE = MergeTree()

PRIMARY KEY EMPID

ORDER BY EMPID

SETTINGS index_granularity = 8192 │

└──────────────────────────

4. did you move a folder with your database to a new location?

No. I didn't move any folder. 

I am a beginner and just started learning clickhouse database. 

Are their any different files/locations where we need to set path parameter other than config.xml?

Regards,

Lavanya

[Denis Zhuravlev]

Not sure what do you want from /var/

for example

/etc/init.d/clickhouse-server stop

 mv /var/lib/clickhouse/ /home/

 vi /etc/clickhouse-server/config.xml
      <path>/home/clickhouse/</path>
      <tmp_path>/home/clickhouse/tmp/</tmp_path>
      <user_files_path>/home/clickhouse/user_files/</user_files_path>
      <local_directory>
          <path>/home/clickhouse/access/</path>
      </local_directory>
      <format_schema_path>/home/clickhouse/format_schemas/</format_schema_path>
/etc/init.d/clickhouse-server start     

https://clickhouse.tech/docs/en/operations/server-configuration-parameters/settings/#server_configuration_parameters-path

https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/mergetree/#table_engine-mergetree-multiple-volumes