Re: Question about Vacuum and Replication failures in 9.3.5

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@aklaver.com