Re: repair table? database? how ? neccessary?

On Mon, 2003-01-06 at 17:21, mitchell laks wrote:
> The main issue I had with Access was that as the database got larger, My
> application began to fail regularly, and I began to need to do "repair" of
> the database almost on a daily or other daily basis.
 . . .
> the Postgresql database is currently 5 times the size of the level when I
> began to have m$ft problems
 . . .
> without problems (knock wood) but what do I do if i need to repair?

Fortunately for you, you're now in a different league of database
backends. PostgreSQL won't need to be 'repaired' unless something
catastrophic happens to it (ie: the HDD becomes corrupted, Linux virtual
memory goes wacko, whatever) -- Each of these problems has an extremely
low likelihood of happening.

However, you are a DBA, and I am a DBA, so we must be paranoid and
assume that tomorrow PostgreSQL will corrupt all our data, Linux will
churn it up with /dev/urandom, and your nice Quantum HDD array will be
subjected to some strong electromagnetic radiation.

Take regular backups of your database (can be done while database is
live), store the backups on media that is physically distant from the
main database.

On a regular basis, restore the backup you've made to another machine
and do at least minimal sanity checking. My own method is to restore a
copy of the production database to the developers' development database
so they can use development versions of production software against the
database.

As for your concerns about killing the postmaster, don't "kill -9" the
postmaster, which is a nasty SIGKILL signal which won't let it clean up.
I believe "kill -15" SIGTERM will make the postmaster shut down as
immediately as is safe. The pg_ctl command is a better way to go about
it, however:

$ pg_ctl --help
 . . .
  pg_ctl stop    [-W] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
 . . .
Shutdown modes are:
  smart                 Quit after all clients have disconnected
  fast                  Quit directly, with proper shutdown
  immediate             Quit without complete shutdown; will lead
                        to recovery run on restart

So in the future, try "pg_ctl stop -m fast" or even "-m immediate" if
necessary. I think, without looking into pg_ctl, that shutdown immediate
is a wrapper for a nice smart "kill -15" -- take this with a grain of
salt.

Enjoy,
--
Tim Ellis
Senior Database Architect