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).

Do you do things like truncate on the master? Cause truncates don't
get replicated in slony.