由于数据库对象（table）太多太大，而且业务比较繁忙，在收集统计对象大小信息的过程中经常会增删改对象，导致数据库报对象不存在的错误，于是写了个脚本用于完成上述功能，并到处到csv文件便于分发相关维护、开发人员。 gp_gather_object_size script #!/us
由于数据库对象（table）太多太大，而且业务比较繁忙，在收集统计对象大小信息的过程中经常会增删改对象，导致数据库报对象不存在的错误，于是写了个脚本用于完成上述功能，并到处到csv文件便于分发相关维护、开发人员。
gp_gather_object_size script
#!/usr/bin/env python# -*- coding: utf-8 -*-## copyright [gtlions lai].# create date:# update date:summarization ahout this script.detail ahout this script class(): summarization about class ... function(): summarization about function ...__authors__ = 'gtlions lai 'import psycopg2import csvdb = psycopg2.connect(dbname=gtlions, user=gpadmin, host=10.1.1.1)# db = psycopg2.connect(dbname=gtlions, user=gpadmin, host=10.1.1.1)# db = psycopg2.connect(dbname=gtlions, user=gpadmin, host=10.1.1.1)cur = db.cursor()cur.execute('select current_database()')current_database = cur.fetchone()f = open(gp_object_size + current_database[0] + .csv, w)writer = csv.writer(f, lineterminator=\n, quoting=csv.quote_nonnumeric)cur.execute( '''select a.schemaname ,a.tablename ,a.tableowner from pg_tables a where a.schemaname not like 'pg_temp%' and a.schemaname not in ('gp_toolkit','information_schema','pg_catalog','gpmg') order by 1,2;''')writer.writerow((schemaname, tablename, tableowner, size-1, size-byte), )for object in cur.fetchall(): objectname = object[0] + '.' + object[1] try: cur.execute( select pg_size_pretty(pg_total_relation_size(' + objectname + ')),pg_total_relation_size(' + objectname + ');) sizeinfo = cur.fetchone() writer.writerow(object + sizeinfo) except psycopg2.programmingerror, e: print ef.close()cur.close()db.commit()db.close()
-e0f-