From: | "Markus Wollny" <Markus(dot)Wollny(at)computec(dot)de> |
---|---|
To: | "pgsql-general" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: More Praise for 7.4RC2 |
Date: | 2003-11-14 09:30:59 |
Message-ID: | 2266D0630E43BB4290742247C891057502B9D33B@dozer.computec.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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";
}
} ?>
From | Date | Subject | |
---|---|---|---|
Next Message | jini us | 2003-11-14 13:17:38 | Re: embedded postgresql + C++ IDE |
Previous Message | Paul Thomas | 2003-11-14 09:05:40 | Re: RHEL |