Thread: repair table? database? how ? neccessary?

Hi. I have suffered for the last few years with M$ft access as the database
backbone for an application that I have been running. After scouring the
internet, I have switched the application to running on Linux, and using
Postgresql.

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. I had a choice of moving
to Mysql or Postgresql. I currently have 2 machines using MySql and 5 using
Postgresql. Now I am still in the early stages of useage, and belatedly it
occured to me that I don't know how to 'repair' a postgresql database.

Now I have been religiously running Vacuumdb --analyze (nightly) at 130am, and
the Postgresql database is currently 5 times the size of the level when I
began to have m$ft problems (i know that by the number of records that are
being stored) {by the way how do you find the size of the database itself?
where is it? somewhere in /var/lib/pgsql/data on my redhat7.3 system?)
without problems (knock wood) but what do I do if i need to repair? I dont
see anything in the three booksi bought (Momjian, Stinson,Worsley/Drake). I
see that Mysql has such facilities (myisamchk etc). Is Vacuum doing that
stuff already? What "stuff" is that stuff anyway? Currently about 500,000
entries in some tables.

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

Also I had an occurence. My application seems to be a connection hog. I didn't
know - and left the max_connections = 64. Well I blew past  that the other
day and My application bombed. I got a postgresql error:

 Fatal 1: Too many clients (or perhaps - was it - not enough clients??).

So I tried to go back to the beginning and I killed the application (But not
the postman - I keep seeing in Tom Lanes responses to people a little line on
the bottom - tip: dont kill the postman, but no reference as to why not - so
I didn't! - i may be ignorant but i'm no dummy!). Then I logged in as the
postgres user and I did
pg_ctl stop. Well It didnt. Well I did pg_ctl stop again. And it didnt. Well
then I waited for 5 minutes and did it again. And it didnt. But Tom Lane
really scared me so I DIDN"T KILL THE POSTMAN (but I really wanted to, not in
cold blood you understand, but I had all these people screaming that they
WANTED THEIR APPLICATION, but I locked the door and went to lunch, and a
while later the postman died on his own (ie the demon stopped)). Then I
started up the postman again and the application and it WORKED fine. Also I
upped the client max to 256.

So what was going on in the background when I said pg_ctl stop - usually I get
a pretty immediate shutdown of the postgresl demon? and what would have
happened if instead of waiting for the postman to die a natural death, I had
shutdown -h now the PC?Would I have damaged the Postgresql
installation/database? Would it actually have been ok anyway, with both the
postmaster and application starting at boot as usual? Ie: What do I do next
time?

Thanks millions for having this forum!

Mitchell

[Sorry to all for the large amount of quoted text]

> Now I have been religiously running Vacuumdb --analyze (nightly) at
> 130am, and
> the Postgresql database is currently 5 times the size of the level
> when I
> began to have m$ft problems (i know that by the number of records that
> are
> being stored) {by the way how do you find the size of the database
> itself?
> where is it? somewhere in /var/lib/pgsql/data on my redhat7.3 system?)
> without problems (knock wood) but what do I do if i need to repair? I
> dont
> see anything in the three booksi bought (Momjian,
> Stinson,Worsley/Drake). I
> see that Mysql has such facilities (myisamchk etc). Is Vacuum doing
> that
> stuff already? What "stuff" is that stuff anyway? Currently about
> 500,000
> entries in some tables.

What exactly do you mean by "repair"?  If you mean cleaning out deleted
tuples and such, then that's what VACUUM is for.

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

You shouldn't have a problem with tables being corrupted by Postgresql.
  It's pretty good about not breaking itself.

> Also I had an occurence. My application seems to be a connection hog.
> I didn't
> know - and left the max_connections = 64. Well I blew past  that the
> other
> day and My application bombed. I got a postgresql error:
>
>  Fatal 1: Too many clients (or perhaps - was it - not enough
> clients??).

Too many clients, not enough max connections.

> So I tried to go back to the beginning and I killed the application
> (But not
> the postman - I keep seeing in Tom Lanes responses to people a little
> line on
> the bottom - tip: dont kill the postman, but no reference as to why
> not - so
> I didn't! - i may be ignorant but i'm no dummy!). Then I logged in as
> the
> postgres user and I did
> pg_ctl stop. Well It didnt. Well I did pg_ctl stop again. And it
> didnt. Well
> then I waited for 5 minutes and did it again. And it didnt. But Tom
> Lane
> really scared me so I DIDN"T KILL THE POSTMAN (but I really wanted to,
> not in
> cold blood you understand, but I had all these people screaming that
> they
> WANTED THEIR APPLICATION, but I locked the door and went to lunch, and
> a
> while later the postman died on his own (ie the demon stopped)). Then I
> started up the postman again and the application and it WORKED fine.
> Also I
> upped the client max to 256.
>
> So what was going on in the background when I said pg_ctl stop -
> usually I get
> a pretty immediate shutdown of the postgresl demon? and what would have
> happened if instead of waiting for the postman to die a natural death,
> I had
> shutdown -h now the PC?Would I have damaged the Postgresql
> installation/database? Would it actually have been ok anyway, with
> both the
> postmaster and application starting at boot as usual? Ie: What do I do
> next
> time?

Unless you specify otherwise, pg_ctl defaults to a "Smart" shutdown
where it waits for all clients to disconnect before shutting down the
postmaster.  That's probably why it took so long.  Please read the man
page for pg_ctl.  It has lots of useful information, including other
shutdown modes.

If you shutdown the system without stopping Postgresql first, it will
complain the next time you start it up, but shouldn't do any real
damage.


> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Heh.

--Jeremy

>>>>> "mitchell" == mitchell laks <mlaks@bellatlantic.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

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