Reindex fails with "ERROR: shared table "xxx" can only be reindexed in stand-alone mode"

Products

• VMware Tanzu Greenplum

Issue/Introduction

When attempting to reindex a corrupted catalog index of a shared table with the query, the following error message is outputted:
 

"index "pg_shdepend_reference_index" contains unexpected zero page at block 211".

 

When attempting to reindex a corrupted catalog index of a shared table with the query:

flightdata=# reindex table pg_shdepend; ERROR: shared table "pg_shdepend" can only be reindexed in stand-alone mode

The following error message is outputted: 

ERROR: index "pg_shdepend_reference_index" contains unexpected zero page at block 211 (seg25 sdw7:40001 pid=6728)

Environment

Product Version: 4.3

Resolution

Shared tables are tables that are common between all databases in the cluster and they cannot be reindexed if the database is up and running.

Use the below query to identify the shared tables in the cluster:

select relname from pg_class where relisshared='t'; 

Follow the steps below to reindex a shared table:

Note: The database should be in single sign-on (SSO) mode.

1. Shutdown the database:

gpstop -af 

2. Navigate to the host or segment that has the corrupted index and issue the below command to reindex the shared table:

• For Master, run:

echo 'reindex table pg_shdepend'|$GPHOME/bin/postgres --single -P -O -p <master PORT> -D $MASTER_DATA_DIRECTORY -c gp_session_role=utility template

• For Segment, run:

echo 'reindex table pg_shdepend'|$GPHOME/bin/postgres --single -P -O -p <segment PORT> -D <Segment_data_directory> -c gp_session_role=utility template1 

In the case that you want to perform this operation on all segments, please use the below command and save it as a script. The script uses ssh to login into each segment and executes a reindex.

select $$ssh $$||hostname||$$ " source /usr/local/greenplum-db/greenplum_path.sh ; echo 'reindex table pg_shdepend'|/usr/local/greenplum-db/bin/postgres --single -P -O -p $$|| port||' -D '||fselocation||' -c gp_session_role=utility template1 "' from gp_segment_configuration , pg_filespace_entry where dbid=fsedbid and role='p'; 

IMPORTANT NOTE:
As per R&D confirmation via MPP-30176, we need perform the steps below once reindex is done in stand-alone mode

1. We should always do a full recovery on all mirror instances which corresponding primary has been reindexed in stand-alone mode. 
2. After done reindex in stand-alone mode, when doing gpstart, it has a chance that some of the mirror segments might not able to start.
3. Find out all mirror instance which the corresponding primary has been reindexed in stand-alone mode, if it still online, bring down the mirror via pg_ctl

# ssh [hostname]
# source $GPHOME/greenplum_path.sh
# pg_ctl stop -D [mirror data folders]

4. Wait until all mirror has been marked as down in gpstate -e output
5. Do full recovery again all mirror instance

# gprecoverseg -F

WARNING:

Please do not try incremental recovery (gprecoverseg without -F), although incremental recovery can bring up the mirror but it may not fully copy all the content from primary to mirror. A full recovery is mandatory after reindexing in stand-alone mode.