Re: More Praise for 7.4RC2 - Mailing list pgsql-general

Hello!

> You can use the oid2name program in the contrib directory to kinda
> research which files are big under those trees and see if
> it's a table or
> index growth problem.

I found it a tedious operation, if you want to keep a check on growth of
your databases regularly. So I wrote a litte script which outputs a
sorted comma separated list of all objects within a database - so I can
do

      ./showdbsize foodb >foodb.csv

and import this thing in Excel for further processing or do whatever I
like with it. There may surely be more elegant ways of getting the task
done using another language or just plain bash-scripting and you have to
have oid2name made and installed, but in terms of language php is what I
am most comfortable with - if deemed necessary, anyone might write their
own little tool in their preferred ways and languages; but it's short
enough, so I'll just post it here if anyone wants to make use of it as
it is.

Kind Regards,

   Markus


Here goes:


        #!/usr/bin/php -q
        <?php
        #     showdbsize for PostgreSQL     #
        #                MWollny - 2003     #
        ######################################
        /*         Config Begin             */

        $pg_user = 'postgres';
        $pg_bindir = '/opt/pgsql/bin/';
        $pg_data ='/var/lib/pgsql/data/base/';

        /*          Config End              */
        ######################################
        /* DO NOT EDIT BELOW THIS LINE      */

        $argv=$_SERVER['argv'];
        $argc=$_SERVER['argc'];
        if ($argc != 2 || in_array($argv[1], array('--help', '-help',
'-h', '-?'))) {
        ?>
        This is a commandline PHP script to generate
        a list of object-ids, names and filesizes of
        all tables/indexes within a specified
        POSTGRESQL-database.

          Usage:
          <?php echo $argv[0]; ?> <database>

          <database> is the name of the database you
          wish to generate the list of.

          With the --list, -list, -l or --show,
          -show or -s options, you can get a list of
          all available databases on this server.

          With the --help, -help, -h,
          or -? options, you can get this help.

        <?php } else {

        /* Function to make bytesize numbers human-readable */
        function fsize($file) {
               $a = array("B", "KB", "MB", "GB", "TB", "PB");

               $pos = 0;
               $size = filesize($file);
               while ($size >= 1024) {
                      $size /= 1024;
                       $pos++;
               }

              return round($size,2)." ".$a[$pos];
        }

        /* One Ring To Find Them All */
        $pg_data=$pg_data.'base/';
        $db_exec=$pg_bindir.'oid2name -U '.$pg_user;
        $alldb=`$db_exec`;
        $i=1;
        $lines = explode ("\n", $alldb);
        foreach($lines as $value) {
         if (!strpos($value, "=")===false) {
          $dboid[$i] = trim(substr($value,0,strpos($value, "=")-1));
          $dbname[$i] = trim(substr(strstr($value,'='),2));
          $i++;
          }}


        if (in_array($argv[1], array('--show', '-show', '-s', '-l',
'--list', '-list'))) {
         echo "Databases available on this server:\n";
         foreach($dbname as $value) {echo "  $value\n";}
         die();
         }

        /* Is argument the name of an existing database on this server?
*/
        if (!in_array ($argv[1], $dbname)) {
            die ("Database $argv[1] not found on this server.\n");
        }

        /* Still alive? Okay, give me the OID of that DB! */
        $i=array_search($argv[1], $dbname);
        $use_oid=$dboid[$i];
        $use_name=$dbname[$i];
        $dbdir=$pg_data.$use_oid.'/';
        chdir ($dbdir);

        /* Let's see the list of files of the DB */
        $handle=opendir($dbdir);
        $i=0;
        while ($file = readdir ($handle)) {
            if ($file != "." && $file != "..") {
                $i++;
                $oid[$i]=$file;
            }
        }
        closedir($handle);

        /* Now gather data about actual names and filesizes of these
objects */
        for ($j = 1; $j <= $i; $j++) {
            if (is_numeric($oid[$j]))  {
            $oid_size[$j]=filesize($oid[$j]);
            $oid_hsize[$j]=fsize($oid[$j]);
            $db_exec=$pg_bindir.'oid2name -U '.$pg_user.' -d
'.$use_name.' -o '.$oid[$j];
            $raw_name=`$db_exec`;
            $full_name[$j]=trim(substr(substr(strstr($raw_name,'='), 1),
0, -1));
            # echo "$oid[$j]; $full_name[$j]; $oid_size[$j];
$oid_hsize[$j] \n";
        }}

        /* Sort and output the list so that it can be piped to a
CSV-file */
        asort ($oid_size);
        reset ($oid_size);
        echo "OID; Name; Size (Bytes); Size (readable)\n";
        foreach($oid_size as $key => $tablesize) {
        echo "$oid[$key]; $full_name[$key]; $oid_size[$key];
$oid_hsize[$key] \n";
        }
        } ?>