Search Postgresql Archives

does database shut down cleanly when WAL device fails?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx   http://rhodesmill.org/brandon


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux