合 GreenPlum中的primary和mirror之间一直处于Not In Sync状态

2023年12月29日  

0    220    1

Tags： 故障处理GreenPlumNot In Sync镜像同步

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

显示/隐藏全文目录

目录

• 现象
• 分析
• 解决

现象

[gpadmin@mdw ~]$ gpstate -m 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-Starting gpstate with args: -m 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.23.1 build commit:2731a45ecb364317207c560730cf9e2cbf17d7e4 Open Source' 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 9.4.26 (Greenplum Database 6.23.1 build commit:2731a45ecb364317207c560730cf9e2cbf17d7e4 Open Source) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Feb 7 2023 22:54:40' 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-Obtaining Segment details from master... 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-------------------------------------------------------------- 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:--Current GPDB mirror list and status 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:--Type = Group 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-------------------------------------------------------------- 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- Mirror Datadir Port Status Data Status 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- sdw2 /dbaup/opt/greenplum/data/mirror/gpseg0 7000 Passive Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- sdw2 /dbaup/opt/greenplum/data/mirror/gpseg1 7001 Passive Not In Sync 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- sdw2 /dbaup/opt/greenplum/data/mirror/gpseg2 7002 Passive Not In Sync 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- sdw2 /dbaup/opt/greenplum/data/mirror/gpseg3 7003 Passive Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- sdw3 /dbaup/opt/greenplum/data/mirror/gpseg4 7000 Passive Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- sdw3 /dbaup/opt/greenplum/data/mirror/gpseg5 7001 Passive Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- sdw3 /dbaup/opt/greenplum/data/mirror/gpseg6 7002 Passive Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- sdw3 /dbaup/opt/greenplum/data/mirror/gpseg7 7003 Passive Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- sdw1 /dbaup/opt/greenplum/data/mirror/gpseg8 7000 Passive Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- sdw1 /dbaup/opt/greenplum/data/mirror/gpseg9 7001 Passive Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- sdw1 /dbaup/opt/greenplum/data/mirror/gpseg10 7002 Passive Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- sdw1 /dbaup/opt/greenplum/data/mirror/gpseg11 7003 Passive Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-------------------------------------------------------------- [gpadmin@mdw ~]$ gpstate -e 20231229:17:48:17:485051 gpstate:mdw:gpadmin-[INFO]:-Starting gpstate with args: -e 20231229:17:48:17:485051 gpstate:mdw:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.23.1 build commit:2731a45ecb364317207c560730cf9e2cbf17d7e4 Open Source' 20231229:17:48:17:485051 gpstate:mdw:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 9.4.26 (Greenplum Database 6.23.1 build commit:2731a45ecb364317207c560730cf9e2cbf17d7e4 Open Source) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Feb 7 2023 22:54:40' 20231229:17:48:17:485051 gpstate:mdw:gpadmin-[INFO]:-Obtaining Segment details from master... 20231229:17:48:17:485051 gpstate:mdw:gpadmin-[INFO]:-Gathering data from segments... 20231229:17:48:18:485051 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20231229:17:48:18:485051 gpstate:mdw:gpadmin-[INFO]:-Segment Mirroring Status Report 20231229:17:48:19:485051 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20231229:17:48:19:485051 gpstate:mdw:gpadmin-[INFO]:-Unsynchronized Segment Pairs 20231229:17:48:19:485051 gpstate:mdw:gpadmin-[INFO]:- Current Primary Port WAL sync remaining bytes Mirror Port 20231229:17:48:19:485051 gpstate:mdw:gpadmin-[INFO]:- sdw1 6001 623870910848 sdw2 7001 20231229:17:48:19:485051 gpstate:mdw:gpadmin-[INFO]:- sdw1 6002 656085874704 sdw2 7002 [gpadmin@mdw ~]$ gpstate -e 20231229:17:48:22:488177 gpstate:mdw:gpadmin-[INFO]:-Starting gpstate with args: -e 20231229:17:48:22:488177 gpstate:mdw:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.23.1 build commit:2731a45ecb364317207c560730cf9e2cbf17d7e4 Open Source' 20231229:17:48:22:488177 gpstate:mdw:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 9.4.26 (Greenplum Database 6.23.1 build commit:2731a45ecb364317207c560730cf9e2cbf17d7e4 Open Source) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Feb 7 2023 22:54:40' 20231229:17:48:22:488177 gpstate:mdw:gpadmin-[INFO]:-Obtaining Segment details from master... 20231229:17:48:22:488177 gpstate:mdw:gpadmin-[INFO]:-Gathering data from segments... 20231229:17:48:23:488177 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20231229:17:48:23:488177 gpstate:mdw:gpadmin-[INFO]:-Segment Mirroring Status Report 20231229:17:48:24:488177 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20231229:17:48:24:488177 gpstate:mdw:gpadmin-[INFO]:-Unsynchronized Segment Pairs 20231229:17:48:24:488177 gpstate:mdw:gpadmin-[INFO]:- Current Primary Port WAL sync remaining bytes Mirror Port 20231229:17:48:24:488177 gpstate:mdw:gpadmin-[INFO]:- sdw1 6001 623688459664 sdw2 7001 20231229:17:48:24:488177 gpstate:mdw:gpadmin-[INFO]:- sdw1 6002 655917579296 sdw2 7002 [gpadmin@mdw ~]$

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

