原 在初始化GP时，如何自定义p和m的关系，从而达到 机房容灾 的效果

发布日期 2024年11月5日 · 已更新 2024年11月6日  

0    71    1

Tags： 原创GreenPlumgpinitsystem初始化GP机房容灾

👉 本文共约523个字，系统预计阅读时间或需2分钟。

显示/隐藏全文目录

目录

• 简介
• 架构图
• 实现过程
• 测试：机房2宕机
• 测试：机房1宕机
• 总结

简介

问题一： 安装GP时，p和m如何自定义对应关系 ？？? 达到 机房容灾？？

问题二：2个机房之间网络断开，会出现脑裂的情况吗？？？ 到底以哪个为准呢？？？？

架构图

实现过程

在GP初始化阶段，通过-O和-I参数即可实现该功能。

docker rm -f lhrgptest docker run -itd --name lhrgptest -h lhrgptest \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/greenplum:gpdball_6.25.3 \ /usr/sbin/init docker exec -it lhrgptest bash docker start mdw smdw sdw1 sdw2 sdw3 sdw4 docker exec -it mdw su - gpadmin gpstart -a gpcc start gpcc status gpstate gpdeletesystem -d /opt/greenplum/data/master/gpseg-1 -f gpinitsystem -c /home/gpadmin/conf/initgp_config -s smdw -P 5432 -S /opt/greenplum/data/master/gpseg-1 -O /home/gpadmin/conf/cluster_init.config cat /home/gpadmin/conf/cluster_init.config gpinitsystem -I /home/gpadmin/conf/cluster_init_new.config -s smdw -P 5432 -S /opt/greenplum/data/master/gpseg-1 # host~port~data_directory/seg_prefix<segment_id>~dbid~content_id # dbid从2开始依次递增，seg_prefix<segment_id>和content_id保持一致，p和m保持一致 cat > /home/gpadmin/conf/cluster_init_new.config <<"EOF" ARRAY_NAME="lhrgp" TRUSTED_SHELL=/bin/ssh CHECK_POINT_SEGMENTS=8 ENCODING=UTF-8 SEG_PREFIX=gpseg HEAP_CHECKSUM=on HBA_HOSTNAMES=0 QD_PRIMARY_ARRAY=mdw~mdw~5432~/opt/greenplum/data/master/gpseg-1~1~-1 declare -a PRIMARY_ARRAY=( sdw1~sdw1~6000~/opt/greenplum/data/primary/gpseg0~2~0 sdw1~sdw1~6001~/opt/greenplum/data/primary/gpseg1~3~1 sdw1~sdw1~6002~/opt/greenplum/data/primary/gpseg2~4~2 sdw1~sdw1~6003~/opt/greenplum/data/primary/gpseg3~5~3 sdw2~sdw2~6000~/opt/greenplum/data/primary/gpseg4~6~4 sdw2~sdw2~6001~/opt/greenplum/data/primary/gpseg5~7~5 sdw2~sdw2~6002~/opt/greenplum/data/primary/gpseg6~8~6 sdw2~sdw2~6003~/opt/greenplum/data/primary/gpseg7~9~7 sdw3~sdw3~6000~/opt/greenplum/data/primary/gpseg12~10~12 sdw3~sdw3~6001~/opt/greenplum/data/primary/gpseg13~11~13 sdw3~sdw3~6002~/opt/greenplum/data/primary/gpseg14~12~14 sdw3~sdw3~6003~/opt/greenplum/data/primary/gpseg15~13~15 sdw4~sdw4~6000~/opt/greenplum/data/primary/gpseg8~14~8 sdw4~sdw4~6001~/opt/greenplum/data/primary/gpseg9~15~9 sdw4~sdw4~6002~/opt/greenplum/data/primary/gpseg10~16~10 sdw4~sdw4~6003~/opt/greenplum/data/primary/gpseg11~17~11 ) declare -a MIRROR_ARRAY=( sdw1~sdw1~7000~/opt/greenplum/data/mirror/gpseg12~18~12 sdw1~sdw1~7001~/opt/greenplum/data/mirror/gpseg13~19~13 sdw1~sdw1~7002~/opt/greenplum/data/mirror/gpseg14~20~14 sdw1~sdw1~7003~/opt/greenplum/data/mirror/gpseg15~21~15 sdw2~sdw2~7000~/opt/greenplum/data/mirror/gpseg8~22~8 sdw2~sdw2~7001~/opt/greenplum/data/mirror/gpseg9~23~9 sdw2~sdw2~7002~/opt/greenplum/data/mirror/gpseg10~24~10 sdw2~sdw2~7003~/opt/greenplum/data/mirror/gpseg11~25~11 sdw3~sdw3~7000~/opt/greenplum/data/mirror/gpseg0~26~0 sdw3~sdw3~7001~/opt/greenplum/data/mirror/gpseg1~27~1 sdw3~sdw3~7002~/opt/greenplum/data/mirror/gpseg2~28~2 sdw3~sdw3~7003~/opt/greenplum/data/mirror/gpseg3~29~3 sdw4~sdw4~7000~/opt/greenplum/data/mirror/gpseg4~30~4 sdw4~sdw4~7001~/opt/greenplum/data/mirror/gpseg5~31~5 sdw4~sdw4~7002~/opt/greenplum/data/mirror/gpseg6~32~6 sdw4~sdw4~7003~/opt/greenplum/data/mirror/gpseg7~33~7 ) EOF select d1.content,d1.hostname p_hostname,d2.hostname m_hostname,d1.datadir p_datadir,d2.datadir m_datadir from gp_segment_configuration d1 join gp_segment_configuration d2 on d1.content=d2.content and d2.role='m' and d1.role='p' order by content; postgres=# select d1.content,d1.hostname p_hostname,d2.hostname m_hostname,d1.datadir p_datadir,d2.datadir m_datadir from gp_segment_configuration d1 join gp_segment_configuration d2 on d1.content=d2.content and d2.role='m' and d1.role='p' order by content; content | p_hostname | m_hostname | p_datadir | m_datadir ---------+------------+------------+-------------------------------------+------------------------------------ -1 | mdw | smdw | /opt/greenplum/data/master/gpseg-1 | /opt/greenplum/data/master/gpseg-1 0 | sdw1 | sdw3 | /opt/greenplum/data/primary/gpseg0 | /opt/greenplum/data/mirror/gpseg0 1 | sdw1 | sdw3 | /opt/greenplum/data/primary/gpseg1 | /opt/greenplum/data/mirror/gpseg1 2 | sdw1 | sdw3 | /opt/greenplum/data/primary/gpseg2 | /opt/greenplum/data/mirror/gpseg2 3 | sdw1 | sdw3 | /opt/greenplum/data/primary/gpseg3 | /opt/greenplum/data/mirror/gpseg3 4 | sdw2 | sdw4 | /opt/greenplum/data/primary/gpseg4 | /opt/greenplum/data/mirror/gpseg4 5 | sdw2 | sdw4 | /opt/greenplum/data/primary/gpseg5 | /opt/greenplum/data/mirror/gpseg5 6 | sdw2 | sdw4 | /opt/greenplum/data/primary/gpseg6 | /opt/greenplum/data/mirror/gpseg6 7 | sdw2 | sdw4 | /opt/greenplum/data/primary/gpseg7 | /opt/greenplum/data/mirror/gpseg7 8 | sdw4 | sdw2 | /opt/greenplum/data/primary/gpseg8 | /opt/greenplum/data/mirror/gpseg8 9 | sdw4 | sdw2 | /opt/greenplum/data/primary/gpseg9 | /opt/greenplum/data/mirror/gpseg9 10 | sdw4 | sdw2 | /opt/greenplum/data/primary/gpseg10 | /opt/greenplum/data/mirror/gpseg10 11 | sdw4 | sdw2 | /opt/greenplum/data/primary/gpseg11 | /opt/greenplum/data/mirror/gpseg11 12 | sdw3 | sdw1 | /opt/greenplum/data/primary/gpseg12 | /opt/greenplum/data/mirror/gpseg12 13 | sdw3 | sdw1 | /opt/greenplum/data/primary/gpseg13 | /opt/greenplum/data/mirror/gpseg13 14 | sdw3 | sdw1 | /opt/greenplum/data/primary/gpseg14 | /opt/greenplum/data/mirror/gpseg14 15 | sdw3 | sdw1 | /opt/greenplum/data/primary/gpseg15 | /opt/greenplum/data/mirror/gpseg15 (17 rows)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101

docker rm -f lhrgptest docker run -itd --name lhrgptest -h lhrgptest \   -v /sys/fs/cgroup:/sys/fs/cgroup \   --privileged=true lhrbest/greenplum:gpdball_6.25.3 \   /usr/sbin/init   docker exec -it lhrgptest bash   docker start mdw smdw sdw1 sdw2 sdw3 sdw4     docker exec -it mdw su - gpadmin   gpstart -a gpcc start   gpcc status gpstate   gpdeletesystem -d /opt/greenplum/data/master/gpseg-1 -f   gpinitsystem -c /home/gpadmin/conf/initgp_config  -s smdw -P 5432 -S /opt/greenplum/data/master/gpseg-1 -O /home/gpadmin/conf/cluster_init.config   cat /home/gpadmin/conf/cluster_init.config   gpinitsystem -I /home/gpadmin/conf/cluster_init_new.config -s smdw -P 5432 -S /opt/greenplum/data/master/gpseg-1   # host~port~data_directory/seg_prefix<segment_id>~dbid~content_id   # dbid从2开始依次递增，seg_prefix<segment_id>和content_id保持一致，p和m保持一致   cat > /home/gpadmin/conf/cluster_init_new.config <<"EOF" ARRAY_NAME="lhrgp" TRUSTED_SHELL=/bin/ssh CHECK_POINT_SEGMENTS=8 ENCODING=UTF-8 SEG_PREFIX=gpseg HEAP_CHECKSUM=on HBA_HOSTNAMES=0 QD_PRIMARY_ARRAY=mdw~mdw~5432~/opt/greenplum/data/master/gpseg-1~1~-1 declare -a PRIMARY_ARRAY=( sdw1~sdw1~6000~/opt/greenplum/data/primary/gpseg0~2~0 sdw1~sdw1~6001~/opt/greenplum/data/primary/gpseg1~3~1 sdw1~sdw1~6002~/opt/greenplum/data/primary/gpseg2~4~2 sdw1~sdw1~6003~/opt/greenplum/data/primary/gpseg3~5~3 sdw2~sdw2~6000~/opt/greenplum/data/primary/gpseg4~6~4 sdw2~sdw2~6001~/opt/greenplum/data/primary/gpseg5~7~5 sdw2~sdw2~6002~/opt/greenplum/data/primary/gpseg6~8~6 sdw2~sdw2~6003~/opt/greenplum/data/primary/gpseg7~9~7 sdw3~sdw3~6000~/opt/greenplum/data/primary/gpseg12~10~12 sdw3~sdw3~6001~/opt/greenplum/data/primary/gpseg13~11~13 sdw3~sdw3~6002~/opt/greenplum/data/primary/gpseg14~12~14 sdw3~sdw3~6003~/opt/greenplum/data/primary/gpseg15~13~15 sdw4~sdw4~6000~/opt/greenplum/data/primary/gpseg8~14~8 sdw4~sdw4~6001~/opt/greenplum/data/primary/gpseg9~15~9 sdw4~sdw4~6002~/opt/greenplum/data/primary/gpseg10~16~10 sdw4~sdw4~6003~/opt/greenplum/data/primary/gpseg11~17~11 ) declare -a MIRROR_ARRAY=( sdw1~sdw1~7000~/opt/greenplum/data/mirror/gpseg12~18~12 sdw1~sdw1~7001~/opt/greenplum/data/mirror/gpseg13~19~13 sdw1~sdw1~7002~/opt/greenplum/data/mirror/gpseg14~20~14 sdw1~sdw1~7003~/opt/greenplum/data/mirror/gpseg15~21~15 sdw2~sdw2~7000~/opt/greenplum/data/mirror/gpseg8~22~8 sdw2~sdw2~7001~/opt/greenplum/data/mirror/gpseg9~23~9 sdw2~sdw2~7002~/opt/greenplum/data/mirror/gpseg10~24~10 sdw2~sdw2~7003~/opt/greenplum/data/mirror/gpseg11~25~11 sdw3~sdw3~7000~/opt/greenplum/data/mirror/gpseg0~26~0 sdw3~sdw3~7001~/opt/greenplum/data/mirror/gpseg1~27~1 sdw3~sdw3~7002~/opt/greenplum/data/mirror/gpseg2~28~2 sdw3~sdw3~7003~/opt/greenplum/data/mirror/gpseg3~29~3 sdw4~sdw4~7000~/opt/greenplum/data/mirror/gpseg4~30~4 sdw4~sdw4~7001~/opt/greenplum/data/mirror/gpseg5~31~5 sdw4~sdw4~7002~/opt/greenplum/data/mirror/gpseg6~32~6 sdw4~sdw4~7003~/opt/greenplum/data/mirror/gpseg7~33~7 ) EOF   select d1.content,d1.hostname p_hostname,d2.hostname m_hostname,d1.datadir p_datadir,d2.datadir m_datadir from gp_segment_configuration d1 join gp_segment_configuration d2 on d1.content=d2.content and d2.role='m' and d1.role='p' order by content;   postgres=# select d1.content,d1.hostname p_hostname,d2.hostname m_hostname,d1.datadir p_datadir,d2.datadir m_datadir from gp_segment_configuration d1 join gp_segment_configuration d2 on d1.content=d2.content and d2.role='m' and d1.role='p' order by content; content | p_hostname | m_hostname |              p_datadir              |             m_datadir               ---------+------------+------------+-------------------------------------+------------------------------------       -1 | mdw        | smdw       | /opt/greenplum/data/master/gpseg-1  | /opt/greenplum/data/master/gpseg-1        0 | sdw1       | sdw3       | /opt/greenplum/data/primary/gpseg0  | /opt/greenplum/data/mirror/gpseg0        1 | sdw1       | sdw3       | /opt/greenplum/data/primary/gpseg1  | /opt/greenplum/data/mirror/gpseg1        2 | sdw1       | sdw3       | /opt/greenplum/data/primary/gpseg2  | /opt/greenplum/data/mirror/gpseg2        3 | sdw1       | sdw3       | /opt/greenplum/data/primary/gpseg3  | /opt/greenplum/data/mirror/gpseg3        4 | sdw2       | sdw4       | /opt/greenplum/data/primary/gpseg4  | /opt/greenplum/data/mirror/gpseg4        5 | sdw2       | sdw4       | /opt/greenplum/data/primary/gpseg5  | /opt/greenplum/data/mirror/gpseg5        6 | sdw2       | sdw4       | /opt/greenplum/data/primary/gpseg6  | /opt/greenplum/data/mirror/gpseg6        7 | sdw2       | sdw4       | /opt/greenplum/data/primary/gpseg7  | /opt/greenplum/data/mirror/gpseg7        8 | sdw4       | sdw2       | /opt/greenplum/data/primary/gpseg8  | /opt/greenplum/data/mirror/gpseg8        9 | sdw4       | sdw2       | /opt/greenplum/data/primary/gpseg9  | /opt/greenplum/data/mirror/gpseg9       10 | sdw4       | sdw2       | /opt/greenplum/data/primary/gpseg10 | /opt/greenplum/data/mirror/gpseg10       11 | sdw4       | sdw2       | /opt/greenplum/data/primary/gpseg11 | /opt/greenplum/data/mirror/gpseg11       12 | sdw3       | sdw1       | /opt/greenplum/data/primary/gpseg12 | /opt/greenplum/data/mirror/gpseg12       13 | sdw3       | sdw1       | /opt/greenplum/data/primary/gpseg13 | /opt/greenplum/data/mirror/gpseg13       14 | sdw3       | sdw1       | /opt/greenplum/data/primary/gpseg14 | /opt/greenplum/data/mirror/gpseg14       15 | sdw3       | sdw1       | /opt/greenplum/data/primary/gpseg15 | /opt/greenplum/data/mirror/gpseg15 (17 rows)

