Gp_gather_object_size script, zerosizeobject

Gp_gather_object_size script, zerosizeobject

Because the database object (table) is too large and the business is busy, during the process of collecting statistics object size information, objects are often deleted and modified, resulting in the database reports that the object does not exist, therefore, a script is written to complete the above functions and to distribute csv files everywhere for maintenance and developers.

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-