On Mon, Feb 14, 2011 at 2:27 PM, Christopher Browne
<cbbrowne at ca.afilias.info> wrote:
> Aleksey Tsalolikhin <atsaloli.tech at gmail.com> writes:
>> Dear Melvin,
>>
>>   Thanks for answering my questions.
>>
>>   My slave has same hardware configuration as the master.
>>
>>   I ran the SQL command you sent, and it reports database size
>> 100 GB on the slave.   Same SQL command reports 58 GB on
>> the master.
>>
>>   I tried VACUUM FULL on the database, but the size remains
>> 100 GB.
>>
>>   The following command tells me that 97 GB is used by my large
>> table and its TOAST and index:
>>
>>    SELECT relname as "Table", pg_size_pretty(pg_total_relation_size
>> (relid))
>>        As "Size" from pg_catalog.pg_statio_user_tables
>>    ORDER BY pg_total_relation_size(relid) DESC;
>>
>>
>>   My disk on the slave is nearly full (only a couple of GB free) so
>> when I get a maintenance window, I will try dropping my large table
>> (which is 45 GB on production) from replication, and then add it back,
>> maybe it will come back smaller?
>>
>>   I would like to understand why it's larger on the slave.
>
> If anything, I'd expect it to be smaller on the replica, because the
> replica won't get any of the
>   BEGIN; UPDATE; oops, something conflicted...  ROLLBACK;
> traffic that would be expected to generate dead space on the "master"
> node.
>
> What I expect is that there was some other problem that caused the
> attempt to populate the big table to fail on the replica, so it has
> filled up with dead tuples.
>
> I'd suggest that you run TRUNCATE against that table, on the replica,
> which should not have any bad side, because that's exactly what Slony is
> going to do as part of the subscription process.
>
> If you clear it out yourself, via TRUNCATE, you can verify that things
> are in good order, and have greater confidence that the subscription
> process will succeed.


Hi.  I dropped my large table from the replication set and then ran TRUNCATE
against that table on the replica.  Disk usage according to "df" dropped from
98% to 96%.  I ran VACUUM FULL on that table but that did not help.
I then shut down PostgreSQL and then started it again, and usage dropped
to 6%.

Then I set up a 2nd replication set consisting of just that one table (since one
can't add a table to an existing replication set) and started
replication.  Let's
see how this goes!

Jan:  I did get notices

   NOTICE:  truncate of "public"."my_big_table" succeeded

on prior replication attempts.

Thanks very much!
Aleksey