Re: More Praise for 7.4RC2

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";
}
} ?>