Recovery from hard drive failure ... the hard way ...

Well all, I just spent a bit of time trying to figure out how to recover a
database where the tables appear to be intact with postgres in 'single
user mode', and came up with a quick and dirty that might not be totally
complete, but might help someone else in a similar situation ...

----------------------
#!/usr/bin/perl

$table = $ARGV[0];

while(<STDIN>) {
if(length($fields) > 0 && /\s+----/) {
print "INSERT INTO $table ( $fields ) VALUES ( $values );\n";
$fields = "";
$values = "";
}
if(/\s+\d: (\w+) = "(.+)"/) {
if(length($fields) > 0) { $fields .= ","; }
$fields .= $1;
if(length($values) > 0) { $values .= ","; }
if(/typeid = 23/) {
$values .= $2;
} else {
$values .= "'" . $2 . "'";
}
}
}
----------------------

To run it, use:

echo "SELECT * FROM <table>;" | \
~/bin/postgres -O -P -D/home/staff/scrappy/recovery/sales.org <database> | \
../convert.pl <table>

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org