gp_gather_object_sizescript

由于数据库对象（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__ = &#39;"Gtlions Lai" <gtlions.l@qq.com>&#39;

import psycopg2
import csv

db = 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(&#39;select current_database()&#39;)
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(
    &#39;&#39;&#39;select a.schemaname ,a.tablename ,a.tableowner from pg_tables a where a.schemaname not like &#39;pg_temp%&#39; and a.schemaname not in (&#39;gp_toolkit&#39;,&#39;information_schema&#39;,&#39;pg_catalog&#39;,&#39;gpmg&#39;) order by 1,2;&#39;&#39;&#39;)
writer.writerow(("schemaname", "tablename", "tableowner", "size-1", "size-byte"), )

for object in cur.fetchall():
    objectname = object[0] + &#39;.&#39; + object[1]
    try:
        cur.execute(
            "select pg_size_pretty(pg_total_relation_size(&#39;" + objectname + "&#39;)),pg_total_relation_size(&#39;" + objectname + "&#39;);")
        sizeinfo = cur.fetchone()
        writer.writerow(object + sizeinfo)
    except psycopg2.ProgrammingError, e:
        print e

f.close()
cur.close()
db.commit()
db.close()

-E0F-