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(at)gmail(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +