记一次单机clickhouse升级

前言

clickhouse低版本不支持表级注释，根据官方说明，表级注释从23.9才开始提供，但目前线上测试环境的clickhouse版本为20.5，根据要求必须加上表级注释

https://clickhouse.com/docs/en/sql-reference/statements/alter/comment

升级准备：

1. 下载clickhouse的rpm包：根据机器选择对应架构包：clickhouse-client、clickhouse-common-static、clickhouse-server
   

   https://github.com/ClickHouse/ClickHouse/releases

2. 查询/etc/clickhouse-server/config.xml中\<path/>配置的数数据目录: /data/clickhouse
3. 停止数据库用于备份数据: service clickhouse-server stop
4. 备份数据目录: /usr/bin/cp -r /data/clickhouse /data/clickhouse_bak
5. 备份配置文件: /etc/clickhouse-server下的config.xml与users.xml

升级操作

1. 因当前版本通过rpm安装，需先卸载：

   # 查询有哪些包，需要卸载
   rpm -qa | grepclickhouse
   # 卸载
   rpm -e clickhouse-server-20.5.4.40-2.noarch
   rpm -e clickhouse-common-static-20.5.4.40-2.x86_64
   rpm -e clickhouse-clisent-20.5.4.40-2.noarch

2. 安装新版clickhouse
   将rpm包上传至服务器独立的目录，执行以下语句安装

   rpm -ivh *.rpm

3. 恢复配置文件：

   rm -f /etc/clickhouse-server/config.xml
   rm -f /etc/clickhouse-server/user.xml
   cp /root/clickhouse_config.xml /etc/clickhouse-server/config.xml
   cp /root/clickhouse_users.xml /etc/clickhouse-server/users.xml
   chown clickhouse:clickhouse /etc/clickhouse-server/config.xml
   chown clickhouse:clickhouse /etc/clickhouse-server/users.xml

4. 启动clickhouse

   service clickhouse-server stop
   service clickhouse-server start
   或
   service clickhouse-server restart

   若启动失败，需查询错误日志：
   /var/log/clickhouse-server/clickhouse-server.err.log
   出现如下报错：
   DB::Exception: Datadirectory for system database exists, but metadata file does not. probably you are trying to upgrade fro version older than 20.7. If so, you should through intermediate version.
   报错很明显了：提示数据文件目录存在，但元数据文件不存在，升级失败了，但他提供了解决方法：通过中间版本升级，那就按他的，按前面的步骤先升级到20.7，再启动，启动成功后再升级到目标版本即可