analyzeall.py
author Radek Brich <radek.brich@devl.cz>
Fri, 25 Jan 2013 17:44:49 +0100
changeset 60 bb6b20106ff5
parent 52 26121a8fe78b
child 83 515fadd3d286
permissions -rwxr-xr-x
PgDiff: Update patch for table column changed.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     1
#!/usr/bin/env python3.2
30
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     2
"""
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     3
analyzeall
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     4
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     5
Analyze/vacuum all tables in selected schemas.
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     6
See also "VACUUM ANALYZE VERBOSE;" query.
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     7
Unlike that, this program skips pg_catalog etc.
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     8
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
     9
"""
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    10
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    11
from pgtoolkit import pgbrowser, toolbase
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    12
from pgtoolkit.highlight import highlight
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    13
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    14
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    15
class AnalyzeAllTool(toolbase.SimpleTool):
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    16
    def __init__(self):
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    17
        toolbase.SimpleTool.__init__(self, name='analyzeall', desc='Analyze all tables.')
28
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    18
        self.parser.add_argument('-s', dest='schema', nargs='*', help='Schema filter')
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    19
        self.parser.add_argument('--vacuum', action='store_true', help='Call VACUUM ANALYZE')
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    20
        self.parser.add_argument('--full', action='store_true', help='Call VACUUM FULL ANALYZE')
52
26121a8fe78b Update analyzeall tool: add REINDEX option. Add ibrowser tool (useful for PgBrowser testing). Fix PgBrowser.list_columns default value.
Radek Brich <radek.brich@devl.cz>
parents: 30
diff changeset
    21
        self.parser.add_argument('--reindex', action='store_true', help='Call REINDEX TABLE')
30
a8b7cd92f39f Fix analyzeall tool. Add user output formating to batchquery tool. Add isolation_level setting support to ToolBase.
Radek Brich <radek.brich@devl.cz>
parents: 28
diff changeset
    22
        self.target_isolation_level = 'autocommit'
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    23
        self.init()
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    24
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    25
    def main(self):
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    26
        browser = pgbrowser.PgBrowser(self.pgm.get_conn('target'))
52
26121a8fe78b Update analyzeall tool: add REINDEX option. Add ibrowser tool (useful for PgBrowser testing). Fix PgBrowser.list_columns default value.
Radek Brich <radek.brich@devl.cz>
parents: 30
diff changeset
    27
28
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    28
        query_pattern = 'ANALYZE %s.%s;'
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    29
        if self.args.vacuum:
28
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    30
            query_pattern = 'VACUUM ANALYZE %s.%s;'
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    31
        if self.args.full:
28
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    32
            query_pattern = 'VACUUM FULL ANALYZE %s.%s;'
52
26121a8fe78b Update analyzeall tool: add REINDEX option. Add ibrowser tool (useful for PgBrowser testing). Fix PgBrowser.list_columns default value.
Radek Brich <radek.brich@devl.cz>
parents: 30
diff changeset
    33
        if self.args.reindex:
26121a8fe78b Update analyzeall tool: add REINDEX option. Add ibrowser tool (useful for PgBrowser testing). Fix PgBrowser.list_columns default value.
Radek Brich <radek.brich@devl.cz>
parents: 30
diff changeset
    34
            query_pattern = 'REINDEX TABLE %s.%s;'
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    35
28
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    36
        schema_list = self.args.schema
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    37
        if not schema_list:
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    38
            schema_list = [schema['name'] for schema in browser.list_schemas() if not schema['system']]
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    39
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    40
        for schema in schema_list:
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    41
            tables = browser.list_tables(schema=schema)
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    42
            with self.pgm.cursor('target') as curs:
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    43
                for table in tables:
28
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    44
                    query = query_pattern % (schema, table['name'])
52
26121a8fe78b Update analyzeall tool: add REINDEX option. Add ibrowser tool (useful for PgBrowser testing). Fix PgBrowser.list_columns default value.
Radek Brich <radek.brich@devl.cz>
parents: 30
diff changeset
    45
                    self.log.info(query)
28
27fc0504663d analyzeall: analyze tables from all schemas by default
Radek Brich <radek.brich@devl.cz>
parents: 27
diff changeset
    46
                    curs.execute(query, [])
27
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    47
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    48
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    49
tool = AnalyzeAllTool()
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    50
tool.main()
5fb4883604d6 Add analyzeall tool. Updates, fixes.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    51