Sachi's Greenplum user database maintenance script

Post date: Oct 16, 2014 12:23:22 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.