原 GreenPlum查询状态报错 [WARNING]:-could not query segment

发布日期 2023年12月29日 · 已更新 2024年6月26日  

0    364    3

Tags： 原创故障处理GreenPlum

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

显示/隐藏全文目录

目录

• 现象
• 解决
• 脚本

现象

[gpadmin@mdw ~]$ gpstate -e 20231229:17:05:24:3562260 gpstate:mdw:gpadmin-[INFO]:-Starting gpstate with args: -e 20231229:17:05:24:3562260 gpstate:mdw:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.23.1 build commit:2731a45ecb364317207c560730cf9e2cbf17d7e4 Open Source' 20231229:17:05:24:3562260 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:05:24:3562260 gpstate:mdw:gpadmin-[INFO]:-Obtaining Segment details from master... 20231229:17:05:24:3562260 gpstate:mdw:gpadmin-[INFO]:-Gathering data from segments... . 20231229:17:05:25:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 5 (sdw1:6003) 20231229:17:05:25:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 6 (sdw2:6000) 20231229:17:05:25:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 7 (sdw2:6001) 20231229:17:05:25:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 8 (sdw2:6002) 20231229:17:05:25:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 9 (sdw2:6003) 20231229:17:05:25:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 22 (sdw1:7000) 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 24 (sdw1:7002) 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 25 (sdw1:7003) 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[INFO]:-Segment Mirroring Status Report 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[INFO]:-Segments with Primary and Mirror Roles Switched 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[INFO]:- Current Primary Port Mirror Port 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[INFO]:- sdw1 7000 sdw3 6000 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[INFO]:- sdw1 7001 sdw3 6001 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[INFO]:- sdw1 7002 sdw3 6002 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[INFO]:- sdw1 7003 sdw3 6003 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 5 (sdw1:6003) 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 6 (sdw2:6000) 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 7 (sdw2:6001) 20231229:17:05:27:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 8 (sdw2:6002) 20231229:17:05:27:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 9 (sdw2:6003) 20231229:17:05:27:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 22 (sdw1:7000) 20231229:17:05:27:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 24 (sdw1:7002) 20231229:17:05:27:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 25 (sdw1:7003) 20231229:17:05:27:3562260 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20231229:17:05:27:3562260 gpstate:mdw:gpadmin-[INFO]:-Unsynchronized Segment Pairs 20231229:17:05:27:3562260 gpstate:mdw:gpadmin-[INFO]:- Current Primary Port WAL sync remaining bytes Mirror Port 20231229:17:05:27:3562260 gpstate:mdw:gpadmin-[INFO]:- sdw1 6000 1655701504 sdw2 7000 20231229:17:05:27:3562260 gpstate:mdw:gpadmin-[INFO]:- sdw1 6001 640789118976 sdw2 7001 20231229:17:05:27:3562260 gpstate:mdw:gpadmin-[INFO]:- sdw1 6002 673619509248 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

[gpadmin@mdw ~]$ gpstate -e 20231229:17:05:24:3562260 gpstate:mdw:gpadmin-[INFO]:-Starting gpstate with args: -e 20231229:17:05:24:3562260 gpstate:mdw:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.23.1 build commit:2731a45ecb364317207c560730cf9e2cbf17d7e4 Open Source' 20231229:17:05:24:3562260 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:05:24:3562260 gpstate:mdw:gpadmin-[INFO]:-Obtaining Segment details from master... 20231229:17:05:24:3562260 gpstate:mdw:gpadmin-[INFO]:-Gathering data from segments... . 20231229:17:05:25:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 5 (sdw1:6003) 20231229:17:05:25:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 6 (sdw2:6000) 20231229:17:05:25:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 7 (sdw2:6001) 20231229:17:05:25:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 8 (sdw2:6002) 20231229:17:05:25:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 9 (sdw2:6003) 20231229:17:05:25:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 22 (sdw1:7000) 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 24 (sdw1:7002) 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 25 (sdw1:7003) 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[INFO]:-Segment Mirroring Status Report 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[INFO]:-Segments with Primary and Mirror Roles Switched 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[INFO]:-   Current Primary   Port   Mirror   Port 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[INFO]:-   sdw1              7000   sdw3     6000 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[INFO]:-   sdw1              7001   sdw3     6001 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[INFO]:-   sdw1              7002   sdw3     6002 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[INFO]:-   sdw1              7003   sdw3     6003 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 5 (sdw1:6003) 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 6 (sdw2:6000) 20231229:17:05:26:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 7 (sdw2:6001) 20231229:17:05:27:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 8 (sdw2:6002) 20231229:17:05:27:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 9 (sdw2:6003) 20231229:17:05:27:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 22 (sdw1:7000) 20231229:17:05:27:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 24 (sdw1:7002) 20231229:17:05:27:3562260 gpstate:mdw:gpadmin-[WARNING]:-could not query segment 25 (sdw1:7003) 20231229:17:05:27:3562260 gpstate:mdw:gpadmin-[INFO]:----------------------------------------------------- 20231229:17:05:27:3562260 gpstate:mdw:gpadmin-[INFO]:-Unsynchronized Segment Pairs 20231229:17:05:27:3562260 gpstate:mdw:gpadmin-[INFO]:-   Current Primary   Port   WAL sync remaining bytes   Mirror   Port 20231229:17:05:27:3562260 gpstate:mdw:gpadmin-[INFO]:-   sdw1              6000   1655701504                 sdw2     7000 20231229:17:05:27:3562260 gpstate:mdw:gpadmin-[INFO]:-   sdw1              6001   640789118976               sdw2     7001 20231229:17:05:27:3562260 gpstate:mdw:gpadmin-[INFO]:-   sdw1              6002   673619509248               sdw2     7002 [gpadmin@mdw ~]$

解决

找到报错的实例，例如“[WARNING]:-could not query segment 5 (sdw1:6003)”，然后修改各个segment的pg_hba.conf文件，添加对mdw的trust认证：

host all gpadmin 192.10.25.83/32 trust

1	host all gpadmin 192.10.25.83/32  trust

本人提供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严重故障导致不能启库的解决办法（pg_resetxlog重置日志）
• 初始化GreenPlum数据库实例时报错ssh_exchange_identification: connection reset by peer
• 使用gpmon登陆gpcc报错Error: User gpmon is not allowed to login Command Center.
• 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
• gprecoverseg恢复GreenPlum的时候报错“gprecoverseg failed(Reason ''ascii' codec can't encode character u'\u2018' in positition 229: ordinal not in range(128)'）exiting”
• GreenPlum查询报错failed to initialize zstd stream: Allocation error : not enough memory
• GreenPlum在何时会发生自动故障切换、节点漂移及如何排查（OOM）
• GreenPlum空间满不能启动的处理办法
• GreenPlum报错ERROR: Canceling query because of high VMEM usage 或 ERROR: Out of memory Vmem limit reached
• GreenPlum登录报错 ERROR: deadlock detected, locking against self

打赏赞(3)分享

标签： 原创故障处理GreenPlum

小麦苗

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

• 下一篇 GreenPlum恢复时报错Did not start the following segments due to failure while updating the port的排查过程
• 上一篇 GreenPlum中的primary和mirror之间一直处于Not In Sync状态

您可能还喜欢...

• 0

  PGCA+PGCE+PGCM+OCP+OCM等IT类认证在综合所得年度汇算中的退税操作指南

  2022年3月4日

   由 小麦苗 · Published 2022年3月4日 · Last modified 2023年3月14日

  · 已更新 2023年3月14日  

  0    2502    15
• 0

  Windows的cmd获取年月日时分秒的日期时间

  2024年8月3日

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

  · 已更新 2024年8月5日  

  0    90    1
• 0

  【DB宝29】使用Docker搭建Oracle 11g的DG环境

  2021年6月21日

   由 小麦苗 · Published 2021年6月21日 · Last modified 2022年7月12日

  · 已更新 2022年7月12日  

  0    5007    6