【测试类型：SQL功能】【测试版本：5.1.1】【自动化】一主两备，涉及执行索引相关SQL卡死，且重启集群，备机重启失败

【标题描述】: 一主两备，涉及执行索引相关SQL后，备机重启失败
【测试类型：SQL功能】【测试版本：5.1.1】 一主两备，涉及执行索引相关SQL后，备机重启失败

【操作系统和硬件信息】(查询命令: cat /etc/system-release, uname -a):
CentOS Linux release 7.9.2009 (Core)

【测试环境】(单机/1主x备x级联备):
一主两备

【被测功能】:
正常SQL执行后，进行重启

【测试类型】:
功能

【数据库版本】(查询命令: gaussdb -V):
gaussdb (openGauss 5.1.0 build 4e532edf) compiled at 2023-09-22 10:07:20 commit 0 last mr

【预置条件】:
数据库状态正常

【操作步骤】(请填写详细的操作步骤):
1. 设置guc参数，重启（详细步骤见【日志信息】）
gs_guc set -N all -D /openGauss/peilq_centos/cluster/dn1 -c "synchronous_standby_names='*'";
gs_guc set -N all -D /openGauss/peilq_centos/cluster/dn1 -c "synchronous_commit='remote_apply'";
gs_guc set -N all -D /openGauss/peilq_centos/cluster/dn1 -c "hot_standby=on";
gs_guc set -N all -D /openGauss/peilq_centos/cluster/dn1 -c "wal_level='hot_standby'";
gs_guc set -N all -D /openGauss/peilq_centos/cluster/dn1 -c "recovery_max_workers=4"; 
2. 执行建表建索引语句
drop table if exists t_hash_index_0003;
create table t_hash_index_0003 (id int, num int, sex varchar(20) default 'male');
insert into t_hash_index_0003 select random()*10, random()*3, 'XXX' from generate_series(1,5000);
drop index if exists i_hash_index_0003;
create index i_hash_index_0003 on t_hash_index_0003 using hash (id);
3. 重启备机

【预期输出】:
1. 设置guc参数，重启成功
2. 执行建表建索引语句成功
3. 重启备机成功

【实际输出】:
1. 设置guc参数，重启成功
2. 执行建表建索引语句卡死
3. 重启备机失败

【原因分析】:
1. 这个问题的根因
2. 问题推断过程
3. 还有哪些原因可能造成类似现象
4. 该问题是否有临时规避措施
5. 问题解决方案
6. 预计修复问题时间

【日志信息】(请附上日志文件、截图、coredump信息):

