repair table? database? how ? neccessary?

>>>>> "mitchell" == mitchell laks <mlaks(at)bellatlantic(dot)net> writes:

mitchell> The main issue I had with Access was that as the database
mitchell> got larger, My application began to fail regularly, and I
mitchell> began to need to do "repair" of the database almost on a
mitchell> daily or other daily basis.

mitchell> Is it that Postgresql is just so robust that bad tables
mitchell> can't happen? I am a bit of a newbie, so I don't know
mitchell> exactly what it is that i have been doing with "repair" to
mitchell> repair whatever it was that was broken in msft that may not
mitchell> occur here with Postgresql. Sigh. I need more
mitchell> knowledge. What do I read?

I'll preface this with the fact that I havn't had a postgres failure
in 3-ish years, but I'm still paranoid. We run a database of 5-ish
million records in the main table ... and probably 20 million records
in all. Most days see 10k to 100k records change. I have run
databases with much higher rates of change without problems

That all said, my data is important to me and we dump the database to
a flat file hourly. We take that data and use cvs to archive each
hours file. To 'recover' postgres, you drop and re-create the
database and then import the dump file.

Here is my script for dumping the database and archiving it:

(the 'WARNING: $db has 0 length' occurs when you fill your disk)

#! /bin/bash
#
# Backup the velocet database.

if [ "$1" = "-d" ]
then
debug_mode=1
else
debug_mode=0
fi

# Where we want to be.
cd /u/dgilbert/dbbak

# Config to run Postgres
PATH=$PATH:/usr/local/bin
PGLIB=/usr/local/pgsql/lib
PGDATA=/usr/local/pgsql/data

for db in sales trsales velocet keystone; do
pg_dump $db > $db.1
test "$debug_mode" -gt 0 && echo backup up $db
if [ -s $db.1 ]; then
mv $db.1 $db
else
echo "WARNING: $db has 0 length"
fi

cvs commit -m "Automatic Database Backup" $db
done