前段时间学习opengauss3.1.1,在openeuler 2203 sp3下部署一主二备集群，5.0.0发布后，尝试从3.1.1升级到5.0.0。

系统资源情况
操作系统版本
[root@openeuler1 srv]# cat /etc/os-release
NAME=“openEuler”
VERSION=“20.03 (LTS-SP3)”
ID=“openEuler”
VERSION_ID=“20.03”
PRETTY_NAME=“openEuler 20.03 (LTS-SP3)”
ANSI_COLOR=“0;31”

硬盘空间

[root@openeuler1 srv]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 1.7G 0 1.7G 0% /dev
tmpfs 1.7G 12K 1.7G 1% /dev/shm
tmpfs 1.7G 17M 1.7G 1% /run
tmpfs 1.7G 0 1.7G 0% /sys/fs/cgroup
/dev/mapper/openeuler-root 54G 42G 9.7G 82% /
tmpfs 1.7G 0 1.7G 0% /tmp
/dev/sda1 974M 181M 726M 20% /boot
tmpfs 343M 0 343M 0% /run/user/5701
tmpfs 343M 0 343M 0% /run/user/0

python版本
[root@openeuler1 srv]# python -V
Python 3.7.9

集群状态
[omm@openeuler1 ~]$ gs_om -t status --detail
[ CMServer State ]

node node_ip instance state

1 openeuler1 192.168.56.31 1 /opt/huawei/data/cmserver/cm_server Standby
2 openeuler2 192.168.56.32 2 /opt/huawei/data/cmserver/cm_server Standby
3 openeuler3 192.168.56.33 3 /opt/huawei/data/cmserver/cm_server Primary

[ Cluster State ]

cluster_state : Normal
redistributing : No
balanced : Yes
current_az : AZ_ALL

[ Datanode State ]

node node_ip instance state

1 openeuler1 192.168.56.31 6001 /opt/huawei/install/data/dn P Primary Normal
2 openeuler2 192.168.56.32 6002 /opt/huawei/install/data/dn S Standby Normal
3 openeuler3 192.168.56.33 6003 /opt/huawei/install/data/dn S Standby Normal
下载5.0.0安装包

解压安装包
tar xvf openGauss-5.0.0-openEuler-64bit-all.tar.gz
tar xvf openGauss-5.0.0-openEuler-64bit.tar.bz2
tar xvf openGauss-5.0.0-openEuler-64bit-om.tar.gz
tar xvf openGauss-5.0.0-openEuler-64bit-cm.tar.gz
在三个节点间建立互信
进行预安装检查
[root@openeuler2 srv]# script/gs_preinstall -X db.xml -U omm -G dbgrp
Parsing the configuration file.
Successfully parsed the configuration file.
Installing the tools on the local node.
Successfully installed the tools on the local node.
Are you sure you want to create trust for root (yes/no)?no
Setting host ip env
Successfully set host ip env.
Distributing package.
Begin to distribute package to tool path.
Successfully distribute package to tool path.
Begin to distribute package to package path.
Successfully distribute package to package path.
Successfully distributed package.
Are you sure you want to create the user[omm] and create trust for it (yes/no)? no
Preparing SSH service.
Successfully prepared SSH service.
Installing the tools in the cluster.
Successfully installed the tools in the cluster.
Checking hostname mapping.
Successfully checked hostname mapping.
Checking OS software.
Successfully check os software.
Checking OS version.
Successfully checked OS version.
Creating cluster’s path.
Successfully created cluster’s path.
Set and check OS parameter.
Setting OS parameters.
Successfully set OS parameters.
Warning: Installation environment contains some warning messages.
Please get more details by “/srv/script/gs_checkos -i A -h openeuler1,openeuler2,openeuler3 --detail”.
Set and check OS parameter completed.
Preparing CRON service.
Successfully prepared CRON service.
Setting user environmental variables.
Successfully set user environmental variables.
Setting the dynamic link library.
Successfully set the dynamic link library.
Setting Core file
Successfully set core path.
Setting pssh path
Successfully set pssh path.
Setting Cgroup.
Successfully set Cgroup.
Set ARM Optimization.
No need to set ARM Optimization.
Fixing server package owner.
Setting finish flag.
Successfully set finish flag.
Preinstallation succeeded.
检查通过，进行升级
切换为omm 用户
su - omm
[omm@openeuler2 srv]$ gs_upgradectl -X db.xml -t auto-upgrade --grey
Static configuration matched with old static configuration files.
Wait for the cluster status normal or degrade.
Start check CMS parameter.
Old cluster version number less than 92574.
Successfully set upgrade_mode to 0.
Checking upgrade environment.
Successfully checked upgrade environment.
Start to do health check.
Successfully checked cluster status.
Upgrade all nodes.
NOTICE: The directory /opt/huawei/install/app_70980198 will be deleted after commit-upgrade, please make sure there is no personal data.
Performing grey rollback.
No need to rollback.
The directory /opt/huawei/install/app_70980198 will be deleted after commit-upgrade, please make sure there is no personal data.
Installing new binary.
Wait for the cluster status normal or degrade.
copy certs from /opt/huawei/install/app_70980198 to /opt/huawei/install/app_a07d57c3.
Successfully copy certs from /opt/huawei/install/app_70980198 to /opt/huawei/install/app_a07d57c3.
Successfully backup hotpatch config file.
Sync cluster configuration.
Successfully synced cluster configuration.
Switch symbolic link to new binary directory.
Successfully switch symbolic link to new binary directory.
Start check CMS parameter.
Old cluster version number less than 92574.
Switching all db processes.
Check cluster state.
Cluster state: [ Cluster State ]

cluster_state : Normal
redistributing : No
current_az : AZ_ALL

[ Datanode State ]

node      node_ip         port      instance     state

---

1 openeuler1 192.168.56.31 15400 6001 P Standby Normal
2 openeuler2 192.168.56.32 15400 6002 S Standby Normal
3 openeuler3 192.168.56.33 15400 6003 S Primary Normal
Wait for the cluster status normal or degrade.
Wait for the cluster status normal or degrade.
Create checkpoint before switching.
Start to wait for om_monitor.
Switching DN processes.
Switch DN processes for rolling upgrade.
Ready to grey start cluster.
Grey start cluster successfully.
Wait for the cluster status normal or degrade.
Successfully switch all process version
The nodes [‘openeuler1’, ‘openeuler2’, ‘openeuler3’] have been successfully upgraded to new version. Then do health check.
Start to do health check.
Successfully checked cluster status.
Waiting for the cluster status to become normal.
.
The cluster status is normal.
Upgrade main process has been finished, user can do some check now.
Once the check done, please execute following command to commit upgrade:

gs_upgradectl -t commit-upgrade -X /srv/db.xml

Successfully upgrade all nodes.
二进制升级成功，版本如下
[omm@openeuler2 srv]$ gsql -V
gsql (openGauss 5.0.0 build a07d57c3) compiled at 2023-03-29 03:09:38 commit 0 last mr
[omm@openeuler2 srv]$ gs_om -V
gs_om (openGauss OM 5.0.0 build 244a7e05) compiled at 2023-03-29 03:23:37 commit 0 last mr

提交升级
[omm@openeuler2 srv]$ gs_upgradectl -t commit-upgrade -X /srv/db.xml
Wait for the cluster status normal or degrade.
Start check CMS parameter.
Old cluster version number less than 92574.
Start to do health check.
Successfully checked cluster status.
Wait for the cluster status normal or degrade.
Wait for the cluster status normal or degrade.
Start check CMS parameter.
Old cluster version number less than 92574.
Successfully cleaned old install path.
Commit upgrade succeeded.
Start check CMS parameter.
Old cluster version number less than 92574.

进行TPCH测试
用dbgen 生成1G数据
dbgen -s 1 -f
建库tpch并导入数据

[omm@openeuler1 ~]$ gsql -r -d postgres -p 15400
gsql ((openGauss 5.0.0 build a07d57c3) compiled at 2023-03-29 03:37:13 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type “help” for help.

openGauss=# \du
List of roles
Role name | Attributes | Member of
-----------±-----------------------------------------------------------------------------------------------------------------±----------
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}

openGauss=# create user test_user identified by ‘admin@110’;
CREATE ROLE
openGauss=# create database tpch owner test_user;
CREATE DATABASE
openGauss=# \c tpch
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database “tpch” as user “omm”.
tpch=# grant all on tpch to test_user;
ERROR: relation “tpch” does not exist
tpch=# grant all on database tpch to test_user;
GRANT
tpch=# grant usage on schema public to test_user;
GRANT
tpch=# GRANT ALL PRIVILEGES TO test_user;
ALTER ROLE
导入数据
\COPY lineitem FROM ‘/srv/tpch/lineitem.tbl’ WITH DELIMITER ‘|’;

\COPY orders FROM ‘/srv/tpch/orders.tbl’ WITH DELIMITER ‘|’;

\COPY partsupp FROM ‘/srv/tpch/partsupp.tbl’ WITH DELIMITER ‘|’;

\COPY customer FROM ‘/srv/tpch/customer.tbl’ WITH DELIMITER ‘|’;

\COPY supplier FROM ‘/srv/tpch/supplier.tbl’ WITH DELIMITER ‘|’;

\COPY nation FROM ‘/srv/tpch/nation.tbl’ WITH DELIMITER ‘|’;

\COPY region FROM ‘/srv/tpch/region.tbl’ WITH DELIMITER ‘|’;

\COPY part FROM ‘/srv/tpch/part.tbl’ WITH DELIMITER ‘|’;

测试脚本
total_cost=0
for i in {1…22}
do
echo “begin run Q{i},tpch-qi,tpch−qi.sql , date"
begin_time=date +%s.%N
gsql -d tpch -Utest_user -W admin@110 -f query_{i}.sql>log_qi.sql>logq​{i}.out
rc=? end_time=`date +%s.%N` cost=`echo "end_time-begin_time"|bc` total_cost=`echo "begint​ime"∣bc‘totalc​ost=‘echo"total_cost+$cost”|bcif [ $rc -ne 0 ] ; then printf "run Q%s fail, cost: %.2f, totalCost: %.2f,date\n" $i $cost $total_cost else printf "run Q%s succ, cost: %.2f, totalCost: %.2f,date`\n" $i $cost $total_cost
fi
done
在opengauss3.1.1下的结果

run Q1 succ, cost: 20.09, totalCost: 20.09, Fri Apr 7 12:44:26 CST 2023
begin run Q2, tpch-q2.sql , Fri Apr 7 12:44:26 CST 2023
run Q2 succ, cost: 0.10, totalCost: 20.19, Fri Apr 7 12:44:26 CST 2023
begin run Q3, tpch-q3.sql , Fri Apr 7 12:44:26 CST 2023
run Q3 succ, cost: 2.58, totalCost: 22.77, Fri Apr 7 12:44:29 CST 2023
begin run Q4, tpch-q4.sql , Fri Apr 7 12:44:29 CST 2023
run Q4 succ, cost: 2.43, totalCost: 25.20, Fri Apr 7 12:44:31 CST 2023
begin run Q5, tpch-q5.sql , Fri Apr 7 12:44:31 CST 2023
run Q5 succ, cost: 1.93, totalCost: 27.13, Fri Apr 7 12:44:33 CST 2023
begin run Q6, tpch-q6.sql , Fri Apr 7 12:44:33 CST 2023
run Q6 succ, cost: 1.95, totalCost: 29.08, Fri Apr 7 12:44:35 CST 2023
begin run Q7, tpch-q7.sql , Fri Apr 7 12:44:35 CST 2023
run Q7 succ, cost: 2.42, totalCost: 31.50, Fri Apr 7 12:44:37 CST 2023
begin run Q8, tpch-q8.sql , Fri Apr 7 12:44:37 CST 2023
run Q8 succ, cost: 1.94, totalCost: 33.44, Fri Apr 7 12:44:39 CST 2023
begin run Q9, tpch-q9.sql , Fri Apr 7 12:44:39 CST 2023
run Q9 succ, cost: 5.76, totalCost: 39.20, Fri Apr 7 12:44:45 CST 2023
begin run Q10, tpch-q10.sql , Fri Apr 7 12:44:45 CST 2023
run Q10 succ, cost: 2.87, totalCost: 42.07, Fri Apr 7 12:44:48 CST 2023
begin run Q11, tpch-q11.sql , Fri Apr 7 12:44:48 CST 2023
run Q11 succ, cost: 0.35, totalCost: 42.42, Fri Apr 7 12:44:48 CST 2023
begin run Q12, tpch-q12.sql , Fri Apr 7 12:44:48 CST 2023
run Q12 succ, cost: 2.96, totalCost: 45.38, Fri Apr 7 12:44:51 CST 2023
begin run Q13, tpch-q13.sql , Fri Apr 7 12:44:51 CST 2023
run Q13 succ, cost: 3.74, totalCost: 49.12, Fri Apr 7 12:44:55 CST 2023
begin run Q14, tpch-q14.sql , Fri Apr 7 12:44:55 CST 2023
run Q14 succ, cost: 2.49, totalCost: 51.61, Fri Apr 7 12:44:57 CST 2023
begin run Q15, tpch-q15.sql , Fri Apr 7 12:44:57 CST 2023
run Q15 succ, cost: 4.09, totalCost: 55.70, Fri Apr 7 12:45:02 CST 2023
begin run Q16, tpch-q16.sql , Fri Apr 7 12:45:02 CST 2023
run Q16 succ, cost: 0.86, totalCost: 56.56, Fri Apr 7 12:45:02 CST 2023
begin run Q17, tpch-q17.sql , Fri Apr 7 12:45:02 CST 2023
run Q17 succ, cost: 2.53, totalCost: 59.09, Fri Apr 7 12:45:05 CST 2023
begin run Q18, tpch-q18.sql , Fri Apr 7 12:45:05 CST 2023
run Q18 succ, cost: 7.16, totalCost: 66.25, Fri Apr 7 12:45:12 CST 2023
begin run Q19, tpch-q19.sql , Fri Apr 7 12:45:12 CST 2023
run Q19 succ, cost: 2.17, totalCost: 68.42, Fri Apr 7 12:45:14 CST 2023
begin run Q20, tpch-q20.sql , Fri Apr 7 12:45:14 CST 2023
run Q20 succ, cost: 3.38, totalCost: 71.80, Fri Apr 7 12:45:18 CST 2023
begin run Q21, tpch-q21.sql , Fri Apr 7 12:45:18 CST 2023
run Q21 succ, cost: 62.73, totalCost: 134.52, Fri Apr 7 12:46:20 CST 2023
begin run Q22, tpch-q22.sql , Fri Apr 7 12:46:20 CST 2023
run Q22 succ, cost: 1.02, totalCost: 135.54, Fri Apr 7 12:46:21 CST 2023

在openGauss5.0下的结果

begin run Q1, tpch-q1.sql , Fri Apr 7 14:36:53 CST 2023
run Q1 succ, cost: 17.54, totalCost: 17.54, Fri Apr 7 14:37:11 CST 2023
begin run Q2, tpch-q2.sql , Fri Apr 7 14:37:11 CST 2023
run Q2 succ, cost: 9.08, totalCost: 26.62, Fri Apr 7 14:37:20 CST 2023
begin run Q3, tpch-q3.sql , Fri Apr 7 14:37:20 CST 2023
run Q3 succ, cost: 2.46, totalCost: 29.08, Fri Apr 7 14:37:22 CST 2023
begin run Q4, tpch-q4.sql , Fri Apr 7 14:37:22 CST 2023
run Q4 succ, cost: 14.75, totalCost: 43.83, Fri Apr 7 14:37:37 CST 2023
begin run Q5, tpch-q5.sql , Fri Apr 7 14:37:37 CST 2023
run Q5 succ, cost: 2.15, totalCost: 45.98, Fri Apr 7 14:37:39 CST 2023
begin run Q6, tpch-q6.sql , Fri Apr 7 14:37:39 CST 2023
run Q6 succ, cost: 1.84, totalCost: 47.81, Fri Apr 7 14:37:41 CST 2023
begin run Q7, tpch-q7.sql , Fri Apr 7 14:37:41 CST 2023
run Q7 succ, cost: 2.98, totalCost: 50.79, Fri Apr 7 14:37:44 CST 2023
begin run Q8, tpch-q8.sql , Fri Apr 7 14:37:44 CST 2023
run Q8 succ, cost: 2.12, totalCost: 52.91, Fri Apr 7 14:37:46 CST 2023
begin run Q9, tpch-q9.sql , Fri Apr 7 14:37:46 CST 2023
run Q9 succ, cost: 4.91, totalCost: 57.82, Fri Apr 7 14:37:51 CST 2023
begin run Q10, tpch-q10.sql , Fri Apr 7 14:37:51 CST 2023
run Q10 succ, cost: 2.88, totalCost: 60.71, Fri Apr 7 14:37:54 CST 2023
begin run Q11, tpch-q11.sql , Fri Apr 7 14:37:54 CST 2023
run Q11 succ, cost: 0.31, totalCost: 61.01, Fri Apr 7 14:37:54 CST 2023
begin run Q12, tpch-q12.sql , Fri Apr 7 14:37:54 CST 2023
run Q12 succ, cost: 2.11, totalCost: 63.12, Fri Apr 7 14:37:56 CST 2023
begin run Q13, tpch-q13.sql , Fri Apr 7 14:37:56 CST 2023
run Q13 succ, cost: 1.87, totalCost: 64.99, Fri Apr 7 14:37:58 CST 2023
begin run Q14, tpch-q14.sql , Fri Apr 7 14:37:58 CST 2023
run Q14 succ, cost: 1.72, totalCost: 66.71, Fri Apr 7 14:38:00 CST 2023
begin run Q15, tpch-q15.sql , Fri Apr 7 14:38:00 CST 2023
run Q15 succ, cost: 3.74, totalCost: 70.45, Fri Apr 7 14:38:04 CST 2023
begin run Q16, tpch-q16.sql , Fri Apr 7 14:38:04 CST 2023
run Q16 succ, cost: 0.65, totalCost: 71.10, Fri Apr 7 14:38:04 CST 2023
begin run Q17, tpch-q17.sql , Fri Apr 7 14:38:04 CST 2023
run Q17 succ, cost: 2.48, totalCost: 73.58, Fri Apr 7 14:38:07 CST 2023
begin run Q18, tpch-q18.sql , Fri Apr 7 14:38:07 CST 2023
run Q18 succ, cost: 6.84, totalCost: 80.41, Fri Apr 7 14:38:14 CST 2023
begin run Q19, tpch-q19.sql , Fri Apr 7 14:38:14 CST 2023
run Q19 succ, cost: 2.92, totalCost: 83.34, Fri Apr 7 14:38:17 CST 2023
begin run Q20, tpch-q20.sql , Fri Apr 7 14:38:17 CST 2023
run Q20 succ, cost: 3.06, totalCost: 86.39, Fri Apr 7 14:38:20 CST 2023
begin run Q21, tpch-q21.sql , Fri Apr 7 14:38:20 CST 2023
run Q21 succ, cost: 5.24, totalCost: 91.64, Fri Apr 7 14:38:25 CST 2023
begin run Q22, tpch-q22.sql , Fri Apr 7 14:38:25 CST 2023
run Q22 succ, cost: 0.43, totalCost: 92.07, Fri Apr 7 14:38:25 CST 2023
每个查询相比前一版本都有大幅度提升。