Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, Mar 8, 2012 at 1:10 PM, Aleksey Tsalolikhin
<atsaloli.tech@xxxxxxxxx> wrote:
>> On Thu, Mar 8, 2012 at 12:10 PM, Aleksey Tsalolikhin
>> <atsaloli.tech@xxxxxxxxx> wrote:
>>> On Wed, Mar 7, 2012 at 10:05 AM, Aleksey Tsalolikhin
>>> <atsaloli.tech@xxxxxxxxx> 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@xxxxxxxxxxxxxxxx> 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.marlowe@xxxxxxxxx> 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.

If you pg_dump -t tablename from each machine, are the backups about
the same size?

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux