说明
该三种方案只是在测试环境操作过，如需生产操作，请自行评估风险
方案一.
utility模式下删除所有的状态为u的segment和正在使用的master上的对应的表
1.拼出语句
select E'PGOPTIONS\=\'-c gp_session_role\=utility\' psql -h '||hostname||' -p '||port||' -Atc "drop table SCHEMA.TABLENAME;" -d databse' from gp_segment_configuration where role='p';
2.执行删除语句
PGOPTIONS='-c gp_session_role=utility' psql -h mdw -p 5432 DATABASE -c "drop table SCHEMA.TABLENAME;"
PGOPTIONS='-c gp_session_role=utility' psql -h sdw1 -p 40000 DATABASE -c "drop table SCHEMA.TABLENAME;"
PGOPTIONS='-c gp_session_role=utility' psql -h sdw1 -p 40001 DATABASE -c "drop table SCHEMA.TABLENAME;"
PGOPTIONS='-c gp_session_role=utility' psql -h sdw2 -p 40000 DATABASE -c "drop table SCHEMA.TABLENAME;"
PGOPTIONS='-c gp_session_role=utility' psql -h sdw2 -p 40001 DATABASE -c "drop table SCHEMA.TABLENAME;"
PGOPTIONS='-c gp_session_role=utility' psql -h sdw3 -p 40000 DATABASE -c "drop table SCHEMA.TABLENAME;"
PGOPTIONS='-c gp_session_role=utility' psql -h sdw3 -p 40001 DATABASE -c "drop table SCHEMA.TABLENAME;"
3.检查(需要在数据库空闲状态下执行)
gpcheckcat -g /home/gpadmin/20161012/ -p 5432 test
方案二.
1.用utility模式从一个正常的segment将对应的表导出,并插入异常的segment中
\copy (select oid,* from pg_type where oid=17727) to '/home/gpadmin/20161012/pg_type.out';
\copy pg_type from '/home/gpadmin/20161012/pg_type.out' with oids;
\copy (select oid,* from pg_class where oid=20751) to '/home/gpadmin/20161012/pg_class.out';
\copy pg_class from '/home/gpadmin/20161012/pg_class.out' with oids;
\copy (select * from pg_attribute where attrelid=17726) to '/home/gpadmin/20161012/pg_attribute.out';
\copy pg_attribute from '/home/gpadmin/20161012/pg_attribute.out';
2.检查(需要在数据库空闲状态下执行)
gpcheckcat -g /home/gpadmin/20161012/ -p 5432 test
方案三.
1.出现persistent问题,可用下面工具修复[pivotal内部工具,建议不得到原厂的支持不要修复]
/usr/local/greenplum-db/sbin/gppersistentrebuild -c 5 -D /home/gpadmin/20161012
2.检查(需要在数据库空闲状态下执行)
gpcheckcat -g /home/gpadmin/20161012/ -p 5432 test
注意：修改字典表风险很大，操作需要谨慎再谨慎