#!/bin/bash -x
# ###########################################################################
# Name: postgres_table_size_collect.sh# Location:/usr/local/mysql/dba/sh# Function: Collect all postgres table sizeindev environment and update them into t_postgres_table_size table.
# Author: ***
# Create Date:07/05/2018#############################################################################
PGHOME=/usr/local/pgsql
MYSQL_HOME=/usr/local/mysql
PATH=$PGHOME/bin:$MYSQL_BASE/bin:$PATH:/usr/local/bin
LOG_PATH=/usr/local/mysql/dba/log
LOG_FILE=/usr/local/mysql/dba/log/postgres_table_size_collect.log
SQL_PATH=/usr/local/mysql/dba/sql
CURRENT_DATE=`date '+%Y-%m-%d %H:%M:%S'`
CONFIG_FILE=$MYSQL_HOME/dba/config/ec2t-dbaadmin-01.cfgif [ -s ${CONFIG_FILE} ]then. ${CONFIG_FILE}
exe_mysql="${MYSQL_HOME}/bin/mysql ${MONITOR_DB_CONNECT}"export PGPASSWORD=agm43gadsgelsemail-s "[${GET_ENVIRONMENT} Critical:] $0: There is no configure file ${CONFIG_FILE}. !" ${MAIL_DBA} < /dev/nullexit1
fi#Update instance statusintable t_postgres_instance
instance_list=`${exe_mysql} -Nse "select instance_address from t_postgres_instance where instance_location <> 'RDS'" 2>/dev/null | awk BEGIN{RS=EOF}'{gsub(/\n/," ");print}'`for i in${instance_list}do
/usr/sbin/fping ${i} > /dev/null
if [ $? = 0 ]; then${exe_mysql}-e "update t_postgres_instance set instance_status='running' where instance_address='${i}';"
else${exe_mysql}-e "update t_postgres_instance set instance_status='stopped' where instance_address='${i}';"
fi
done##Update database service statusintable t_postgres_instance
#alive_instance_list=`${exe_mysql} -Nse "select instance_address from t_postgres_instance where instance_status='running' and instance_location <> 'RDS'" 2>/dev/null | awk BEGIN{RS=EOF}'{gsub(/\n/," ");print}'`
#for i in${instance_list}
#do# $PGHOME/bin/psql -h ${i}
#Clear up temp table t_postgres_table_size_temp
${exe_mysql}--show-warnings -v -v -v -e "truncate t_postgres_table_size_temp;"#Query all postgres instances
postgres_instance=`${exe_mysql} -Nse "select concat(instance_hostname,'::',instance_address,'::',database_port,'::',database_user,'::',instance_location) from t_postgres_instance where monitor_flag=1 and instance_status='running'" 2>/dev/null`
#Get all databasesineach instancefor instance_info in${postgres_instance}doinstance_hostname=`echo ${instance_info} | awk -F:: '{print $1}'`
instance_address=`echo ${instance_info} | awk -F:: '{print $2}'`
database_port=`echo ${instance_info} | awk -F:: '{print $3}'`
database_user=`echo ${instance_info} | awk -F:: '{print $4}'`
instance_location=`echo ${instance_info} | awk -F:: '{print $5}'`if [ ${instance_location} = "RDS" ];thendatabase_info=`$PGHOME/bin/psql -h ${instance_hostname} -U ${database_user} -p ${database_port} postgres -tc "select datname from pg_database where datname not in ('test','template1','template0','template_postgis','rdsadmin');" | grep -v ^$ | sed s/[[:space:]]//g`
if [ $? -ne 0 ]; then
echo "Postgres database service not running on instance ${instance_hostname}."
fi
elsedatabase_info=`$PGHOME/bin/psql -h ${instance_address} -U ${database_user} -p ${database_port} postgres -tc "select datname from pg_database where datname not in ('test','template1','template0','template_postgis');" | grep -v ^$ | sed s/[[:space:]]//g`
if [ $? -ne 0 ]; then
echo "Postgresql service not running on instance ${instance_hostname}."
fi
fi#Get all tables sizeineach databasefor postgres_database in${database_info}do$PGHOME/bin/psql -h ${instance_address} -U ${database_user} -p ${database_port} -d ${postgres_database} -f ${SQL_PATH}/postgres_table_size_collect.sql -o ${LOG_PATH}/${instance_hostname}_${postgres_database}_schema_table_size.txt 2>${LOG_PATH}/${instance_hostname}_${postgres_database}_schema_table_size.errif [ -s "${LOG_PATH}/${instance_hostname}_${postgres_database}_schema_table_size.err" ];then
echo "Get table size information failed in database ${postgres_database} on instance ${instance_hostname},please check ${LOG_FILE}."mail-s "[${GET_ENVIRONMENT} Critical:] Get table size information failed in database ${postgres_database} on instance ${instance_hostname},please check ${LOG_FILE}." ${MAIL_DBA} < /dev/nullexit1
fi
if [ ! -s "${LOG_PATH}/${instance_hostname}_${postgres_database}_schema_table_size.txt" ];then
echo "No tables find in current database ${postgres_database}"
else${exe_mysql}--show-warnings -v -v -v -e "LOAD DATA INFILE '${LOG_PATH}/${instance_hostname}_${postgres_database}_schema_table_size.txt' INTO TABLE t_postgres_table_size_temp FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' (instance_address,database_name,schema_name,table_name,table_size,index_size,total_size,table_type);" 2>${LOG_PATH}/${instance_hostname}_${postgres_database}_schema_table_size.err ; sed -i '1d' ${LOG_PATH}/${instance_hostname}_${postgres_database}_schema_table_size.errif [ -s "${LOG_PATH}/${instance_hostname}_${postgres_database}_schema_table_size.err" ];then
echo "Load table size information into table t_postgres_table_size_temp failed on instance ${SHORT_HOST_NAME},please check ${LOG_FILE}."mail-s "[${GET_ENVIRONMENT} Critical:] Load table size information into table t_postgres_table_size_temp failed on instance ${SHORT_HOST_NAME},please check ${LOG_FILE}." ${MAIL_DBA} < /dev/nullexit1
fi${exe_mysql}--show-warnings -v -v -v -e "update t_postgres_table_size_temp set instance_hostname='${instance_hostname}' where instance_address='${instance_address}' and database_name='${postgres_database}';" 2>${LOG_PATH}/${instance_hostname}_${postgres_database}_schema_table_size.err ; sed -i '1d' ${LOG_PATH}/${instance_hostname}_${postgres_database}_schema_table_size.errif [ -s "${LOG_PATH}/${instance_hostname}_${postgres_database}_schema_table_size.err" ];then
echo "Load table size information into table t_postgres_table_size_temp failed on moniter instance ${SHORT_HOST_NAME},please check ${LOG_FILE}."mail-s "[${GET_ENVIRONMENT} Critical:] Load table size information into table t_postgres_table_size_temp failed on moniter instance ${SHORT_HOST_NAME},please check ${LOG_FILE}." ${MAIL_DBA} < /dev/nullexit1
else
echo "Collect database ${postgres_database} table data on instance ${instance_hostname} finished."
fi
fi
done
done#Compare table t_postgres_table_size_temp with t_postgres_table_size,clear up the tables which have been dropped intable t_postgres_table_size/bin/rm -rf ${LOG_PATH}/last_tables_info.txt/bin/rm -rf ${LOG_PATH}/stale_tables_info.txt
${exe_mysql}-Nse "select concat(instance_hostname,'::',instance_address,'::',database_name,'::',schema_name,'::',table_name) from t_postgres_table_size_temp" > ${LOG_PATH}/last_tables_info.txt
${exe_mysql}-Nse "select concat(instance_hostname,'::',instance_address,'::',database_name,'::',schema_name,'::',table_name) from t_postgres_table_size where instance_hostname not in (select instance_hostname from t_postgres_instance where instance_status='stopped')" > ${LOG_PATH}/stale_tables_info.txt
\cd ${LOG_PATH}/bin/sort stale_tables_info.txt last_tables_info.txt last_tables_info.txt | uniq -u >need_delete.txt
need_delete_count=`cat ${LOG_PATH}/need_delete.txt | wc -l`echo "Find ${need_delete_count} records is stale and will be deleted from t_postgres_table_size."
for need_delete_table in `/bin/cat ${LOG_PATH}/need_delete.txt`do
hostname=`echo ${need_delete_table} | awk -F:: '{print $1}'`
address=`echo ${need_delete_table} | awk -F:: '{print $2}'`
dbname=`echo ${need_delete_table} | awk -F:: '{print $3}'`
schema=`echo ${need_delete_table} | awk -F:: '{print $4}'`
table=`echo ${need_delete_table} | awk -F:: '{print $5}'`
${exe_mysql}--show-warnings -v -v -v -e "delete from t_postgres_table_size where instance_hostname='${hostname}' and instance_address='${address}' and database_name='${dbname}' and schema_name='${schema}' and table_name='${table}';"
done#update the table size information into table t_postgres_table_size
${exe_mysql}--show-warnings -v -v -v -e "insert into t_postgres_table_size(instance_address,instance_hostname,database_name,schema_name,table_name,table_size,index_size,total_size,table_type) select instance_address,instance_hostname,database_name,schema_name,table_name,table_size,index_size,total_size,table_type from t_postgres_table_size_temp ON DUPLICATE KEY UPDATE table_size=values(table_size),index_size=values(index_size),total_size=values(total_size);" 2>${LOG_PATH}/sync_table_size.err ; sed -i '1d' ${LOG_PATH}/sync_table_size.errif [ $? = 0 ]; then
echo "update postgres table size successfully."
else
echo "update postgres table size failed,please check ${LOG_FILE}."
fi#endfile
扫一扫
4521
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
