目前开发环境共有40台左右PostgreSQL数据库服务器，现在将这些数据库服务器所有数据库的所有schema由脚本统一收集并写入MySQL数据库中，可以做个定时任务，每隔一段时间运行一次，实时收集数据
在MySQL数据库中创建元数据表
mysql> desc t_postgres_instance; +-------------------+--------------------------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------------------------------+------+-----+-------------------+-----------------------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | instance_hostname | varchar(100) | NO | | NULL | | | instance_address | varchar(100) | NO | UNI | NULL | | | database_port | smallint(4) | NO | | 5432 | | | database_user | varchar(50) | NO | | dbadmin | | | instance_location | enum('EC2','RDS','SH','US','ALIYUN') | YES | | NULL | | | instance_status | enum('running','stopped') | YES | | NULL | | | database_status | enum('running','stopped') | YES | | NULL | | | monitor_flag | smallint(4) | NO | | 1 | | | create_time | datetime | NO | | CURRENT_TIMESTAMP | | | update_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------------------+--------------------------------------+------+-----+-------------------+-----------------------------+ 11 rows in set (0.04 sec)
插入元数据，如下所示
mysql> select * from t_postgres_instance;
+----+----------------------------------------------------------------------+------------------+---------------+---------------+-------------------+-----------------+-----------------+--------------+---------------------+---------------------+ | id | instance_hostname | instance_address | database_port | database_user | instance_location | instance_status | database_status | monitor_flag | create_time | update_time | +----+----------------------------------------------------------------------+------------------+---------------+---------------+-------------------+-----------------+-----------------+--------------+---------------------+---------------------+ | 1 | sh-poicontentdb-01 | 172.16.100.235 | 5432 | dbadmin | SH | running | running | 1 | 2018-07-13 02:13:36 | 2019-10-27 23:50:01 | | 2 | sh-addressdb-01 | 172.16.100.232 | 5432 | dbadmin | SH | running | running | 1 | 2018-07-13 02:13:36 | 2019-10-27 23:50:01 | | 3 | shcnt-hadoopdn-01 | 172.16.100.212 | 5432 | dbadmin | SH | running | running | 1 | 2018-07-13 02:13:36 | 2019-10-27 23:50:01 | | 4 | sh-denalicnpgsql-01 | 172.16.102.49 | 5432 | dbadmin | SH | running | running | 1 | 2018-07-13 02:13:36 | 2019-05-16 23:50:02 | | 5 | ec2d-geocoderdata-04 | 10.189.101.223 | 5432 | dbadmin | EC2 | running | running | 1 | 2018-07-13 04:32:29 | 2019-05-16 23:50:02 | | 6 | ec2d-geocoderdata-05 | 10.189.103.37 | 5432 | dbadmin | EC2 | running | running | 1 | 2018-07-13 04:32:29 | 2019-11-03 23:50:02 | | 7 | ec2d-poipipeline-tenv-01 | 10.189.100.229 | 5432 | dbadmin | EC2 | stopped | running | 0 | 2018-07-13 04:32:29 | 2019-12-11 23:50:07 | | 8 | ec2d-postgresdb-01 | 10.189.100.232 | 5432 | dbadmin | EC2 | running | running | 1 | 2018-07-13 04:32:29 | 2019-05-16 23:50:02 |
预配置文件
cat ec2t-dbaadmin-01.cfg #!/bin/bash export GET_ENVIRONMENT=Corporation export DATE_SERVER=`date '+%Y-%m-%d %H:%M:%S'` export DATE=`date +%Y%m%d` export DATE_DETAIL=`date '+%Y%m%d%H%M%S'` export MONITOR_IP=10.189.101.160 export MONITOR_DB_CONNECT="-u dbaadmin -p****** -h 10.189.101.160 -P 3306 dba" export SHORT_HOST_NAME=`hostname|awk -F. '{print $1}'` export MYSQL_BASE=/usr/local/mysql export PGHOME=/usr/local/pgsql export PATH=$PGHOME/bin:$MYSQL_BASE/bin:$PATH:/usr/local/bin export MAIL_DBA=****** #export MAIL_DBA=****** export HOST_NAME=`hostname` export IP_ADDRESS=`netstat -rn | grep UG | awk '{print $8}' | /usr/bin/xargs -i /sbin/ifconfig {} | grep "inet" | awk '{print $2}'`
收集脚本，如下所示
#!/bin/bash -x # ########################################################################### # Name: postgres_table_size_collect.sh # Location: /usr/local/mysql/dba/sh # Function: Collect all postgres table size in dev 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.cfg if [ -s ${CONFIG_FILE} ] then . ${CONFIG_FILE} exe_mysql="${MYSQL_HOME}/bin/mysql ${MONITOR_DB_CONNECT}" export PGPASSWORD=agm43gadsg else mail -s "[${GET_ENVIRONMENT} Critical:] $0: There is no configure file ${CONFIG_FILE}. !" ${MAIL_DBA} < /dev/null exit 1 fi #Update instance status in table 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(/ /," ");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 status in table 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(/ /," ");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 databases in each instance for instance_info in ${postgres_instance} do instance_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" ];then database_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 else database_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 size in each database for 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.err if [ -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/null exit 1 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 ' ' (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.err if [ -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/null exit 1 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.err if [ -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/null exit 1 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 in table 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.err if [ $? = 0 ]; then echo "update postgres table size successfully." else echo "update postgres table size failed,please check ${LOG_FILE}." fi #end file
生成数据如下所示
mysql> select * from t_postgres_table_size limit 20; +------+------------------+----------------------+---------------+-------------+------------------------------+------------+------------+------------+------------+---------------------+---------------------+ | id | instance_address | instance_hostname | database_name | schema_name | table_name | table_size | index_size | total_size | table_type | create_time | update_time | +------+------------------+----------------------+---------------+-------------+------------------------------+------------+------------+------------+------------+---------------------+---------------------+ | 1547 | 172.16.102.49 | sh-denalicnpgsql-01 | postgres | public | spatial_ref_sys | 3293184 | 147456 | 3440640 | BASE TABLE | 2018-09-07 19:11:46 | 2018-09-07 19:11:46 | | 1548 | 172.16.102.49 | sh-denalicnpgsql-01 | arp | facts | auto_events | 12312576 | 0 | 12312576 | BASE TABLE | 2018-09-07 19:11:46 | 2018-09-07 19:11:46 | | 1549 | 172.16.102.49 | sh-denalicnpgsql-01 | denali | facts | auto_events | 4221132800 | 0 | 4221132800 | BASE TABLE | 2018-09-07 19:11:46 | 2018-11-22 23:55:29 | | 1550 | 172.16.102.49 | sh-denalicnpgsql-01 | denali | facts | auto_events_bak_20171101 | 783155200 | 0 | 783155200 | BASE TABLE | 2018-09-07 19:11:46 | 2018-09-07 19:11:46 | | 1551 | 172.16.102.49 | sh-denalicnpgsql-01 | denali | facts | auto_events2 | 3055616 | 0 | 3055616 | BASE TABLE | 2018-09-07 19:11:46 | 2018-09-07 19:11:46 | | 1552 | 172.16.102.49 | sh-denalicnpgsql-01 | denali | dimensions | date | 671744 | 163840 | 835584 | BASE TABLE | 2018-09-07 19:11:46 | 2018-09-07 19:11:46 | | 1553 | 172.16.102.49 | sh-denalicnpgsql-01 | denali | public | jsontemp | 638976 | 0 | 638976 | BASE TABLE | 2018-09-07 19:11:46 | 2018-10-23 23:57:23 | | 1554 | 172.16.102.49 | sh-denalicnpgsql-01 | denali | facts | homearea90 | 81920 | 0 | 81920 | BASE TABLE | 2018-09-07 19:11:46 | 2018-09-07 19:11:46 | | 1555 | 172.16.102.49 | sh-denalicnpgsql-01 | denali | facts | homearea99 | 81920 | 0 | 81920 | BASE TABLE | 2018-09-07 19:11:46 | 2018-09-07 19:11:46 | | 1556 | 172.16.102.49 | sh-denalicnpgsql-01 | denali | facts | homearea65 | 81920 | 0 | 81920 | BASE TABLE | 2018-09-07 19:11:46 | 2018-09-07 19:11:46 | | 1557 | 172.16.102.49 | sh-denalicnpgsql-01 | denali | public | denalihomearea | 49152 | 0 | 49152 | BASE TABLE | 2018-09-07 19:11:46 | 2018-09-07 19:11:46 | | 1558 | 172.16.102.49 | sh-denalicnpgsql-01 | denali | public | predictivedestinationcluster | 8192 | 0 | 8192 | BASE TABLE | 2018-09-07 19:11:46 | 2018-09-07 19:11:46 | | 1559 | 172.16.102.49 | sh-denalicnpgsql-01 | denali | facts | homearea99_dbscan | 8192 | 0 | 8192 | BASE TABLE | 2018-09-07 19:11:46 | 2018-09-07 19:11:46 | | 1560 | 172.16.102.49 | sh-denalicnpgsql-01 | denali | public | keydestinationcluster | 8192 | 0 | 8192 | BASE TABLE | 2018-09-07 19:11:46 | 2018-09-07 19:11:46 | | 1561 | 172.16.102.49 | sh-denalicnpgsql-01 | denali | facts | homearea65_dbscan | 8192 | 0 | 8192 | BASE TABLE | 2018-09-07 19:11:46 | 2018-09-07 19:11:46 | | 1562 | 172.16.102.49 | sh-denalicnpgsql-01 | denali | facts | homearea90_dbscan | 8192 | 0 | 8192 | BASE TABLE | 2018-09-07 19:11:46 | 2018-09-07 19:11:46 | | 1563 | 172.16.102.49 | sh-denalicnpgsql-01 | denali | public | denalihomearea_dbscan | 8192 | 0 | 8192 | BASE TABLE | 2018-09-07 19:11:46 | 2018-09-07 19:11:46 | | 1564 | 172.16.102.49 | sh-denalicnpgsql-01 | denali | datascience | clusterwithstartstop | 0 | 0 | 0 | BASE TABLE | 2018-09-07 19:11:46 | 2018-09-07 19:11:46 | | 1565 | 172.16.102.49 | sh-denalicnpgsql-01 | denali | public | mlalgooutput | 0 | 0 | 0 | BASE TABLE | 2018-09-07 19:11:46 | 2018-09-07 19:11:46 | | 1566 | 10.189.101.223 | ec2d-geocoderdata-04 | postgres | public | spatial_ref_sys | 3293184 | 147456 | 3440640 | BASE TABLE | 2018-09-07 19:11:46 | 2018-09-07 19:11:46 | +------+------------------+----------------------+---------------+-------------+------------------------------+------------+------------+------------+------------+---------------------+---------------------+ 20 rows in set (0.00 sec)