[gpadmin@mdw ~]$ gpstate -m 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-Starting gpstate with args: -m 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.23.1 build commit:2731a45ecb364317207c560730cf9e2cbf17d7e4 Open Source' 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 9.4.26 (Greenplum Database 6.23.1 build commit:2731a45ecb364317207c560730cf9e2cbf17d7e4 Open Source) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Feb  7 2023 22:54:40' 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-Obtaining Segment details from master... 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-------------------------------------------------------------- 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:--Current GPDB mirror list and status 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:--Type = Group 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-------------------------------------------------------------- 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   Mirror   Datadir                                      Port   Status    Data Status     20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   sdw2     /dbaup/opt/greenplum/data/mirror/gpseg0    7000   Passive   Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   sdw2     /dbaup/opt/greenplum/data/mirror/gpseg1    7001   Passive   Not In Sync 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   sdw2     /dbaup/opt/greenplum/data/mirror/gpseg2    7002   Passive   Not In Sync 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   sdw2     /dbaup/opt/greenplum/data/mirror/gpseg3    7003   Passive   Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   sdw3     /dbaup/opt/greenplum/data/mirror/gpseg4    7000   Passive   Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   sdw3     /dbaup/opt/greenplum/data/mirror/gpseg5    7001   Passive   Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   sdw3     /dbaup/opt/greenplum/data/mirror/gpseg6    7002   Passive   Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   sdw3     /dbaup/opt/greenplum/data/mirror/gpseg7    7003   Passive   Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   sdw1     /dbaup/opt/greenplum/data/mirror/gpseg8    7000   Passive   Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   sdw1     /dbaup/opt/greenplum/data/mirror/gpseg9    7001   Passive   Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   sdw1     /dbaup/opt/greenplum/data/mirror/gpseg10   7002   Passive   Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   sdw1     /dbaup/opt/greenplum/data/mirror/gpseg11   7003   Passive   Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-------------------------------------------------------------- [gpadmin@mdw ~]$ gpstate -e 20231229:17:48:17:485051 gpstate:mdw:gpadmin-[INFO]:-Starting gpstate with args: -e 20231229:17:48:17:485051 gpstate:mdw:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.23.1 build commit:2731a45ecb364317207c560730cf9e2cbf17d7e4 Open Source' 20231229:17:48:17:485051 gpstate:mdw:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 9.4.26 (Greenplum Database 6.23.1 build commit:2731a45ecb364317207c560730cf9e2cbf17d7e4 Open Source) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Feb  7 2023 22:54:40' 20231229:17:48:17:485051 gpstate:mdw:gpadmin-[INFO]:-Obtaining Segment details from master... 20231229:17:48:17:485051 gpstate:mdw:gpadmin-[INFO]:-Gathering data from segments... 20231229:17:48:18:485051 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20231229:17:48:18:485051 gpstate:mdw:gpadmin-[INFO]:-Segment Mirroring Status Report 20231229:17:48:19:485051 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20231229:17:48:19:485051 gpstate:mdw:gpadmin-[INFO]:-Unsynchronized Segment Pairs 20231229:17:48:19:485051 gpstate:mdw:gpadmin-[INFO]:-   Current Primary   Port   WAL sync remaining bytes   Mirror   Port 20231229:17:48:19:485051 gpstate:mdw:gpadmin-[INFO]:-   sdw1              6001   623870910848               sdw2     7001 20231229:17:48:19:485051 gpstate:mdw:gpadmin-[INFO]:-   sdw1              6002   656085874704               sdw2     7002 [gpadmin@mdw ~]$ gpstate -e 20231229:17:48:22:488177 gpstate:mdw:gpadmin-[INFO]:-Starting gpstate with args: -e 20231229:17:48:22:488177 gpstate:mdw:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.23.1 build commit:2731a45ecb364317207c560730cf9e2cbf17d7e4 Open Source' 20231229:17:48:22:488177 gpstate:mdw:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 9.4.26 (Greenplum Database 6.23.1 build commit:2731a45ecb364317207c560730cf9e2cbf17d7e4 Open Source) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Feb  7 2023 22:54:40' 20231229:17:48:22:488177 gpstate:mdw:gpadmin-[INFO]:-Obtaining Segment details from master... 20231229:17:48:22:488177 gpstate:mdw:gpadmin-[INFO]:-Gathering data from segments... 20231229:17:48:23:488177 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20231229:17:48:23:488177 gpstate:mdw:gpadmin-[INFO]:-Segment Mirroring Status Report 20231229:17:48:24:488177 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20231229:17:48:24:488177 gpstate:mdw:gpadmin-[INFO]:-Unsynchronized Segment Pairs 20231229:17:48:24:488177 gpstate:mdw:gpadmin-[INFO]:-   Current Primary   Port   WAL sync remaining bytes   Mirror   Port 20231229:17:48:24:488177 gpstate:mdw:gpadmin-[INFO]:-   sdw1              6001   623688459664               sdw2     7001 20231229:17:48:24:488177 gpstate:mdw:gpadmin-[INFO]:-   sdw1              6002   655917579296               sdw2     7002 [gpadmin@mdw ~]$

