Re: postgresql.conf archive_command example

On Wed, Sep 7, 2011 at 11:53 PM, Robert Treat <rob(at)xzilla(dot)net> wrote:
> On Tue, Sep 6, 2011 at 10:11 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>> I agree that basically archive_command should not overwrite an existing file.
>> But if the size of existing file is less than 16MB, it should do that.
>> Otherwise,
>> that WAL file would be lost forever.
>
> I think best practice in this case is that if you ever find an
> existing file with the same name already in place, you should error
> and investigate. We don't ship around partially completed WAL files,
> and finding an existing one probably means something went wrong. (Of
> course, we use rsync instead of copy/move, so we have some better
> guarantees about this).

That's an option. But I don't think that finding an existing file is so serious
problem. The most common cases which cause a partially-filled archived
file are;

1. The server crashes while WAL file is being archived, and then the server
restarts. In this case, the restarted server would find partially-filled
archived file.

2. In replication environment, the master crashes while WAL file is being
archived, and then a failover happens. In this case, new master would
find partially-filled archived file.

In these cases, I don't think it's so unsafe to overwrite an existing file.

OTOH, the practice you explained might fill up an archive area and
pg_xlog directory and then cause a PANIC error. Such a PANIC error
is more serious thing at least for me. So I'd like to overwrite an exiting
file when its size is not 16MB.

>> I have another feature request;
>> (5) Maybe not in the initial version, but eventually it might be
>> nice to support calling posix_fadvise(POSIX_FADV_DONTNEED)
>> after copying a WAL file.
>>
>
> Can you go into more details on how you envision this working. I'm
> mostly curious because I think rsync might already support this, which
> would make it easy to incorporate.

I'm expecting that the executable is written in C, it calls posix_fadvice
against the file descriptor created when opening the WAL file in pg_xlog
directory, just before closing that descriptor.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center