Large files in main/base

Hello everyone,

We had a problem with one of our servers and had noticed that the
postgres/8.3/main folder had become quite large (>650mb).
PostgreSQL confirmed this via a query but when I queried the size of the
tables I was barely reaching 3mb.

After looking around, I found one file with about 580 mb. From the
contents I have the feeling it is some kind of changelog or similar.

My Questions:
What is it?
How can I reduce it?
How can I prevent it from reaching that size again?

Thanks

Frank

DISCLAIMER:
Unless indicated otherwise, the information contained in this message is pr=
ivileged and confidential, and is intended only for the use of the addresse=
e(s) named above and others who have been specifically authorized to receiv=
e it. If you are not the intended recipient, you are hereby notified that a=
ny dissemination, distribution or copying of this message and/or attachment=
s is strictly prohibited. The company accepts no liability for any damage c=
aused by any virus transmitted by this email. Furthermore, the company does=
not warrant a proper and complete transmission of this information, nor do=
es it accept liability for any delays. If you have received this message in=
error, please contact the sender and delete the message. Thank you.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Fri, Jul 9, 2010 at 7:31 AM, Henry, Frank <frank.henry [at] barco.com> wrote:
> Hello everyone,
>
> We had a problem with one of our servers and had noticed that the
> postgres/8.3/main folder had become quite large (>650mb).
> PostgreSQL confirmed this via a query but when I queried the size of the
> tables I was barely reaching 3mb.
>
> After looking around, I found one file with about 580 mb. From the
> contents I have the feeling it is some kind of changelog or similar.

And it is? So close to telling us.

>
> My Questions:
> What is it?

Why don't you tell us?

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Scott Marlowe <scott.marlowe [at] gmail.com> writes:
> On Fri, Jul 9, 2010 at 7:31 AM, Henry, Frank <frank.henry [at] barco.com> wrote:
>> What is it?

> Why don't you tell us?

The exact name of the file might be useful information, too.

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Fri, 2010-07-09 at 15:31 +0200, Henry, Frank wrote:
> Hello everyone,
>
> We had a problem with one of our servers and had noticed that the
> postgres/8.3/main folder had become quite large (>650mb).
> PostgreSQL confirmed this via a query but when I queried the size of the
> tables I was barely reaching 3mb.
>
> After looking around, I found one file with about 580 mb. From the
> contents I have the feeling it is some kind of changelog or similar.

Name of file?
Version of PostgreSQL?

JD

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Fri, Jul 9, 2010 at 7:31 AM, Henry, Frank <frank.henry [at] barco.com> wrote:
> Hello everyone,
>
> We had a problem with one of our servers and had noticed that the
> postgres/8.3/main folder had become quite large (>650mb).
> PostgreSQL confirmed this via a query but when I queried the size of the
> tables I was barely reaching 3mb.
>
> After looking around, I found one file with about 580 mb. From the
> contents I have the feeling it is some kind of changelog or similar.

pg_clog? If that's growing you've got a possible problem with a long
running query.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

This is a multi-part message in MIME format.

------_=_NextPart_001_01CB201C.F2437015
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

>> What is it?

> Why don't you tell us?

> The exact name of the file might be useful information, too.

It is : postgres/8.3/main/base/16385/2613

Thanks.
Ciao Dirk.

DISCLAIMER:
Unless indicated otherwise, the information contained in this message is =
privileged and confidential, and is intended only for the use of the =
addressee(s) named above and others who have been specifically =
authorized to receive it. If you are not the intended recipient, you are =
hereby notified that any dissemination, distribution or copying of this =
message and/or attachments is strictly prohibited. The company accepts =
no liability for any damage caused by any virus transmitted by this =
email. Furthermore, the company does not warrant a proper and complete =
transmission of this information, nor does it accept liability for any =
delays. If you have received this message in error, please contact the =
sender and delete the message. Thank you.

------_=_NextPart_001_01CB201C.F2437015
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
6.5.7654.12">
<TITLE>Re: Large files in main/base</TITLE>
</HEAD>
<BODY>


