Re: recovering from "too many failures" wal error

On Mon, Dec 1, 2014 at 10:59 PM, Andy Colson <andy(at)squeakycode(dot)net> wrote:

> We have a postgresql 9.2 cluster setup to do continuous wal archiving.
>> We were archiving to a mount point that went offline. As a result the db
>> could not archive the wal files, we ended up with many many errors in
>> the logs indicating the file could not be archived:
>>
>> WARNING: transaction log file "0000000100000FB100000050" could not be
>> archived: too many failures
>>
>> So we caught the issue before the file system filled up, fixed the mount
>> point and I see wal files being added to the target wal archive
>> directory. However the pg_xlog directory does not seem to be shrinking,
>> there are currently 27,546 files in the pg_xlog directory and that
>> number is not changed in some time (since we fixed the mount point.
>>
>> I assume the db will at some point remove the backed up files in the
>> pg_xlog dir, is this true? or do I need to intervene?
>>
>> Thanks in advance
>>
>>
>>
> From what I recall from this list, you should never play in pg_xlog.
> You'll probably do more damage than good. PG should take care of itself.
>
> ​+1
Don't ever do that​

> Are you still getting error messages? Looks like its been a few days, has
> it shrunk yet?

​if you are still getting error try to force an archival with

set synchronous_commit=off; -- needed only if you are replicating to
synchronous slaves

select pg_switch_xlog(); -- though not neccessary

select pg_start_backup('test');​

select pg_stop_backup();

I use these commands to test if archival is working fine or to force
archival

Best Regards,

*Sameer Kumar | Database Consultant*

*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533

M: *+65 8110 0350* T: +65 6438 3504 | www.ashnik.com

*[image: icons]*

[image: Email patch] <http://www.ashnik.com/>

This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).