Re: Table space grow big - PostgreSQL

Thanks again for the replies:

Responding to Kevin, Ian, and Inigo:

1. We'll try an change the order in the script
2. Daily vacuum analyze was disabled, which was running from a different script, we'll try to put it back.
3. The monthly script does work.
4. In my original email about the directory that takes more space:

/usr/local/pgsql/data/base directory shows the following where 95186722/ takes a lot of space :

3.6M ./1
3.6M ./16975
51G ./95186722
4.8M ./4830693
51G .

There is nothing else in /usr/local/pgsql/data/base/95186722/ directory except the table space names with different spaces for each one of them. And in other servers these table space sizes are now at 1.0G, for an example there is about 15 of them which makes about 15G.

-rw------- 1 postgres postgres 16K May 5 12:50 219436402
-rw------- 1 postgres postgres 16K May 5 12:50 219436401
-rw------- 1 postgres postgres 16K May 5 12:50 219436400
-rw------- 1 postgres postgres 16K May 5 12:50 219436399
-rw------- 1 postgres postgres 34M May 5 12:50 219436274
-rw------- 1 postgres postgres 42M May 5 12:50 219436273
-rw------- 1 postgres postgres 43M May 5 12:50 219436272
-rw------- 1 postgres postgres 42M May 5 12:50 219436271
-rw------- 1 postgres postgres 34M May 5 12:50 219436270
-rw------- 1 postgres postgres 42M May 5 12:50 219436269
-rw------- 1 postgres postgres 42M May 5 12:50 219436268
-rw------- 1 postgres postgres 51M May 5 12:50 219436267
-rw------- 1 postgres postgres 34M May 5 12:50 219436266
-rw------- 1 postgres postgres 51M May 5 12:50 219436265
-rw------- 1 postgres postgres 15M May 5 12:50 218478745
-rw------- 1 postgres postgres 11M May 5 12:50 218478744
-rw------- 1 postgres postgres 10M May 5 12:50 218478743
-rw------- 1 postgres postgres 13M May 5 12:50 218478742
-rw------- 1 postgres postgres 440M May 5 12:50 216081969.1

-----Original Message-----
From: Kevin Grittner [mailto:Kevin(dot)Grittner(at)wicourts(dot)gov]
Sent: Wednesday, May 05, 2010 3:59 PM
To: pgsql-admin(at)postgresql(dot)org; Khangelani Gama
Subject: Re: [ADMIN] Table space grow big - PostgreSQL

Khangelani Gama <Khangelani(dot)Gama(at)ucs-software(dot)co(dot)za> wrote:

> There is a script that runs once a month

Most likely that should be daily, or at least weekly.

> "REINDEX TABLE ${table}"
> "VACUUM FULL VERBOSE ${table}"
> "VACUUM ANALYZE ${table}"

That's the wrong order. Try:

"VACUUM FULL VERBOSE ANALYZE ${table}"
"REINDEX TABLE ${table}"

With your current order, the VACUUM FULL bloats the indexes you've
just rebuilt.

If you vacuum frequently enough, you should not need to use the FULL
option.

> Dumping and restoring the database doesn't decrease the space

Now, that's odd. You're not restoring back into the same database
without dropping it first (using the "clean" option), are you?
Perhaps you have some very wide indexes, or a very large number of
small tables?

> There is nothing set in the postgresql.conf file that has to do
> with vacuum analyze.

Well, that wasn't the only thing I would look for; however, I'm not
sure how many of the things I usually check exist in 7.3 or work the
same way. :-(

-Kevin

The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of UCS Group and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited. If you are not the intended addressee please notify the writer immediately and destroy the e-mail. UCS Group Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.