Its version 9.3.5, whats interesting the that the table grew in size after the vacuum full, which I did to try to see why the auto vacuum wasn¹t working. However, after I stopped the PostgreSQL slave instance, then vacuum full did result in a much much smaller size, as expected. So it appears to be that there must be some interaction between a slave that trying to do streaming replication but failing, because the requests WALs have been cycled out and vacuuming on the master. I am not entirely sure that¹s the case, but I think observed it twice. Is it the master can¹t clean up tuples that might be visible at the slave¹s last replayed transaction? I didn¹t think the master was aware of the slave¹s state, and why locks can¹t be coordinated between the master and slave. On 9/22/14, 4:04 PM, "Adrian Klaver" <adrian.klaver@xxxxxxxxxxx> wrote: >On 09/22/2014 01:42 PM, Joel Avni wrote: >> I noticed that tables on my master PostgreSQL server were growing, and >> running vacuum full analyze on them actually made them even bigger. > >First what version of Postgres are you using? > >Second VACUUM FULL is usually not recommended for the reason you found >out and which is documented here: > >http://www.postgresql.org/docs/9.3/interactive/sql-vacuum.html > >FULL > > Selects "full" vacuum, which can reclaim more space, but takes much >longer and exclusively locks the table. This method also requires extra >disk space, since it writes a new copy of the table and doesn't release >the old copy until the operation is complete. Usually this should only >be used when a significant amount of space needs to be reclaimed from >within the table. >" > >> >> At the same time, a slave PostgreSQL server had fallen behind in trying >> to replicate, and was stuck in constantly looping over Œstarted >> streaming WAL from primary atŠ¹ and Œrequested WAL segment Š. has >> already been removed¹. Once I stopped running the slave instance, I was >> able to manually vacuum the tables, and appears that auto vacuum is now >> able to vacuum as well. One table (for instance) dropped from 10Gb down >> to 330Mb after this operation. I don¹t see anything about auto vacuum >> not able to acquire locks while the slave wasn¹t able to replicate. I >> am unclear why a slave trying to continue streaming would block the auto >> vacuum, or is something else at play? > >My guess related to the locks your VACUUM FULL was taking, though it >would require more information on what all the various parts where doing >over the time frame. > >> >> I did check, and no base backups were in progress at the time this >>occurred. >> >> Thank you, >> Joel Avni >> > > >-- >Adrian Klaver >adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