可以看到，有2个实例一直处于“ Not In Sync”状态，使用gpstate -e可以看到，需要同步的wal日志还剩600G，还是相当的大。

分析

分析发现，sdw1的6002实例和6001实例的wal日志的确还有600G需要传输：

[gpadmin@sdw1 gpseg2]$ cd /dbaup/opt/greenplum/data/primary/gpseg2/pg_xlog/ [gpadmin@sdw1 pg_xlog]$ du -sh . 610G .

1 2 3

[gpadmin@sdw1 gpseg2]$ cd /dbaup/opt/greenplum/data/primary/gpseg2/pg_xlog/ [gpadmin@sdw1 pg_xlog]$ du -sh . 610G    .

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

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

验证码：

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

相关文章

• 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查询表有数据，但是drop表报错does not exist，vacuumdb也报错
• 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
• GreenPlum启动报错could not load library /usr/local/greenplum-db-6.27.0/lib/postgresql/pg_cron.so的故障处理
• GreenPlum连接报错Sorry,too many clients already
• GreenPlum严重故障导致不能启库的解决办法（pg_resetxlog重置日志）
• 初始化GreenPlum数据库实例时报错ssh_exchange_identification: connection reset by peer
• GreenPlum安装gpcc报错rsync: [sender] write error: Broken pipe (32)
• GreenPlum添加standby master报错Error initializing standby master ExecutionError non-zero rc 2
• GreenPlum删除了pg_xlog文件夹内容报错could not locate a valid checkpoint record
• GreenPlum查询状态报错 [WARNING]:-could not query segment
• 使用gpmon登陆gpcc报错Error: User gpmon is not allowed to login Command Center.
• GreenPlum启动报错fe_sendauth no password supplied
• GreenPlum启动数据库报错pre-existing shared memory block
• GreenPlum数据库SQL查询卡慢，报错或告警 Interconnect encountered a network error, please check your network
• GreenPlum初始化时一直卡住“Completed restart of Greenplum instance in production mode”，psql postgres 命令也一直卡住，警告Failed to connect to template1

打赏赞(1)分享

标签： 故障处理GreenPlumNot In Sync镜像同步

小麦苗

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

• 下一篇 GreenPlum查询状态报错 [WARNING]:-could not query segment
• 上一篇 使用gpmon登陆gpcc报错Error: User gpmon is not allowed to login Command Center.

您可能还喜欢...

• 0

  GreenPlum官方监控工具gpcc简介、使用及使用gpcc进行调优

  2023年3月2日

   由 小麦苗 · Published 2023年3月2日 · Last modified 2023年7月19日

  · 已更新 2023年7月19日  

  0    381    2
• 0

  GreenPlum查看会话内存使用信息

  2023年2月2日

   由 小麦苗 · Published 2023年2月2日

   

  0    181    3
• 0

  GreenPlum恢复时报错Did not start the following segments due to failure while updating the port的排查过程

  2023年12月29日

   由 小麦苗 · Published 2023年12月29日

   

  0    183    1