# HG changeset patch # User Radek Brich # Date 1311003577 -7200 # Node ID efee419b7a2d8783e18a6f4c1633aa253f13fd99 # Parent eaae9539e910ac83474c3d80749defca7ff29729 Fix pgmanager exceptions. Add CLI tools: bigtables, longqueries, schemadiff. diff -r eaae9539e910 -r efee419b7a2d .hgignore --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/.hgignore Mon Jul 18 17:39:37 2011 +0200 @@ -0,0 +1,2 @@ +.*\.pyc +pgtoolkit\.conf diff -r eaae9539e910 -r efee419b7a2d bigtables.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/bigtables.py Mon Jul 18 17:39:37 2011 +0200 @@ -0,0 +1,23 @@ +#!/usr/bin/env python3.2 + +from pgtools import pgbrowser +from common import prettysize +from toolbase import ToolBase + + +class BigTablesTool(ToolBase): + def __init__(self): + ToolBase.__init__(self, name='bigtables', desc='List largest tables.') + self.init() + + def main(self): + browser = pgbrowser.PgBrowser(self.pgm.get_conn('target')) + + tables = browser.list_tables() + for table in sorted(tables, reverse=True, key=lambda x: x['size'])[:20]: + print(prettysize.prettysize_short(table['size']).rjust(8), table['name'], sep=' ') + + +tool = BigTablesTool() +tool.main() + diff -r eaae9539e910 -r efee419b7a2d common/config.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/common/config.py Mon Jul 18 17:39:37 2011 +0200 @@ -0,0 +1,44 @@ +import logging + + +class ConfigParser: + def __init__(self): + self.args = {} # config file arguments + self.registered_args = {} + self.log = logging.getLogger('config') + + def add_argument(self, name, type=str, default=None): + self.registered_args[name] = {'type':type, 'default':default} + + def load(self, fname): + # parse config file + with open(fname) as f: + exec(f.read(), self.args) + # check contents + return self.check() + + def check(self): + ok = True + for key in self.args.keys(): + if key == '__builtins__': + continue + if key in self.registered_args: + # arg registered, check type + type = self.registered_args[key]['type'] + if not isinstance(self.args[key], type): + ok = False + self.log.error("Bad type of config parameter '%s': is %s but should be %s", + key, type(self.args[key]), type) + else: + # arg not registered + ok = False + self.log.error("Unknown config parameter '%s'.", key) + return ok + + def __getattr__(self, name): + if name in self.args: + return self.args[name] + + # raise error if not found + raise AttributeError() + diff -r eaae9539e910 -r efee419b7a2d common/prettysize.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/common/prettysize.py Mon Jul 18 17:39:37 2011 +0200 @@ -0,0 +1,67 @@ +# -*- coding: utf-8 -*- +# +# prettysize - print data sizes in format easily readable by humans +# +# Copyright (c) 2011 Radek Brich +# +# Permission is hereby granted, free of charge, to any person obtaining a copy +# of this software and associated documentation files (the "Software"), to deal +# in the Software without restriction, including without limitation the rights +# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell +# copies of the Software, and to permit persons to whom the Software is +# furnished to do so, subject to the following conditions: +# +# The above copyright notice and this permission notice shall be included in +# all copies or substantial portions of the Software. +# +# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR +# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, +# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE +# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER +# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, +# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN +# THE SOFTWARE. + +iec_prefixes = ('Ki', 'Mi', 'Gi', 'Ti', 'Pi', 'Ei', 'Zi', 'Yi') +si_prefixes = 'kMGTPEZY' +big_prefixes = 'KMGTPEZY' + + +def prettysize(num, base=1024, prefixes=si_prefixes, unit='B', sep=' '): + '''Convert data size in bytes to human readable aproximation. + + Beware that default output is invalid by recent standardization. + Still, this form was in use for ages. + + For standardized output, see prettysize_si and prettysize_iec. + + ''' + prefixes = list(prefixes) + prefix = '' + num = float(num) + while True: + if num < base: + # format number + out = '{:.2f}'.format(num) + # cut off trailing zeros + out = out.rstrip('0') + if out.endswith('.'): + out = out[:-1] + return out + sep + prefix + unit + num /= base + prefix = prefixes.pop(0) + + raise OverflowError('Number is too large.') + + +def prettysize_si(num, **kw): + return prettysize(num, base=1000, **kw) + + +def prettysize_short(num): + return prettysize(num, unit='', sep='', prefixes=big_prefixes) + + +def prettysize_iec(num): + return prettysize(num, prefixes=iec_prefixes) + diff -r eaae9539e910 -r efee419b7a2d diff.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/diff.py Mon Jul 18 17:39:37 2011 +0200 @@ -0,0 +1,50 @@ +#!/usr/bin/env python3.2 + +import sys +import argparse + +from pgtools import pgmanager, pgbrowser, pgdiff +from common import config + +parser = argparse.ArgumentParser(description='Database schema diff.') +parser.add_argument('source', metavar='source', type=str, help='Source database') +parser.add_argument('destination', metavar='destination', type=str, help='Destination database') +parser.add_argument('-s', dest='schema', nargs='*', help='Schema filter') +parser.add_argument('-t', dest='table', nargs='*', help='Table filter') +args = parser.parse_args() + +config = config.ConfigParser() +config.add_argument('databases', type=dict) +config.add_argument('meta_db') +config.add_argument('meta_query') +config.load('pgtoolkit.conf') + +pgm = pgmanager.get_instance() + +pgm.create_conn(name='meta', dns=config.meta_db) + +def build_conn(name, targetname): + with pgm.cursor('meta') as curs: + curs.execute(config.meta_query, [targetname]) + row = curs.fetchone_dict() + curs.connection.commit() + + pgm.create_conn(name=name, **row) + +build_conn('src', args.source) +build_conn('dst', args.destination) + +pgm.close_conn('meta') + +srcbrowser = pgbrowser.PgBrowser(pgm.get_conn('src')) +dstbrowser = pgbrowser.PgBrowser(pgm.get_conn('dst')) + +pgd = pgdiff.PgDiff(srcbrowser, dstbrowser) + +if args.schema: + pgd.filter_schemas(include=args.schema) + +if args.table: + pgd.filter_tables(include=args.table) + +pgd.print_diff() diff -r eaae9539e910 -r efee419b7a2d gtkbrowser.py diff -r eaae9539e910 -r efee419b7a2d longqueries.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/longqueries.py Mon Jul 18 17:39:37 2011 +0200 @@ -0,0 +1,22 @@ +#!/usr/bin/env python3.2 + +from pgtools import pgstats +from toolbase import ToolBase + + +class LongQueriesTool(ToolBase): + def __init__(self): + ToolBase.__init__(self, name='longqueries', desc='Lis long queries.') + self.init() + + def main(self): + stats = pgstats.PgStats(self.pgm.get_conn('target')) + + for ln in stats.list_long_queries(): + print(ln['query']) + print(' ', 'query_start:', ln['query_start'].strftime('%F %T')) + + +tool = LongQueriesTool() +tool.main() + diff -r eaae9539e910 -r efee419b7a2d pgbrowser-gtk.py diff -r eaae9539e910 -r efee419b7a2d pgtoolkit.conf.example --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pgtoolkit.conf.example Mon Jul 18 17:39:37 2011 +0200 @@ -0,0 +1,10 @@ +### named connections +databases = { + 'test' : 'host=127.0.0.1 user=test password=test dbname=test' +} + +### meta database +# connection string (dsn) to meta db +meta_db = 'host=10.8.0.1 dbname=central' +# query in meta db, input is database name (will be placed instead of %s), output is values for create_conn +meta_query = '''SELECT host, port, dbname, user, password FROM config.databases WHERE name = %s LIMIT 1''' diff -r eaae9539e910 -r efee419b7a2d pgtools/pgmanager.py --- a/pgtools/pgmanager.py Thu May 26 18:09:05 2011 +0200 +++ b/pgtools/pgmanager.py Mon Jul 18 17:39:37 2011 +0200 @@ -151,7 +151,7 @@ def create_conn(self, name='default', isolation_level=None, dsn=None, **kw): '''Create named connection.''' if name in self.conn_known: - raise PgManagerError('Connection name "%s" already registered.', name) + raise PgManagerError('Connection name "%s" already registered.' % name) if dsn is None: dsn = ' '.join([x[0]+'='+str(x[1]) for x in kw.items()]) @@ -179,7 +179,7 @@ ''' if not name in self.conn_known: - raise PgManagerError('Connection name "%s" not registered.', name) + raise PgManagerError('Connection name "%s" not registered.' % name) self.close_conn(name) @@ -191,7 +191,7 @@ self.lock.acquire() try: if not name in self.conn_known: - raise PgManagerError("Connection name '%s' not registered.", name) + raise PgManagerError("Connection name '%s' not registered." % name) conn = None while len(self.conn_pool[name]) and conn is None: @@ -222,7 +222,7 @@ self.lock.acquire() try: if not name in self.conn_known: - raise PgManagerError("Connection name '%s' not registered.", name) + raise PgManagerError("Connection name '%s' not registered." % name) if len(self.conn_pool[name]) >= self.conn_known[name].keep_open: conn.close() diff -r eaae9539e910 -r efee419b7a2d pgtools/pgstats.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pgtools/pgstats.py Mon Jul 18 17:39:37 2011 +0200 @@ -0,0 +1,49 @@ +# -*- coding: utf-8 -*- +# +# PgStats - browse database statistics +# +# Copyright (c) 2011 Radek Brich +# +# Permission is hereby granted, free of charge, to any person obtaining a copy +# of this software and associated documentation files (the "Software"), to deal +# in the Software without restriction, including without limitation the rights +# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell +# copies of the Software, and to permit persons to whom the Software is +# furnished to do so, subject to the following conditions: +# +# The above copyright notice and this permission notice shall be included in +# all copies or substantial portions of the Software. +# +# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR +# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, +# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE +# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER +# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, +# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN +# THE SOFTWARE. + + +class PgStats: + def __init__(self, conn=None): + self.conn = conn + + def setconn(self, conn=None): + self.conn = conn + + def _query(self, query, *args): + try: + curs = self.conn.cursor() + curs.execute(query, args) + curs.connection.commit() + rows = curs.fetchall() + return [dict(zip([desc[0] for desc in curs.description], row)) for row in rows] + finally: + curs.close() + + def list_long_queries(self, longer_than='1m'): + return self._query('''SELECT datname, procpid, usename, current_query AS query, + waiting, xact_start, query_start, backend_start + FROM pg_stat_activity + WHERE current_query <> '' AND query_start < now() - interval %s;''', + longer_than) + diff -r eaae9539e910 -r efee419b7a2d tests/testprettysize.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/tests/testprettysize.py Mon Jul 18 17:39:37 2011 +0200 @@ -0,0 +1,26 @@ +#!/usr/bin/env python3 + +import unittest +import prettysize + +class TestHumansize(unittest.TestCase): + def test_humansize(self): + psize = prettysize.prettysize_short + expected = ( + (0, '0'), + (1000, '1000'), + (1024, '1K'), + (1050, '1.03K'), + (2050, '2K'), + (333333, '325.52K'), + (1048576, '1M'), + (1050000, '1M'), + (5555555, '5.3M'), + (1073741824, '1G'), + ) + for input, result in expected: + self.assertEqual(psize(input), result) + + +if __name__ == '__main__': + unittest.main() diff -r eaae9539e910 -r efee419b7a2d toolbase.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/toolbase.py Mon Jul 18 17:39:37 2011 +0200 @@ -0,0 +1,38 @@ +import argparse + +from pgtools import pgmanager +from common import config + + +class ToolBase: + def __init__(self, name, desc): + self.parser = argparse.ArgumentParser(description=desc) + self.parser.add_argument('target', metavar='target', type=str, help='Target database') + + self.config = config.ConfigParser() + self.config.add_argument('databases', type=dict) + self.config.add_argument('meta_db') + self.config.add_argument('meta_query') + + self.pgm = pgmanager.get_instance() + + def init(self): + self.config.load('pgtoolkit.conf') + self.args = self.parser.parse_args() + + if self.config.meta_db: + self.pgm.create_conn(name='meta', dsn=self.config.meta_db) + self.buildconn('target', self.args.target) + self.pgm.close_conn('meta') + + def buildconn(self, name, targetname): + with self.pgm.cursor('meta') as curs: + curs.execute(self.config.meta_query, [targetname]) + row = curs.fetchone_dict() + curs.connection.commit() + + if not row: + raise Exception('Unknown database "%s"' % targetname) + + self.pgm.create_conn(name=name, **row) +