Re: A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?

> On Thu, Mar 8, 2012 at 12:10 PM, Aleksey Tsalolikhin
> <atsaloli(dot)tech(at)gmail(dot)com> wrote:
>> On Wed, Mar 7, 2012 at 10:05 AM, Aleksey Tsalolikhin
>> <atsaloli(dot)tech(at)gmail(dot)com> wrote:
>>>  We're replicating a PostgreSQL 8.4.x database using Slony1-1.2.x
>>>
>>>
>>>  My biggest table measures 154 GB on the origin, and 533 GB on
>>>  the slave.
>>>
>>>  Why is my slave bigger than my master?  How can I compact it, please?
>>
>>
>> On Wed, Mar 7, 2012 at 11:54 PM, Stuart Bishop
>> <stuart(at)stuartbishop(dot)net> wrote back:
>>>
>>> Do you have a long running transaction on the slave? vacuum will not
>>> reuse space that was freed after the longest running transaction.
>>>
>>> You need to use the CLUSTER command to compact it, or VACUUM FULL
>>> followed by a REINDEX if you don't have enough disk space to run
>>> CLUSTER. And neither of these will do anything if the space is still
>>> live because some old transaction might still need to access the old
>>> tuples.
>>
>> Dear Stuart,
>>
>>  We do not run any transactions on the slave besides we pg_dump the
>> entire database every 3 hours.  I don't have enough disk space to CLUSTER
>> the table; I ran VACUUM FULL yesterday, and I just fired up a REINDEX
>> TABLE.
>>
>>  I'd love to get some insight into how much logical data I have versus how
>> much physical space it is taking up.  Is there some admin tool or command
>> or query that will report that?  For each table (and index), I'd like
>> to know how
>> much data is in that object (logical data size) and how much space it is taking
>> up on disk (physical data size).

On Thu, Mar 8, 2012 at 11:58 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> Do you do things like truncate on the master?  Cause truncates don't
> get replicated in slony.

Dear Scott,

No, we do not truncate this table on the master. We only add to it.

The REINDEX FULL completed and the table is still swollen.

Yours,
Aleksey