Sachi's Greenplum user database maintenance script

posted Oct 16, 2014, 5:23 AM by Sachchida Ojha   [ updated Jul 23, 2018, 12:03 PM ]
Script name: projectdb_maintenance_daily_sachi.sh
Parameter files: 
a) projectdb: parameter files to read list of user database in the maintenance script. (Can be generated dynamically)
[gpadmin@sachi myscripts]$ cat projectdb
sachi

b)tablelist.sql: List of tables for projectdb to be used (vacuum and analyzed) in maintenance script ( generated dynamically)

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/projectdb_maintenance_daily_sachi.sh > $SCRIPTS_LOG/projectdb_maintenance_daily_sachi_ouput_$(date +\%Y\%m\%d_\%H:\%M).log 2>&1 &

Script output
[gpadmin@sachi myscripts]$ ./projectdb_maint_daily.sh 
##############################################################################################
VACUUM and ANALYZE Greenplum user database tables. 
##############################################################################################
TIME_ELAPSED to finish public.abc maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.foo maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.usha maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.usha3 maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sachi maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.test maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_other_months_3_prt_europe maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_other_months_3_prt_asia maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_2_3_prt_usa maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_2_3_prt_asia maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_3_3_prt_usa maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_3_3_prt_asia maintenance of sachi, 00:00:00

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

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

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_4_3_prt_europe maintenance of sachi, 00:00:00

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

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

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_5_3_prt_usa maintenance of sachi, 00:00:00

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

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

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_5_3_prt_other_regions maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_6_3_prt_europe maintenance of sachi, 00:00:00

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

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

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_7_3_prt_usa maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_7_3_prt_asia maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_7_3_prt_other_regions maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_8_3_prt_usa maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_8_3_prt_asia maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_9_3_prt_usa maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_9_3_prt_europe maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_9_3_prt_other_regions maintenance of sachi, 00:00:00

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

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

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_10_3_prt_asia maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_11_3_prt_usa maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_11_3_prt_asia maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_12_3_prt_usa maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_12_3_prt_asia maintenance of sachi, 00:00:00

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

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

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_13_3_prt_europe maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_13_3_prt_asia maintenance of sachi, 00:00:00

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

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_other_months_3_prt_europe maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_other_months_3_prt_other_regions maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_2_3_prt_europe maintenance of sachi, 00:00:00

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

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_3_3_prt_usa maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_3_3_prt_asia maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_4_3_prt_usa maintenance of sachi, 00:00:00

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

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_4_3_prt_other_regions maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_5_3_prt_europe maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_5_3_prt_other_regions maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_6_3_prt_europe maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_6_3_prt_other_regions maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_7_3_prt_europe maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_7_3_prt_other_regions maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_8_3_prt_europe maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_8_3_prt_other_regions maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_9_3_prt_europe maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_9_3_prt_other_regions maintenance of sachi, 00:00:00

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

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_10_3_prt_asia maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_11_3_prt_usa maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_11_3_prt_asia maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_12_3_prt_usa maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_12_3_prt_europe maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_12_3_prt_other_regions maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_13_3_prt_europe maintenance of sachi, 00:00:00

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_13_3_prt_other_regions maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales maintenance of sachi, 00:00:39

TIME_ELAPSED to finish public.sales_1_prt_outlying_years maintenance of sachi, 00:00:20

TIME_ELAPSED to finish public.sales_1_prt_2 maintenance of sachi, 00:00:19

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

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_9 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_10 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_11 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_12 maintenance of sachi, 00:00:02

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_other_months maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_2 maintenance of sachi, 00:00:02

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_4 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_other_months maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_5 maintenance of sachi, 00:00:02

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_7 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_8 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_9 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_10 maintenance of sachi, 00:00:01
TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_11 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_12 maintenance of sachi, 00:00:02

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_other_months maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_2 maintenance of sachi, 00:00:02

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_4 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_other_months maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_5 maintenance of sachi, 00:00:02

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_7 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_8 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_9 maintenance of sachi, 00:00:02

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_11 maintenance of sachi, 00:00:03

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

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_13 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_2 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_3 maintenance of sachi, 00:00:02

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

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_5 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_6 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_7 maintenance of sachi, 00:00:03

TIME_ELAPSED to finish sachi maintenance , 00:03:03


TIME_ELAPSED to finish projectdb_maintenance_daily_sachi maintenance script , 00:03:03
[gpadmin@sachi myscripts]$ 

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

Comments