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

2023年12月29日  

0    71    1

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

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

显示/隐藏全文目录

目录

• 现象
• 分析
• 解决

本页目录 隐藏

1) 现象

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 /xmmup/opt/greenplum/data/mirror/gpseg0 7000 Passive Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- sdw2 /xmmup/opt/greenplum/data/mirror/gpseg1 7001 Passive Not In Sync 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- sdw2 /xmmup/opt/greenplum/data/mirror/gpseg2 7002 Passive Not In Sync 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- sdw2 /xmmup/opt/greenplum/data/mirror/gpseg3 7003 Passive Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- sdw3 /xmmup/opt/greenplum/data/mirror/gpseg4 7000 Passive Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- sdw3 /xmmup/opt/greenplum/data/mirror/gpseg5 7001 Passive Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- sdw3 /xmmup/opt/greenplum/data/mirror/gpseg6 7002 Passive Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- sdw3 /xmmup/opt/greenplum/data/mirror/gpseg7 7003 Passive Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- sdw1 /xmmup/opt/greenplum/data/mirror/gpseg8 7000 Passive Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- sdw1 /xmmup/opt/greenplum/data/mirror/gpseg9 7001 Passive Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- sdw1 /xmmup/opt/greenplum/data/mirror/gpseg10 7002 Passive Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:- sdw1 /xmmup/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     /xmmup/opt/greenplum/data/mirror/gpseg0    7000   Passive   Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   sdw2     /xmmup/opt/greenplum/data/mirror/gpseg1    7001   Passive   Not In Sync 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   sdw2     /xmmup/opt/greenplum/data/mirror/gpseg2    7002   Passive   Not In Sync 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   sdw2     /xmmup/opt/greenplum/data/mirror/gpseg3    7003   Passive   Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   sdw3     /xmmup/opt/greenplum/data/mirror/gpseg4    7000   Passive   Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   sdw3     /xmmup/opt/greenplum/data/mirror/gpseg5    7001   Passive   Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   sdw3     /xmmup/opt/greenplum/data/mirror/gpseg6    7002   Passive   Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   sdw3     /xmmup/opt/greenplum/data/mirror/gpseg7    7003   Passive   Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   sdw1     /xmmup/opt/greenplum/data/mirror/gpseg8    7000   Passive   Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   sdw1     /xmmup/opt/greenplum/data/mirror/gpseg9    7001   Passive   Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   sdw1     /xmmup/opt/greenplum/data/mirror/gpseg10   7002   Passive   Synchronized 20231229:17:47:37:468672 gpstate:mdw:gpadmin-[INFO]:-   sdw1     /xmmup/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，还是相当的大。

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

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章！

验证码：

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

相关文章

• GreenPlum启动报错could not load library /usr/local/greenplum-db-6.27.0/lib/postgresql/pg_cron.so的故障处理
• GreenPlum连接报错Sorry,too many clients already
• GreenPlum严重故障导致不能启库的解决办法
• 初始化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中查询用户表卡住 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
• gprecoverseg恢复GreenPlum的时候报错“gprecoverseg failed(Reason ''ascii' codec can't encode character u'\u2018' in positition 229: ordinal not in range(128)'）exiting”
• pg_restore导入数据报警告 pg_restore: WARNING: column "foobar" has type "unknown" DETAIL: Proceeding with relation creation anyway.
• GreenPlum查询报错failed to initialize zstd stream: Allocation error : not enough memory
• GreenPlum在何时会发生自动故障切换、节点漂移及如何排查（OOM）
• GreenPlum空间满不能启动的处理办法
• GreenPlum报错ERROR: Canceling query because of high VMEM usage.
• GreenPlum登录报错 ERROR: deadlock detected, locking against self

打赏赞(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    146    2
• 0

  Foxmail邮箱客户端使用过程中一直提示“密码错误，请输入您的QQ密码”问题解决方法

  2023年3月25日

   由 小麦苗 · Published 2023年3月25日

   

  0    583    15
• 0

  GreenPlum数据库巡检脚本

  2022年3月8日

   由 小麦苗 · Published 2022年3月8日 · Last modified 2024年2月8日

  · 已更新 2024年2月8日  

  0    482    6