pg_dbsizes.py

import urlparse
import argparse
import psycopg2


class Bunch:
    def __init__(self, **kwds):
        self.__dict__.update(kwds)


def psycopg_args(db_url):
    db = urlparse.urlparse(db_url)
    if db.scheme not in ['postgres', 'psql', 'pgsql']:
        raise RuntimeError(('Couldn\'t parse postgres connection {!r},'
                            ' unexpected scheme {!r}'), db_url, db.scheme)
    return Bunch(database=db.path[1:],
                 user=db.username,
                 password=db.password,
                 host=db.hostname,
                 port=db.port)


if __name__ == '__main__':
    parser = argparse.ArgumentParser()
    parser.add_argument('dburl')
    parser.add_argument('--limit', type=int, default=100)
    args = parser.parse_args()
    db = psycopg_args(args.dburl)
    limit = args.limit * 1024 * 1024
    conn = psycopg2.connect(**db.__dict__)
    cur = conn.cursor()
    query = ('select pg_size_pretty(pg_relation_size(C.oid)) AS pretty_size, relname '
             'from pg_class C where pg_relation_size(C.oid) > {} '
             'order by pg_relation_size(C.oid) desc').format(limit)
    cur.execute(query)
    for row in cur.fetchall():
        print row