does database shut down cleanly when WAL device fails? - Mailing list pgsql-general

From Brandon Craig Rhodes
Subject does database shut down cleanly when WAL device fails?
Date
Msg-id 87mzrce604.fsf@ten22.rhodesmill.org
Whole thread Raw
Responses Re: does database shut down cleanly when WAL device fails?
Re: does database shut down cleanly when WAL device
List pgsql-general
In planning a Postgresql deployment, our team discovered that we have
different understandings of how the WAL affects database reliability,
and we have not found the Postgresql manual quite explicit enough to
distinguish between the following two theories:

   a) Putting the WAL on a separate device from the database tables
      not only increases efficiency, but reliability as well - because
      as long as one keeps a database backup and a WAL history that
      goes back to the last backup (as described in 22.3 of the 8.0.1
      manual), then one is insulated from losing data from a single
      disk failure:

        - If the drive holding the tables fails, then take the most
          recent backup and bring it up to date using the WAL.

        - If the backup fails, make another backup.

      And the disputed point:

        - If the drive holding the WAL fails, then the database engine
          will shut down cleanly by writing everything in RAM out to
          the real database tables, and no data will be lost.

The counter theory:

   b) Putting the WAL on a separate device from the database tables
      increases efficiency but does not by itself improve reliability.
      Just as, when the WAL and tables are on a single device, a
      failure of that device makes you lose all data written since the
      your last backup, so when the WAL is placed on a separate drive,
      a failure of that drive may result in the loss of any data
      committed but not yet written out to the main database tables.

Essentially, the documentation does not make clear what recovery can
take place - under what guarantees - should the WAL be destroyed in
mid-operation.

Please advise as to which understanding is the correct one.

--
Brandon Craig Rhodes   brandon@rhodesmill.org   http://rhodesmill.org/brandon

pgsql-general by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: Dynamically access to field on a RECORD variable
Next
From: Tom Lane
Date:
Subject: Re: postgresql 8 abort with signal 10