测试：机房2宕机

假设，我们关闭机房2，即关闭smdw sdw3 sdw4，测试一下：

-- 查询状态 [gpadmin@mdw ~]$ gpstate -e 20241106:09:12:20:102955 gpstate:mdw:gpadmin-[INFO]:-Starting gpstate with args: -e 20241106:09:12:20:102955 gpstate:mdw:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.25.3 build commit:367edc6b4dfd909fe38fc288ade9e294d74e3f9a Open Source' 20241106:09:12:20:102955 gpstate:mdw:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 9.4.26 (Greenplum Database 6.25.3 build commit:367edc6b4dfd909fe38fc288ade9e294d74e3f9a Open Source) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Oct 4 2023 23:27:58' 20241106:09:12:20:102955 gpstate:mdw:gpadmin-[INFO]:-Obtaining Segment details from master... 20241106:09:12:20:102955 gpstate:mdw:gpadmin-[INFO]:-Gathering data from segments... . 20241106:09:12:22:102955 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20241106:09:12:22:102955 gpstate:mdw:gpadmin-[INFO]:-Segment Mirroring Status Report 20241106:09:12:22:102955 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20241106:09:12:22:102955 gpstate:mdw:gpadmin-[INFO]:-All segments are running normally -- 关闭机房2 docker stop smdw sdw3 sdw4 -- 查询状态 ，操作数据库发现不受影响 [gpadmin@mdw ~]$ gpstate -e 20241106:09:13:03:103062 gpstate:mdw:gpadmin-[INFO]:-Starting gpstate with args: -e 20241106:09:13:03:103062 gpstate:mdw:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.25.3 build commit:367edc6b4dfd909fe38fc288ade9e294d74e3f9a Open Source' 20241106:09:13:03:103062 gpstate:mdw:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 9.4.26 (Greenplum Database 6.25.3 build commit:367edc6b4dfd909fe38fc288ade9e294d74e3f9a Open Source) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Oct 4 2023 23:27:58' 20241106:09:13:03:103062 gpstate:mdw:gpadmin-[INFO]:-Obtaining Segment details from master... 20241106:09:13:03:103062 gpstate:mdw:gpadmin-[INFO]:-Gathering data from segments... .................................. 20241106:09:13:37:103062 gpstate:mdw:gpadmin-[WARNING]:-pg_stat_replication shows no standby connections 20241106:09:13:37:103062 gpstate:mdw:gpadmin-[WARNING]:-pg_stat_replication shows no standby connections 20241106:09:13:38:103062 gpstate:mdw:gpadmin-[WARNING]:-pg_stat_replication shows no standby connections 20241106:09:13:38:103062 gpstate:mdw:gpadmin-[WARNING]:-pg_stat_replication shows no standby connections 20241106:09:13:38:103062 gpstate:mdw:gpadmin-[WARNING]:-pg_stat_replication shows no standby connections 20241106:09:13:38:103062 gpstate:mdw:gpadmin-[WARNING]:-pg_stat_replication shows no standby connections 20241106:09:13:38:103062 gpstate:mdw:gpadmin-[WARNING]:-pg_stat_replication shows no standby connections 20241106:09:13:38:103062 gpstate:mdw:gpadmin-[WARNING]:-pg_stat_replication shows no standby connections 20241106:09:13:41:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 14 (sdw4:6000) 20241106:09:13:44:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 15 (sdw4:6001) 20241106:09:13:47:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 16 (sdw4:6002) 20241106:09:13:50:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 17 (sdw4:6003) 20241106:09:13:53:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 10 (sdw3:6000) 20241106:09:13:56:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 11 (sdw3:6001) 20241106:09:13:59:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 12 (sdw3:6002) 20241106:09:14:02:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 13 (sdw3:6003) 20241106:09:14:02:103062 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20241106:09:14:02:103062 gpstate:mdw:gpadmin-[INFO]:-Segment Mirroring Status Report 20241106:09:14:05:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 14 (sdw4:6000) 20241106:09:14:08:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 15 (sdw4:6001) 20241106:09:14:11:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 16 (sdw4:6002) 20241106:09:14:14:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 17 (sdw4:6003) 20241106:09:14:17:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 10 (sdw3:6000) 20241106:09:14:20:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 11 (sdw3:6001) 20241106:09:14:23:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 12 (sdw3:6002) 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 13 (sdw3:6003) 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-Unsynchronized Segment Pairs 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- Current Primary Port WAL sync remaining bytes Mirror Port 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw1 6000 Unknown sdw3 7000 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw1 6001 Unknown sdw3 7001 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw1 6002 Unknown sdw3 7002 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw1 6003 Unknown sdw3 7003 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw2 6000 Unknown sdw4 7000 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw2 6001 Unknown sdw4 7001 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw2 6002 Unknown sdw4 7002 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw2 6003 Unknown sdw4 7003 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-Downed Segments (may include segments where status could not be retrieved) 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- Segment Port Config status Status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw3 7000 Up Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw3 7001 Up Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw3 7002 Up Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw3 7003 Up Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw4 7000 Up Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw4 7001 Up Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw4 7002 Up Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw4 7003 Up Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw4 6000 Up Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw4 6001 Up Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw4 6002 Up Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw4 6003 Up Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw3 6000 Up Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw3 6001 Up Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw3 6002 Up Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:- sdw3 6003 Up Unknown -- unable to load segment status [gpadmin@mdw ~]$ psql psql (9.4.26) Type "help" for help. postgres=# select * from gp_segment_configuration where status='d'; dbid | content | role | preferred_role | mode | status | port | hostname | address | datadir ------+---------+------+----------------+------+--------+------+----------+---------+------------------------------------- 15 | 9 | m | p | n | d | 6001 | sdw4 | sdw4 | /opt/greenplum/data/primary/gpseg9 14 | 8 | m | p | n | d | 6000 | sdw4 | sdw4 | /opt/greenplum/data/primary/gpseg8 10 | 12 | m | p | n | d | 6000 | sdw3 | sdw3 | /opt/greenplum/data/primary/gpseg12 11 | 13 | m | p | n | d | 6001 | sdw3 | sdw3 | /opt/greenplum/data/primary/gpseg13 16 | 10 | m | p | n | d | 6002 | sdw4 | sdw4 | /opt/greenplum/data/primary/gpseg10 17 | 11 | m | p | n | d | 6003 | sdw4 | sdw4 | /opt/greenplum/data/primary/gpseg11 12 | 14 | m | p | n | d | 6002 | sdw3 | sdw3 | /opt/greenplum/data/primary/gpseg14 13 | 15 | m | p | n | d | 6003 | sdw3 | sdw3 | /opt/greenplum/data/primary/gpseg15 19 | 1 | m | m | n | d | 7001 | sdw3 | sdw3 | /opt/greenplum/data/mirror/gpseg1 22 | 4 | m | m | n | d | 7000 | sdw4 | sdw4 | /opt/greenplum/data/mirror/gpseg4 18 | 0 | m | m | n | d | 7000 | sdw3 | sdw3 | /opt/greenplum/data/mirror/gpseg0 20 | 2 | m | m | n | d | 7002 | sdw3 | sdw3 | /opt/greenplum/data/mirror/gpseg2 21 | 3 | m | m | n | d | 7003 | sdw3 | sdw3 | /opt/greenplum/data/mirror/gpseg3 23 | 5 | m | m | n | d | 7001 | sdw4 | sdw4 | /opt/greenplum/data/mirror/gpseg5 24 | 6 | m | m | n | d | 7002 | sdw4 | sdw4 | /opt/greenplum/data/mirror/gpseg6 25 | 7 | m | m | n | d | 7003 | sdw4 | sdw4 | /opt/greenplum/data/mirror/gpseg7 (16 rows) postgres=# create database db2; CREATE DATABASE postgres=#

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108

-- 查询状态 [gpadmin@mdw ~]$ gpstate -e 20241106:09:12:20:102955 gpstate:mdw:gpadmin-[INFO]:-Starting gpstate with args: -e 20241106:09:12:20:102955 gpstate:mdw:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.25.3 build commit:367edc6b4dfd909fe38fc288ade9e294d74e3f9a Open Source' 20241106:09:12:20:102955 gpstate:mdw:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 9.4.26 (Greenplum Database 6.25.3 build commit:367edc6b4dfd909fe38fc288ade9e294d74e3f9a Open Source) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Oct  4 2023 23:27:58' 20241106:09:12:20:102955 gpstate:mdw:gpadmin-[INFO]:-Obtaining Segment details from master... 20241106:09:12:20:102955 gpstate:mdw:gpadmin-[INFO]:-Gathering data from segments... . 20241106:09:12:22:102955 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20241106:09:12:22:102955 gpstate:mdw:gpadmin-[INFO]:-Segment Mirroring Status Report 20241106:09:12:22:102955 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20241106:09:12:22:102955 gpstate:mdw:gpadmin-[INFO]:-All segments are running normally   -- 关闭机房2 docker stop smdw sdw3 sdw4   -- 查询状态 ，操作数据库发现不受影响 [gpadmin@mdw ~]$ gpstate -e 20241106:09:13:03:103062 gpstate:mdw:gpadmin-[INFO]:-Starting gpstate with args: -e 20241106:09:13:03:103062 gpstate:mdw:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.25.3 build commit:367edc6b4dfd909fe38fc288ade9e294d74e3f9a Open Source' 20241106:09:13:03:103062 gpstate:mdw:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 9.4.26 (Greenplum Database 6.25.3 build commit:367edc6b4dfd909fe38fc288ade9e294d74e3f9a Open Source) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Oct  4 2023 23:27:58' 20241106:09:13:03:103062 gpstate:mdw:gpadmin-[INFO]:-Obtaining Segment details from master... 20241106:09:13:03:103062 gpstate:mdw:gpadmin-[INFO]:-Gathering data from segments... .................................. 20241106:09:13:37:103062 gpstate:mdw:gpadmin-[WARNING]:-pg_stat_replication shows no standby connections 20241106:09:13:37:103062 gpstate:mdw:gpadmin-[WARNING]:-pg_stat_replication shows no standby connections 20241106:09:13:38:103062 gpstate:mdw:gpadmin-[WARNING]:-pg_stat_replication shows no standby connections 20241106:09:13:38:103062 gpstate:mdw:gpadmin-[WARNING]:-pg_stat_replication shows no standby connections 20241106:09:13:38:103062 gpstate:mdw:gpadmin-[WARNING]:-pg_stat_replication shows no standby connections 20241106:09:13:38:103062 gpstate:mdw:gpadmin-[WARNING]:-pg_stat_replication shows no standby connections 20241106:09:13:38:103062 gpstate:mdw:gpadmin-[WARNING]:-pg_stat_replication shows no standby connections 20241106:09:13:38:103062 gpstate:mdw:gpadmin-[WARNING]:-pg_stat_replication shows no standby connections 20241106:09:13:41:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 14 (sdw4:6000) 20241106:09:13:44:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 15 (sdw4:6001) 20241106:09:13:47:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 16 (sdw4:6002) 20241106:09:13:50:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 17 (sdw4:6003) 20241106:09:13:53:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 10 (sdw3:6000) 20241106:09:13:56:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 11 (sdw3:6001) 20241106:09:13:59:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 12 (sdw3:6002) 20241106:09:14:02:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 13 (sdw3:6003) 20241106:09:14:02:103062 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20241106:09:14:02:103062 gpstate:mdw:gpadmin-[INFO]:-Segment Mirroring Status Report 20241106:09:14:05:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 14 (sdw4:6000) 20241106:09:14:08:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 15 (sdw4:6001) 20241106:09:14:11:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 16 (sdw4:6002) 20241106:09:14:14:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 17 (sdw4:6003) 20241106:09:14:17:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 10 (sdw3:6000) 20241106:09:14:20:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 11 (sdw3:6001) 20241106:09:14:23:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 12 (sdw3:6002) 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 13 (sdw3:6003) 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-Unsynchronized Segment Pairs 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   Current Primary   Port   WAL sync remaining bytes   Mirror   Port 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw1              6000   Unknown                    sdw3     7000 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw1              6001   Unknown                    sdw3     7001 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw1              6002   Unknown                    sdw3     7002 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw1              6003   Unknown                    sdw3     7003 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw2              6000   Unknown                    sdw4     7000 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw2              6001   Unknown                    sdw4     7001 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw2              6002   Unknown                    sdw4     7002 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw2              6003   Unknown                    sdw4     7003 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-Downed Segments (may include segments where status could not be retrieved) 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   Segment   Port   Config status   Status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw3      7000   Up              Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw3      7001   Up              Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw3      7002   Up              Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw3      7003   Up              Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw4      7000   Up              Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw4      7001   Up              Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw4      7002   Up              Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw4      7003   Up              Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw4      6000   Up              Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw4      6001   Up              Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw4      6002   Up              Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw4      6003   Up              Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw3      6000   Up              Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw3      6001   Up              Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw3      6002   Up              Unknown -- unable to load segment status 20241106:09:14:26:103062 gpstate:mdw:gpadmin-[INFO]:-   sdw3      6003   Up              Unknown -- unable to load segment status [gpadmin@mdw ~]$ psql psql (9.4.26) Type "help" for help.   postgres=# select * from gp_segment_configuration where status='d'; dbid | content | role | preferred_role | mode | status | port | hostname | address |               datadir               ------+---------+------+----------------+------+--------+------+----------+---------+-------------------------------------    15 |       9 | m    | p              | n    | d      | 6001 | sdw4     | sdw4    | /opt/greenplum/data/primary/gpseg9    14 |       8 | m    | p              | n    | d      | 6000 | sdw4     | sdw4    | /opt/greenplum/data/primary/gpseg8    10 |      12 | m    | p              | n    | d      | 6000 | sdw3     | sdw3    | /opt/greenplum/data/primary/gpseg12    11 |      13 | m    | p              | n    | d      | 6001 | sdw3     | sdw3    | /opt/greenplum/data/primary/gpseg13    16 |      10 | m    | p              | n    | d      | 6002 | sdw4     | sdw4    | /opt/greenplum/data/primary/gpseg10    17 |      11 | m    | p              | n    | d      | 6003 | sdw4     | sdw4    | /opt/greenplum/data/primary/gpseg11    12 |      14 | m    | p              | n    | d      | 6002 | sdw3     | sdw3    | /opt/greenplum/data/primary/gpseg14    13 |      15 | m    | p              | n    | d      | 6003 | sdw3     | sdw3    | /opt/greenplum/data/primary/gpseg15    19 |       1 | m    | m              | n    | d      | 7001 | sdw3     | sdw3    | /opt/greenplum/data/mirror/gpseg1    22 |       4 | m    | m              | n    | d      | 7000 | sdw4     | sdw4    | /opt/greenplum/data/mirror/gpseg4    18 |       0 | m    | m              | n    | d      | 7000 | sdw3     | sdw3    | /opt/greenplum/data/mirror/gpseg0    20 |       2 | m    | m              | n    | d      | 7002 | sdw3     | sdw3    | /opt/greenplum/data/mirror/gpseg2    21 |       3 | m    | m              | n    | d      | 7003 | sdw3     | sdw3    | /opt/greenplum/data/mirror/gpseg3    23 |       5 | m    | m              | n    | d      | 7001 | sdw4     | sdw4    | /opt/greenplum/data/mirror/gpseg5    24 |       6 | m    | m              | n    | d      | 7002 | sdw4     | sdw4    | /opt/greenplum/data/mirror/gpseg6    25 |       7 | m    | m              | n    | d      | 7003 | sdw4     | sdw4    | /opt/greenplum/data/mirror/gpseg7 (16 rows)   postgres=# create database db2; CREATE DATABASE postgres=#

