Sachi's greenplum database weekly/daily index maintenance scripts

Post date: Oct 16, 2014 2:18:36 PM

Script name: projectdb_reindex_weekly.sh

Features: a) Fully Customizable b) Read database from a parameter files. can also be generated dynamically c) Displays time to complete single index, all indexes of a db and all indexes for all db in the projectdb. d) creates script output log for future reference

Parameter files:

a) projectdb: parameter files to read list of user database in the maintenance script (list can be generated dynamically).

[gpadmin@sachi myscripts]$ cat projectdb

sachi

b) indexlist.sql: SQL to generate list of indexes for the tables in projectdb. List is generated dynamically and customizable.

c)dir_param: Scripts and log file locations

[gpadmin@sachi myscripts]$ cat dir_param

#########################################################################################################

## Script name: dir_param

## Decription: Script to define directory structure used in Greenplum database maintenance scripts

#######################################################################################################

export SCRIPTS_DIR=/home/gpadmin/maintenance/myscripts

export SCRIPTS_LOG=/home/gpadmin/maintenance/myscripts_log

########################################################################################################

c) Running the script

nohup $SCRIPTS_DIR/gpdb_catalog_maintenance_daily_sachi.sh > $SCRIPTS_LOG/gpdb_catalog_maintenance_daily_sachi_ouput_$(date +\%Y\%m\%d_\%H:\%M).log 2>&1 &

Script output

[gpadmin@sachi myscripts]$ ./projectdb_reindex_weekly.sh

##############################################################################################

VACUUM and ANALYZE Greenplum user database tables.

##############################################################################################

TIME_ELAPSED to finish pg_toast.pg_toast_2606_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_10785_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_10790_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_10795_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_10800_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_10805_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_10775_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_10780_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_3231_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_9903_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_3220_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_2619_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_1255_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_2604_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_25234_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_52289_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_52336_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_52361_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_52386_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_52411_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_60478_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_2618_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_2609_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_78039_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74505_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_74530_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74555_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74580_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74606_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_74632_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74658_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_74683_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74709_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74736_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_74763_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74790_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74816_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_74842_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74869_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74896_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74923_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74949_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74975_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75002_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75029_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75056_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75082_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75108_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75135_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75162_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75189_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75215_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75241_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75268_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75295_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75322_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75348_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75374_index maintenance of sachi database, 00:00:02

TIME_ELAPSED to finish pg_toast.pg_toast_75401_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75428_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75455_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_75481_index maintenance of sachi database, 00:00:02

TIME_ELAPSED to finish pg_toast.pg_toast_75507_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_75534_index maintenance of sachi database, 00:00:02

TIME_ELAPSED to finish pg_toast.pg_toast_75561_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75588_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75614_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75640_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75667_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75694_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75721_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75747_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75773_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75800_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75827_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75854_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75880_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75906_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75933_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75960_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75987_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76013_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76039_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76066_index maintenance of sachi database, 00:00:02

TIME_ELAPSED to finish pg_toast.pg_toast_76093_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76120_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76146_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_76172_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76199_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76226_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76253_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76279_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76305_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76331_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76358_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76385_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76412_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76438_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76465_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76493_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76521_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76549_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76576_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76603_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76631_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76659_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76687_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76714_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76741_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76769_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76797_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76825_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76852_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_76879_index maintenance of sachi database, 00:00:02

TIME_ELAPSED to finish pg_toast.pg_toast_76907_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76935_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76963_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76990_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77017_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77045_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77073_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_78067_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77101_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77929_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77128_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77155_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77183_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77211_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77239_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77266_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77293_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77321_index maintenance of sachi database, 00:00:02

TIME_ELAPSED to finish pg_toast.pg_toast_77349_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77377_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_77404_index maintenance of sachi database, 00:00:02

TIME_ELAPSED to finish pg_toast.pg_toast_77431_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77459_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77487_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77515_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77542_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77569_index maintenance of sachi database, 00:00:02

TIME_ELAPSED to finish pg_toast.pg_toast_77597_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77625_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77653_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77680_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77707_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77735_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77763_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77791_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77818_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77845_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77873_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77901_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77956_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77983_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_78011_index maintenance of sachi database, 00:00:02

TIME_ELAPSED to finish public.abc_id maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish sachi index maintenance , 00:02:26

TIME_ELAPSED to finish projectdb_reindex_weekly maintenance , 00:02:26

[gpadmin@sachi myscripts]$

Email us at gpdba@greenplumdba.com to get this script.