gp_gather_object_size script，zerosizeobject

gp_gather_object_size script，zerosizeobject

由於資料庫物件（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" <gtlions.l@qq.com>'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-