Merge.
--- a/.hgignore Wed Jun 01 11:48:07 2011 +0200
+++ b/.hgignore Wed Jul 20 10:51:18 2011 +0200
@@ -1,1 +1,2 @@
.*\.pyc
+pgtoolkit\.conf
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/bigtables.py Wed Jul 20 10:51:18 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()
+
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/common/config.py Wed Jul 20 10:51:18 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()
+
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/common/prettysize.py Wed Jul 20 10:51:18 2011 +0200
@@ -0,0 +1,67 @@
+# -*- coding: utf-8 -*-
+#
+# prettysize - print data sizes in format easily readable by humans
+#
+# Copyright (c) 2011 Radek Brich <radek.brich@devl.cz>
+#
+# 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)
+
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/diff.py Wed Jul 20 10:51:18 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()
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/longqueries.py Wed Jul 20 10:51:18 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()
+
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/pgtoolkit.conf.example Wed Jul 20 10:51:18 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'''
--- a/pgtools/pgmanager.py Wed Jun 01 11:48:07 2011 +0200
+++ b/pgtools/pgmanager.py Wed Jul 20 10:51:18 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()
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/pgtools/pgstats.py Wed Jul 20 10:51:18 2011 +0200
@@ -0,0 +1,49 @@
+# -*- coding: utf-8 -*-
+#
+# PgStats - browse database statistics
+#
+# Copyright (c) 2011 Radek Brich <radek.brich@devl.cz>
+#
+# 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 <> '<IDLE>' AND query_start < now() - interval %s;''',
+ longer_than)
+
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/tests/testprettysize.py Wed Jul 20 10:51:18 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()
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/toolbase.py Wed Jul 20 10:51:18 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)
+