Pivotal Knowledge Base

Follow

Greenplum Magic Tool (GPMT) gpcheckcat -pgclass_extra Utility

Environment

Product Version
Pivotal Greenplum (GPDB)  All Versions
OS  All supported OS

Purpose

"gpmt gpcheckcat" is a tool to help users with analyzing gpcheckcat log file. The "-pgclass_extra" action is designed to help with dropping the extra/orphan objects.

This tool should be run on the master server on the target database as it connects to the database to run the query.

NOTE: Before executing the generated script, review the commands generated in the output file. The tool has no way of determining whether a table is a master partitioned table and if so can drop all table partitions.

Procedure

./gpmt gpcheckcat -pgclass_extra -d <database-name>

 

Details:

"-pgclass_extra" Action

The tool has two input modes:

  • gpcheckcat log file - parse gpcheckcat log file, find the SUMMARY REPORT section, extract information about the extra tables and generate script with DROP TABLE statements
  • gpmt gpcheckcat -split output database directory - find the "summary_report" file, extract information about the extra tables and generate script with DROP TABLE statements

Parameters:

  • -infile <filename> - use gpcheckcat log file as input
  • -indir <dirname> - use "gpmt gpcheckcat -split" output database directory as input 
  • -outdir <dirname> - output directory (if not specified generates output in a directory with default generated name, including timestamp for uniqueness)

 

Examples:

  1. gpmt gpcheckcat with input from pre-existing -split output directory:
$ ls -l | grep split
drwxr-xr-x  4 lpetrov  staff      136 May 13 14:56 gpcheckcat-split-gpcheckcat_synthetic_1.log-20160513-145619
$ 
$ ls -l gpcheckcat-split-gpcheckcat_synthetic_1.log-20160513-145619/
total 0
drwxr-xr-x  4 lpetrov  staff  136 May 13 14:56 dst-20160202:20:08:40
drwxr-xr-x  4 lpetrov  staff  136 May 13 14:56 flightdata-20160415:10:52:50
$
$ ls -l gpcheckcat-split-gpcheckcat_synthetic_1.log-20160513-145619/dst-20160202\:20\:08\:40/
total 16
-rw-r--r--  1 lpetrov  staff   565 May 13 14:56 duplicate
-rw-r--r--  1 lpetrov  staff  2673 May 13 14:56 summary_report
$ 
$

 

  • Execute the "gpmt gpcheckcat -pgclass_extra" with the top level "-split" output directory - wrong!
$ ./gpmt gpcheckcat -pgclass_extra -indir gpcheckcat-split-gpcheckcat_synthetic_1.log-20160513-145619/
ERROR:Tool gpcheckcat Failed
open gpcheckcat-split-gpcheckcat_synthetic_1.log-20160513-145619/summary_report: no such file or directory
$

 

  • Execute the "gpmt gpcheckcat -pgclass_extra" with the database level "-split" output directory - correct!
$ ./gpmt gpcheckcat -pgclass_extra -indir gpcheckcat-split-gpcheckcat_synthetic_1.log-20160513-145619/dst-20160202\:20\:08\:40/
Output written in directory gpcheckcat-pgclass_extra-20160606-115508/
Done.
$

 

  • Review the output:
$ ls -l gpcheckcat-pgclass_extra-20160606-115508/
total 0
drwxr-xr-x  3 lpetrov  staff  102 Jun  6 11:55 dst-20160202:20:08:40
$ 
$ ls -l gpcheckcat-pgclass_extra-20160606-115508/dst-20160202\:20\:08\:40/
total 8
-rw-r--r--  1 lpetrov  staff  231 Jun  6 11:55 master.sh
$ 
$ cat gpcheckcat-pgclass_extra-20160606-115508/dst-20160202\:20\:08\:40/master.sh 
#!/bin/sh
PGOPTIONS='-c gp_session_role=utility' psql -a -h mdw.gphd.local -p 5432 dst <<EOF
DROP TABLE IF EXISTS pg_temp_400545._madlib_kmeans_state CASCADE;
DROP TABLE IF EXISTS pg_temp_400545._madlib_kmeanspp_state CASCADE;
EOF
$



 2. gpmt gpcheckcat with input from gpcheckcat output file:

$ ./gpmt gpcheckcat -pgclass_extra -infile /tmp/gpcheckcat_synthetic_1.log -outdir /tmp/pgclass_extra_output
Output written in directory /tmp/pgclass_extra_output/
Done.
$

 

  • Review output:
$ ls -l /tmp/pgclass_extra_output/
total 0
drwxr-xr-x  3 lpetrov  wheel  102 Jun  6 12:02 dst-20160202:20:08:40
drwxr-xr-x  5 lpetrov  wheel  170 Jun  6 12:02 flightdata-20160415:10:52:50
$ 
$ ls -l /tmp/pgclass_extra_output/dst-20160202\:20\:08\:40/
total 8
-rw-r--r--  1 lpetrov  wheel  231 Jun  6 12:02 master.sh
$ 
$ cat /tmp/pgclass_extra_output/dst-20160202\:20\:08\:40/master.sh 
#!/bin/sh
PGOPTIONS='-c gp_session_role=utility' psql -a -h mdw.gphd.local -p 5432 dst <<EOF
DROP TABLE IF EXISTS pg_temp_400545._madlib_kmeans_state CASCADE;
DROP TABLE IF EXISTS pg_temp_400545._madlib_kmeanspp_state CASCADE;
EOF
$

 

$ ls -l /tmp/pgclass_extra_output/flightdata-20160415\:10\:52\:50/
total 24
-rw-r--r--  1 lpetrov  wheel  193 Jun  6 12:02 seg5.sh
-rw-r--r--  1 lpetrov  wheel  193 Jun  6 12:02 seg7.sh
-rw-r--r--  1 lpetrov  wheel  193 Jun  6 12:02 seg8.sh
$

$ cat /tmp/pgclass_extra_output/flightdata-20160415\:10\:52\:50/seg8.sh 
#!/bin/sh
PGOPTIONS='-c gp_session_role=utility' psql -a -h sdw1.gphd.local -p 1032 flightdata <<EOF
DROP TABLE IF EXISTS pg_temp_593989.__madlib_temp_2398219_1420725800_9237032__ CASCADE;
EOF
$ 



Additional Information

Here are the Related Tools: GPMT (Greenplum Magic Tool)

Version History

Tool Version Points of Contact Revision Date Upgrade or Modification details
GPMT gpcheckcat log -pgclass_extra Utility 1.0.0 Lubomir Petrov March 13, 2016  First Version created
       

Comments

Powered by Zendesk