Re: [RFC] What should we do for reliable WAL archiving?

On Fri, Mar 21, 2014 at 01:16:08PM -0700, Jeff Janes wrote:
> On Sun, Mar 16, 2014 at 3:23 AM, MauMau <maumau307@gmail.com> wrote:
> 
>     Hello,
> 
>     The PostgreSQL documentation describes cp (on UNIX/Linux) or copy (on
>     Windows) as an example for archive_command.  However, cp/copy does not sync
>     the copied data to disk.  As a result, the completed WAL segments would be
>     lost in the following sequence:
> 
>     1. A WAL segment fills up.
> 
>     2. The archiver process archives the just filled WAL segment using
>     archive_command.  That is, cp/copy reads the WAL segment file from pg_xlog/
>     and writes to the archive area.  At this point, the WAL file is not
>     persisted to the archive area yet, because cp/copy doesn't sync the writes.
> 
>     3. The checkpoint processing removes the WAL segment file from pg_xlog/.
> 
> 
> Note that it takes two checkpoints for this to happen, at least as currently
> coded.
> 
> Also, if the system crashed badly enough to need media recovery, rather than
> just automatic crash recovery, some lost transactions are expected.  Although
> this could silently break your PITR chain, of a crash happened and automatic
> recover used the copy in pg_xlog (which of course was synced) , while copy in
> the archive was not synced.

That is one good reason to keep checkpoint_warning=30, so the typical
file system sync that happens every 30 seconds warns that those files
might not on permanent storage.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +