Re: Increasing WAL usage followed by sudden drop

On Mon, Aug 20, 2012 at 1:51 PM, delongboy <sdelong(at)saucontech(dot)com> wrote:
>
> Jeff Janes wrote
>>
>> Maybe there is an easier way, but one thing would be to compile a test
>> server (of the same version as the production) with WAL_DEBUG defined
>> in src/include/pg_config_manual.h, turn on the wal_debug guc, and
>> crank up trace_recovery_messages. Then replay the WAL log files from
>> production through this test server and see what it logs. That
>> requires that you have
>>

Sorry, I got distracted during editing and didn't finish my sentence.
It requires you have a backup to apply the WAL to, and that you have
the entire history of WAL from the when the backup was started, until
the time when the interesting things are happening. That is rather
annoying.

It seems like it shouldn't be all that hard to write a tool to parse
WAL logs in a context-free basis (i.e. without the backup to start
applying them to) and emit some kind of descriptions of the records
and their sizes. But I don't know about such a tool already existing,
and am not able to offer to create one. (And assuming one existed,
keeping it in sync with the main code would be a continuing problem)

>
> This sounds like it may help me see what is going on. However I am not
> finding very much documentation as to how to do this exactly. What I have
> is it seems this has to be set and postgres needs to be re-compiled to
> enable it. Is this true?

Yes. The compilation only needs to happen on the test server,
however, not the production server.

> As that would not really be a viable option right
> now. I am in position to set up a test server and run wal files through it.
> But I am not sure how to accomplish this exactly? Is there somewhere you
> anyone could point me to find documentation on how to do this?

creating the backup, accumulating the logs, and replaying them are described in:

http://www.postgresql.org/docs/9.1/static/continuous-archiving.html

Of course it does not explicitly describe the case of replaying
through a toy system rather than another production system. It
assumes you are replaying through a soon-to-become production server.

I'm not sure how to address that part, other than to have you ask
specific questions.

Cheers,

Jeff