原 在初始化GP时，如何自定义p和m的关系，从而达到 机房容灾 的效果
Tags： 原创GreenPlumgpinitsystem初始化GP机房容灾
👉 本文共约533个字，系统预计阅读时间或需3分钟。
简介
问题一： 安装GP时，p和m如何自定义对应关系 ？？? 达到 机房容灾？？
问题二：2个机房之间网络断开，会出现脑裂的情况吗？？？ 到底以哪个为准呢？？？？
架构图
实现过程
在GP初始化阶段，通过-O和-I参数即可实现该功能。
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，测试一下：
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，谢谢！
