analyzeall.py
author Radek Brich <radek.brich@devl.cz>
Thu, 24 Jan 2013 17:11:17 +0100
changeset 58 0bcc13460dae
parent 52 26121a8fe78b
child 83 515fadd3d286
permissions -rwxr-xr-x
PgBrowser: Add functions. PgDiff: Compare functions.

#!/usr/bin/env python3.2
"""
analyzeall

Analyze/vacuum all tables in selected schemas.
See also "VACUUM ANALYZE VERBOSE;" query.
Unlike that, this program skips pg_catalog etc.

"""

from pgtoolkit import pgbrowser, toolbase
from pgtoolkit.highlight import highlight


class AnalyzeAllTool(toolbase.SimpleTool):
    def __init__(self):
        toolbase.SimpleTool.__init__(self, name='analyzeall', desc='Analyze all tables.')
        self.parser.add_argument('-s', dest='schema', nargs='*', help='Schema filter')
        self.parser.add_argument('--vacuum', action='store_true', help='Call VACUUM ANALYZE')
        self.parser.add_argument('--full', action='store_true', help='Call VACUUM FULL ANALYZE')
        self.parser.add_argument('--reindex', action='store_true', help='Call REINDEX TABLE')
        self.target_isolation_level = 'autocommit'
        self.init()

    def main(self):
        browser = pgbrowser.PgBrowser(self.pgm.get_conn('target'))

        query_pattern = 'ANALYZE %s.%s;'
        if self.args.vacuum:
            query_pattern = 'VACUUM ANALYZE %s.%s;'
        if self.args.full:
            query_pattern = 'VACUUM FULL ANALYZE %s.%s;'
        if self.args.reindex:
            query_pattern = 'REINDEX TABLE %s.%s;'

        schema_list = self.args.schema
        if not schema_list:
            schema_list = [schema['name'] for schema in browser.list_schemas() if not schema['system']]

        for schema in schema_list:
            tables = browser.list_tables(schema=schema)
            with self.pgm.cursor('target') as curs:
                for table in tables:
                    query = query_pattern % (schema, table['name'])
                    self.log.info(query)
                    curs.execute(query, [])


tool = AnalyzeAllTool()
tool.main()