Re: Table space grow big - PostgreSQL

In your original email I think you showed a directory that held
something like 50Gb of files with a listing of that directory that
added up to something much smaller.  If that is right - what else is
in that directory?

Does your monthly vacuum script work?


--
Ian.


On Wed, May 5, 2010 at 2:42 PM, Khangelani Gama
<Khangelani.Gama@ucs-software.co.za> wrote:
> Many Thanks for all the replies.
>
>
> The conversion project to version 8 is still in progress, hence we are still experiencing problems on a version
that'snot supported. That's an honest answer I can give. The thing is it's frustrating to not to have a source of
supportas we still have to give support on this version 7.3.4 
>
>
> There is a script that runs once a month which reindex, vacuum full verborse and vacuum analyze :
>
> Small picture inside the script:
>
>        echo "VACUUMING TABLE ${table} " >>$log 2>>$log
>        /usr/local/pgsql/bin/psql -U $dbuser -h localhost ${db} -tc "REINDEX TABLE ${table}" >>$log 2>>$log
>        /usr/local/pgsql/bin/psql -U $dbuser -h localhost ${db} -tc "VACUUM FULL VERBOSE ${table}" >>$log  2>>$log
>        /usr/local/pgsql/bin/psql -U $dbuser -h localhost ${db} -tc "VACUUM ANALYZE ${table}" >>$log  2>>$log
>
>
> Dumping and restoring the database doesn't decrease the space
>
>
>
> There is nothing set in the postgresql.conf file that has to do with vacuum analyze.
>
> If we could have a way to reduce the space in /usr/local/pgsql/data/base/95186722 directory we would be sorted
>
>
>
>
>
>
>
>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Brett Parker
> Sent: Wednesday, May 05, 2010 1:42 PM
> To: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Table space grow big - PostgreSQL
>
> On 05 May 13:36, Khangelani Gama wrote:
>> Hi all
>>
>>
>> Please assist on this scenario, I am a junior DBA, perhaps the
>> question I have is too simple please bear with me.
>>
>> I have a server with one PostgreSQL database and the data reside in
>> /usr/local/pgsql/data/ running on Redhat 9 O/S.
>
> <snippage class="lots" />
>
>> The main question I have is: What makes the
>> /usr/local/pgsql/data/base/95186722/  grow so big while the actual
>> data with its schema is only 2.8G and that is there a way to reduce
>> the table space sizes or anything related to that?
>
> OK - what version of postgres would be a good starting question...
>
> But, at a guess, I'd say you have a fair amount of table churn, and that
> you're probably due running a VACUUM FULL over the database that is
> using the most space, it may fail, but if it does it should tell you
> what bit of config you'll need to change to make it work.
>
> The other possible usage of lots of space is indexes on the database
> tables, these are not included in the dump, and so the size of the dump
> doesn't accurately reflect the size of the on disc data.
>
> Hope that gives you somewhere to start from.
>
> Thanks,
> --
> Brett Parker
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
> The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential
informationof UCS Group and/or its subsidiaries.  Any review, use or dissemination thereof by anyone other than the
intendedaddressee is prohibited.  If you are not the intended addressee please notify the writer immediately and
destroythe e-mail.  UCS Group Limited and its subsidiaries distance themselves from and accept no liability for
unauthoriseduse of their e-mail facilities or e-mails sent other than strictly for business purposes. 
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>