>> What is =
it?


> Why don't you =
tell us?


> The exact name =
of the file might be useful information, too.


It is : =
postgres/8.3/main/base/16385/2613


Thanks.

 Ciao =
Dirk.


DISCLAIMER: Unless indicated otherwise, the information =
contained in this message is privileged and confidential, and is =
intended only for the use of the addressee(s) named above and others who =
have been specifically authorized to receive it. If you are not the =
intended recipient, you are hereby notified that any dissemination, =
distribution or copying of this message and/or attachments is strictly =
prohibited. The company accepts no liability for any damage caused by =
any virus transmitted by this email. Furthermore, the company does not =
warrant a proper and complete transmission of this information, nor does =
it accept liability for any delays. If you have received this message in =
error, please contact the sender and delete the message. Thank =
you.</BODY>
</HTML>
------_=_NextPart_001_01CB201C.F2437015--

Le 10/07/2010 12:43, Neuber, Dirk a =E9crit :
>>> What is it?
>
>> Why don't you tell us?
>
>> The exact name of the file might be useful information, too.
>
> It is : postgres/8.3/main/base/16385/2613
>

This is a system catalog, pg_largeobject, which holds binary objects. If
you use Large Objects, no wonder this table could be really big.

--
Guillaume
http://www.postgresql.fr
http://dalibo.com

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Guillaume Lelarge <guillaume [at] lelarge.info> writes:
> Le 10/07/2010 12:43, Neuber, Dirk a écrit :
>>> The exact name of the file might be useful information, too.
>>
>> It is : postgres/8.3/main/base/16385/2613

> This is a system catalog, pg_largeobject, which holds binary objects. If
> you use Large Objects, no wonder this table could be really big.

Also worth noting is that there's no automatic deletion mechanism for
large objects. It could be that the space is being eaten by LOs that
aren't referenced anymore. If so, you should think about applying
contrib/lo and/or contrib/vacuumlo to manage your LOs.

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

> >> It is : postgres/8.3/main/base/16385/2613
>
> > This is a system catalog, pg_largeobject, which holds
> binary objects. If
> > you use Large Objects, no wonder this table could be really big.
>
> Also worth noting is that there's no automatic deletion mechanism for
> large objects. It could be that the space is being eaten by LOs that
> aren't referenced anymore. If so, you should think about applying
> contrib/lo and/or contrib/vacuumlo to manage your LOs.
>

Thanks a lot.
vacuumlo did the job. Indeed there were more the 100.000 orphaned LOs in
pg_largeobject.
We have to check how we can avoid this by using contrib/lo or in general
without using LOs.
One last question. After using vacuumlo the file size of 16385/2613 is
still the same as before.
It seems the content has been removed but the file itself hasn't been
compacted.
Is there an option or tool which can do this as well ?

Ciao Dirk.

DISCLAIMER:
Unless indicated otherwise, the information contained in this message is pr=
ivileged and confidential, and is intended only for the use of the addresse=
e(s) named above and others who have been specifically authorized to receiv=
e it. If you are not the intended recipient, you are hereby notified that a=
ny dissemination, distribution or copying of this message and/or attachment=
s is strictly prohibited. The company accepts no liability for any damage c=
aused by any virus transmitted by this email. Furthermore, the company does=
not warrant a proper and complete transmission of this information, nor do=
es it accept liability for any delays. If you have received this message in=
error, please contact the sender and delete the message. Thank you.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

"Neuber, Dirk" <dirk.neuber [at] barco.com> writes:
> One last question. After using vacuumlo the file size of 16385/2613 is
> still the same as before.
> It seems the content has been removed but the file itself hasn't been
> compacted.
> Is there an option or tool which can do this as well ?

Yeah, you still need to apply VACUUM FULL to bring the physical file
size down. (Ordinary VACUUM mostly just recycles space within the
file --- it can truncate the file if the end pages are empty, but
it won't move data around to make that possible.)

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin