[candlepin] collect sizes of DB tables

Relevant to: #1749

Signed-off-by: Pavel Moravec <pmoravec@redhat.com>

1 files changed, 58 insertions, 0 deletions

diff --git a/sos/plugins/candlepin.py b/sos/plugins/candlepin.py
index a21a1207..243b5f5b 100644
--- a/sos/plugins/candlepin.py
+++ b/sos/plugins/candlepin.py
@@ -9,6 +9,8 @@
 # See the LICENSE file in the source distribution for further information.
 
 from sos.plugins import Plugin, RedHatPlugin
+from pipes import quote
+from re import match
 
 
 class Candlepin(Plugin, RedHatPlugin):
@@ -18,6 +20,33 @@ class Candlepin(Plugin, RedHatPlugin):
     packages = ('candlepin',)
 
     def setup(self):
+        # for external DB, search in /etc/candlepin/candlepin.conf for:
+        # org.quartz.dataSource.myDS.URL=..
+        #
+        # and for DB password, search for
+        # org.quartz.dataSource.myDS.password=..
+        self.dbhost = "localhost"
+        self.dbpasswd = ""
+        cfg_file = "/etc/candlepin/candlepin.conf"
+        try:
+            for line in open(cfg_file).read().splitlines():
+                # skip empty lines and lines with comments
+                if not line or line[0] == '#':
+                    continue
+                if match(r"^\s*org.quartz.dataSource.myDS.URL=\S+", line):
+                    self.dbhost = line.split('=')[1]
+                    # separate hostname from value like
+                    # jdbc:postgresql://localhost:5432/candlepin
+                    self.dbhost = self.dbhost.split('/')[2].split(':')[0]
+                if match(r"^\s*org.quartz.dataSource.myDS.password=\S+", line):
+                    self.dbpasswd = line.split('=')[1]
+        except (IOError, IndexError):
+            # fallback when the cfg file is not accessible or parseable
+            pass
+        # set the password to os.environ when calling psql commands to prevent
+        # printing it in sos logs
+        # we can't set os.environ directly now: other plugins can overwrite it
+        self.env = {"PGPASSWORD": self.dbpasswd}
 
         # Always collect the full active log of these
         self.add_copy_spec([
@@ -36,6 +65,35 @@ class Candlepin(Plugin, RedHatPlugin):
         ])
 
         self.add_cmd_output("du -sh /var/lib/candlepin/*/*")
+        # collect tables sizes, ordered
+        _cmd = self.build_query_cmd("\
+            SELECT schema_name, relname, \
+                   pg_size_pretty(table_size) AS size, table_size \
+            FROM ( \
+              SELECT \
+                pg_catalog.pg_namespace.nspname AS schema_name, \
+                relname, \
+                pg_relation_size(pg_catalog.pg_class.oid) AS table_size \
+              FROM pg_catalog.pg_class \
+              JOIN pg_catalog.pg_namespace \
+                ON relnamespace = pg_catalog.pg_namespace.oid \
+            ) t \
+            WHERE schema_name NOT LIKE 'pg_%' \
+            ORDER BY table_size DESC;")
+        self.add_cmd_output(_cmd, suggest_filename='candlepin_db_tables_sizes',
+                            env=self.env)
+
+    def build_query_cmd(self, query, csv=False):
+        """
+        Builds the command needed to invoke the pgsql query as the postgres
+        user.
+        The query requires significant quoting work to satisfy both the
+        shell and postgres parsing requirements. Note that this will generate
+        a large amount of quoting in sos logs referencing the command being run
+        """
+        csvformat = "-A -F , -X" if csv else ""
+        _dbcmd = "psql -h %s -p 5432 -U candlepin -d candlepin %s -c %s"
+        return _dbcmd % (self.dbhost, csvformat, quote(query))
 
     def postproc(self):
         reg = r"(((.*)(pass|token|secret)(.*))=)(.*)"