然后后恢复和平衡操作。

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

相关文章

• Greenplum数据库初始化命令gpinitsystem和删除数据库命令gpdeletesystem
• GreenPlum在执行超过30个表的关联SQL时，产生执行计划很慢的解决办法（join_collapse_limit、from_collapse_limit）
• 【异地备份】如何通过sshfs将GreenPlum备份到远程主机?
• gpcc监控不显示内存、cpu等资源
• GreenPlum查询报错could not open file "base/231962/24824104": No such file or directory (seg44 slice2 192.168.163.54:6014 pid=1499766)
• GreenPlum数据库不定时报错interconnect error connection closed prematurely、timed out waiting for response from motion receiver during TCP interconnect teardown
• GreenPlum切换用户流程
• GreenPlum中的is null可以走索引吗？is null和''空字符串一样吗？
• 将GreenPlum数据库从6升级到7版本（大版本升级）
• GreenPlum通过gpbackup和gprestore在MinIO的S3存储中的异地备份和还原
• GreenPlum中查询主机历史CPU、内存、磁盘、网络等情况（gpmetrics.gpcc_system_history）
• GreenPlum查询表有数据，但是drop表报错does not exist，vacuumdb也报错
• GreenPlum中的union可能比union all要快很多
• gpload加载数据报错 ProgrammingError: ERROR: column "relstorage" does not exist（GP7中的pg_class没有relstorage字段）
• gpload加载数据报错 UnboundLocalError: local variable 'has_seq_bool' referenced before assignment
• GreenPlum启动报错ModuleNotFoundError: No module named 'gppylib'
• GreenPlum查询参数报错schema gp_toolkit does not exist
• GP7.2在欧拉22.03上安装的一些报错处理
• GreenPlum相关软件在博通网站的下载方式
• GreenPlum 7.2.0新特性介绍

打赏赞(1)分享

标签： 原创GreenPlumgpinitsystem初始化GP机房容灾

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

• 下一篇 gpcc监控不显示内存、cpu等资源
• 上一篇 PG的copy命令如何导出单个斜杠到文件中？

您可能还喜欢...

• 0

  PG或GP删除数据库报错dropdb: database removal failed: ERROR: database "db1" is being accessed by other users DETAIL: There are 3 other sessions using the database.

  2024年6月14日

   由 小麦苗 · Published 2024年6月14日 · Last modified 2024年6月24日

  · 已更新 2024年6月24日  

  0    399    5
• 0

  【DB宝27】在Oracle 19c中创建容器数据库（4）--Duplicating a CDB（从18c开始）

  2021年6月21日

   由 小麦苗 · Published 2021年6月21日 · Last modified 2021年8月6日

  · 已更新 2021年8月6日  

  0    7853    4
• 0

  MySQL OCP考证

  2021年6月29日

   由 小麦苗 · Published 2021年6月29日 · Last modified 2021年7月15日

  · 已更新 2021年7月15日  

  0    23731    5