```
[2023-09-26 17:27:08 INFO Logger.py 25 3323287] --修改参数 确认落盘--
[2023-09-26 17:27:08 INFO Logger.py 25 3323287] ----gs_guc start execute----
[2023-09-26 17:27:08 INFO Logger.py 25 3323287] source /home/peilq_centos/gaussdb.bashrc; gs_guc set -N all -D /openGauss/peilq_centos/cluster/dn1 -c "synchronous_standby_names='*'";
[2023-09-26 17:27:08 INFO Logger.py 25 3323287] The gs_guc run with the following arguments: [gs_guc -N all -D /openGauss/peilq_centos/cluster/dn1 -c synchronous_standby_names='*' set ].
Begin to perform the total nodes: 3.
Popen count is 3, Popen success count is 3, Popen failure count is 0.
Begin to perform gs_guc for datanodes.
Command count is 3, Command success count is 3, Command failure count is 0.

Total instances: 3. Failed instances: 0.
ALL: Success to perform gs_guc!
[2023-09-26 17:27:13 INFO Logger.py 25 3323287] ----gs_guc start execute----
[2023-09-26 17:27:13 INFO Logger.py 25 3323287] source /home/peilq_centos/gaussdb.bashrc; gs_guc set -N all -D /openGauss/peilq_centos/cluster/dn1 -c "synchronous_commit='remote_apply'";
[2023-09-26 17:27:14 INFO Logger.py 25 3323287] The gs_guc run with the following arguments: [gs_guc -N all -D /openGauss/peilq_centos/cluster/dn1 -c synchronous_commit='remote_apply' set ].
Begin to perform the total nodes: 3.
Popen count is 3, Popen success count is 3, Popen failure count is 0.
Begin to perform gs_guc for datanodes.
Command count is 3, Command success count is 3, Command failure count is 0.

Total instances: 3. Failed instances: 0.
ALL: Success to perform gs_guc!
[2023-09-26 17:27:19 INFO Logger.py 25 3323287] ----gs_guc start execute----
[2023-09-26 17:27:19 INFO Logger.py 25 3323287] source /home/peilq_centos/gaussdb.bashrc; gs_guc set -N all -D /openGauss/peilq_centos/cluster/dn1 -c "hot_standby=on";
[2023-09-26 17:27:20 INFO Logger.py 25 3323287] The gs_guc run with the following arguments: [gs_guc -N all -D /openGauss/peilq_centos/cluster/dn1 -c hot_standby=on set ].
NOTICE: When hot_standby set to on, wal_level must be set to hot_standby. Otherwise it will cause the database can not be started. In the dual-system environments, hot_standby can not be set to off.
Begin to perform the total nodes: 3.
Popen count is 3, Popen success count is 3, Popen failure count is 0.
Begin to perform gs_guc for datanodes.
Command count is 3, Command success count is 3, Command failure count is 0.

Total instances: 3. Failed instances: 0.
ALL: Success to perform gs_guc!
[2023-09-26 17:27:25 INFO Logger.py 25 3323287] ----gs_guc start execute----
[2023-09-26 17:27:25 INFO Logger.py 25 3323287] source /home/peilq_centos/gaussdb.bashrc; gs_guc set -N all -D /openGauss/peilq_centos/cluster/dn1 -c "wal_level='hot_standby'";
[2023-09-26 17:27:25 INFO Logger.py 25 3323287] The gs_guc run with the following arguments: [gs_guc -N all -D /openGauss/peilq_centos/cluster/dn1 -c wal_level='hot_standby' set ].
NOTICE: If you need to copy the data stream for WAL log archiving and standby machine. You must be set to the parameter with archive or hot_standby. If this parameter is setted to archive. The hot_standby must be setted to off, otherwise it will cause the database can not be started, at the same time the max_wal_senders must be set at least 1.
Begin to perform the total nodes: 3.
Popen count is 3, Popen success count is 3, Popen failure count is 0.
Begin to perform gs_guc for datanodes.
Command count is 3, Command success count is 3, Command failure count is 0.

Total instances: 3. Failed instances: 0.
ALL: Success to perform gs_guc!
[2023-09-26 17:27:30 INFO Logger.py 25 3323287] ----restart_db_cluster----
[2023-09-26 17:27:47 INFO Logger.py 25 3323287] Stopping cluster.
=========================================
Successfully stopped cluster.
=========================================
End stop cluster.
Starting cluster.
=========================================
[SUCCESS] kwepwebenv02644
2023-09-26 17:27:34.688 6512a406.1 [unknown] 140187195064832 [unknown] 0 dn_6001_6002_6003 01000  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2023-09-26 17:27:34.694 6512a406.1 [unknown] 140187195064832 [unknown] 0 dn_6001_6002_6003 01000  0 [BACKEND] WARNING:  Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (4475 Mbytes) is larger.
[SUCCESS] kwepwebenv07952
2023-09-26 17:27:30.870 6512a402.1 [unknown] 139951826371072 [unknown] 0 dn_6001_6002_6003 01000  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2023-09-26 17:27:30.875 6512a402.1 [unknown] 139951826371072 [unknown] 0 dn_6001_6002_6003 01000  0 [BACKEND] WARNING:  Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (4475 Mbytes) is larger.
[SUCCESS] kwemhisprc10431
2023-09-26 17:27:39.372 6512a40b.1 [unknown] 140265248162304 [unknown] 0 dn_6001_6002_6003 01000  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2023-09-26 17:27:39.375 6512a40b.1 [unknown] 140265248162304 [unknown] 0 dn_6001_6002_6003 01000  0 [BACKEND] WARNING:  Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (4475 Mbytes) is larger.
Waiting for check cluster state...
=========================================
Successfully started.
[2023-09-26 17:27:50 INFO Logger.py 25 3323287] True
[2023-09-26 17:27:50 INFO Logger.py 25 3323287] source /home/peilq_centos/gaussdb.bashrc;gs_om -t status
[2023-09-26 17:27:51 INFO Logger.py 25 3323287] -----------------------------------------------------------------------

cluster_name    : peilq_centos
cluster_state   : Normal
redistributing  : No

-----------------------------------------------------------------------
[2023-09-26 17:27:51 INFO Logger.py 25 3323287] --step1:设置参数大于1即可;expect:修改成功--
[2023-09-26 17:27:57 INFO Logger.py 25 3323287] source /home/peilq_centos/gaussdb.bashrc; gsql -d tpccdb -p 15000 -r  -c "show recovery_max_workers;"
[2023-09-26 17:27:57 INFO Logger.py 25 3323287] recovery_max_workers
----------------------
 4
(1 row)
[2023-09-26 17:27:57 INFO Logger.py 25 3323287] ----gs_guc start execute----
[2023-09-26 17:27:57 INFO Logger.py 25 3323287] source /home/peilq_centos/gaussdb.bashrc; gs_guc set -N all -D /openGauss/peilq_centos/cluster/dn1 -c "recovery_max_workers=4";
[2023-09-26 17:27:57 INFO Logger.py 25 3323287] The gs_guc run with the following arguments: [gs_guc -N all -D /openGauss/peilq_centos/cluster/dn1 -c recovery_max_workers=4 set ].
Begin to perform the total nodes: 3.
Popen count is 3, Popen success count is 3, Popen failure count is 0.
Begin to perform gs_guc for datanodes.
Command count is 3, Command success count is 3, Command failure count is 0.

Total instances: 3. Failed instances: 0.
ALL: Success to perform gs_guc!
[2023-09-26 17:28:02 INFO Logger.py 25 3323287] True
[2023-09-26 17:28:02 INFO Logger.py 25 3323287] --step2:重启数据库;expect:重启成功--
[2023-09-26 17:28:02 INFO Logger.py 25 3323287] ----restart_db_cluster----
[2023-09-26 17:28:16 INFO Logger.py 25 3323287] Stopping cluster.
=========================================
Successfully stopped cluster.
=========================================
End stop cluster.
Starting cluster.
=========================================
[SUCCESS] kwepwebenv02644
2023-09-26 17:28:06.904 6512a426.1 [unknown] 139659924046336 [unknown] 0 dn_6001_6002_6003 01000  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2023-09-26 17:28:06.908 6512a426.1 [unknown] 139659924046336 [unknown] 0 dn_6001_6002_6003 01000  0 [BACKEND] WARNING:  Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (4475 Mbytes) is larger.
[SUCCESS] kwepwebenv07952
2023-09-26 17:28:03.119 6512a423.1 [unknown] 139707471675904 [unknown] 0 dn_6001_6002_6003 01000  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2023-09-26 17:28:03.125 6512a423.1 [unknown] 139707471675904 [unknown] 0 dn_6001_6002_6003 01000  0 [BACKEND] WARNING:  Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (4475 Mbytes) is larger.
[SUCCESS] kwemhisprc10431
2023-09-26 17:28:12.739 6512a42c.1 [unknown] 139985520902656 [unknown] 0 dn_6001_6002_6003 01000  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2023-09-26 17:28:12.743 6512a42c.1 [unknown] 139985520902656 [unknown] 0 dn_6001_6002_6003 01000  0 [BACKEND] WARNING:  Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (4475 Mbytes) is larger.
=========================================
Successfully started.
[2023-09-26 17:28:19 INFO Logger.py 25 3323287] True
[2023-09-26 17:28:19 INFO Logger.py 25 3323287] source /home/peilq_centos/gaussdb.bashrc;gs_om -t status --detail
[2023-09-26 17:28:20 INFO Logger.py 25 3323287] --step3:建表并创建hash索引;expect:创建成功--
drop table if exists t_hash_index_0003;
create table t_hash_index_0003 (id int, num int, sex varchar(20) default 'male');
insert into t_hash_index_0003 select random()*10, random()*3, 'XXX' from generate_series(1,5000);
drop index if exists i_hash_index_0003;
create index i_hash_index_0003 on t_hash_index_0003 using hash (id);

```

备机重启pg日志：
![输入图片说明](https://foruda.gitee.com/images/1695778489720756228/588dde82_8568116.png "屏幕截图")
【测试代码】:

openGauss/openGauss-server

内容风险标识

评论 (3)

openGauss/openGauss-server .gitee-modal { width: 500px !important; }

内容风险标识