repair table? database? how ? neccessary?

From: mitchell laks <mlaks(at)bellatlantic(dot)net>
To: pgsql-admin(at)postgresql(dot)org
Subject: repair table? database? how ? neccessary?
Date: 2003-01-06 22:21:53
Message-ID: 200301061721.53120.mlaks@bellatlantic.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Senthil 2003-01-07 03:54:06 Re: Stored procedures doubts
Previous Message Ron Mayer 2003-01-06 22:09:47 Re: ANALYZE not working?