Re: repair table? database? how ? neccessary? - Mailing list pgsql-admin

[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