Re: PostgreSQL WAL file issue

Hi Tom,

As you pointed out what is bugging us is that the "future" WAL files
have timestamps
older than any of the past or current ones. If this is the normal behavior
on the PostgreSQL side, then I believe we should focus on the barman side
to understand why duplicate files are created under "errors" directory
during the backup. I am sure barman handles correctly the existence of
archive_status files and "future" WAL files have timestamps older than any
of the past or current ones but we have done something wrong in the past
which caused this issue.

I had been reading PostgreSQL documentation and WAL file internals for the
last two weeks and I was stuck. Many thanks for the clarification.

Regards,
Gokhan.

On Thu, Nov 9, 2017 at 5:59 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Gokhan Demir <demirgokhan(at)gmail(dot)com> writes:
> > The issue I believe on PostgreSQL server is that server is creating WAL
> > files whose names already exists on previous dates. The question I want
> to
> > ask is: How is this possible and what can be do to fix it?
>
> I don't know anything about barman, but the state of your server's pg_xlog
> directory looks perfectly normal to me. The active WAL file is evidently
> 000000010000000C0000007C, and the files before it have been successfully
> archived, as indicated by the matching archive_status/*.done files.
> There are also 17 "future" WAL files, 7D through 8D, waiting to be used.
> (17 seems a bit high given that your consumption rate is only a couple
> per day; I'd consider backing down the relevant configuration setting.
> But it's not totally unreasonable.)
>
> What's bugging you, I gather, is that the "future" WAL files have
> timestamps older than any of the past or current ones. That's normal
> in a server that's been up for awhile, because "future" files are made
> by renaming old ones that are no longer needed. The alternative is to
> drop them and fill new ones, which would just create extra disk traffic,
> so we don't.
>
> Archiving solutions should always pay attention to the existence of
> archive_status files, not to the apparent file modification dates.
> I'm sure barman gets that right, so I don't know what's causing
> your actual problem.
>
> regards, tom lane
>