Re: Data files become huge fast

Dario Fumagalli wrote:
> And to think that I perform VACUUM ANALYZE at least weekly!
> The only period I didn't VACUUM the database was during the mentioned 2
> weeks vacation.
>
> The database filled its partition and the backend crashed.
> This morning, when I was told of the malfunction, I tried to restart the
> daemon with:
>
> /etc/rc.d/init.d/postgres start
>
> as always and it blew up.
> Then I tried manually and it told me about a FATAL 2 error (searching on
> the archives revealed me it means "disk full" and from that I understod
> that the database grew too large).
>
> So freed some disk space (52 MB) and the backend finally restarted.
>
> Now, the questions are:
> - How is it possible this exceptional growth (N.B. the database server
> has all the default values set in its configuration files - i.e. was not
> "optimized")?

You are pumping all new data in every week, or so it thinks because you
are updating all those rows and vacuum wasn't run to clean it out.

> - More urgently: why now it takes 30 seconds to perform a select
> count(*) on 900 records (no other clients connected), while in the past
> it was almost instantaneous? All database operations are now slow as
> dogs. And I have to live with this 52 MB until the technician comes with
> a new disk (1 week... he is in vacation now).

I think you just need to run VACUUM FULL (or just VACUUM if you are on <
7.2). That should fix all that.

> - Why do the backend crashed immediately if I try to VACUUM (ANALYZE)
> it? I think the backend claims additional disk space for this operation
> and fills the disk again, but I'm not sure.

Oh, that is unusual. There are cases where running out of disk space
would cause problems but I am not sure. Can you show us some output?

> - And last, but not least... is it possible to restore the situation
> without loosing data (backup is 3 weeks old)? I'm able to start the
> daemon and perform SQL operations, but I don't know how to make
> PostgreSQL release the disk space after I dumped the database in order
> to reload it. And I fear an InitDB will destroy db users and their
> privileges.

pg_dumpall does a full dump with user info.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073