greenplum定期维护脚本

定时杀不活跃连接

每隔30分钟执行一次杀连接操作，杀掉普通用户超30分钟不活跃的连接。

[gpload@mydb ~]$ vi killidle.sh
export PGPASSWORD=Aa123456
source /usr/local/greenplum-db-clients/greenplum_loaders_path.sh
echo "select pg_terminate_backend(pid) from pg_stat_activity WHERE (usename like 'tjbb%' or usename like 'dba_%' escape 'a') and coalesce (date_part ('day',current_timestamp-state_change)*24*60+date_part ('hour',current_timestamp-state_change)*60+date_part ('min',current_timestamp-state_change),0) > 30 and datname='etl' and state = 'idle';" | psql -d etl -U tool -h 10.75.1.1 -p 5432
[gpload@mydb ~]$ sudo chmod +x ~/killidle.sh
[gpload@mydb ~]$ sudo ln -s ~/killidle.sh /usr/bin/killidle
[gpload@mydb ~]$ crontab -e
*/30 * * * * killidle

系统表定时vacuum

每天23点清理系统表。

[gpload@mydb ~]$ vi vacuumgp.sh
#! /bin/bash

source /usr/local/greenplum-db/greenplum_path.sh

USER=tool
HOST=10.75.1.1
DATABASE=etl
export PGPASSWORD=Aa123456

#fetch the names of cata_logs and vacuum analyze them
psql -U $USER -h $HOST -d $DATABASE -tAXc "select n.nspname||'.'||c.relname relname
   from pg_class c, pg_namespace n
  where c.relnamespace=n.oid
    and n.nspname='pg_catalog'
    and relkind='r' and relstorage<>'x'"|while read line    
do
    echo vacuum analyze $line\; 
    psql -U $USER -h $HOST -d $DATABASE -tAXc "vacuum analyze $line"
done
[gpload@mydb ~]$ sudo chmod +x ~/vacuumgp.sh
[gpload@mydb ~]$ sudo ln -s ~/vacuumgp.sh /usr/bin/vacuumgp
[gpload@mydb ~]$ crontab -e
0 23 * * * vacuumgp