Re: Question about Vacuum and Replication failures in 9.3.5

It 9.3.5 and I did the manual vacuum to try to see where the problem might
be.

On 9/22/14, 4:04 PM, "Adrian Klaver" <adrian(dot)klaver(at)aklaver(dot)com> 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(dot)klaver(at)aklaver(dot)com